MyBatis-Plus（基于SpringBoot）—— CRUD 的快速使用
开发环境：
- IDEA：2021.3.3
- JDK： JDK8+
- 构建工具：maven 3.8.1
- MySQL：8.0.27
- SpringBoot：2.6.6
- MyBatis-Plus：3.5.1
环境搭建
-
新建数据库
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(20) NOT NULL AUTO_INCREMENT, `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `pwd` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, `gender` int DEFAULT '0', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES (1, '张三疯', '123654', '133@qq.com', 133, '0'); INSERT INTO `user` VALUES (2, '李四', 'abcdef', '1453@qq.com', 12, '0'); INSERT INTO `user` VALUES (3, '王五', '987654', '798@qq.com', 23, '0'); INSERT INTO `user` VALUES (4, '赵六', '56+897', '789456@qq.com', 35, '0'); SET FOREIGN_KEY_CHECKS = 1;
- 搭建工程
-
在 SpringBoot 项目中导入依赖
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <scope>provided</scope> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.5.1</version> </dependency> </dependencies>注意：使用mybatis-plus可以节省大量代码，不要同时导入mybatis和mybatis-plus，可能存在版本冲突
-
配置 application.yml 核心配置文件
server: port: 8080 spring: # 配置数据源信息 datasource: username: root password: root url: jdbc:mysql://localhost:3306/mybatis_plus?useSSL=false&characterEncoding=utf8&serverTimezone=Asia/Shanghai driver-class-name: com.mysql.cj.jdbc.Driver # 数据源类型 type: com.zaxxer.hikari.HikariDataSource注意：
-
驱动类driver-class-name
- spring boot 2.0(内置jdbc5驱动)，驱动类使用:driver-class-name: com.mysql.jdbc.Driver
- spring boot 2.1及以上(内置]dbc8驱动)，驱动类使用:driver-class-name: com.mysql.cj.jdbc.Driver
否则运行测试用例的时候会有WARN信息
-
连接地址url
-
MySQL5.7版本的url：
jdbc:mysql//localhost:3306/mybatis_plus?characterEncoding=utf-8&useSSL=false
-
MySQL8.0版本的url:
jdbc:mysql:/localhost:3306/mybatis_plus?serverTimezone=GMT9%62B8&characterEncoding=utf-8&useSSL=false
否则运行测试用例报告如下错误:
java.sql.SQLException: The server time zone value 'OE1u4ex%E+Ya' is unrecognized or represents more
-
-
-
在 pojo 包下新建 User 实体类
package cn.edu.hziee.mybatisplus.entity; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @NoArgsConstructor @AllArgsConstructor public class User { private Integer id; private String name; private String pwd; private String email; private Integer age; private Integer gender; } -
在启动类 MainApplication 中增加 @MapperScan 注解
package cn.edu.hziee.boot; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; @SpringBootApplication @MapperScan("cn.edu.hziee.mapper") public class MainApplication { public static void main(String[] args) { SpringApplication.run(MainApplication.class,args); } }@MapperScan 注解：用于扫描指定包下的 Mapper 接口如果不添加
@MapperScan 注解则需要在每一个 Mapper 接口中添加@Mapper注解 -
在 mapper 包下新建 UserMapper 接口
package cn.edu.hziee.mybatisplus.mapper; import cn.edu.hziee.mybatisplus.entity.User; import com.baomidou.mybatisplus.core.mapper.BaseMapper; public interface UserMapper extends BaseMapper<User> { }==让 UserMapper 接口继承 MyBatisPlus 提供的 BaseMapper 接口==
在 BaseMapper 接口中 MyBatisPlus 已经给我们提供好了基本的 CRUD 代码
-
新建测试类
package cn.edu.hziee; import cn.edu.hziee.mapper.UserMapper; import cn.edu.hziee.pojo.User; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; @SpringBootTest public class ApplicationTests { @Autowired UserMapper userMapper; }
1、MyBatis-Plus 配置日志
MyBatis-Plus 集成了 logback 日志
所以只需在 application.yml 中增加配置
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
2、查询数据
2.1、查询所有数据
-
测试类
@SpringBootTest class MyBatisPlusApplicationTests { @Autowired private UserMapper userMapper; @Test void getUserList(){ List<User> userList = userMapper.selectList(null); userList.forEach(user -> System.out.println("user = " + user)); } } -
运行结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5b8572df] was not registered for synchronization because synchronization is not active 2022-04-10 14:38:23.569 INFO 15208 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-04-10 14:38:23.871 INFO 15208 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@1772471998 wrapping com.mysql.cj.jdbc.ConnectionImpl@451e2fa9] will not be managed by Spring ==> Preparing: SELECT id,name,pwd,email,age,gender FROM user ==> Parameters: <== Columns: id, name, pwd, email, age, gender <== Row: 1, 张三疯, 123654, 133@qq.com, 133, 0 <== Row: 2, 李四, abcdef, 1453@qq.com, 12, 0 <== Row: 3, 王五, 987654, 798@qq.com, 23, 0 <== Row: 4, 赵六, 56+897, 789456@qq.com, 35, 0 <== Total: 4 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5b8572df] user = User(id=1, name=张三疯, pwd=123654, email=133@qq.com, age=133, gender=0) user = User(id=2, name=李四, pwd=abcdef, email=1453@qq.com, age=12, gender=0) user = User(id=3, name=王五, pwd=987654, email=798@qq.com, age=23, gender=0) user = User(id=4, name=赵六, pwd=56+897, email=789456@qq.com, age=35, gender=0)-
JDBC Connection [HikariProxyConnection@1772471998 wrapping com.mysql.cj.jdbc.ConnectionImpl@451e2fa9]：为使用的数据源 -
==> Preparing: SELECT id,name,pwd,email,age,gender FROM user：为执行的 SQL 语句
<== Columns: id, name, pwd, email, age, gender <== Row: 1, 张三疯, 123654, 133@qq.com, 133, 0 <== Row: 2, 李四, abcdef, 1453@qq.com, 12, 0 <== Row: 3, 王五, 987654, 798@qq.com, 23, 0 <== Row: 4, 赵六, 56+897, 789456@qq.com, 35, 0 <== Total: 4为查询结果
-
2.2、根据主键查询
-
测试类
@SpringBootTest class MyBatisPlusApplicationTests { @Autowired private UserMapper userMapper; @Test void getUserById(){ User user = userMapper.selectById(1); System.out.println("user = " + user); } } -
运行结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@35eee641] was not registered for synchronization because synchronization is not active 2022-04-10 14:54:20.711 INFO 17196 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-04-10 14:54:21.099 INFO 17196 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@1340769295 wrapping com.mysql.cj.jdbc.ConnectionImpl@32ae8f27] will not be managed by Spring ==> Preparing: SELECT id,name,pwd,email,age,gender FROM user WHERE id=? ==> Parameters: 1(Integer) <== Columns: id, name, pwd, email, age, gender <== Row: 1, 张三疯, 123654, 133@qq.com, 133, 0 <== Total: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@35eee641] user = User(id=1, name=张三疯, pwd=123654, email=133@qq.com, age=133, gender=0)
2.3、根据 ID 批量查询
-
测试类
@SpringBootTest class MyBatisPlusApplicationTests { @Autowired private UserMapper userMapper; @Test void getUserList(){ List<Integer> idList = Arrays.asList(1, 2, 3); List<User> userList = userMapper.selectBatchIds(idList); userList.forEach(list -> System.out.println("list = " + list)); } } -
运行结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@738a5848] was not registered for synchronization because synchronization is not active 2022-04-10 16:13:59.803 INFO 14452 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-04-10 16:14:00.108 INFO 14452 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@2034385122 wrapping com.mysql.cj.jdbc.ConnectionImpl@74a820bf] will not be managed by Spring ==> Preparing: SELECT id,name,pwd,email,age,gender FROM user WHERE id IN ( ? , ? , ? ) ==> Parameters: 1(Integer), 2(Integer), 3(Integer) <== Columns: id, name, pwd, email, age, gender <== Row: 1, 张三, 123789, 123@qq.com, 13, 1 <== Row: 2, 李四, abcdef, 1453@qq.com, 12, 1 <== Row: 3, 王五, 987654, 798@qq.com, 23, 1 <== Total: 3 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@738a5848] list = User(id=1, name=张三, pwd=123789, email=123@qq.com, age=13, gender=1) list = User(id=2, name=李四, pwd=abcdef, email=1453@qq.com, age=12, gender=1) list = User(id=3, name=王五, pwd=987654, email=798@qq.com, age=23, gender=1)
2.4、根据 Map 中的条件查询
-
测试类
@SpringBootTest class MyBatisPlusApplicationTests { @Autowired private UserMapper userMapper; @Test void getUserList(){ Map<String, Object> columnMap = new HashMap<String, Object>(); columnMap.put("name", "李四"); columnMap.put("age", 12); List<User> user = userMapper.selectByMap(columnMap); System.out.println("user = " + user); } } -
运行结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ac730cd] was not registered for synchronization because synchronization is not active 2022-04-10 16:19:41.768 INFO 18976 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-04-10 16:19:42.064 INFO 18976 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@2004952636 wrapping com.mysql.cj.jdbc.ConnectionImpl@5fdd97c1] will not be managed by Spring ==> Preparing: SELECT id,name,pwd,email,age,gender FROM user WHERE name = ? AND age = ? ==> Parameters: 李四(String), 12(Integer) <== Columns: id, name, pwd, email, age, gender <== Row: 2, 李四, abcdef, 1453@qq.com, 12, 1 <== Total: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1ac730cd] user = [User(id=2, name=李四, pwd=abcdef, email=1453@qq.com, age=12, gender=1)]
3、插入数据
-
在实体类中声明主键生成策略 @TableId(value = "id",type = IdType.AUTO)（在下面详细讲解）
package cn.edu.hziee.mybatisplus.entity; import com.baomidou.mybatisplus.annotation.IdType; import com.baomidou.mybatisplus.annotation.TableId; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @NoArgsConstructor @AllArgsConstructor public class User { @TableId(value = "id",type = IdType.AUTO) private Integer id; private String name; private String pwd; private String email; private Integer age; private Integer gender; } -
测试类
@SpringBootTest class MyBatisPlusApplicationTests { @Autowired private UserMapper userMapper; @Test void insertUser(){ User user = new User(null,"小明","123qwe","qaz@139.com",(short)20,"1"); int result = userMapper.insert(user); System.out.println("插入了" + result + "条数据"); System.out.println("user = " + user); } } -
运行结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@59636c47] was not registered for synchronization because synchronization is not active 2022-04-10 15:17:19.235 INFO 17224 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-04-10 15:17:19.540 INFO 17224 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@700791887 wrapping com.mysql.cj.jdbc.ConnectionImpl@3009eed7] will not be managed by Spring ==> Preparing: INSERT INTO user ( name, pwd, email, age, gender ) VALUES ( ?, ?, ?, ?, ? ) ==> Parameters: 小明(String), 123qwe(String), qaz@139.com(String), 20(Short), 1(String) <== Updates: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@59636c47] 插入了1条数据 user = User(id=5, name=小明, pwd=123qwe, email=qaz@139.com, age=20, gender=1)MyBatis-Plus 自动帮我提交事务，帮我配置了主键回填（除此之外还帮我们开启了驼峰命名等配置）
4、更新数据
-
测试类
@SpringBootTest class MyBatisPlusApplicationTests { @Autowired private UserMapper userMapper; @Test void updateUser(){ int result = userMapper.updateById(new User(1,"张三","123789","123@qq.com",13, 1)); System.out.println("更新了" + result + "条数据"); } } -
运行结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5aa62ee7] was not registered for synchronization because synchronization is not active 2022-04-10 16:07:46.190 INFO 17164 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-04-10 16:07:46.641 INFO 17164 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@2032479363 wrapping com.mysql.cj.jdbc.ConnectionImpl@6fbf5db2] will not be managed by Spring ==> Preparing: UPDATE user SET name=?, pwd=?, email=?, age=?, gender=? WHERE id=? ==> Parameters: 张三(String), 123789(String), 123@qq.com(String), 13(Integer), 1(Integer), 1(Integer) <== Updates: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5aa62ee7] 更新了1条数据
5、删除数据
5.1、根据 ID 删除
-
测试类
@SpringBootTest class MyBatisPlusApplicationTests { @Autowired private UserMapper userMapper; @Test void deleteUserById(){ int result = userMapper.deleteById(5); System.out.println("删除了" + result + "条数据"); } } -
运行结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6371cf2f] was not registered for synchronization because synchronization is not active 2022-04-10 15:21:46.390 INFO 20276 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-04-10 15:21:46.644 INFO 20276 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@565517913 wrapping com.mysql.cj.jdbc.ConnectionImpl@1785d194] will not be managed by Spring ==> Preparing: DELETE FROM user WHERE id=? ==> Parameters: 5(Integer) <== Updates: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@6371cf2f] 删除了1条数据
5.2、根据 Map 中的条件删除
-
测试类
@SpringBootTest class MyBatisPlusApplicationTests { @Autowired private UserMapper userMapper; @Test void deleteUserByMap(){ Map<String, Object> columnMap = new HashMap<>(); columnMap.put("name", "小明"); columnMap.put("age", 20); int result = userMapper.deleteByMap(columnMap); System.out.println("删除了" + result + "条数据"); } } -
运行结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5aa62ee7] was not registered for synchronization because synchronization is not active 2022-04-10 15:28:52.998 INFO 17156 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-04-10 15:28:53.397 INFO 17156 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@293669143 wrapping com.mysql.cj.jdbc.ConnectionImpl@13278a41] will not be managed by Spring ==> Preparing: DELETE FROM user WHERE name = ? AND age = ? ==> Parameters: 小明(String), 20(Integer) <== Updates: 1 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5aa62ee7] 删除了1条数据
5.3、根据 ID 批量删除
-
测试类
@SpringBootTest class MyBatisPlusApplicationTests { @Autowired private UserMapper userMapper; @Test void deleteUserList(){ List<Integer> usersId = Arrays.asList(1, 2, 3, 4); int result = userMapper.deleteBatchIds(usersId); System.out.println("删除了" + result + "条数据"); } } -
运行结果
Creating a new SqlSession SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ab802f4] was not registered for synchronization because synchronization is not active 2022-04-10 15:33:35.295 INFO 15072 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Starting... 2022-04-10 15:33:35.573 INFO 15072 --- [ main] com.zaxxer.hikari.HikariDataSource : HikariPool-1 - Start completed. JDBC Connection [HikariProxyConnection@1352294549 wrapping com.mysql.cj.jdbc.ConnectionImpl@57cabdc3] will not be managed by Spring ==> Preparing: DELETE FROM user WHERE id IN ( ? , ? , ? , ? ) ==> Parameters: 1(Integer), 2(Integer), 3(Integer), 4(Integer) <== Updates: 4 Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7ab802f4] 删除了4条数据
