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

发表于 更新于 分类于 ， ，
阅读次数： 本文字数： 1.3k 阅读时长 ≈ 5 分钟

摘要

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

  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的整型主键，另外，为了方便使用分页插件，要在每个实体对象中定义两个属性–page和rows，我们可以把这些通用的属性放到父类中，比如我们创建一个父类：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