Spring Boot学习笔记05--Mybatis+通用Mapper+分页插件

摘要

看完本文你将掌握如下知识点:

  1. Spring Boot项目中,Mybatis+通用Mapper+分页插件的配置方法

SpringBoot系列Spring Boot学习笔记


前言

前文已经对Spring Boot中各种类型的数据访问做了说明,本文是对Spring Boot中使用Mybatis的扩展,重点说明如何在mybatis中集成通用Mapper和分页插件。

本文代码是在上文中讲到的mybatis单数据源配置的基础上进行扩展。

配置说明

pom中增加通用Mapper和分页插件的依赖:

1
2
3
4
5
6
7
8
9
10
11
12
<!--分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.2.1</version>
</dependency>
<!--通用Mapper-->
<dependency>
<groupId>tk.mybatis</groupId>
<artifactId>mapper</artifactId>
<version>3.3.9</version>
</dependency>

MyBatisConfig:在SqlSessionFactory中增加分页插件配置,因为通用Mapper是基于注解的,所以这里去掉xml的加载。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
@Bean(name = "sqlSessionFactory")
public SqlSessionFactory sqlSessionFactoryBean() {
SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
bean.setDataSource(dataSource());

bean.setTypeAliasesPackage("com.example.pojo");

//分页插件设置
PageHelper pageHelper = new PageHelper();
Properties properties = new Properties();
properties.setProperty("reasonable", "true");
properties.setProperty("supportMethodsArguments", "true");
properties.setProperty("returnPageInfo", "check");
properties.setProperty("params", "count=countSql");
pageHelper.setProperties(properties);

//添加分页插件
bean.setPlugins(new Interceptor[]{pageHelper});

ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
try {
//基于注解扫描Mapper,不需配置xml路径
//bean.setMapperLocations(resolver.getResources("classpath:mapper/*.xml"));
return bean.getObject();
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException(e);
}
}

MyBatisMapperScannerConfig
注意这里使用的是tk.mybatis.spring.mapper.MapperScannerConfigurer

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
package com.example;

import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import tk.mybatis.spring.mapper.MapperScannerConfigurer;
import java.util.Properties;

@Configuration
//必须在MyBatisConfig注册后再加载MapperScannerConfigurer,否则会报错
@AutoConfigureAfter(MyBatisConfig.class)
public class MyBatisMapperScannerConfig {
@Bean
public MapperScannerConfigurer mapperScannerConfigurer() {
MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
mapperScannerConfigurer.setBasePackage("com.example.mapper");

//初始化扫描器的相关配置,这里我们要创建一个Mapper的父类
Properties properties = new Properties();
properties.setProperty("mappers", "com.example.MyMapper");
properties.setProperty("notEmpty", "false");
properties.setProperty("IDENTITY", "MYSQL");

mapperScannerConfigurer.setProperties(properties);

return mapperScannerConfigurer;
}
}

MyMapper

1
2
3
4
5
6
7
8
9
package com.example;

import tk.mybatis.mapper.common.Mapper;
import tk.mybatis.mapper.common.MySqlMapper;

public interface MyMapper<T> extends Mapper<T>, MySqlMapper<T> {
//TODO
//FIXME 特别注意,该接口不能被扫描到,否则会出错
}

PersonMapper:实体mapper继承MyMapper即可,业务方法中我们就可以使用通用Mapper提供的各种方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
package com.example.mapper;

import com.example.MyMapper;
import com.example.pojo.Person;

