一、使用条件构造器的自定义SQL
1、使用注解的方式
public interface UserMapper extends BaseMapper<User> { @Select("select * from user ${ew.customSqlSegment}") List<User> selectAll(@Param("ew") Wrapper<User> queryWrapper); }
写法就是参照源码：@Param("ew") 也就是 @Param(Constants.WRAPPER)
测试
@Test public void selectMy() { LambdaQueryWrapper<User> lambdaQuery = Wrappers.<User>lambdaQuery(); lambdaQuery.likeRight(User::getName, "王") .and(lqw -> lqw.lt(User::getAge, 40).or().isNotNull(User::getEmail)); List<User> list = userMapper.selectAll(lambdaQuery); list.forEach(System.out::println); }
控制台输出
DEBUG==> Preparing: select * from user WHERE name LIKE ? AND ( age < ? OR email IS NOT NULL ) DEBUG==> Parameters: 王%(String), 40(Integer) TRACE<== Columns: id, name, age, email, manager_id, create_time TRACE<== Row: 1088248166370832385, 王天风, 25, wtf@baomidou.com, 1087982257332887553, 2019-02-05 11:12:22 DEBUG<== Total: 1 User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=Tue Feb 05 11:12:22 CST 2019, remark=null)
2、使用xml文件的方式
public interface UserMapper extends BaseMapper<User> { List<User> selectAll(@Param(Constants.WRAPPER) Wrapper<User> queryWrapper); }
xml文件内容，这里没有where
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mp.dao.UserMapper"> <select id="selectAll" resultType="com.mp.entity.User"> select * from user ${ew.customSqlSegment} </select> </mapper>
注意需要扫描xml文件
# Mybatis Plus配置
mybatis-plus:
mapper-locations: classpath*:mapper/**/*.xml
二、分页插件
1、配置分页插件
package com.mp.config; import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; @Configuration public class MybatisPlusConfig { @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); } }
2、使用Page类进行分页
@Test public void selectPage() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.ge("age", 20); Page page = new Page(1, 2); IPage iPage = userMapper.selectPage(page, queryWrapper); System.out.println("总页数：" + iPage.getPages()); System.out.println("总记录数：" + iPage.getTotal()); List<User> list = (List<User>)iPage.getRecords(); list.forEach(System.out::println); } @Test public void selectPage2() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.ge("age", 20); Page page = new Page(1, 2); IPage iPage = userMapper.selectMapsPage(page, queryWrapper); System.out.println("总页数：" + iPage.getPages()); System.out.println("总记录数：" + iPage.getTotal()); List<Map<String, Object>> list = (List<Map<String, Object>>) iPage.getRecords(); list.forEach(System.out::println); }
控制台输出：
DEBUG==> Preparing: SELECT COUNT(1) FROM user WHERE age >= ? DEBUG==> Parameters: 20(Integer) TRACE<== Columns: COUNT(1) TRACE<== Row: 6 DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE age >= ? LIMIT ?,? DEBUG==> Parameters: 20(Integer), 0(Long), 2(Long) TRACE<== Columns: id, name, age, email, manager_id, create_time TRACE<== Row: 1087982257332887553, 大boss, 40, boss@baomidou.com, null, 2019-01-11 14:20:20 TRACE<== Row: 1088248166370832385, 王天风, 25, wtf@baomidou.com, 1087982257332887553, 2019-02-05 11:12:22 DEBUG<== Total: 2 总页数：3 总记录数：6 User(id=1087982257332887553, name=大boss, age=40, email=boss@baomidou.com, managerId=null, createTime=Fri Jan 11 14:20:20 CST 2019, remark=null) User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=Tue Feb 05 11:12:22 CST 2019, remark=null)
不查记录数的分页方式：new Page(1, 2, false);
@Test public void selectPage() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.ge("age", 20); Page page = new Page(1, 2, false); IPage iPage = userMapper.selectPage(page, queryWrapper); System.out.println("总页数：" + iPage.getPages()); System.out.println("总记录数：" + iPage.getTotal()); List<User> list = (List<User>)iPage.getRecords(); list.forEach(System.out::println); }
控制台输出：
DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE age >= ? LIMIT ?,? DEBUG==> Parameters: 20(Integer), 0(Long), 2(Long) TRACE<== Columns: id, name, age, email, manager_id, create_time TRACE<== Row: 1087982257332887553, 大boss, 40, boss@baomidou.com, null, 2019-01-11 14:20:20 TRACE<== Row: 1088248166370832385, 王天风, 25, wtf@baomidou.com, 1087982257332887553, 2019-02-05 11:12:22 DEBUG<== Total: 2 总页数：0 总记录数：0 User(id=1087982257332887553, name=大boss, age=40, email=boss@baomidou.com, managerId=null, createTime=Fri Jan 11 14:20:20 CST 2019, remark=null) User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou.com, managerId=1087982257332887553, createTime=Tue Feb 05 11:12:22 CST 2019, remark=null)
3、多表联查的分页
演示的单表但多表联查也是一样
public interface UserMapper extends BaseMapper<User> { IPage<User> selectUserPage(Page<User> page, @Param(Constants.WRAPPER) Wrapper<User> queryWrapper); }
xml文件
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mp.dao.UserMapper"> <select id="selectUserPage" resultType="com.mp.entity.User"> select * from user ${ew.customSqlSegment} </select> </mapper>
测试
@Test public void selectMyPage() { QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.ge("age", 20); Page page = new Page(1, 2); IPage iPage = userMapper.selectUserPage(page, queryWrapper); System.out.println("总页数：" + iPage.getPages()); System.out.println("总记录数：" + iPage.getTotal()); List<User> list = (List<User>) iPage.getRecords(); list.forEach(System.out::println); }