MybatisPlus⾃定义SQL和分页插件
⼀、使⽤条件构造器的⾃定义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, 1087982257332887553, 2019-02-05 11:12:22
DEBUG<==      Total: 1
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou, 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 "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.mp.dao.UserMapper">
<select id="selectAll" resultType="ity.User">
select * from user ${ew.customSqlSegment}
</select>
</mapper>
注意需要扫描xml⽂件
# Mybatis Plus配置
mybatis-plus:
mapper-locations: classpath*:mapper/**/*.xml
⼆、分页插件分页查询插件
1、配置分页插件
package fig;
import sion.plugins.PaginationInterceptor;
import t.annotation.Bean;
import t.annotation.Configuration;
@Configuration
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
}
2、使⽤Page类进⾏分页
@Test
public void selectPage() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
<("age", 20);
Page page = new Page(1, 2);
IPage iPage = userMapper.selectPage(page, queryWrapper);
System.out.println("总页数：" + Pages());
System.out.println("总记录数：" + Total());
List<User> list = (List<User>)Records();
list.forEach(System.out::println);
}
@Test
public void selectPage2() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
<("age", 20);
Page page = new Page(1, 2);
IPage iPage = userMapper.selectMapsPage(page, queryWrapper);
System.out.println("总页数：" + Pages());
System.out.println("总记录数：" + Total());
List<Map<String, Object>> list = (List<Map<String, Object>>) Records();
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, null, 2019-01-11 14:20:20
TRACE<==        Row: 1088248166370832385, 王天风, 25, wtf@baomidou, 1087982257332887553, 2019-02-05 11:12:22
DEBUG<==      Total: 2
总页数：3
总记录数：6
User(id=1087982257332887553, name=⼤boss, age=40, email=boss@baomidou, managerId=null, createTime=Fri Jan 11 14:20:20 CST 2019, remark=null)
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou, 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<>();
<("age", 20);
Page page = new Page(1, 2, false);
IPage iPage = userMapper.selectPage(page, queryWrapper);
System.out.println("总页数：" + Pages());
System.out.println("总记录数：" + Total());
List<User> list = (List<User>)Records();
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, null, 2019-01-11 14:20:20
TRACE<==        Row: 1088248166370832385, 王天风, 25, wtf@baomidou, 1087982257332887553, 2019-02-05 11:12:22
DEBUG<==      Total: 2
总页数：0
总记录数：0
User(id=1087982257332887553, name=⼤boss, age=40, email=boss@baomidou, managerId=null, createTime=Fri Jan 11 14:20:20 CST 2019, remark=null)
User(id=1088248166370832385, name=王天风, age=25, email=wtf@baomidou, 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 "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"> <mapper namespace="com.mp.dao.UserMapper">
<select id="selectUserPage" resultType="ity.User">
select * from user ${ew.customSqlSegment}
</select>
</mapper>
测试
@Test
public void selectMyPage() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
<("age", 20);
Page page = new Page(1, 2);
IPage iPage = userMapper.selectUserPage(page, queryWrapper);
System.out.println("总页数：" + Pages());
System.out.println("总记录数：" + Total());
List<User> list = (List<User>) Records();
list.forEach(System.out::println);
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。