Mybatis-plus
1.入门
依赖
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.2.6.RELEASE</version>
</parent>
<dependencies>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.17</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
<scope>provided</scope>
</dependency>
</dependencies>
数据源
#mysql
spring.datasource.url=jdbc:mysql:///mybatis_plus01?useUnicode=true&characterEncoding=utf8&&serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=admin
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
# 配置slq打印日志
logging.level.cn.wolfcode.mp.mapper=debug
启动类
@SpringBootApplication
@MapperScan(basePackages = "cn.wolfcode.mybatis.mapper")
public class App {
}
mapper
public interface EmployeeMapper extends BaseMapper<Employee> {
}
实体类
@Setter
@Getter
@ToString
public class Employee {
private Long id;
private String name;
private String password;
private String email;
private int age;
private int admin;
private Long deptId;
}
测试类
mapper -> insert,deleteById,updateById,selectById,selectList
@SpringBootTest
public class CRUDTest {
@Autowired
private EmployeeMapper employeeMapper;
@Test
public void testSave(){
Employee employee = new Employee();
employee.setAdmin(1);
employee.setAge(18);
employee.setDeptId(1L);
employee.setEmail("dafei@wolfcode");
employee.setName("dafei");
employee.setPassword("111");
employeeMapper.insert(employee);
}
@Test
public void testUpdate(){
Employee employee = new Employee();
employee.setId(1327139013313564673L);
employee.setAdmin(1);
employee.setAge(18);
employee.setDeptId(1L);
employee.setEmail("dafei@wolfcode.cn");
employee.setName("xiaofei");
employee.setPassword("111");
employeeMapper.updateById(employee);
}
@Test
public void testDelete(){
employeeMapper.deleteById(1327139013313564673L);
}
@Test
public void testGet(){
System.out.println(employeeMapper.selectById(1327139013313564673L));
}
@Test
public void testList(){
System.out.println(employeeMapper.selectList(null));
}
}
实体类注解
//@TableName("数据库表名")
@TableName("employee_copy")
public class Employee {
@TableId(type = IdType.AUTO) //设置雪花算法为自动增长
private Long id;
@TableField(value = "name") //设置列名
private String namexxx;
private String password;
private String email;
private int age;
private int admin;
private Long deptId;
@TableField(exist = false) //列名不存在反射不会报异常
private String test;
}
优化日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
2.接口方法明细
2.1.新增
属性值为空不会拼接字段
@Test
public void testSave() {
Employee employee = new Employee();
employee.setAdmin(1);
//employee.setAge(100); //数值值没有添加的数据就为空
//employee.setDeptId(1L);
employee.setEmail("dafei@wolfcode");
employee.setNamexxx("dafei");
employee.setPassword("111");
employeeMapper.insert(employee);
}
2.2.修改
updateById：全字段修改
@Test
public void testUpdate() {
Employee employee = new Employee();
employee.setId(1408779223650152452L);
employee.setAdmin(1);
//employee.setAge(100);
//employee.setDeptId(1L);
//employee.setEmail("dafei@wolfcode.cn");
//employee.setNamexxx("xiaofei");
//employee.setPassword("111");
//不希望出现为空的情况需要要基本类型设置成包装类型
employeeMapper.updateById(employee);
}
update：部分字段修改
@Test
public void testUpdate02() {
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
//理解拼接where的sql语句片段
wrapper.set("password","12345");
wrapper.eq("name","xiaofei");
employeeMapper.update(null,wrapper);
//update()第一个参数还可以替换成实体类对象，感觉有点冗余
}
2.3.删除
单个id，多个id,map,wrapper
deleteById
@Test
public void testDelete01() {
employeeMapper.deleteById(1408779223650152450L);
}
deleteBatChIds
@Test
public void testDelete02() {
employeeMapper.deleteBatchIds(Arrays.asList("1408779223650152451L","1408779223650152452L"));
}
deleteByMap
@Test
public void testDelete03() {
HashMap<String,Object> hashMap = new HashMap<>();
hashMap.put("name","Yupeng");
hashMap.put("password","111");
employeeMapper.deleteByMap(hashMap);
}
delete
@Test
public void testDelete04() {
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.eq("name","Yupeng");
wrapper.eq("password","112");
employeeMapper.delete(wrapper);
}
2.4.查询
selectById
@Test
public void testSelect01() {
System.out.println(employeeMapper.selectById(1L));
}
selectBatchIds
@Test
public void testSelect02() {
System.out.println(employeeMapper.selectBatchIds(Arrays.asList(1L,2L,3L)));
}
selectByMap
@Test
public void testSelect03() {
HashMap map = new HashMap();
map.put("password","1");
System.out.println(employeeMapper.selectByMap(map));
}
selectCount
@Test
public void testSelect04() {
System.out.println(employeeMapper.selectCount(null)); //null表示没有条件，即查询所有
}
selectList
@Test
public void testSelect05() {
List<Employee> employees = employeeMapper.selectList(null);
for(Employee employee:employees){
System.out.println(employee);
}
}
selectMaps
@Test
public void testSelect06() {
//查询出来的数据如果不能封装成一个类则采用map
List<Map<String, Object>> maps = employeeMapper.selectMaps(null);
for (Map<String, Object> map : maps) {
System.out.println(map);
}
}
分页
启动类配置分页
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
paginationInnerInterceptor.setOverflow(true); //合理化
interceptor.addInnerInterceptor(paginationInnerInterceptor);
return interceptor;
}
分页逻辑 selectPage
@Test
public void testSelect07(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//参数1：当前页， 参数2：每页显示条数
IPage<Employee> page = new Page<>(2, 3);
//分页参数对象，条件构造器（可以为null为没有数据的对象）
employeeMapper.selectPage(page, wrapper);
System.out.println("当前页：" + page.getCurrent());
System.out.println("每页显示条数：" + page.getSize());
System.out.println("总页数：" + page.getPages());
System.out.println("总数：" + page.getTotal());
System.out.println("当前页数据：" + page.getRecords());
}
其他
selectOne()
selectObject():查一个字段
selectMapPage():查询一个map的分页结果
3.条件构造器
拼接SQL片段
3.1.共同方法
allEq
eq
ne
gt
ge
lt
le
between
notBetween
like
notLike
likeLeft
likeRight
isNull
isNotNull
in
notIn
inSql
notInSql
groupBy
orderByAsc
orderByDesc
orderBy
having
func
or
and
nested
apply
last
exists
notExists
3.2.工具类
@Test
public void testWraaper() {
UpdateWrapper<Employee> wrapperUpdate01 = new UpdateWrapper<>();
UpdateWrapper<Employee> wrapperUpdate02 = Wrappers.<Employee>update();
LambdaUpdateWrapper<Employee> wrapperUpdate03 = new LambdaUpdateWrapper<>();
LambdaUpdateWrapper<Employee> wrapperUpdate04 = Wrappers.<Employee>lambdaUpdate();
LambdaUpdateWrapper<Employee> wrapperUpdate05 = wrapperUpdate01.lambda();
QueryWrapper<Employee> wrapperQuery01 = new QueryWrapper<>();
QueryWrapper<Employee> wrapperQuery02 = Wrappers.<Employee>query();
LambdaQueryWrapper<Employee> wrapperQuery03 = new LambdaQueryWrapper<>();
LambdaQueryWrapper<Employee> wrapperQuery04 = Wrappers.<Employee>lambdaQuery();
LambdaQueryWrapper<Employee> wrapperQuery05 = wrapperQuery01.lambda();
}
3.3.UpdateWrapper
原始
@Test
public void testWraaper01() {
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
//set方法的第一个参数可以是boolean来判空
//wrapper.set(true,"password","123456");
wrapper.set("password","123456"); //占位符
wrapper.eq("name","xiaofei");
//不希望出现为空的情况需要要基本类型设置成包装类型
employeeMapper.update(null,wrapper);
}
拼接SQL片段
@Test
public void testWraaper02() {
UpdateWrapper<Employee> wrapper = new UpdateWrapper<>();
wrapper.setSql("password=1234"); //直接塞
wrapper.eq("name","xiaofei");
//不希望出现为空的情况需要要基本类型设置成包装类型
employeeMapper.update(null,wrapper);
}
LambdaUpdateWrapper
@Test
public void testWraaper03() {
LambdaUpdateWrapper<Employee> wrapper = new LambdaUpdateWrapper<>();
wrapper.set(Employee::getPassword,90); //通过get方法解析属性，避免手误写错
wrapper.eq(Employee::getId,20L);
//不希望出现为空的情况需要要基本类型设置成包装类型
employeeMapper.update(null,wrapper);
}
3.4.QueryWrapper
原始
@Test
public void testWraaper04(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.eq("name", "xiaofei") //可以链式
.eq("age", 0);
System.out.println(employeeMapper.selectList(wrapper));
}
Lambda
@Test
public void testWraaper05(){
LambdaQueryWrapper<Employee> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(Employee::getNamexxx, "xiaofei") //可以链式
.eq(Employee::getAge, 0);
System.out.println(employeeMapper.selectList(wrapper));
}
4.高級查詢
4.1.投影
//查询所有员工，只要passwrod和age
@Test
public void testQuery1(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//wrapper.select("password","age"); //占位符
wrapper.select("password,age"); //Sql
List<Employee> employees = employeeMapper.selectList(wrapper);
}
4.2.排序
//age正排序，id正排序
@Test
public void testQuery2(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//需要注意排序的顺序
//wrapper.orderByAsc("age");
//wrapper.orderByAsc("id");
//wrapper.orderByDesc("id");
//参数一满足才执行条件，参数二正序，参数三列名
wrapper.orderBy(true,true,"age");
//wrapper.orderByAsc();
//wrapper.orderByDesc();
List<Employee> employees = employeeMapper.selectList(wrapper);
}
4.3.分组
//按照部门进行分组，并查询每个部门员工的数量
@Test
public void testQuery3(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.select("dept_id","count(id) count");
wrapper.groupBy("dept_id");
List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper);
for(Map<String,Object> map:maps){
System.out.println(map);
}
}
4.4.分组过滤
////按照部门进行分组，并查询每个部门员工的数量,过滤大于3
@Test
public void testQuery4(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.select("dept_id","count(id) count");
wrapper.groupBy("dept_id");
//wrapper.having("count > 3");
wrapper.having("count > {0}",3);
List<Map<String, Object>> maps = employeeMapper.selectMaps(wrapper);
for(Map<String,Object> map:maps){
System.out.println(map);
}
}
4.5.条件查询
//查询name = dafei,age = 18
@Test
public void testQuery5(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//Map<String,Object> map =new HashMap<>();
//必须等全部相等才能够匹配
//map.put("age",18);
//map.put("password","1234");
//wrapper.allEq(map);
//wrapper.eq("age",18);
//wrapper.ne("age",18);
wrapper.gt("age",10);
//wrapper.ge("age",10);
employeeMapper.selectList(wrapper);
}
4.6.范围查询
//范围查询
@Test
public void testQuery6(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.between("age",18,30);
//wrapper.notBetween("age",18,30);
employeeMapper.selectList(wrapper);
}
4.7.空值
//查询字段值为null的
@Test
public void testQuery7(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.isNull("dept_id");
//wrapper.notBetween("age",18,30);
employeeMapper.selectList(wrapper);
}
4.8.in
//查询id为1和2的信息
@Test
public void testQuery8(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//占位符
wrapper.in("id",1L,2L);
//SQL片段
//wrapper.inSql("id","1,2");
employeeMapper.selectList(wrapper);
}
4.9.模糊查询
//模糊查询
@Test
public void testQuery9(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//%fei%
wrapper.like("name","fei");
//fei%
//wrapper.likeRight("name","fei");
//%fei
//wrapper.likeLeft("name","fei");
employeeMapper.selectList(wrapper);
}
4.10.逻辑查询
//and or
//查询age = 18 或者 name = dafei 或者 id = 1的用户
@Test
public void testQuery10(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//wrapper.eq("age",18);
//wrapper.or();
//wrapper.eq("name","dafei");
//wrapper.or();
//wrapper.eq("id",1L);
wrapper.eq("age",18)
.or().eq("name","dafei")
.or().eq("id",1L);
employeeMapper.selectList(wrapper);
}
//查询name = dafei 或者 年龄在某个范围内
//不用between
//select * from employee where (name like '%fei%) or (age>=18 and age<=30)
@Test
public void testQuery11(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//结果是错的
//默认and
/*wrapper.like("name","fei");
wrapper.or();
wrapper.ge("age",18);
wrapper.le("age",30);*/
//正确写法
wrapper.like("name","fei");
//当成or后面在拼接一个wrapper
wrapper.or(
wp->wp.ge("age",18).le("age",30)
);
employeeMapper.selectList(wrapper);
}
//查询name = dafei 并且 年龄不在某个范围内
//select * from employee where (name like '%fei%) and (age>=18 and age<=30)
@Test
public void testQuery12(){
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
//正确写法
wrapper.like("name","fei");
//当成or后面在拼接一个wrapper
wrapper.and(
wp->wp.lt("age",18).or().gt("age",30)
);
employeeMapper.selectList(wrapper);
}
5.自定义SQL
5.1.正常
5.2.注解
5.3.连表的额外SQL
@Test
public void testQuery15(){
//在不写sql的前提下，使用额外查询的方式,提高性能的话老老实实写吧
List<Employee> employees = employeeMapper.selectList(null);
for (Employee employee : employees) {
employee.setDept(departmentMapper.selectById(employee.getDeptId()));
}
}
6.业务层
insert -> save
deleteById -> removeById
selectById -> getById
selectList -> list
6.1.接口处理
业务接口
/**
* mybatis-plus
* 继承通用接口
* 指定泛型
*/
public interface IEmployeeSerivce extends IService<Employee> {
}
业务实现类
/**
* 实现类也得继承
*/
@Service
public class EmployeeService extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeSerivce {
@Override
public IPage<Employee> queryPage(EmployeeQueryObject queryObject) {
}
}
6.2.常用方法
getBaseMapper
getOne(wrapper)
list(wrapper) //mapper的selectList
page(page,wrpper)
6.3.分页
接口
public interface IEmployeeSerivce extends IService<Employee> {
IPage<Employee> queryPage(EmployeeQueryObject queryObject);
}
实现类
@Service
public class EmployeeService extends ServiceImpl<EmployeeMapper, Employee> implements IEmployeeSerivce {
@Override
public IPage<Employee> queryPage(EmployeeQueryObject queryObject) {
IPage<Employee> page = new Page<>(queryObject.getCurrentPage(),queryObject.getPageSize());
QueryWrapper<Employee> wrapper = new QueryWrapper<>();
wrapper.like(StringUtils.hasText(queryObject.getKeyWord()),"name",queryObject.getKeyWord());
return super.page(page,wrapper);
}
}
6.4.事务
在接口的实现类上@Tranxxxx就行了
