Mybatis增强工具——MyBatis-Plus
1.概念：
a.简介：MyBatis-Plus（简称 MP）是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。
b.支持数据库：mysql 、mariadb、oracle、db2、h2、hsql、sqlite、postgresql、sqlserver
2.使用：
a.导入Maven依赖
<properties> ...... <!-- spring --> <spring.version>5.1.1.RELEASE</spring.version> <!-- jackson-json --> <jackson.version>2.9.4</jackson.version> <!-- log4j --> <slf4j.version>1.7.18</slf4j.version> <log4j.version>1.2.17</log4j.version> </properties> <dependencies> <!-- spring --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-core</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-webmvc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-oxm</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-tx</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-aop</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-context-support</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-test</artifactId> <version>${spring.version}</version> </dependency> <!-- Jackson --> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-core</artifactId> <version>${jackson.version}</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-databind</artifactId> <version>${jackson.version}</version> </dependency> <dependency> <groupId>com.fasterxml.jackson.core</groupId> <artifactId>jackson-annotations</artifactId> <version>${jackson.version}</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> <scope>test</scope> </dependency> <!-- AOP --> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjrt</artifactId> <version>1.8.6</version> </dependency> <dependency> <groupId>org.aspectj</groupId> <artifactId>aspectjweaver</artifactId> <version>1.8.6</version> </dependency> <!-- 日志相关 --> <dependency> <groupId>log4j</groupId> <artifactId>log4j</artifactId> <version>${log4j.version}</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>${slf4j.version}</version> </dependency> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-log4j12</artifactId> <version>${slf4j.version}</version> </dependency> <!-- spring-jdbc --> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <!-- MySQL --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.13</version> </dependency> <!-- 连接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.1.12</version> </dependency> <!-- mp 依赖 --> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus</artifactId> <version>3.2.0</version> </dependency> </dependencies>
b.书写 spring.xml 和 jdbc.properties 配置文件
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-4.3.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-4.3.xsd"> <!-- 引入外部properties --> <context:property-placeholder location="classpath:jdbc.properties" /> <!-- 扫描包 --> <context:component-scan base-package="com.wode" /> <!-- 开启AOP代理 --> <aop:aspectj-autoproxy proxy-target-class="true" /> <!-- 开启注解处理器 --> <context:annotation-config /> <!-- 开启注解声明式事务 --> <tx:annotation-driven/> <!-- 数据源 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"> <property name="driverClassName" value="${jdbc.driver}"/> <property name="url" value="${jdbc.url}"/> <property name="username" value="${jdbc.username}"/> <property name="password" value="${jdbc.password}"/> </bean> <!-- mybatits-plus 整合 Spring --> <bean id="sqlSessionFactory" class="com.baomidou.mybatisplus.extension.spring.MybatisSqlSessionFactoryBean"> <!-- 加载数据源 --> <property name="dataSource" ref="dataSource" /> <!-- 指定 pojo 目录 --> <property name="typeAliasesPackage" value="com.wode.entity"/> <!-- mybatis-plus 插件 --> <property name="plugins"> <array> <!-- 分页插件 --> <bean class="com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor"></bean> </array> </property> <!-- 加载 mybatis-plus 全局属性 --> <property name="globalConfig" ref="globalConfig" /> </bean> <!-- 配置和 mybatis-plus 全局属性 --> <bean id="globalConfig" class="com.baomidou.mybatisplus.core.config.GlobalConfig"> <property name="dbConfig" ref="dbConfig"/> </bean> <bean id="dbConfig" class="com.baomidou.mybatisplus.core.config.GlobalConfig.DbConfig"> <!-- AUTO -> 数据库ID自增 INPUT -> 用户输入ID ID_WORKER -> 全局唯一ID UUID -> 全局唯一ID --> <property name="idType" value="UUID"/> <!-- 全局的表前缀策略配置 --> <property name="tablePrefix" value="tb_" /> </bean> <!-- mybatis 的动态代理 --> <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> <property name="basePackage" value="com.wode.dao" /> <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/> </bean> <!-- 事务代理管理器 --> <bean name="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> <property name="dataSource" ref="dataSource" /> </bean> </beans>
jdbc.driver=com.mysql.jdbc.Driver jdbc.url=jdbc:mysql://localhost:3306/mytest?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC jdbc.username=root jdbc.password=123456
c.在数据库创建`tb_user`用户表
-- 用户表 CREATE TABLE `tb_user` ( `id` varchar(255) NOT NULL, `user_name` varchar(255) NOT NULL, `user_code` int(11) NOT NULL, PRIMARY KEY (`id`) );
d.创建User用户实体类
@TableName("tb_user")
public class User {
@TableId
private String id;
private String userName;
private int userCode;
public String getId() {
return id;
}
public void setId(String id) {
this.id = id;
}
public String getUserName() {
return userName;
}
public void setUserName(String userName) {
this.userName = userName;
}
public int getUserCode() {
return userCode;
}
public void setUserCode(int userCode) {
this.userCode = userCode;
}
@Override
public String toString() {
return "User(id[" + id + "], userName[" + userName + "], userCode[" + userCode + "])";
}
}
e.创建 UserMapper 接口继承 BaseMapper
public interface UserMapper extends BaseMapper<User> { }
f.使用
-------------------------------------------------------------新增-------------------------------------------------------------
1）新增
ApplicationContext context = new ClassPathXmlApplicationContext("spring.xml"); UserMapper userMapper = (UserMapper) context.getBean("userMapper"); User user = new User(); user.setUserCode(509); user.setUserName("赵九"); userMapper.insert(user);
-------------------------------------------------------------修改-------------------------------------------------------------
2）修改（根据ID）
User user = new User(); user.setId("000a03f373eb563789e8311225eeee87"); user.setUserCode(502); userMapper.updateById(user);
3）修改（根据Wrapper）
User user = new User(); user.setUserCode(504); UpdateWrapper<User> wrapper = new UpdateWrapper<>(); wrapper.ge("user_code", 503); wrapper.eq("user_name", "李四"); userMapper.update(user, wrapper);
-------------------------------------------------------------删除-------------------------------------------------------------
4）删除（根据ID）
userMapper.deleteById("5f9f4315153a422c33af8a671c999812");
5）删除（根据ID集合）
List<String> ids = new ArrayList<>(); ids.add("24cc20d8453e61dad42c1be0df356807"); ids.add("7edb2acaa7b8cacb08b7e25a947979eb"); ids.add("e4faed134b055ef80fb1d5e8382b9373"); userMapper.deleteBatchIds(ids);
6）删除（根据Map）
Map<String, Object> filter = new HashMap<>(); filter.put("user_name", "赵七"); userMapper.deleteByMap(filter);
7）删除（根据Wrapper）
QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.eq("user_name", "赵八"); userMapper.delete(wrapper);
-------------------------------------------------------------查询一条-------------------------------------------------------------
8）查询一条（根据ID）
User user = userMapper.selectById("000a03f373eb563789e8311225eeee87"); System.out.println(user);
9）查询一条（根据Wrapper）
QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.eq("user_name", "张三"); User user = userMapper.selectOne(wrapper); System.out.println(user);
-------------------------------------------------------------查询多条-------------------------------------------------------------
10）查询（根据ID集合）
List<String> ids = new ArrayList<>(); ids.add("9175a689f2a28b9b6ca1a72d153fc7ec"); ids.add("460a3f6192c2e27595fd1a2fa0defa16"); List<User> userList = userMapper.selectBatchIds(ids); System.out.println(userList);
11）查询（根据Wrapper）
QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.like("user_name", "赵%"); wrapper.gt("user_code", 507); List<User> userList = userMapper.selectList(wrapper); System.out.println(userList);
12）查询（根据Map）
Map<String, Object> filter = new HashMap<>(); filter.put("user_name", "张三"); List<User> userList = userMapper.selectByMap(filter); System.out.println(userList);
-------------------------------------------------------------分页查询-------------------------------------------------------------
13）分页查询（根据Wrapper）
Page<User> page = new Page<>(1, 3); QueryWrapper<User> queryWrapper = new QueryWrapper<>(); IPage<User> userIPage = userMapper.selectPage(page, queryWrapper); System.out.println("总条数：" + userIPage.getTotal()); System.out.println("每页条数：" + userIPage.getSize()); System.out.println("当前页数：" + userIPage.getCurrent()); System.out.println("总页数：" + userIPage.getPages()); System.out.println("查询结果：" + userIPage.getRecords());
-------------------------------------------------------------条数查询-------------------------------------------------------------
14）查询条数（根据Wrapper）
QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.like("user_name", "赵%"); int count = userMapper.selectCount(queryWrapper); System.out.println(count);
3.其他
a.条件查询or
QueryWrapper<User> wrapper = new QueryWrapper<>(); wrapper.ge("user_code", 502); //不写则默认为and拼接 wrapper.le("user_code", 503); //or嵌套 wrapper.or(wrapper1 -> wrapper1.like("user_name", "赵%").ne("id", "f463c76d52056ca51bc76e348fd0572e")); //or拼接 wrapper.or().eq("id", "460a3f6192c2e27595fd1a2fa0defa16"); List<User> userList = userMapper.selectList(wrapper); System.out.println(userList);
b.排序
QueryWrapper<User> wrapper = new QueryWrapper<>(); //正序 wrapper.orderByAsc("user_name"); //倒序 wrapper.orderByDesc("user_code"); List<User> userList = userMapper.selectList(wrapper); System.out.println(userList);
c.返回指定字段
QueryWrapper<User> wrapper = new QueryWrapper<>(); //只返回指定字段 wrapper.select("user_name", "user_code"); List<User> userList = userMapper.selectList(wrapper); System.out.println(userList);
4.多表分页查询
a.创建 tb_info 用户信息表，并添加数据
-- 用户信息表 CREATE TABLE `tb_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` varchar(255) NOT NULL, `nick_name` varchar(255) NOT NULL, `gender` int(11) NOT NULL, PRIMARY KEY (`id`) );
b.在 UserMapper 中添加多表联查自定义方法
public interface UserMapper extends BaseMapper<User> { //普通连表查询 @Select("SELECT u.id,u.user_name,u.user_code,i.nick_name,i.gender FROM tb_user u,tb_info i WHERE u.id=i.user_id and u.user_code>=#{userCode} order by u.user_code desc") List<UserInfoVo> getQuestionStudentList(@Param("userCode") int userCode); //分页连表查询 @Select("SELECT u.id,u.user_name,u.user_code,i.nick_name,i.gender FROM tb_user u,tb_info i WHERE u.id=i.user_id and u.user_code>=#{userCode} order by u.user_code desc") IPage<UserInfoVo> getQuestionStudentPage(Page<UserInfoVo> page, @Param("userCode") int userCode); }
c.测试使用
1）普通连表查询测试
List<UserInfoVo> userList = userMapper.getQuestionStudentList(503); for(UserInfoVo userInfo : userList){ System.out.println(userInfo); }
2）分页连表查询测试
Page<UserInfoVo> page = new Page<>(1, 3); IPage<UserInfoVo> userIPage = userMapper.getQuestionStudent(page, 503); System.out.println("总条数：" + userIPage.getTotal()); System.out.println("每页条数：" + userIPage.getSize()); System.out.println("当前页数：" + userIPage.getCurrent()); System.out.println("总页数：" + userIPage.getPages()); System.out.println("查询结果："); for(UserInfoVo userInfo : userIPage.getRecords()){ System.out.println(userInfo); }
5.官方文档：
https://mp.baomidou.com/guide/
