当前位置：首页 >数据库 > MyBatisPlus的用法

MyBatisPlus的用法

简介

MyBatis-Plus（简称 MP），是一个MyBatis的增强工具包，只做增强不做改变，为简化开发、提高效率而生。

环境搭建

测试数据

SET FOREIGN_KEY_CHECKS=0;DROP TABLE IF EXISTS `user`;CREATE TABLE `user` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `useame` varchar(20) NOT NULL COMMENT '用户名',  `password` varchar(255) NOT NULL COMMENT '密码',  `real_name` varchar(255) NOT NULL COMMENT '真实名',  `sex` varchar(255) DEFAULT NULL COMMENT '性别',  `birthday` date DEFAULT NULL COMMENT '生日',  `phone` varchar(255) NOT NULL COMMENT '电话',  `utype` tinyint(1) DEFAULT NULL COMMENT '用户类型',  `addtime` datetime DEFAULT NULL COMMENT '添加时间',  `adduser` varchar(255) DEFAULT NULL COMMENT '添加者',  PRIMARY KEY (`id`),  UNIQUE KEY `phone` (`phone`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into `user` values(1,'qzcsbj1','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj1','男','2018-09-06','13800000001',0,now(),'qzcsbj');insert into `user` values(2,'qzcsbj2','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj2','女','2018-09-07','13800000002',1,now(),'qzcsbj');insert into `user` values(3,'qzcsbj3','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj3','男','2018-09-08','13800000003',0,now(),'qzcsbj');insert into `user` values(4,'qzcsbj4','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj4','女','2018-09-09','13800000004',1,now(),'qzcsbj');insert into `user` values(5,'qzcsbj5','62f7f5673e94eca261b8fce7ae7863a4','qzcsbj5','女','2018-09-10','13800000005',0,now(),'qzcsbj');

实体类

package com.qzcsbj.bean;import java.util.Date;/** * @公众号 : 全栈测试笔记 * @博客 : www.cnblogs.com/uncleyong * @微信 : ren168632201 * @描述 : <> */public class User {  private Integer id;  // 实体类推荐用包装类  private String useame;  private String password;  private String realName;  private String sex;  private Date birthday;  private String phone;  private String utype;  private String addtime;  private String adduser;  public User() {  }  public User(String useame, String password, String realName, String sex, Date birthday, String phone, String utype,String adduser, String addtime) {this.useame = useame;this.password = password;this.realName = realName;this.sex = sex;this.birthday = birthday;this.phone = phone;this.utype = utype;this.adduser = adduser;this.addtime = addtime;  }  public Integer getId() {retu id;  }  public void setId(Integer id) {this.id = id;  }  public String getUseame() {retu useame;  }  public void setUseame(String useame) {this.useame = useame;  }  public String getPassword() {retu password;  }  public void setPassword(String password) {this.password = password;  }  public String getRealName() {retu realName;  }  public void setRealName(String realName) {this.realName = realName;  }  public String getSex() {retu sex;  }  public void setSex(String sex) {this.sex = sex;  }  public Date getBirthday() {retu birthday;  }  public void setBirthday(Date birthday) {this.birthday = birthday;  }  public String getPhone() {retu phone;  }  public void setPhone(String phone) {this.phone = phone;  }  public String getUtype() {retu utype;  }  public void setUtype(String utype) {this.utype = utype;  }  public String getAddtime() {retu addtime;  }  public void setAddtime(String addtime) {this.addtime = addtime;  }  public String getAdduser() {retu adduser;  }  public void setAdduser(String adduser) {this.adduser = adduser;  }  @Override  public String toString() {retu "User{" +"id=" + id +", useame='" + useame + '\'' +", password='" + password + '\'' +", realName='" + realName + '\'' +", sex='" + sex + '\'' +", birthday=" + birthday +", phone='" + phone + '\'' +", utype='" + utype + '\'' +", addtime=" + addtime +", adduser='" + adduser + '\'' +'}';  }}

pom依赖

<?xml version="1.0" encoding="UTF-8"?><project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><groupId>com.qzcsbj.mybatisplus</groupId><artifactId>mybatisplus</artifactId><version>1.0-SNAPSHOT</version><dependencies><!-- mybatis-plus --><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus</artifactId><version>3.4.2</version></dependency><!-- mysql驱动 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>5.1.47</version></dependency><!-- https://mvnrepository.com/artifact/log4j/log4j --><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency><!--单元测试--><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>4.12</version><scope>test</scope></dependency></dependencies><build><resources><resource><directory>src/main/java</directory><includes><include>**/*.xml</include></includes></resource></resources></build></project>

说明：mybatis-plus包含了mybatis等相关依赖

添加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><properties resource="jdbc.properties"></properties><!--实体类取别名--><typeAliases><!--方法一：别名可以随意定义，比如：XXX，但是最好和类名一样，见名知意--><!--<typeAlias type="com.qzcsbj.bean.User22" alias="User2"/>--><!--方法二（推荐）：直接给所有的实体类取别名--><package name="com.qzcsbj.bean"/></typeAliases><!--配置MyBatis连接数据库--><!--环境--><environments default="development"><environment id="development"><transactionManager type="JDBC"/><dataSource type="POOLED"><property name="driver" value="${jdbc.driver}"/><property name="url" value="${jdbc.url}"/><property name="useame" value="${jdbc.useame}"/><property name="password" value="${jdbc.password}"/></dataSource></environment></environments><!--引入映射文件--><mappers><!--方法一：路径相对于资源目录根路径--><!--<mapper resource="com\qzcsbj\mapper\UserMapper.xml"/>--><!--<mapper resource="com/qzcsbj/mapper/UserMapper.xml"/>--><!--方法二：写接口全路径，此时映射文件名必须和接口名一样--><!--<mapper class="com.qzcsbj.mapper.UserMapper"/>--><!--方法三（推荐）：写接口的包名，直接映射包的名字--><package name="com.qzcsbj.mapper"/></mappers></configuration>

添加log4j.properties

### 根logger 设置###log4j.rootLogger=DEBUG, Console### 输出信息到控制台 ###log4j.appender.Console=org.apache.log4j.ConsoleAppenderlog4j.appender.Console.layout=org.apache.log4j.PatteLayoutlog4j.appender.Console.layout.ConversionPatte=%d [%t] %-5p [%c] - %m%nlog4j.logger.java.sql.ResultSet=INFOlog4j.logger.org.apache=INFOlog4j.logger.java.sql.Connection=DEBUGlog4j.logger.java.sql.Statement=DEBUGlog4j.logger.java.sql.PreparedStatement=DEBUG

添加jdbc连接信息配置：jdbc.properties

jdbc.driver=com.mysql.jdbc.Driverjdbc.url=jdbc:mysql://192.168.117.180:3306/gift?useUnicode=true&characterEncoding=utf-8&useSSL=truejdbc.useame=rootjdbc.password=qzcsbj

工具类MyBatisUtils中集成mybatis-plus：使用MyBatisPlus的Session工厂

package com.qzcsbj.utils;import com.baomidou.mybatisplus.core.MybatisSqlSessionFactoryBuilder;import org.apache.ibatis.io.Resources;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import java.io.IOException;import java.io.InputStream;/** * @公众号 : 全栈测试笔记 * @博客 : www.cnblogs.com/uncleyong * @微信 : ren168632201 * @描述 : <> */public class MyBatisUtils {public static SqlSession getSession(){SqlSession session=null;InputStream inputStream=null;try {inputStream = Resources.getResourceAsStream("mybatis-config.xml");// 使用MyBatisPlus的Session工厂SqlSessionFactory sqlSessionFactory = new MybatisSqlSessionFactoryBuilder().build(inputStream);session = sqlSessionFactory.openSession(true);  // mybatis增、删、改要手动提交事务，true表示自动提交事务retu session;} catch (IOException e) {e.printStackTrace();}finally {if (inputStream != null){try {inputStream.close();} catch (IOException e) {e.printStackTrace();}}}retu null;}public static void closeSession(SqlSession session){if(session != null){session.close();}}}

CRUD实现方式

　　1、基于mybatis编写UserMapper接口、CRUD方法，提供UserMapper.xml映射文件，并手动编写每个方法对应的SQL语句

　　2、基于mybatis-plus（MP）创建UserMapper接口，并继承BaseMapper接口，然后就可以使用MP完成相关操作，甚至不需要创建SQL映射文件（参考：https://baomidou.com/pages/10c804/#abstractwrapper），说明：只适用于单表

mapper接口

package com.qzcsbj.mapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.qzcsbj.bean.User;import java.util.List;/** * @公众号 : 全栈测试笔记 * @博客 : www.cnblogs.com/uncleyong * @微信 : ren168632201 * @描述 : <> */public interface UserMapper extends BaseMapper<User> {}

这个父接口里面有很多方法，mybatis-plus已经给我们写好了，直接调用即可

实体类注解

@TableName

@TableId

@TableField

package com.qzcsbj.bean;import com.baomidou.mybatisplus.annotation.IdType;import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableId;import com.baomidou.mybatisplus.annotation.TableName;import java.util.Date;/** * @公众号 : 全栈测试笔记 * @博客 : www.cnblogs.com/uncleyong * @微信 : ren168632201 * @描述 : <> */@TableName(value = "user")  // MybatisPlus默认会使用实体类类名去数据库找对应的表，如果实体类类名和表名一样，这个注解可以省略public class User {  @TableId(value = "id", type = IdType.AUTO)  // value是表中主键列的列名，如果和实体类属性名一样，可以省略不指定；type指定主键策略，IdType.AUTO表示这个字段在数据库表中是主键、且自增长  private Integer id;  // 实体类推荐用包装类  // @TableField("useame")  // 如果不写，默认有一个@TableField("useame")  private String useame;  private String password;  // @TableField("real_name")  // 可以省略不写，因为mybatisplus默认支持  private String realName;  private String sex;  private Date birthday;  private String phone;  private String utype;  private String addtime;  private String adduser;  @TableField(exist = false)  // 忽略这个字段，和数据库没有映射关系，也就是表中没这个字段  private String job;  public User() {  }  public User(String useame, String password, String realName, String sex, Date birthday, String phone, String utype,String adduser, String addtime) {this.useame = useame;this.password = password;this.realName = realName;this.sex = sex;this.birthday = birthday;this.phone = phone;this.utype = utype;this.adduser = adduser;this.addtime = addtime;  }  public Integer getId() {retu id;  }  public void setId(Integer id) {this.id = id;  }  public String getUseame() {retu useame;  }  public void setUseame(String useame) {this.useame = useame;  }  public String getPassword() {retu password;  }  public void setPassword(String password) {this.password = password;  }  public String getRealName() {retu realName;  }  public void setRealName(String realName) {this.realName = realName;  }  public String getSex() {retu sex;  }  public void setSex(String sex) {this.sex = sex;  }  public Date getBirthday() {retu birthday;  }  public void setBirthday(Date birthday) {this.birthday = birthday;  }  public String getPhone() {retu phone;  }  public void setPhone(String phone) {this.phone = phone;  }  public String getUtype() {retu utype;  }  public void setUtype(String utype) {this.utype = utype;  }  public String getAddtime() {retu addtime;  }  public void setAddtime(String addtime) {this.addtime = addtime;  }  public String getAdduser() {retu adduser;  }  public void setAdduser(String adduser) {this.adduser = adduser;  }  @Override  public String toString() {retu "User{" +"id=" + id +", useame='" + useame + '\'' +", password='" + password + '\'' +", realName='" + realName + '\'' +", sex='" + sex + '\'' +", birthday=" + birthday +", phone='" + phone + '\'' +", utype='" + utype + '\'' +", addtime=" + addtime +", adduser='" + adduser + '\'' +'}';  }}

示例：基础CRUD之增

测试类

package com.qzcsbj;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.qzcsbj.bean.User;import com.qzcsbj.mapper.UserMapper;import com.qzcsbj.utils.MyBatisUtils;import org.apache.ibatis.session.SqlSession;import org.junit.After;import org.junit.Before;import org.junit.Test;import java.text.SimpleDateFormat;import java.util.*;/** * @公众号 : 全栈测试笔记 * @博客 : www.cnblogs.com/uncleyong * @微信 : ren168632201 * @描述 : <> */public class testMybatis {SqlSession session = null;UserMapper userMapper = null;@Beforepublic void init(){System.out.println("初始化。。。");session = MyBatisUtils.getSession();userMapper = session.getMapper(UserMapper.class);}@Afterpublic void destory(){System.out.println("关闭session");MyBatisUtils.closeSession(session);}// 新增用户@Testpublic void testAddUser(){User user = new User("qzcsbj9","123456","qzcsbj9","男",new Date(),"13800000009","1","qzcsbj",new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));int n = userMapper.insert(user);System.out.println(n>0?"新增成功":"新增失败");}}

结果　

示例：基础CRUD之删

// 根据id删除@Testpublic void testDeleteById(){int n = userMapper.deleteById(9);System.out.println(n>0?"删除成功":"删除失败");}

// 根据map删除（单条件）@Testpublic void testDeleteByMap(){Map<String,Object> map = new HashMap<String, Object>();map.put("real_name","qzcsbj5");  // 根据真实名删除数据；这里写的是数据库里面的列名int n = userMapper.deleteByMap(map);System.out.println(n>0?"删除成功":"删除失败");}

// 根据map删除（多条件，sql的条件是and关系，说明：这里条件只能是等值关系，不能是大于小于之类的）@Testpublic void testDeleteByMap2(){Map<String,Object> map = new HashMap<String, Object>();map.put("sex","男");map.put("utype","1");int n = userMapper.deleteByMap(map);System.out.println(n>0?"删除成功":"删除失败");}

// 批量删除，sql的条件是in@Testpublic void testDeleteBatch(){List<Integer> ids = new ArrayList<Integer>();ids.add(3);ids.add(4);int n = userMapper.deleteBatchIds(ids);System.out.println(n>0?"删除成功":"删除失败");}

示例：基础CRUD之改

// 根据id更新@Testpublic void testUpdateById(){User user = userMapper.selectById(1);user.setUseame("qzcsbj1-1");user.setRealName("qzcsbj1-1");int n = userMapper.updateById(user);System.out.println(n>0?"更新成功":"更新失败");}

示例：基础CRUD之查

// 根据id查找@Testpublic void testSelectById(){User user = userMapper.selectById(1);System.out.println(user);}

// 根据map查找（多个条件是and关系）@Testpublic void testSelectByMap(){Map<String,Object> map = new HashMap<String, Object>();map.put("sex","男");map.put("utype","0");List<User> users = userMapper.selectByMap(map);for (User user : users) {System.out.println(user);}}

// 批量查找@Testpublic void testSelectBatch(){List<Integer> ids = new ArrayList<Integer>();ids.add(1);ids.add(2);List<User> users = userMapper.selectBatchIds(ids);for (User user : users) {System.out.println(user);}}

条件构造器

参考：https://baomidou.com/pages/10c804/#abstractwrapper

Wrapper类型的泛型参数，就是需要传一个条件构造器

示例：需要条件构造器之增

无方法

示例：需要条件构造器之删

@Testpublic void testDelete(){int n = userMapper.delete(new QueryWrapper<User>().eq("id", 1));System.out.println(n>0?"删除成功":"删除失败");}

示例：需要条件构造器之改

@Testpublic void testUpdate(){User user = new User("qzcsbj10","123456","qzcsbj10","男",new Date(),"13800000010","1","qzcsbj",new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));int n = userMapper.update(user, new UpdateWrapper<User>().eq("id", 1));System.out.println(n>0?"更新成功":"更新失败");}

@Testpublic void testUpdate2(){User user = new User();user.setSex("男");int n = userMapper.update(user, new UpdateWrapper<User>().eq("id", 2));System.out.println(n>0?"更新成功":"更新失败");}

示例：需要条件构造器之查

无条件

// 如果传null，就是查找所有，select * from user@Testpublic void testSelectAll(){List<User> users = userMapper.selectList(null);for (User user : users) {System.out.println(user);}}

多条件：and

 // select * from user where id>=2 and sex = '女';@Testpublic void testQuery(){QueryWrapper<User> qw = new QueryWrapper<User>();// 多个条件，默认是and关系qw.ge("id",2);qw.eq("sex","女");List<User> users = userMapper.selectList(qw);for (User user : users) {System.out.println(user);}}

多条件：or

// select * from user where id>=2 or sex = '男';@Testpublic void testQuery2(){QueryWrapper<User> qw = new QueryWrapper<User>();qw.ge("id",2);qw.or();  // 设置多个条件是or的关系qw.eq("sex","男");List<User> users = userMapper.selectList(qw);for (User user : users) {System.out.println(user);}}

多条件：or和and混用

// select * from user where id>=2 or (sex = '男' and id<=8);@Testpublic void testQuery3(){QueryWrapper<User> qw = new QueryWrapper<User>();qw.ge("id",2);qw.or(i -> i.eq("sex","男").le("id",8));  // lambdas表达式List<User> users = userMapper.selectList(qw);for (User user : users) {System.out.println(user);}}

between and

// 查一个范围，select * from user where id between 1 and 2 and utype='0';@Testpublic void testQuery4(){List<User> users = userMapper.selectList(new QueryWrapper<User>().between("id", 1, 2).eq("utype","0"));for (User user : users) {System.out.println(user);}}

// 查一个范围，select * from user where id between 1 and 2 or utype='0';@Testpublic void testQuery5(){List<User> users = userMapper.selectList(new QueryWrapper<User>().between("id", 1, 2).or().eq("utype","0"));for (User user : users) {System.out.println(user);}}

in

@Testpublic void testIn(){List<Integer> ids = new ArrayList<>();ids.add(1);ids.add(2);List<User> users = userMapper.selectList(new QueryWrapper<User>().in("id", ids));for (User user : users) {System.out.println(user);}}

模糊查询

// 模糊查询，select * from user where useame like "%qzcs%";@Testpublic void testLike(){List<User> users = userMapper.selectList(new QueryWrapper<User>().like("useame", "qzcs"));for (User user : users) {System.out.println(user);}}

// 模糊查询，select * from user where useame not like "%qzcs%";@Testpublic void testNotLike(){List<User> users = userMapper.selectList(new QueryWrapper<User>().notLike("useame", "qzcs"));for (User user : users) {System.out.println(user);}}

// 模糊查询，select * from user where useame like "qzcs%";@Testpublic void testRightLike(){List<User> users = userMapper.selectList(new QueryWrapper<User>().likeRight("useame", "qzcs"));for (User user : users) {System.out.println(user);}}

// 模糊查询，select * from user where useame like "%bj2";@Testpublic void testLeftLike(){List<User> users = userMapper.selectList(new QueryWrapper<User>().likeLeft("useame", "bj2"));for (User user : users) {System.out.println(user);}}

null、not null

// 非空，select * from user where sex is not null;@Testpublic void testNotNull(){List<User> users = userMapper.selectList(new QueryWrapper<User>().isNotNull("sex"));for (User user : users) {System.out.println(user);}}

查总数

// 查总数，select count(*) from user where sex is not null;@Testpublic void testCount(){Integer n = userMapper.selectCount(new QueryWrapper<User>().isNotNull("sex"));System.out.println("总数为：" + n);}

排序

// 排序，一个排序字段，select * from user where id between 1 and 2 or utype='0' order by id asc;@Testpublic void testQuery6(){List<User> users = userMapper.selectList(new QueryWrapper<User>().between("id", 1, 2).or().eq("utype","0").orderByAsc("id"));for (User user : users) {System.out.println(user);}}

// 排序，多个排序字段，select * from user where id between 1 and 2 or utype='0' order by id asc, useame desc;@Testpublic void testQuery7(){List<User> users = userMapper.selectList(new QueryWrapper<User>().between("id", 1, 2).or().eq("utype","0").orderByAsc("id").orderByDesc("useame"));for (User user : users) {System.out.println(user);}}

聚合查询

// 聚合查询：select max(id),min(id) from user@Testpublic void testQuery8(){List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id)", "max(id)"));for (Map<String, Object> list : lists) {System.out.println(list);}}

@Testpublic void testQuery8_2(){// List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id)", "max(id)"));List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id),max(id)"));System.out.println(lists.size());Map<String, Object> map = lists.get(0);Set<Map.Entry<String, Object>> entries = map.entrySet();for (Map.Entry<String, Object> entry : entries) {System.out.println(entry.getKey() + "--->" + entry.getValue());}}

聚合、分组查询

// 分组查询：select max(id),min(id),sex from user group by sex@Testpublic void testQuery9(){// List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id)", "max(id)","sex").groupBy("sex"));List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id), max(id),sex").groupBy("sex").orderByAsc("min(id)"));for (Map<String, Object> list : lists) {System.out.println(list);}}

// 分组查询：select max(id),min(id),sex from user group by sex@Testpublic void testQuery9_2(){// List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id)", "max(id)","sex").groupBy("sex"));List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id), max(id),sex").groupBy("sex").orderByAsc("min(id)"));System.out.println(lists.size());for (Map<String, Object> map : lists) {Set<Map.Entry<String, Object>> entries = map.entrySet();for (Map.Entry<String, Object> entry : entries) {System.out.println(entry.getKey() + "--->" + entry.getValue());}}}

分组条件having

// 分组查询：select max(id),min(id),sex from user group by sex having min(id)>1 order by min(id) asc@Testpublic void testQuery10(){// List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id)", "max(id)","sex").groupBy("sex"));List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id), max(id),sex").groupBy("sex").having("min(id)>1").orderByAsc("min(id)"));for (Map<String, Object> list : lists) {System.out.println(list);}}

// 分组查询：select max(id),min(id),sex from user group by sex having min(id)>1 order by min(id) asc@Testpublic void testQuery10_2(){// List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id)", "max(id)","sex").groupBy("sex"));List<Map<String, Object>> lists = userMapper.selectMaps(new QueryWrapper<User>().select("min(id), max(id),sex").groupBy("sex").having("min(id)>1").orderByAsc("min(id)"));for (Map<String, Object> map : lists) {Set<Map.Entry<String, Object>> entries = map.entrySet();for (Map.Entry<String, Object> entry : entries) {System.out.println(entry.getKey() + "--->" + entry.getValue());}}}