1.创建springboot项目,引入pom依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.1.2</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.1.2</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.29</version>
</dependency>
<!-- 提供mysql驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.16</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-freemarker</artifactId>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.54</version>
</dependency>
<dependency>
<groupId>com.github.ikidou</groupId>
<artifactId>TypeBuilder</artifactId>
<version>1.0</version>
<scope>test</scope>
</dependency>
2.application.yml配置文件
server:
port: 8000
spring:
profiles:
active: dev
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper-locations: classpath:/mapper/*Mapper.xml
logging:
level:
com.baomidou.mybatisplus.samples.pagination: debug
spring:
datasource:
# 使用druid数据源
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/ds_0?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8
username: root
password: root
filters: stat
maxActive: 20
initialSize: 1
maxWait: 60000
minIdle: 1
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: select 1 FROM DUAL
testWhileIdle: true
testOnBorrow: false
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatements: 20
3.设置分页配置
@Configuration
@MapperScan("com.nh.mapper")
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor(){
// 开启 count 的 join 优化,只针对 left join !!!
return new PaginationInterceptor().setCountSqlParser(new JsqlParserCountOptimize(true));
}
}
4.mapper接口文件
public interface UserMapper extends BaseMapper<User> {
MyPage<User> mySelectPageMap(@Param("pg") MyPage<User> myPage, @Param("map") Map param);
List<User> mySelectMap(Map param);
List<User> myPageSelect(MyPage<User> myPage);
List<User> iPageSelect(IPage<User> myPage);
List<User> rowBoundList(RowBounds rowBounds, Map map);
}
5.xml文件
<select id="mySelectPage" resultType="com.nh.vo.User">
select *
from user
where (age = #{pg.selectInt} and name = #{pg.selectStr})
or (age = #{ps.yihao} and name = #{ps.erhao})
</select>
<select id="mySelectPageMap" resultType="com.nh.vo.User">
select * from user
<where>
<if test="map.name!=null and map.name!=''">
name like #{map.name}
</if>
</where>
</select>
<select id="mySelectMap" resultType="com.nh.vo.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like #{name}
</if>
</where>
</select>
<select id="myPageSelect" resultType="com.nh.vo.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like '%'||#{name}||'%'
</if>
</where>
</select>
<select id="iPageSelect" resultType="com.nh.vo.User">
select * from user
<where>
<if test="name!=null and name!=''">
name like #{name}
</if>
</where>
</select>
6.单元测试
@Test
public void tests1() {
System.out.println("----- baseMapper 自带分页 ------");
Page<User> page = new Page<>(1, 5);
IPage<User> userIPage = mapper.selectPage(page, new QueryWrapper<User>()
.eq("age", 20).eq("name", "Jack"));
assertThat(page).isSameAs(userIPage);
System.out.println("总条数 ------> " + userIPage.getTotal());
System.out.println("当前页数 ------> " + userIPage.getCurrent());
System.out.println("当前每页显示数 ------> " + userIPage.getSize());
print(userIPage.getRecords());
System.out.println("----- baseMapper 自带分页 ------");
System.out.println("json 正反序列化 begin");
String json = JSON.toJSONString(page);
Page<User> page1 = JSON.parseObject(json, TypeBuilder.newInstance(Page.class).addTypeParam(User.class).build());
print(page1.getRecords());
System.out.println("json 正反序列化 end");
System.out.println("----- 自定义 XML 分页 ------");
MyPage<User> myPage = new MyPage<User>(1, 5).setSelectInt(20).setSelectStr("Jack");
ParamSome paramSome = new ParamSome(20, "Jack");
MyPage<User> userMyPage = mapper.mySelectPage(myPage, paramSome);
assertThat(myPage).isSameAs(userMyPage);
System.out.println("总条数 ------> " + userMyPage.getTotal());
System.out.println("当前页数 ------> " + userMyPage.getCurrent());
System.out.println("当前每页显示数 ------> " + userMyPage.getSize());
print(userMyPage.getRecords());
System.out.println("----- 自定义 XML 分页 ------");
}
@Test
public void tests2() {
/* 下面的 left join 不会对 count 进行优化,因为 where 条件里有 join 的表的条件 */
MyPage<UserChildren> myPage = new MyPage<>(1, 5);
myPage.setSelectInt(18).setSelectStr("Jack");
MyPage<UserChildren> userChildrenMyPage = mapper.userChildrenPage(myPage);
List<UserChildren> records = userChildrenMyPage.getRecords();
records.forEach(System.out::println);
/* 下面的 left join 会对 count 进行优化,因为 where 条件里没有 join 的表的条件 */
myPage = new MyPage<UserChildren>(1, 5).setSelectInt(18);
userChildrenMyPage = mapper.userChildrenPage(myPage);
records = userChildrenMyPage.getRecords();
records.forEach(System.out::println);
}
@Test
public void testMyPageMap() {
MyPage<User> myPage = new MyPage<User>(1, 5).setSelectInt(20).setSelectStr("Jack");
Map map = new HashMap(1);
map.put("name", "%a");
mapper.mySelectPageMap(myPage, map);
myPage.getRecords().forEach(System.out::println);
}
@Test
public void testMap() {
Map map = new HashMap(1);
map.put("name", "%a");
mapper.mySelectMap(map).forEach(System.out::println);
}
@Test
public void myPage() {
MyPage<User> page = new MyPage<>(1, 5);
page.setName("a");
mapper.myPageSelect(page).forEach(System.out::println);
}
@Test
public void iPageTest() {
IPage<User> page = new Page<User>(1, 5) {
private String name = "%";
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
};
List<User> list = mapper.iPageSelect(page);
System.out.println("list.size=" + list.size());
System.out.println("page.total=" + page.getTotal());
}
@Test
public void rowBoundsTest() {
RowBounds rowBounds = new RowBounds(0, 5);
Map map = new HashMap(1);
map.put("name", "%");
List<User> list = mapper.rowBoundList(rowBounds, map);
System.out.println("list.size=" + list.size());
}