13MyBatis

MyBatis

ORMapping: Object Relationship Mapping 对象关系映射 对象指⾯向对象 关系指关系型数据库 Java 到 MySQL 的映射，开发者可以以⾯向对象的思想来管理数据库。

如何使用

• 新建 Maven ⼯程，pom.xml

<dependencies>
 <dependency>
 <groupId>org.mybatis</groupId>
 <artifactId>mybatis</artifactId>
 <version>3.4.5</version>
 </dependency>
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>8.0.11</version>
 </dependency>
 <dependency>
 <groupId>org.projectlombok</groupId>
 <artifactId>lombok</artifactId>
 <version>1.18.6</version>
 <scope>provided</scope>
 </dependency>
</dependencies>
<build>
 <resources>
 <resource>
 <directory>src/main/java</directory>
 <includes>
 <include>**/*.xml</include>
 </includes>
 </resource>
 </resources>
</build>

• 新建数据库

  use mybatis;
  create table t_account(
  id int primary key auto_increment,
  username varchar(11),
  password varchar(11),
  age int
  )

• 新建数据表对应的实体类 Account

  package com.southwind.entity;
  import lombok.Data;
  @Data
  public class Account {
  private long id;
  private String username;
  private String password;
  private int age;
  }

• 创建 MyBatis 的配置⽂件 config.xml，⽂件名可⾃定义

  <?xml version="1.0" encoding="UTF-8"?>
  <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
  <configuration>
  <!-- 配置MyBatis运⾏环境 -->
  <environments default="development">
  <environment id="development">
  <!-- 配置JDBC事务管理 -->
  <transactionManager type="JDBC"></transactionManager>
  <!-- POOLED配置JDBC数据源连接池 -->
  <dataSource type="POOLED">
  <property name="driver" value="com.mysql.cj.jdbc.Driver">
  </property>
  <property name="url"
  value="jdbc:mysql://localhost:3306/mybatis?
  useUnicode=true&amp;characterEncoding=UTF-8"></property>
  <property name="username" value="root"></property>
  <property name="password" value="root"></property>
  </dataSource>
  </environment>
  </environments>
  </configuration>

使⽤原⽣接⼝

MyBatis 框架需要开发者⾃定义 SQL 语句，写在 Mapper.xml ⽂件中，实际开发中，会为每个实体 类创建对应的 Mapper.xml ，定义管理该对象数据的 SQL。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.southwind.mapper.AccoutMapper">
 <insert id="save" parameterType="com.southwind.entity.Account">
 insert into t_account(username,password,age) values(#{username},#
{password},#{age})
 </insert>
</mapper>

namespace 通常设置为⽂件所在包+⽂件名的形式。

标签内容

insert 标签表示执⾏添加操作。

select 标签表示执⾏查询操作。

update 标签表示执⾏更新操作。

delete 标签表示执⾏删除操作。

id 是实际调⽤ MyBatis ⽅法时需要⽤到的参数。

parameterType 是调⽤对应⽅法时参数的数据类型。

自定义接口

package com.southwind.repository;
import com.southwind.entity.Account;
import java.util.List;
public interface AccountRepository {
 public int save(Account account);
 public int update(Account account);
 public int deleteById(long id);
 public List<Account> findAll();
 public Account findById(long id);
}

创建接⼝对应的 Mapper.xml，定义接⼝⽅法对应的 SQL 语句。

statement 标签可根据 SQL 执⾏的业务选择 insert、delete、update、select。 MyBatis 框架会根据规则⾃动创建接⼝实现类的代理对象。

规则：

Mapper.xml 中 namespace 为接⼝的全类名。

Mapper.xml 中 statement 的 id 为接⼝中对应的⽅法名。 Mapper.xml 中 statement 的 parameterType 和接⼝中对应⽅法的参数类型⼀致。

Mapper.xml 中 statement 的 resultType 和接⼝中对应⽅法的返回值类型⼀致。

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.southwind.repository.AccountRepository">
 <insert id="save" parameterType="com.southwind.entity.Account">
 insert into t_account(username,password,age) values(#{username},#
{password},#{age})
 </insert>
 <update id="update" parameterType="com.southwind.entity.Account">
 update t_account set username = #{username},password = #{password},age
= #{age} where id = #{id}
 </update>
 <delete id="deleteById" parameterType="long">
 delete from t_account where id = #{id}
 </delete>
 <select id="findAll" resultType="com.southwind.entity.Account">
 select * from t_account
 </select>
 <select id="findById" parameterType="long"
resultType="com.southwind.entity.Account">
 select * from t_account where id = #{id}
 </select>
</mapper>

在 config.xml 中注册 AccountRepository.xml

调⽤接⼝的代理对象完成相关的业务操作

Mapper.xml

statement 标签：select、update、delete、insert 分别对应查询、修改、删除、添加操作。

parameterType：参数数据类型

1.基本数据类型，通过 id 查询 Account

<select id="findById" parameterType="long"
resultType="com.southwind.entity.Account">
 select * from t_account where id = #{id}
</select>

2.String 类型，通过 name 查询 Account

<select id="findByName" parameterType="java.lang.String"
resultType="com.southwind.entity.Account">
 select * from t_account where username = #{username}
</select>

3.包装类，通过 id 查询 Account

<select id="findById2" parameterType="java.lang.Long"
resultType="com.southwind.entity.Account">
 select * from t_account where id = #{id}
</select>

4.多个参数，通过 name 和 age 查询 Account

<select id="findByNameAndAge" resultType="com.southwind.entity.Account">
 select * from t_account where username = #{arg0} and age = #{arg1}
</select>

5.Java Bean

<update id="update" parameterType="com.southwind.entity.Account">
 update t_account set username = #{username},password = #{password},age =
#{age} where id = #{id}
</update>

resultType：结果类型

1.基本数据类型，统计 Account 总数

<select id="count" resultType="int">
 select count(id) from t_account
</select>

2.包装类，统计 Account 总数

<select id="count2" resultType="java.lang.Integer">
 select count(id) from t_account
</select>

3.String 类型，通过 id 查询 Account 的 name

<select id="findNameById" resultType="java.lang.String">
 select username from t_account where id = #{id}
</select>

4.Java Bean

<select id="findById" parameterType="long"
resultType="com.southwind.entity.Account">
 select * from t_account where id = #{id}
</select>

及联查询

一对多

Student

package com.southwind.entity;
import lombok.Data;
@Data
public class Student {
 private long id;
 private String name;
 private Classes classes;
}

Classes

package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Classes {
 private long id;
 private String name;
 private List<Student> students;
}

StudentRepository

package com.southwind.repository;
import com.southwind.entity.Student;
public interface StudentRepository {
 public Student findById(long id);
}

StudentRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.southwind.repository.StudentRepository">
 <resultMap id="studentMap" type="com.southwind.entity.Student">
 <id column="id" property="id"></id>
 <result column="name" property="name"></result>
 <association property="classes" javaType="com.southwind.entity.Classes">
 <id column="cid" property="id"></id>
 <result column="cname" property="name"></result>
 </association>
 </resultMap>
 <select id="findById" parameterType="long" resultMap="studentMap">
 select s.id,s.name,c.id as cid,c.name as cname from student s,classes c
where s.id = #{id} and s.cid = c.id
 </select>
</mapper>

ClassesRepository

package com.southwind.repository;
import com.southwind.entity.Classes;
public interface ClassesRepository {
 public Classes findById(long id);
}

ClassesRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.southwind.repository.ClassesRepository">
 <resultMap id="classesMap" type="com.southwind.entity.Classes">
 <id column="cid" property="id"></id>
 <result column="cname" property="name"></result>
 <collection property="students" ofType="com.southwind.entity.Student">
 <id column="id" property="id"/>
 <result column="name" property="name"/>
 </collection>
 </resultMap>
 <select id="findById" parameterType="long" resultMap="classesMap">
 select s.id,s.name,c.id as cid,c.name as cname from student s,classes c
where c.id = #{id} and s.cid = c.id
 </select>
</mapper>

多对多

Customer

package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Customer {
 private long id;
 private String name;
 private List<Goods> goods;
}

Goods

package com.southwind.entity;
import lombok.Data;
import java.util.List;
@Data
public class Goods {
 private long id;
 private String name;
 private List<Customer> customers;
}

CustomerRepository

package com.southwind.repository;
import com.southwind.entity.Customer;
public interface CustomerRepository {
 public Customer findById(long id);
}

CustomerRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.southwind.repository.CustomerRepository">
 <resultMap id="customerMap" type="com.southwind.entity.Customer">
 <id column="cid" property="id"></id>
 <result column="cname" property="name"></result>
 <collection property="goods" ofType="com.southwind.entity.Goods">
 <id column="gid" property="id"/>
 <result column="gname" property="name"/>
 </collection>
 </resultMap>
 <select id="findById" parameterType="long" resultMap="customerMap">
 select c.id cid,c.name cname,g.id gid,g.name gname from customer c,goods
g,customer_goods cg where c.id = #{id} and cg.cid = c.id and cg.gid = g.id
 </select>
</mapper>

GoodsRepository

package com.southwind.repository;
import com.southwind.entity.Goods;
public interface GoodsRepository {
 public Goods findById(long id);
}

GoodsRepository.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.southwind.repository.GoodsRepository">
 <resultMap id="goodsMap" type="com.southwind.entity.Goods">
 <id column="gid" property="id"></id>
 <result column="gname" property="name"></result>
 <collection property="customers" ofType="com.southwind.entity.Customer">
 <id column="cid" property="id"/>
 <result column="cname" property="name"/>
 </collection>
 </resultMap>
 <select id="findById" parameterType="long" resultMap="goodsMap">
 select c.id cid,c.name cname,g.id gid,g.name gname from customer c,goods
g,customer_goods cg where g.id = #{id} and cg.cid = c.id and cg.gid = g.id
 </select>
</mapper>

MyBatis 动态 SQL

使⽤动态 SQL 可简化代码的开发，减少开发者的⼯作量，程序可以⾃动根据业务参数来决定 SQL 的组 成。

if标签

if 标签可以⾃动根据表达式的结果来决定是否将对应的语句添加到 SQL 中，如果条件不成⽴则不添加， 如果条件成⽴则添加。

<select id="findByAccount" parameterType="com.southwind.entity.Account"
resultType="com.southwind.entity.Account">
 select * from t_account where
<if test="id!=0">
 id = #{id}
 </if>
 <if test="username!=null">
 and username = #{username}
 </if>
 <if test="password!=null">
 and password = #{password}
 </if>
 <if test="age!=0">
 and age = #{age}
 </if>
</select>

where 标签

where 标签可以⾃动判断是否要删除语句块中的 and 关键字，如果检测到 where 直接跟 and 拼接，则 ⾃动删除 and，通常情况下 if 和 where 结合起来使⽤。

<select id="findByAccount" parameterType="com.southwind.entity.Account"
resultType="com.southwind.entity.Account">
 select * from t_account
 <where>
 <if test="id!=0">
 id = #{id}
 </if>
 <if test="username!=null">
 and username = #{username}
 </if>
 <if test="password!=null">
 and password = #{password}
 </if>
 <if test="age!=0">
 and age = #{age}
 </if>
 </where>
</select>

choose 、when 、otherwise标签

choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql。类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

<select id="findByAccount" parameterType="com.southwind.entity.Account"
resultType="com.southwind.entity.Account">
 select * from t_account
 <where>
 <choose>
 <when test="id!=0">
id = #{id}
 </when>
 <when test="username!=null">
 username = #{username}
 </when>
 <when test="password!=null">
 password = #{password}
 </when>
 <when test="age!=0">
 age = #{age}
 </when>
 <otherwise>
 </otherwise>
 </choose>
 </where>
</select>

trim 标签

trim 标签中的 prefix 和 suffix 属性会被⽤于⽣成实际的 SQL 语句，会和标签内部的语句进⾏拼接，如 果语句前后出现了 prefixOverrides 或者 suffixOverrides 属性中指定的值，MyBatis 框架会⾃动将其删 除。

<select id="findByAccount" parameterType="com.southwind.entity.Account"
resultType="com.southwind.entity.Account">
 select * from t_account
 <trim prefix="where" prefixOverrides="and">
 <if test="id!=0">
 id = #{id}
 </if>
 <if test="username!=null">
 and username = #{username}
 </if>
 <if test="password!=null">
 and password = #{password}
 </if>
 <if test="age!=0">
 and age = #{age}
 </if>
 </trim>
</select>

set 标签

set 标签⽤于 update 操作，会⾃动根据参数选择⽣成 SQL 语句。

<update id="update" parameterType="com.southwind.entity.Account">
 update t_account
 <set>
 <if test="username!=null">
 username = #{username},
</if>
 <if test="password!=null">
 password = #{password},
 </if>
 <if test="age!=0">
 age = #{age}
 </if>
 </set>
 where id = #{id}
</update>

foreach 标签

foreach 标签可以迭代⽣成⼀系列值，这个标签主要⽤于 SQL 的 in 语句。

<select id="findByIds" parameterType="com.southwind.entity.Account"
resultType="com.southwind.entity.Account">
 select * from t_account
 <where>
 <foreach collection="ids" open="id in (" close=")" item="id"
separator=",">
 #{id}
 </foreach>
 </where>
</select>