摘要
看完本文你将掌握如下知识点：
- Spring Boot项目中，Mybatis+通用Mapper+分页插件的配置方法
SpringBoot系列：Spring Boot学习笔记
前言
前文已经对Spring Boot中各种类型的数据访问做了说明，本文是对Spring Boot中使用Mybatis的扩展，重点说明如何在mybatis中集成通用Mapper和分页插件。
本文代码是在上文中讲到的mybatis单数据源配置的基础上进行扩展。
配置说明
pom中增加通用Mapper和分页插件的依赖：
<!--分页插件-->
<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的加载。
@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
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
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提供的各种方法
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);
}
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
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;
}
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
}
测试演示
业务方法
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);
}
}
单元测试
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);
}
}
}
