MyBatis 入门（三）：注解使用
MyBatis 提供了使用注解开发方式，可以大幅度减少编写 XML 映射文件。虽然注解开发很便利，但是对于一些复杂的 SQL，还是推荐使用 XML 映射文件方式配置。
前期准备
?? 数据库脚本
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
`username` VARCHAR(32) COMMENT '用户名',
`birthday` DATE COMMENT '生日',
`sex` CHAR(1) COMMENT '性别',
`address` VARCHAR(256) COMMENT '地址'
) COMMENT '用户表';
INSERT INTO `tb_user` VALUES(1, '小米', '1996-01-27', '男', '北京');
INSERT INTO `tb_user` VALUES(2, '小明', '1996-02-02', '女', '上海');
INSERT INTO `tb_user` VALUES(3, '小红', '1996-03-04', '女', '天津');
INSERT INTO `tb_user` VALUES(4, '小黑', '1996-04-04', '男', '广州');
INSERT INTO `tb_user` VALUES(5, '小绿', '1996-05-04', '女', '南京');
INSERT INTO `tb_user` VALUES(6, '小紫', '1996-06-04', '男', '成都');
?? 在 pom.xml 文件中添加相关依赖
org.mybatis
mybatis
3.5.5
mysql
mysql-connector-java
8.0.21
org.projectlombok
lombok
1.18.12
provided
log4j
log4j
1.2.17
junit
junit
4.12
test
org.apache.maven.plugins
maven-compiler-plugin
3.8.1
1.8
1.8
UTF-8
?? 创建实体类
package com.example.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.Date;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable { // 用户实体类
private Integer id; // 编号
private String username; // 用户名
private Date birthday; // 生日
private String sex; // 性别
private String address; // 地址
}
?? 编写 UserMapper 接口
package com.example.mapper;
public interface UserMapper {
// CRUD 方法（使用注解方式）
}
?? 编写 jdbc.properties 配置数据库连接信息
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/learning?characterEncoding=utf-8&useSSL=false&serverTimezone=Hongkong
jdbc.username=root
jdbc.password=123456
?? 编写 MyBatis 核心配置文件 mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
?? 编写 log4j.properteis 配置日志
# 配置日志的目的是在控制台输出 SQL 语句
# 将总体日志级别设置为 warn，com.example.mapper包的日志级别设置为 trace
log4j.rootCategory=warn,stdout
log4j.logger.com.example.mapper=trace
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
简单的 CRUD 操作
Select 注解
?? 普通查询方法
@Select("select id, username, birthday, sex, address from tb_user where id = #{id}")
User selectById(Integer id);
public class UserMapperTest {
private SqlSession sqlSession;
private UserMapper userMapper;
@Before
public void init() throws IOException {
// 读取 MyBatis 核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
// 创建 SqlSessionFactory 工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
// 关闭输入流
resourceAsStream.close();
// 使用 SqlSessionFactory 生产 SqlSession 对象
sqlSession = sqlSessionFactory.openSession();
// 使用 SqlSession 创建 Mapper 接口的代理对象
userMapper = sqlSession.getMapper(UserMapper.class);
}
@After
public void end() {
// 关闭资源
sqlSession.close();
}
@Test
public void selectById() {
User user = userMapper.selectById(1);
System.out.println(user);
}
}
==> Preparing: select id, username, birthday, sex, address from tb_user where id = ?
==> Parameters: 1(Integer)
<== Columns: id, username, birthday, sex, address
<== Row: 1, 小米, 1996-01-27, 男, 北京
<== Total: 1
User(id=1, username=小米, birthday=Sat Jan 27 00:00:00 CST 1996, sex=男, address=北京)
?? 使用 @Results 注解设置字段映射
@Results(id = "userMap", value = {
@Result(property = "id", column = "id", id = true),
@Result(property = "username", column = "username"),
@Result(property = "birthday", column = "birthday"),
@Result(property = "sex", column = "sex"),
@Result(property = "address", column = "address")
})
@Select("select id, username, birthday, sex, address from tb_user")
List selectAll();
这里简要说明：
- 这里的 @Resules 注解对应着 XML 方式里的
- @Result 注解对应着
id=true就对应 - @Results 可以共用，参数 id 是其唯一标识，其它方法只要使用
@ResultMap(id)注解引用即可。
@Test
public void selectAll() {
List userList = userMapper.selectAll();
userList.forEach(System.out::println);
}
==> Preparing: select id, username, birthday, sex, address from tb_user
==> Parameters:
<== Columns: id, username, birthday, sex, address
<== Row: 1, 小米, 1996-01-27, 男, 北京
<== Row: 2, 小明, 1996-02-02, 女, 上海
<== Row: 3, 小红, 1996-03-04, 女, 天津
<== Row: 4, 小黑, 1996-04-04, 男, 广州
<== Row: 5, 小绿, 1996-05-04, 女, 南京
<== Row: 6, 小紫, 1996-06-04, 男, 成都
<== Total: 6
User(id=1, username=小米, birthday=Sat Jan 27 00:00:00 CST 1996, sex=男, address=北京)
User(id=2, username=小明, birthday=Fri Feb 02 00:00:00 CST 1996, sex=女, address=上海)
User(id=3, username=小红, birthday=Mon Mar 04 00:00:00 CST 1996, sex=女, address=天津)
User(id=4, username=小黑, birthday=Thu Apr 04 00:00:00 CST 1996, sex=男, address=广州)
User(id=5, username=小绿, birthday=Sat May 04 00:00:00 CST 1996, sex=女, address=南京)
User(id=6, username=小紫, birthday=Tue Jun 04 00:00:00 CST 1996, sex=男, address=成都)
?? 传入参数为 pojo 类
@ResultMap("userMap")
@Select("select id, username, birthday, sex, address from tb_user " +
"where username like concat('%', #{username}, '%') and sex = #{sex}")
List selectByUsernameLikeAndSex(User user);
@Test
public void selectByUsernameLikeAndSex() {
User user = new User();
user.setUsername("小");
user.setSex("男");
List userList = userMapper.selectByUsernameLikeAndSex(user);
userList.forEach(System.out::println);
}
==> Preparing: select id, username, birthday, sex, address from tb_user where username like concat('%', ?, '%') and sex = ?
==> Parameters: 小(String), 男(String)
<== Columns: id, username, birthday, sex, address
<== Row: 1, 小米, 1996-01-27, 男, 北京
<== Row: 4, 小黑, 1996-04-04, 男, 广州
<== Row: 6, 小紫, 1996-06-04, 男, 成都
<== Total: 3
User(id=1, username=小米, birthday=Sat Jan 27 00:00:00 CST 1996, sex=男, address=北京)
User(id=4, username=小黑, birthday=Thu Apr 04 00:00:00 CST 1996, sex=男, address=广州)
User(id=6, username=小紫, birthday=Tue Jun 04 00:00:00 CST 1996, sex=男, address=成都)
?? 传入多个参数
@ResultMap("userMap")
@Select("select id, username, birthday, sex, address from tb_user " +
"where username = #{u} and address = #{a}")
User selectByUsernameAndAddress(@Param("u") String username, @Param("a") String address);
@Test
public void selectByUsernameAndAddress() {
User user = userMapper.selectByUsernameAndAddress("小米", "北京");
System.out.println(user);
}
==> Preparing: select id, username, birthday, sex, address from tb_user where username = ? and address = ?
==> Parameters: 小米(String), 北京(String)
<== Columns: id, username, birthday, sex, address
<== Row: 1, 小米, 1996-01-27, 男, 北京
<== Total: 1
User(id=1, username=小米, birthday=Sat Jan 27 00:00:00 CST 1996, sex=男, address=北京)
Insert 注解
?? 普通插入方法
@Insert("insert into tb_user value(#{id}, #{username}, #{birthday}, #{sex}, #{address}")
int insert(User user);
@After
public void end() {
// 在实现增删改时，一定要使用 commit() 方法提交事务，要不然数据库不会更新记录
// 提交事务
sqlSession.commit();
// 关闭资源
sqlSession.close();
}
@Test
public void insert() {
User user = new User(7, "小A", new Date(), "男", "苏州");
Assert.assertEquals(1, userMapper.insert(user));
}
==> Preparing: insert into tb_user values(?, ?, ?, ?, ?)
==> Parameters: 7(Integer), 小A(String), 2020-08-13 15:25:23.921(Timestamp), 男(String), 苏州(String)
<== Updates: 1
?? 主键自增回写
@Options(useGeneratedKeys = true, keyProperty = "id")
@Insert("insert into tb_user(username, birthday, sex, address) values " +
"(#{username}, #{birthday}, #{sex}, #{address})")
int insert2(User user);
和 XML 方式一样，如果要主键自增回写需要设置 useGeneratedKeys 和 keyProperty 参数。
@Test
public void insert2() {
User user = new User(null, "小B", new Date(), "女", "苏州");
Assert.assertEquals(1, userMapper.insert2(user));
System.out.println(user.getId());
}
==> Preparing: insert into tb_user(username, birthday, sex, address) values (?, ?, ?, ?)
==> Parameters: 小B(String), 2020-08-13 15:30:25.502(Timestamp), 女(String), 苏州(String)
<== Updates: 1
8
Update 注解
@Update("update tb_user set address = #{address} where id = #{id}")
int updateAddressById(User user);
@Test
public void updateAddressById() {
User user = new User();
user.setId(8);
user.setAddress("上海");
Assert.assertEquals(1, userMapper.updateAddressById(user));
}
==> Preparing: update tb_user set address = ? where id = ?
==> Parameters: 上海(String), 8(Integer)
<== Updates: 1
Delete 注解
@Delete("delete from tb_user where id = #{id}")
int deleteById(Integer id);
@Test
public void deleteById() {
Assert.assertEquals(1, userMapper.deleteById(8));
}
==> Preparing: delete from tb_user where id = ?
==> Parameters: 8(Integer)
<== Updates: 1
关联查询
为了讲解关联查询，此处添加 Order 表以及修改相关实体类、Mapper 接口。
?? 增加 Order 表
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order` (
`id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
`time` TIMESTAMP COMMENT '下单时间',
`money` DOUBLE COMMENT '订单总价格',
`user_id` INT COMMENT '用户编号'
) COMMENT '订单表';
INSERT INTO `tb_order` VALUES(1, '2018-1-12 14:47:08', 40.0, 1);
INSERT INTO `tb_order` VALUES(2, '2018-2-12 17:41:08', 30.0, 1);
INSERT INTO `tb_order` VALUES(3, '2018-3-12 12:39:08', 20.0, 2);
?? 增加 Order 实体类
package com.example.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order implements Serializable { // 订单实体类
private Integer id; // 编号
private Date time; // 下单时间
private Double money; // 订单总价格
}
?? 增加 OrderMapper 接口
package com.example.mapper;
public interface OrderMapper {
}
一对一映射
对于订单和用户而言，一个订单（order）只能对应着一个用户（user），也就是从查询订单信息出发关联查询用户信息为一对一查询。
MyBatis 的注解方式使用 @One 注解来处理一对一关联关系，该注解相当于 XML 方式的
? 在 Order 实体类中增加 user 属性：
public class Order implements Serializable {
// 其它属性
private User user; // 关联的用户
}
? 在 UserMapper 接口中添加 selectById() 方法：
@Select("select id, username, birthday, sex, address from tb_user where id = #{id}")
User selectById(Integer id);
? 在 OrderMapper 接口中添加 selectById() 方法：
@Results(id = "orderWithUserMap", value = {
@Result(property = "id", column = "id", id = true),
@Result(property = "time", column = "time"),
@Result(property = "money", column = "money"),
@Result(property = "user", column = "uid",
one = @One(select = "com.example.mapper.UserMapper.selectById",
fetchType = FetchType.LAZY)
)
})
@Select("select id, time, money, user_id uid from tb_order where id = #{id}")
Order selectById(Integer id);
上面的查询方式等同于 XML 的一对一嵌套查询方式，@One 注解相当于
? 对 selectById() 方法进行测试：
public class OrderMapperTest {
private SqlSession sqlSession;
private OrderMapper orderMapper;
@Before
public void init() throws IOException {
// 读取 MyBatis 核心配置文件
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
// 创建 SqlSessionFactory 工厂对象
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
// 关闭输入流
resourceAsStream.close();
// 使用 SqlSessionFactory 生产 SqlSession 对象
sqlSession = sqlSessionFactory.openSession();
// 使用 SqlSession 创建 Mapper 接口的代理对象
orderMapper = sqlSession.getMapper(OrderMapper.class);
}
@After
public void end() {
// 关闭资源
sqlSession.close();
}
@Test
public void selectById() {
Order order = orderMapper.selectById(1);
System.out.println("====== 执行 order.getUser() 方法 ======");
order.getUser();
System.out.println(order);
}
}
==> Preparing: select id, time, money, user_id uid from tb_order where id = ?
==> Parameters: 1(Integer)
<== Columns: id, time, money, uid
<== Row: 1, 2018-01-12 14:47:08, 40.0, 1
<== Total: 1
====== 执行 order.getUser() 方法 ======
==> Preparing: select id, username, birthday, sex, address from tb_user where id = ?
==> Parameters: 1(Integer)
<== Columns: id, username, birthday, sex, address
<== Row: 1, 小米, 1996-01-27, 男, 北京
<== Total: 1
Order(id=1, time=Fri Jan 12 14:47:08 CST 2018, money=40.0, user=User(id=1, username=小米, birthday=Sat Jan 27 00:00:00 CST 1996, sex=男, address=北京))
一对多映射
对于用户和订单而言，一个用户（user）可以有多个订单（order），也就是从查询用户信息出发关联查询订单信息为一对多查询。
MyBatis 的注解方式使用 @Many 注解来处理一对一关联关系，该注解相当于 XML 方式的
? 修改 Order 实体类和 User 实体类
package com.example.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Order implements Serializable { // 订单实体类
private Integer id; // 编号
private Date time; // 下单时间
private Double money; // 订单总价格
}
package com.example.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
import java.util.Date;
import java.util.List;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class User implements Serializable { // 用户实体类
private Integer id; // 编号
private String username; // 用户名
private Date birthday; // 生日
private String sex; // 性别
private String address; // 地址
private List orderList; // 用户关联的订单
}
? 在 OrderMapper 接口中修改 selectById() 方法：
@Select("select id, time, money from tb_order where id = #{id}")
Order selectById(Integer id);
? 在 UserMapper 接口中修改 selectById() 方法：
@Results(id = "userWithOrderMap", value = {
@Result(property = "id", column = "id", id = true),
@Result(property = "username", column = "username"),
@Result(property = "birthday", column = "birthday"),
@Result(property = "sex", column = "sex"),
@Result(property = "address", column = "address"),
@Result(property = "orderList", column = "id",
many = @Many(select = "com.example.mapper.OrderMapper.selectById",
fetchType = FetchType.LAZY)
)
})
@Select("select id, username, birthday, sex, address from tb_user where id = #{id}")
User selectById(Integer id);
上面的查询方式等同于 XML 的一对多嵌套查询方式，@Many 注解相当于
? 对 selectById() 方法进行测试：
@Test
public void selectById() {
User user = userMapper.selectById(1);
System.out.println("====== 执行 user.getOrderList() 方法 ======");
user.getOrderList();
System.out.println(user);
}
==> Preparing: select id, username, birthday, sex, address from tb_user where id = ?
==> Parameters: 1(Integer)
<== Columns: id, username, birthday, sex, address
<== Row: 1, 小米, 1996-01-27, 男, 北京
<== Total: 1
====== 执行 user.getOrderList() 方法 ======
==> Preparing: select id, time, money from tb_order where id = ?
==> Parameters: 1(Integer)
<== Columns: id, time, money
<== Row: 1, 2018-01-12 14:47:08, 40.0
<== Total: 1
User(id=1, username=小米, birthday=Sat Jan 27 00:00:00 CST 1996, sex=男, address=北京, orderList=[Order(id=1, time=Fri Jan 12 14:47:08 CST 2018, money=40.0)])
基于注解的二级缓存
由于二级缓存的全局开关默认是开启的，所以只需要开启 Mapper 接口上的二级缓存：
@CacheNamespace(blocking = true) // 基于注解方式实现配置二级缓存
public interface UserMapper {
// 接口方法
}