public interface PersonMapper extends MyMapper<Person> {

//以下方法用于演示,当通用Mapper不能买足需求时,可以自己扩展相应的方法
//不允许查询参数为空的情况
@Select({
"select",
"p_id, p_name, p_age",
"from person",
"where p_age between #{startAge} and #{endAge}"
})
@Results({
@Result(column="p_id", property="pId", jdbcType= JdbcType.INTEGER, id=true),
@Result(column="p_name", property="pName", jdbcType=JdbcType.VARCHAR),
@Result(column="p_age", property="pAge", jdbcType=JdbcType.INTEGER)
})
List<Person> queryListByParam(Person person);

//允许查询参数为空的情况
@SelectProvider(type = PersonSqlProvider.class,method = "selectSelective")
@Results({
@Result(column="p_id", property="pId", jdbcType= JdbcType.INTEGER, id=true),
@Result(column="p_name", property="pName", jdbcType=JdbcType.VARCHAR),
@Result(column="p_age", property="pAge", jdbcType=JdbcType.INTEGER)
})
List<Person> queryListByParamSelective(Person person);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
package com.example.mapper;

import com.example.pojo.Person;
import static org.apache.ibatis.jdbc.SqlBuilder.*;

public class PersonSqlProvider {

public String selectSelective(Person record){
BEGIN();

SELECT("p_id, p_name, p_age");
FROM("person");
WHERE("1=1");

if(record.getStartAge()!=null){
AND();
WHERE("p_age >= #{startAge}");
}

if(record.getEndAge()!=null){
AND();
WHERE("p_age <= #{endAge}");
}

return SQL();
}
}

这里要注意,使用通用Mapper时,实体类必须有一个叫做id的整型主键,另外,为了方便使用分页插件,要在每个实体对象中定义两个属性–pagerows,我们可以把这些通用的属性放到父类中,比如我们创建一个父类:BaseEntity

BaseEntity

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
package com.example.pojo;

import javax.persistence.Transient;

public class BaseEntity {

@Id
//注意,如果是老的项目,表中的主键可能不叫做id,这时可以在父类中去掉这个属性,改在子类中实现
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer id;

@Transient
private Integer page = 1;

@Transient
private Integer rows = 10;
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
package com.example.pojo;

import javax.persistence.*;

@Table(name = "person")
public class Person extends BaseEntity{

private String pName;
private Integer pAge;

//以下属性用于演示范围查询
@Transient
private Integer startAge;
@Transient
private Integer endAge;
//setter and getter
}

测试演示

业务方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
package com.example.service;

import com.example.mapper.PersonMapper;
import com.example.pojo.Person;
import com.github.pagehelper.PageHelper;
import org.apache.ibatis.session.RowBounds;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@Service
@Transactional(propagation = Propagation.REQUIRED, readOnly = false, rollbackFor = {Exception.class})
public class PersonService {
@Autowired
private PersonMapper personMapper;

public int insert(Person person){
return personMapper.insert(person);
}

@Transactional(readOnly = true)
public Person selectByPrimaryKey(Integer pId){
return personMapper.selectByPrimaryKey(pId);
}

@Transactional(readOnly = true)
public List<Person> getAllPersonList(){
return personMapper.selectAll();
}

@Transactional(readOnly = true)
public List<Person> getPagePersonList(Person person, RowBounds rowBounds){

return personMapper.selectByRowBounds(person,rowBounds);

}

public List<Person> getPagePersonList(Person person){
if (person.getPage() != null && person.getRows() != null) {
PageHelper.startPage(person.getPage(), person.getRows(), "p_id");
}
return personMapper.selectAll();
}


public List<Person> queryListByParam(Person person){
if (person.getPage() != null && person.getRows() != null) {
PageHelper.startPage(person.getPage(), person.getRows(), "p_id");
}
return personMapper.queryListByParam(person);
}

public List<Person> queryListByParamSelective(Person person){
if (person.getPage() != null && person.getRows() != null) {
PageHelper.startPage(person.getPage(), person.getRows(), "p_id");
}
return personMapper.queryListByParamSelective(person);
}
}

单元测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
package com.example;

import com.example.pojo.Person;
import com.example.service.PersonService;
import org.apache.ibatis.session.RowBounds;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest
@Transactional
public class MybatisMapperPagehelperApplicationTests {


@Autowired
PersonService personService;

@Test
public void selectByPrimaryKey(){
Person person = personService.selectByPrimaryKey(1);
System.out.println(person);
}

@Test
public void insert(){
Person person = new Person();
person.setpName("王五");
person.setpAge(18);
System.out.println(personService.insert(person));

}

@Test
public void getAllPersonList(){
List<Person> list = personService.getAllPersonList();
System.out.println(list.size());
for(Person person : list){
System.out.println(person);
}
}

@Test
public void getPagePersonList(){
Person person = new Person();
person.setpName("王五");
//指定limit和offset
List<Person> list = personService.getPagePersonList(person,new RowBounds(2,3));
System.out.println(list.size());
for(Person p : list){
System.out.println(p);
}
}

@Test
public void getPagePersonList2(){
Person person = new Person();
//每页2行,查询第二页
person.setPage(2);
person.setRows(2);

List<Person> list = personService.getPagePersonList(person);
System.out.println(list.size());
for(Person p : list){
System.out.println(p);
}
}

@Test
public void queryListByParam(){
Person person = new Person();
//每页2行,查询第二页
person.setPage(2);
person.setRows(2);
//查询年龄在15到22之间的数据
person.setStartAge(15);
person.setEndAge(22);

List<Person> list = personService.queryListByParam(person);
System.out.println(list.size());
for(Person p : list){
System.out.println(p);
}
}


@Test
public void queryListByParamSelective(){
Person person = new Person();
//每页2行,查询第一页
person.setPage(1);
person.setRows(2);

//查询年龄大于等于15的数据
person.setStartAge(15);
//person.setEndAge(22);

List<Person> list = personService.queryListByParamSelective(person);
System.out.println(list.size());
for(Person p : list){
System.out.println(p);
}
}
}

本文示例代码下载地址:https://github.com/hanqunfeng/SpringBootStudy

项目参考:https://github.com/abel533/MyBatis-Spring-Boot