spring boot集成mybatis,集成使用mybatis拖沓了好久，今天终于可以补起来了。
本篇源码中,同时使用了Spring data JPA 和 Mybatis两种方式.
在使用的过程中一定要注意,JPA和Mybatis可以在同一个方法中调用,但是如果要保证事务一致性,千万不要把JPA的操作和Mybatis的操作放在一个事务中
项目GitHub地址：GitHub地址
spring boot 2.0
==================================================================
注意点：
1.mybatis是有自动生成实体、Mapper映射类[实际是接口]、Mapper.xml映射文件的插件，插件名字叫【mybatis generator】，这里没有使用。
2.使用mybatis的话，是有两种实现方式的。
1》注解的方式
2》映射文件的方式
其实，注解的方式类似于JPA 的使用注解@Query()的方式。
整个使用下来，还是建议简单的CRUD操作，使用spring-data-JPA进行操作可以节省大量的代码量。
复杂的查询操作，就可以采用mybatis来完成，通过自己写sql语句提高灵活性，当然使用JPA来操作也可以完成。
3.第二点中的注解和映射文件两种方式 可以一起使用。
==================================================================
下面，分【注解方式】和【映射文件方式】两个大模块进行展示，并
1.pom.xml文件
除了spring boot项目的默认配置之外，再加上mysql的jar和mybatis的jar即可【这里先把mybatis的分页插件pagehelper的jar包加上，留在最后说】【使用这个分页jar包，会导致分页无效，至少在spring boot2.0是无效的】
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--mybatis-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper mybatis分页插件-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.4</version>
</dependency>
pagehelper分页插件分页查询无效解决方法
修改之后采用的分页jar包【有效的分页】
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter mybatis分页插件--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.5</version> </dependency>
2.application.properties配置
server.port=9666 #datasource spring.datasource.continue-on-error=false spring.datasource.url=jdbc:mysql://localhost:3306/swapping?useSSL=false&useUnicode=true&characterEncoding=UTF-8 spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver #mybatis相关配置 #mybatis映射文件的位置 mybatis.mapper-locations=classpath:mapper/*.xml #mybatis指定entity位置 mybatis.type-aliases-package=com.sxd.swapping.domain #mybatis展示sql语句执行 logging.level.com.sxd.swapping.dao.mybatis=debug
==========================注解方式======================
3.实体类
package com.sxd.swapping.base; import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Sort; import javax.persistence.*; import java.util.*; import java.util.stream.Collectors; /** * 基础bean */ @MappedSuperclass public class BaseBean { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) private Long id;// 主键 自增 @Column(nullable = false, updatable = false) private Date createDate;// 创建时间 private Date updateDate;// 修改时间 private String updateId; // 修改人 @Column(nullable = false) private String createId; // 创建人 @Column(nullable = false) private String uid; //业务主键 @Transient private Integer pageNum = 0; //分页 页号 @Transient private Integer pageSize = 10; //分页 页量 public Long getId() { return id; } public void setId(Long id) { this.id = id; } public Date getCreateDate() { return createDate; } public void setCreateDate(Date createDate) { this.createDate = createDate; } public Date getUpdateDate() { return updateDate; } public void setUpdateDate(Date updateDate) { this.updateDate = updateDate; } public String getUpdateId() { return updateId; } public void setUpdateId(String updateId) { this.updateId = updateId; } public String getCreateId() { return createId; } public void setCreateId(String createId) { this.createId = createId; } public String getUid() { return uid; } public void setUid(String uid) { this.uid = uid; } public Integer getPageNum() { return pageNum; } public void setPageNum(Integer pageNum) { this.pageNum = pageNum; } public Integer getPageSize() { return pageSize; } public void setPageSize(Integer pageSize) { this.pageSize = pageSize; } public void initEntity(){ this.createDate = new Date(); this.createId = UUID.randomUUID().toString();//如果有当前登陆人，则初始化为当前登陆人 this.uid = UUID.randomUUID().toString(); } public void updateEntity(){ this.updateDate = new Date(); this.updateId = UUID.randomUUID().toString();//如果有当前登陆人，则赋值为当前登陆人 } /** * 分页 工具方法 * @return */ public PageRequest page(){ return PageRequest.of(pageNum,this.pageSize); } /** * 分页 排序工具方法 * * 中文字段排序 需要在查询出来后处理 sort无法解决中文排序的问题 * @param map * @param obj * @return * @throws Exception */ public PageRequest page(Map<String,Sort.Direction> map,Object obj) throws Exception{ //反射获取实体所有属性 List<String> properties = Arrays.stream(obj.getClass().getDeclaredFields()).map(i->i.getName()).collect(Collectors.toList()); Set<String> keySet = map.keySet(); Sort sort = null; if (properties.containsAll(keySet)){ for (String str:keySet){ if (sort == null){ sort = Sort.by(map.get(str),str); }else{ sort = sort.and(Sort.by(map.get(str),str)); } } }else{ throw new Exception("排序字段非本实体字段"); } return PageRequest.of(this.pageNum,this.pageSize,sort); } }
package com.sxd.swapping.domain; import com.sxd.swapping.base.BaseBean; import lombok.Getter; import lombok.Setter; import org.apache.commons.lang3.StringUtils; import org.springframework.data.jpa.domain.Specification; import javax.persistence.Column; import javax.persistence.Entity; import javax.persistence.Table; import javax.persistence.criteria.CriteriaBuilder; import javax.persistence.criteria.CriteriaQuery; import javax.persistence.criteria.Predicate; import javax.persistence.criteria.Root; import java.io.Serializable; import java.util.*; @Entity @Table @Getter @Setter public class HuaYangArea extends BaseBean implements Serializable { private static final long serialVersionUID = -1851783771574739215L; @Column(nullable = false) private String areaName; @Column(nullable = false) private Long areaPerson; public static Specification<HuaYangArea> where(HuaYangArea huaYangArea){ return new Specification<HuaYangArea>() { @Override public Predicate toPredicate(Root<HuaYangArea> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { //创建查询列表 List<Predicate> predicates = new ArrayList<>(); //字段areaName是否查询 String areaName = huaYangArea.getAreaName(); if (StringUtils.isNotBlank(areaName)){ predicates.add(criteriaBuilder.like(root.get("areaName"),"%"+areaName+"%")); } //字段areaPerson是否查询 Long areaPerson = huaYangArea.getAreaPerson(); if (areaPerson != null) { predicates.add(criteriaBuilder.equal(root.get("areaPerson"),areaPerson)); } return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction(); } }; } }
4.自己写的mapper映射类[实际是接口]，等同于dao层【注意@Mapper注解没有加在这个类上，是因为采用了在启动类上加@MapperScan(“Mapper类所在包路径”)的方式】
启动类代码:
package com.sxd.swapping; import org.mybatis.spring.annotation.MapperScan; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import org.springframework.scheduling.annotation.EnableScheduling; @EnableScheduling @SpringBootApplication @MapperScan("com.sxd.swapping.dao.mybatis") public class SwappingApplication { public static void main(String[] args) { SpringApplication.run(SwappingApplication.class, args); } }
【重点是看Mapper层的sql语句怎么写】
package com.sxd.swapping.dao.mybatis; import com.sxd.swapping.domain.HuaYangArea; import org.apache.ibatis.annotations.*; import java.util.Date; import java.util.List; public interface HuaYangAreaMapper { /** * 根据业务主键查询 * @param uid * @return */ @Select("SELECT * FROM hua_yang_area where uid = #{uid}") @Results({ @Result(property = "areaName",column = "area_name",javaType = String.class), @Result(property = "areaPerson",column = "area_person",javaType = Long.class), @Result(property = "createId",column = "create_id",javaType = String.class) }) HuaYangArea findOne(String uid); /** * 根据数据库主键 查询 * @param id * @return */ @Select("SELECT * FROM hua_yang_area WHERE id = #{id}") @Results({ @Result(property = "areaName",column = "area_name",javaType = String.class), @Result(property = "areaPerson",column = "area_person",javaType = Long.class), @Result(property = "createId",column = "create_id",javaType = String.class), @Result(property = "createDate",column = "create_date",javaType = Date.class), @Result(property = "updateDate",column = "update_date",javaType = Date.class) }) HuaYangArea selectById(Long id); /** * 模糊查询 方法1 * @param areaName 不传入值 则查到所有 * @return */ @Select("SELECT * FROM hua_yang_area WHERE area_name like '%${areaName}%'") @Results({ @Result(property = "areaName",column = "area_name",javaType = String.class), @Result(property = "areaPerson",column = "area_person",javaType = Long.class), @Result(property = "createId",column = "create_id",javaType = String.class), @Result(property = "createDate",column = "create_date",javaType = Date.class), @Result(property = "updateDate",column = "update_date",javaType = Date.class) }) List<HuaYangArea> selectByNameLike(@Param("areaName") String areaName); /** * 模糊查询 方法2 * @param areaName 不传入值则 一条也查不到 * @return */ @Select("SELECT * FROM hua_yang_area WHERE area_name like CONCAT(CONCAT('%',#{areaName}),'%')") @Results({ @Result(property = "areaName",column = "area_name",javaType = String.class), @Result(property = "areaPerson",column = "area_person",javaType = Long.class), @Result(property = "createId",column = "create_id",javaType = String.class), @Result(property = "createDate",column = "create_date",javaType = Date.class), @Result(property = "updateDate",column = "update_date",javaType = Date.class) }) List<HuaYangArea> selectByNameLike2(String areaName); /** * 插入 新增 * @param huaYangArea * @return */ @Insert("INSERT INTO hua_yang_area(create_date,create_id,uid,area_name,area_person) VALUES (#{createDate},#{createId},#{uid},#{areaName},#{areaPerson}) ") void insert(HuaYangArea huaYangArea); /** * 更新 根据数据库主键更新 * @param huaYangArea */ @Update("UPDATE hua_yang_area SET update_date=#{updateDate},update_id=#{updateId},area_name=#{areaName},area_person=#{areaPerson} WHERE id=#{id}") void update(HuaYangArea huaYangArea); /** * 删除 根据数据库主键删除 * @param id */ @Delete("DELETE FROM hua_yang_area WHERE id=#{id}") void delete(Long id); }
5.serviceImpl层【service层需要加@service()注解】
package com.sxd.swapping.service.impl; import com.sxd.swapping.dao.jpa.HuaYangAreaDao; import com.sxd.swapping.dao.mybatis.HuaYangAreaMapper; import com.sxd.swapping.domain.HuaYangArea; import com.sxd.swapping.service.HuaYangService; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Page; import org.springframework.data.domain.Sort; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.List; import java.util.Map; @Service public class HuaYangServiceImpl implements HuaYangService { @Autowired HuaYangAreaMapper huaYangAreaMapper; @Override public HuaYangArea getByUidWithMyBatis(String uid) { return huaYangAreaMapper.findOne(uid); } @Override public HuaYangArea insertWithMybatis(HuaYangArea huaYangArea) { huaYangAreaMapper.insert(huaYangArea); return huaYangAreaMapper.findOne(huaYangArea.getUid()); } @Override public HuaYangArea updateWithMyBatis(HuaYangArea huaYangArea) { huaYangAreaMapper.update(huaYangArea); return huaYangAreaMapper.selectById(huaYangArea.getId()); } @Override public void deleteWithMyBatis(Long id) { huaYangAreaMapper.delete(id); } @Override public HuaYangArea seleteWithMyBatis(Long id) { return huaYangAreaMapper.selectById(id); } @Override public List<HuaYangArea> selectLikeNameWithMyBatis(String areaName) { return huaYangAreaMapper.selectByNameLike(areaName); } @Override public List<HuaYangArea> selectLikeNameWithMyBatis2(String areaName) { return huaYangAreaMapper.selectByNameLike2(areaName); } }
6.统一响应体
package com.sxd.swapping.base; import com.sxd.swapping.util.MyException; import lombok.Getter; import lombok.Setter; import org.apache.commons.lang3.StringUtils; import org.springframework.beans.BeanUtils; import java.beans.PropertyDescriptor; import java.io.Serializable; import java.lang.reflect.Method; import java.util.List; @Getter @Setter public class UniVerResponse<T> implements Serializable { private static final long serialVersionUID = 137671534756697880L; /** * 正确 */ public static final int SUCCESS_REQUEST = 200; /** * 参数错误返回码 */ public static final int ERROR_PARAMS = 100001; /** * 业务错误返回码 */ public static final int ERROR_BUSINESS = 200001; /** * 系统异常返回码 */ public static final int ERROR_SYS_EXCPTION = 500001; private boolean success; private String msg; private int code; private T obj; public void beTrue(T obj){ this.success = true; this.msg = "successful"; this.code = SUCCESS_REQUEST; this.obj = obj; } public void beFalse(String msg,int code,T obj){ this.success = false; this.msg = msg; this.code = code; this.obj = obj; } /** * 对象多字段判空检查 * 例如simplCheckField(user,"userId","userName") * @param obj 被检查的对象 * @param propertys 被检查对象中的字段 可多个 * @throws MyException */ public static void checkField(Object obj,String...propertys) throws MyException{ if(obj != null && propertys != null && propertys.length > 0){ //字节码 Class<? extends Object> clazz = obj.getClass(); //遍历所有属性 for (int i = 0; i < propertys.length; i++) { String property = propertys[i]; //内省机制获取属性信息 PropertyDescriptor pd = BeanUtils.getPropertyDescriptor(clazz,property ); if(pd != null){ //获取当前字段的javabean读方法 Method readMethod = pd.getReadMethod(); if(readMethod != null){ Object invoke = null; try { invoke = readMethod.invoke(obj); } catch (Exception e) { throw new MyException("方法 "+ readMethod.getName() +"无法执行",UniVerResponse.ERROR_SYS_EXCPTION); } if(invoke != null){ //String类型单独处理 Class<?> propertyType = pd.getPropertyType(); if("java.lang.String".equals(propertyType.getName())){ if(StringUtils.isBlank((String)invoke)){ throw new MyException("错误 : [ " + property + " ] 不能为空!",UniVerResponse.ERROR_PARAMS); } }else if("java.util.List".equals(propertyType.getName())){ List list = (List)invoke; if(list.size() == 0){ throw new MyException("错误 : [ " + property + " ] 不能为空!",UniVerResponse.ERROR_PARAMS); } } }else{ throw new MyException("错误 : [ " + property + " ] 不能为空!",UniVerResponse.ERROR_PARAMS); } }else{ //抛出异常 throw new MyException("在 " + clazz +"中 找不到"+"[ " + property + " ] 的 读方法",UniVerResponse.ERROR_SYS_EXCPTION); } }else{ //抛出异常 throw new MyException("在 " + clazz +"中 找不到"+"[ " + property + " ] 属性",UniVerResponse.ERROR_SYS_EXCPTION); } } } } /** * 单一字段判空检查 * 可检查对象的单个属性判空 例如simplCheckField(user,"userId") * 也可做某个变量的单独判空 例如simplCheckField(userId,"userId") * @param obj 被检查的对象 * @param property 被检查的对象的字段 * @throws MyException */ public static void simplCheckField(Object obj,String property) throws MyException{ if(obj instanceof String){ if(StringUtils.isBlank((String)obj)){ throw new MyException("错误 : [ " + property + " ] 不能为空!",UniVerResponse.ERROR_PARAMS); } }else if(obj instanceof List){ List list = (List)obj; if(list.size() == 0){ throw new MyException("错误 : [ " + property + " ] 不能为空!",UniVerResponse.ERROR_PARAMS); } }else{ if(obj == null){ throw new MyException("错误 : [ " + property + " ] 不能为空!",UniVerResponse.ERROR_PARAMS); } } } }
7.controller层调用即可
package com.sxd.swapping.controller; import com.sxd.swapping.base.UniVerResponse; import com.sxd.swapping.domain.HuaYangArea; import com.sxd.swapping.service.HuaYangService; import com.sxd.swapping.util.MyException; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.*; import java.util.List; @RestController @RequestMapping("/huayangMybatis") public class HuaYangApiWithMyBatis { @Autowired HuaYangService huaYangService; @PostMapping("/insert") public UniVerResponse<HuaYangArea> insert(@RequestBody HuaYangArea huaYangArea){ UniVerResponse.checkField(huaYangArea,"areaName","areaPerson"); huaYangArea.initEntity(); UniVerResponse<HuaYangArea> response = new UniVerResponse<>(); try { huaYangArea = huaYangService.insertWithMybatis(huaYangArea); response.beTrue(huaYangArea); }catch (Exception e){ throw new MyException("插入失败",UniVerResponse.ERROR_BUSINESS); } return response; } @PutMapping("/update") public UniVerResponse<HuaYangArea> update(@RequestBody HuaYangArea huaYangArea){ UniVerResponse.checkField(huaYangArea,"id","areaName","areaPerson"); huaYangArea.updateEntity(); UniVerResponse<HuaYangArea> response = new UniVerResponse<>(); try { huaYangArea = huaYangService.updateWithMyBatis(huaYangArea); response.beTrue(huaYangArea); }catch (Exception e){ throw new MyException("更新失败",UniVerResponse.ERROR_BUSINESS); } return response; } /** * 不传入值的查询所有 * @param areaName * @return */ @GetMapping("/findByNameLike") public UniVerResponse<List<HuaYangArea>> findAll1(String areaName){ UniVerResponse<List<HuaYangArea>> response = new UniVerResponse<>(); try { response.beTrue(huaYangService.selectLikeNameWithMyBatis(areaName)); }catch (Exception e){ e.printStackTrace(); throw new MyException("查询失败",UniVerResponse.ERROR_BUSINESS); } return response; } /** * 不传入值 查不到结果 * @param areaName * @return */ @GetMapping("/findByNameLike2") public UniVerResponse<List<HuaYangArea>> findAll2(String areaName){ UniVerResponse<List<HuaYangArea>> response = new UniVerResponse<>(); try { response.beTrue(huaYangService.selectLikeNameWithMyBatis2(areaName)); }catch (Exception e){ e.printStackTrace(); throw new MyException("查询失败",UniVerResponse.ERROR_BUSINESS); } return response; } /** * 根据 业务主键获取 * @param uid * @return */ @GetMapping(value = "/oneHuaYang") public UniVerResponse<HuaYangArea> findOne(String uid){ UniVerResponse.simplCheckField(uid,"uid"); UniVerResponse<HuaYangArea> response = new UniVerResponse<>(); HuaYangArea huaYangArea = huaYangService.getByUidWithMyBatis(uid); response.beTrue(huaYangArea); return response; } /** * 根据主键删除 * @param id * @return */ @DeleteMapping(value = "/oneHuaYang") public UniVerResponse<String> deleteOne(Long id ){ UniVerResponse.simplCheckField(id,"id"); UniVerResponse<String> response = new UniVerResponse<>(); huaYangService.deleteWithMyBatis(id); response.beTrue("删除成功"); return response; } }
========================================================================================================
整体看下来单独采用Mapper映射类[实际是接口]中使用注解的方式，跟使用JPA很相似,但是没有JPA写起来代码量少
========================================================================================================
==========================映射文件方式======================
8.按照配置文件中的配置，在resources下创建mapper文件夹，并创建HuaYangAreaMapper.java同名的HuaYangAreaMapper.xml映射文件
注意，
<mapper namespace="com.sxd.swapping.dao.mybatis.HuaYangAreaMapper"> 标签中 标明本mapper映射文件对应的是哪个Mapper类
<select id="findByNameAndPersonAndCreateDate" parameterType="com.sxd.swapping.domain.HuaYangArea" resultType="com.sxd.swapping.base.HuaYangModelBean"> 每个sql语句是一个单独的标签， 查询有<select> 插入有<insert> 更新有<update> 删除有<delete> 标签基本属性 每一个id对应mapper类中的 一个方法名 parameterType属性标明sql接收的入参是什么类型 resultType属性标明sql操作结果的出参是什么类型 这里分别对应了两个实体类型
select hy.uid uid, hy.create_date createDate, hy.update_date updateDate, hy.area_name areaName, hy.area_person areaPerson from hua_yang_area AS hy <where> 1=1 <if test="areaName != null"> AND hy.area_name LIKE '%' #{areaName} '%' </if> <if test="areaPerson != null"> AND hy.area_person >= #{areaPerson} </if> <if test="createDate != null"> AND <![CDATA[hy.create_date >= #{createDate}]]> </if> </where> 查询语句 select 数据表字段1 出参实体属性1， 数据表字段2 出参实体属性2 from 数据表名 where if 入参实体属性1 !=null 数据表字段1 = 入参实体属性1 注意 时间类型的比较 转义字符的 处理
完整代码如下：
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" > <mapper namespace="com.sxd.swapping.dao.mybatis.HuaYangAreaMapper"> <select id="findByNameAndPersonAndCreateDate" parameterType="com.sxd.swapping.domain.HuaYangArea" resultType="com.sxd.swapping.base.HuaYangModelBean"> select hy.uid uid, hy.create_date createDate, hy.update_date updateDate, hy.area_name areaName, hy.area_person areaPerson from hua_yang_area AS hy <where> 1=1 <if test="areaName != null"> AND hy.area_name LIKE '%' #{areaName} '%' </if> <if test="areaPerson != null"> AND hy.area_person >= #{areaPerson} </if> <if test="createDate != null"> AND <![CDATA[hy.create_date >= #{createDate}]]> </if> </where> </select> </mapper>
9.入参的实体类已经在最上面提供了，出参的Model
package com.sxd.swapping.base; import lombok.Getter; import lombok.Setter; import java.util.Date; /** * mybatis 查询返回Model封装 */ @Getter @Setter public class HuaYangModelBean { private String uid; private Date createDate; private Date updateDate; private String areaName; private Long areaPerson; }
10.Mapper映射类[实际是接口]中 就这个方法
11. 分别在service 和controller层调用即可【想看代码 可以看下面分页模块 顺便把这一部分的service层代码 和controller层代码贴出来了】
【这里有一个前台String类型传递给后台Date类型的错误：http://www.cnblogs.com/sxdcgaq8080/p/9055107.html】
==============================================================================================================
映射文件这种方式，就可以很肆意的写SQL了，但是这里考虑到一个问题就是不同数据库的sql语法可能有所不同，所以对于不同数据库的查询，这种方式可移植性就很劣势了。
===============================================================================================================
===========================pagehelper分页插件=====================
最后看一下mybatis分页插件的使用
12.如最上面所说的，分页jar包采用spring boot集成的这个，分页才有效
<!-- https://mvnrepository.com/artifact/com.github.pagehelper/pagehelper-spring-boot-starter mybatis分页插件--> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper-spring-boot-starter</artifactId> <version>1.2.5</version> </dependency>
13.在application.properties增加pagehelper相关的配置
#pagehelper mybatis分页插件 pagehelper.helperDialect=mysql pagehelper.reasonable=true pagehelper.supportMethodsArguments=true pagehelper.params=count=countSql pagehelper.returnPageInfo=check
14.映射文件mapper.xml不变更，mapper映射类[实际是接口]依旧是上面的那个方法
15.service层新增一个返回PageInfo<实体>方法
import com.github.pagehelper.PageInfo; List<HuaYangModelBean> findByNameAndPersonAndCreateDateWithMyBatisFile(HuaYangArea huaYangArea); PageInfo<HuaYangModelBean> findByNameAndPersonAndCreateDateWithMyBatisFileAndpagehelper(HuaYangArea huaYangArea);
service的实现
import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; @Override public List<HuaYangModelBean> findByNameAndPersonAndCreateDateWithMyBatisFile(HuaYangArea huaYangArea) { return huaYangAreaMapper.findByNameAndPersonAndCreateDate(huaYangArea); } @Override public PageInfo<HuaYangModelBean> findByNameAndPersonAndCreateDateWithMyBatisFileAndpagehelper(HuaYangArea huaYangArea) { PageHelper.startPage(huaYangArea.getPageNum(),huaYangArea.getPageSize()); List<HuaYangModelBean> list = huaYangAreaMapper.findByNameAndPersonAndCreateDate(huaYangArea); PageInfo<HuaYangModelBean> pageInfo = new PageInfo<>(list); return pageInfo; }
16.设置分页统一响应体
package com.sxd.swapping.base; import lombok.AllArgsConstructor; import lombok.Getter; import lombok.NoArgsConstructor; import lombok.Setter; import java.util.ArrayList; import java.util.List; /** * 分页 统一响应体 * @param <T> */ @Getter @Setter @NoArgsConstructor @AllArgsConstructor public class PageResponse<T> { private boolean success; private String msg; private int code; private int pageNum; private int pageSize; private long total; private List<T> rows = new ArrayList<T>(); public void beTrue(long total,List<T> rows){ this.success = true; this.msg = "successful"; this.code = UniVerResponse.SUCCESS_REQUEST; this.total = total; this.rows = rows; } public void beTrue(int pageNum,int pageSize,long total,List<T> rows){ this.success = true; this.msg = "successful"; this.code = UniVerResponse.SUCCESS_REQUEST; this.pageNum = pageNum; this.pageSize = pageSize; this.total = total; this.rows = rows; } public void beFalse(String msg,int code){ this.success = false; this.msg = msg; this.code = code; this.total = 0; this.rows = null; } }
17.controller层调用
/** * 根据 三个字段 查询 * 使用mybatis映射文件 * @param huaYangArea * @return */ @GetMapping(value = "/findBy3") public UniVerResponse<List<HuaYangModelBean>> findByNameAndPersonAndCreateDate(HuaYangArea huaYangArea){ UniVerResponse.checkField(huaYangArea,"areaName","areaPerson","createDate"); UniVerResponse<List<HuaYangModelBean>> response = new UniVerResponse<>(); try { List<HuaYangModelBean> list = huaYangService.findByNameAndPersonAndCreateDateWithMyBatisFile(huaYangArea); response.beTrue(list); }catch (Exception e){ throw new MyException("查询错误",UniVerResponse.ERROR_BUSINESS,e); } return response; } /** * 根据三个字段 映射文件 查询 * * 分页查询 * @param huaYangArea * @return */ @GetMapping(value = "/findBy3Page") public PageResponse<HuaYangModelBean> findByNameAndPersonAndCreateDatePage(HuaYangArea huaYangArea){ UniVerResponse.checkField(huaYangArea,"areaName","areaPerson","createDate","pageNum","pageSize"); PageResponse<HuaYangModelBean> response = new PageResponse<>(); try { PageInfo<HuaYangModelBean> pageInfo = huaYangService.findByNameAndPersonAndCreateDateWithMyBatisFileAndpagehelper(huaYangArea); response.beTrue(pageInfo.getPageNum(),pageInfo.getPageSize(),pageInfo.getTotal(),pageInfo.getList()); }catch (Exception e){ throw new MyException("查询错误",UniVerResponse.ERROR_BUSINESS,e); } return response; }
18.测试查询，分页 的pageNum = 0 和pageNum = 1都是返回第一页的数据
满足条件的 总共13条数据，
查询pageNum = 0 pageSize = 10
查询 参数如下：
查询语句打印日志如下：
2018-05-18 14:05:19.469 DEBUG 8188 --- [nio-9666-exec-8] H.findByNameAndPersonAndCreateDate_COUNT : ==> Preparing: select count(0) from (select hy.uid uid, hy.create_date createDate, hy.update_date updateDate, hy.area_name areaName, hy.area_person areaPerson from hua_yang_area AS hy WHERE 1=1 AND hy.area_name LIKE '%' ? '%' AND hy.area_person >= ? AND hy.create_date >= ?) tmp_count 2018-05-18 14:05:19.470 DEBUG 8188 --- [nio-9666-exec-8] H.findByNameAndPersonAndCreateDate_COUNT : ==> Parameters: 华阳(String), 7000(Long), 2018-05-16 00:00:00.0(Timestamp) 2018-05-18 14:05:19.471 DEBUG 8188 --- [nio-9666-exec-8] H.findByNameAndPersonAndCreateDate_COUNT : <== Total: 1 2018-05-18 14:05:19.472 DEBUG 8188 --- [nio-9666-exec-8] s.d.m.H.findByNameAndPersonAndCreateDate : ==> Preparing: select hy.uid uid, hy.create_date createDate, hy.update_date updateDate, hy.area_name areaName, hy.area_person areaPerson from hua_yang_area AS hy WHERE 1=1 AND hy.area_name LIKE '%' ? '%' AND hy.area_person >= ? AND hy.create_date >= ? LIMIT ? 2018-05-18 14:05:19.473 DEBUG 8188 --- [nio-9666-exec-8] s.d.m.H.findByNameAndPersonAndCreateDate : ==> Parameters: 华阳(String), 7000(Long), 2018-05-16 00:00:00.0(Timestamp), 10(Integer) 2018-05-18 14:05:19.476 DEBUG 8188 --- [nio-9666-exec-8] s.d.m.H.findByNameAndPersonAndCreateDate : <== Total: 10
查询结果如下：
=============================================================================================================
查询pageNum = 1 pageSize = 10
查询 参数如下：
查询语句日志打印如下：
2018-05-18 14:03:38.142 DEBUG 8188 --- [nio-9666-exec-3] H.findByNameAndPersonAndCreateDate_COUNT : ==> Preparing: select count(0) from (select hy.uid uid, hy.create_date createDate, hy.update_date updateDate, hy.area_name areaName, hy.area_person areaPerson from hua_yang_area AS hy WHERE 1=1 AND hy.area_name LIKE '%' ? '%' AND hy.area_person >= ? AND hy.create_date >= ?) tmp_count 2018-05-18 14:03:38.143 DEBUG 8188 --- [nio-9666-exec-3] H.findByNameAndPersonAndCreateDate_COUNT : ==> Parameters: 华阳(String), 7000(Long), 2018-05-16 00:00:00.0(Timestamp) 2018-05-18 14:03:38.145 DEBUG 8188 --- [nio-9666-exec-3] H.findByNameAndPersonAndCreateDate_COUNT : <== Total: 1 2018-05-18 14:03:38.146 DEBUG 8188 --- [nio-9666-exec-3] s.d.m.H.findByNameAndPersonAndCreateDate : ==> Preparing: select hy.uid uid, hy.create_date createDate, hy.update_date updateDate, hy.area_name areaName, hy.area_person areaPerson from hua_yang_area AS hy WHERE 1=1 AND hy.area_name LIKE '%' ? '%' AND hy.area_person >= ? AND hy.create_date >= ? LIMIT ? 2018-05-18 14:03:38.147 DEBUG 8188 --- [nio-9666-exec-3] s.d.m.H.findByNameAndPersonAndCreateDate : ==> Parameters: 华阳(String), 7000(Long), 2018-05-16 00:00:00.0(Timestamp), 10(Integer) 2018-05-18 14:03:38.151 DEBUG 8188 --- [nio-9666-exec-3] s.d.m.H.findByNameAndPersonAndCreateDate : <== Total: 10
结果都是下面这样：
显示pageNum 都是 为1
=============================================================================================================
查询pageNum = 2 pageSize = 10
查询参数如下：
查询语句日志打印如下：
2018-05-18 14:02:31.734 DEBUG 8188 --- [nio-9666-exec-1] H.findByNameAndPersonAndCreateDate_COUNT : ==> Preparing: select count(0) from (select hy.uid uid, hy.create_date createDate, hy.update_date updateDate, hy.area_name areaName, hy.area_person areaPerson from hua_yang_area AS hy WHERE 1=1 AND hy.area_name LIKE '%' ? '%' AND hy.area_person >= ? AND hy.create_date >= ?) tmp_count 2018-05-18 14:02:31.753 DEBUG 8188 --- [nio-9666-exec-1] H.findByNameAndPersonAndCreateDate_COUNT : ==> Parameters: 华阳(String), 7000(Long), 2018-05-16 00:00:00.0(Timestamp) 2018-05-18 14:02:31.766 DEBUG 8188 --- [nio-9666-exec-1] H.findByNameAndPersonAndCreateDate_COUNT : <== Total: 1 2018-05-18 14:02:31.772 DEBUG 8188 --- [nio-9666-exec-1] s.d.m.H.findByNameAndPersonAndCreateDate : ==> Preparing: select hy.uid uid, hy.create_date createDate, hy.update_date updateDate, hy.area_name areaName, hy.area_person areaPerson from hua_yang_area AS hy WHERE 1=1 AND hy.area_name LIKE '%' ? '%' AND hy.area_person >= ? AND hy.create_date >= ? LIMIT ?, ? 2018-05-18 14:02:31.772 DEBUG 8188 --- [nio-9666-exec-1] s.d.m.H.findByNameAndPersonAndCreateDate : ==> Parameters: 华阳(String), 7000(Long), 2018-05-16 00:00:00.0(Timestamp), 10(Integer), 10(Integer) 2018-05-18 14:02:31.774 DEBUG 8188 --- [nio-9666-exec-1] s.d.m.H.findByNameAndPersonAndCreateDate : <== Total: 3
查询结果如下：
=============================================================================================================
查询pageNum = 3 pageSize = 10
查询参数如下：
查询语句打印日志如下：
2018-05-18 14:11:52.633 DEBUG 8188 --- [nio-9666-exec-8] H.findByNameAndPersonAndCreateDate_COUNT : ==> Preparing: select count(0) from (select hy.uid uid, hy.create_date createDate, hy.update_date updateDate, hy.area_name areaName, hy.area_person areaPerson from hua_yang_area AS hy WHERE 1=1 AND hy.area_name LIKE '%' ? '%' AND hy.area_person >= ? AND hy.create_date >= ?) tmp_count 2018-05-18 14:11:52.634 DEBUG 8188 --- [nio-9666-exec-8] H.findByNameAndPersonAndCreateDate_COUNT : ==> Parameters: 华阳(String), 7000(Long), 2018-05-16 00:00:00.0(Timestamp) 2018-05-18 14:11:52.636 DEBUG 8188 --- [nio-9666-exec-8] H.findByNameAndPersonAndCreateDate_COUNT : <== Total: 1 2018-05-18 14:11:52.638 DEBUG 8188 --- [nio-9666-exec-8] s.d.m.H.findByNameAndPersonAndCreateDate : ==> Preparing: select hy.uid uid, hy.create_date createDate, hy.update_date updateDate, hy.area_name areaName, hy.area_person areaPerson from hua_yang_area AS hy WHERE 1=1 AND hy.area_name LIKE '%' ? '%' AND hy.area_person >= ? AND hy.create_date >= ? LIMIT ?, ? 2018-05-18 14:11:52.639 DEBUG 8188 --- [nio-9666-exec-8] s.d.m.H.findByNameAndPersonAndCreateDate : ==> Parameters: 华阳(String), 7000(Long), 2018-05-16 00:00:00.0(Timestamp), 10(Integer), 10(Integer) 2018-05-18 14:11:52.641 DEBUG 8188 --- [nio-9666-exec-8] s.d.m.H.findByNameAndPersonAndCreateDate : <== Total: 3
查询结果如下：
==================================================================================
好了 spring boot 2.0+mybatis 暂时告一段落。