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 文件中添加相关依赖
<dependencies>
<!-- MyBatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.5</version>
</dependency>
<!-- MySql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.12</version>
<scope>provided</scope>
</dependency>
<!-- log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<!-- junit4 单元测试 -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-compiler-plugin</artifactId>
<version>3.8.1</version>
<configuration>
<source>1.8</source>
<target>1.8</target>
<encoding>UTF-8</encoding>
</configuration>
</plugin>
</plugins>
</build>
☕️ 创建实体类
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" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- configuration 是 MyBatis 配置文件的根标签 -->
<configuration>
<!-- 加载外部配置的 properties 文件 -->
<properties resource="jdbc.properties"/>
<!-- 设置 -->
<settings>
<!-- 是否开启自动驼峰命名规则(camel case)映射,默认值为 false -->
<setting name="mapUnderscoreToCamelCase" value="true"/>
<!-- 是否开启延迟加载的全局开关,默认值为 false -->
<setting name="lazyLoadingEnable" value="true"/>
<!-- 设置为 false 表示按需加载。默认值在 3.4.1 版本之前为 true,之后为 false -->
<setting name="aggressiveLazyLoading" value="false"/>
<!-- 是否开启二级缓存,默认为 true,所以不需要配置 -->
<setting name="cacheEnabled" value="true"/>
</settings>
<!-- 定义别名 -->
<typeAliases>
<!-- 扫描整个包下的类,别名为类名(首字母大写或小写都可以)-->
<package name="com.example.entity"/>
</typeAliases>
<!-- environments 标签:配置 MyBatis 的运行环境,内部可配置多个环境
default 属性:指定要运行的那个环境的 id
-->
<environments default="development">
<!-- 配置 id 为 development 的环境 -->
<environment id="development">
<!-- 使用 JDBC 的事务管理 -->
<transactionManager type="JDBC"/>
<!-- 配置数据库连接池 -->
<dataSource type="POOLED">
<!-- 配置连接数据库的4个基本信息 -->
<property name="driver" value="${jdbc.driver}"/>
<property name="url" value="${jdbc.url}"/>
<property name="username" value="${jdbc.username}"/>
<property name="password" value="${jdbc.password}"/>
</dataSource>
</environment>
</environments>
<!-- 配置映射信息 -->
<mappers>
<!-- 由于使用注解配置,SQL 语句是定义在接口类中的,所以此处要配置的 Mapper 接口位置:
第一种方式:使用 mapper 标签配置 class 属性,指定接口类
第二种方式:使用 package 标签配置 name 属性,指定 mapper 接口所在的包
-->
<package name="com.example.mapper"/>
</mappers>
</configuration>
☕️ 编写 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<User> selectAll();
这里简要说明:
- 这里的 @Resules 注解对应着 XML 方式里的
<resultMap>
标签; - @Result 注解对应着
<result>
子标签,而在参数中写上id=true
就对应<id>
子标签; - @Results 可以共用,参数 id 是其唯一标识,其它方法只要使用
@ResultMap(id)
注解引用即可。
@Test
public void selectAll() {
List<User> 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<User> selectByUsernameLikeAndSex(User user);
@Test
public void selectByUsernameLikeAndSex() {
User user = new User();
user.setUsername("小");
user.setSex("男");
List<User> 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),也就是从查询订单信息出发关联查询用户信息为一对一查询。
![](https://img2020.cnblogs.com/blog/1606446/202008/1606446-20200820002553447-546388327.jpg)
MyBatis 的注解方式使用 @One 注解来处理一对一关联关系,该注解相当于 XML 方式的<assocation>
标签。
✌ 在 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 注解相当于<assocation>
标签,select 属性用来指定嵌套查询的 sqlmapper,fetchType 属性用来配置延迟加载,会覆盖掉全局的 lazyLoadingEnable 配置。
✌ 对 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),也就是从查询用户信息出发关联查询订单信息为一对多查询。
![](https://img2020.cnblogs.com/blog/1606446/202008/1606446-20200820002553447-546388327.jpg)
MyBatis 的注解方式使用 @Many 注解来处理一对一关联关系,该注解相当于 XML 方式的<Collection>
标签。
✍ 修改 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<Order> 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 注解相当于<collection>
标签,select 属性用来指定嵌套查询的 sqlmapper,fetchType 属性用来配置延迟加载,会覆盖掉全局的 lazyLoadingEnable 配置。
✍ 对 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 {
// 接口方法
}