篇十五：mybatis分页实现
分页有两种实现，第一种基于pagehelper，另一种基于自编的dialect
一、基于PageHelper
1、pom.xml配置相关jar包
注意：mybatis-spring版本问题，参考数据库
<!-- MySQL+Mybatis:mybatis、mybatis-spring、spring-jdbc、druid、mysql-connector-java、pagehelper --> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.3.0</version> </dependency> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis-spring</artifactId> <version>1.2.3</version> </dependency> <dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>${spring.version}</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid</artifactId> <version>1.0.18</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.38</version> </dependency> <dependency> <groupId>com.github.pagehelper</groupId> <artifactId>pagehelper</artifactId> <version>4.1.4</version> </dependency>
2、配置spring-mybatis.xml
a、配置 pagehelper
b、配置 mybatis.config.xml
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xmlns:context="http://www.springframework.org/schema/context" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd"> <!-- 基于Druid数据库链接池的数据源配置 --> <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close"> <!-- 基本属性driverClassName、 url、user、password --> <property name="driverClassName" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> <!-- 配置初始化大小、最小、最大 --> <!-- 通常来说，只需要修改initialSize、minIdle、maxActive --> <property name="initialSize" value="2" /> <property name="minIdle" value="2" /> <property name="maxActive" value="30" /> <property name="testWhileIdle" value="false" /> <!-- 配置获取连接等待超时的时间 --> <property name="maxWait" value="5000" /> <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 --> <property name="minEvictableIdleTimeMillis" value="30000" /> <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 --> <property name="timeBetweenEvictionRunsMillis" value="60000" /> <!-- 解密密码必须要配置的项 --> <property name="filters" value="config" /> <property name="connectionProperties" value="config.decrypt=false" /> </bean> <!-- SqlSession模板类实例 --> <bean id="sqlSession" class="org.mybatis.spring.SqlSessionTemplate" scope="prototype" destroy-method="close"> <constructor-arg index="0" ref="sqlSessionFactory" /> </bean> <!-- 将数据源映射到sqlSessionFactory中 --> <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean"> <property name="configLocation" value="classpath:mybatis/mybatis-config.xml" /> <property name="dataSource" ref="dataSource" /> <property name="plugins"> <array> <bean class="com.github.pagehelper.PageHelper"> <property name="properties"> <value> dialect=hsqldb </value> </property> </bean> </array> </property> </bean> <!--======= 事务配置 Begin ================= --> <!-- 事务管理器（由Spring管理MyBatis的事务） --> <!-- <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager"> 关联数据源 <property name="dataSource" ref="dataSource"></property> </bean> --> <!-- <tx:annotation-driven transaction-manager="transactionManager" /> --> <!--======= 事务配置 End =================== --> </beans>
3、BaseDaoImpl中添加分页
BaseDao
package com.core.dao; import java.util.List; import com.core.dao.common.BaseResponse; import com.core.dao.common.PageData; /** * Dao层公共包 * @author happy * */ public interface BaseDao<T> { /** * 查询单条数据 * @param id * @return */ T selectById(String id); /** * 查询多条数据，不分页 * @param obj * @return */ List<T> selectList(Object obj); /** * 自定义列表查询，不分页 * @param statement * @param obj * @return */ List<T> selectList(String statement,Object obj); /** * 查询多条数据，分页 * @param page * @param obj * @return */ BaseResponse selectListPage(PageData page,Object obj); /** * 自定义列表查询，分页 * @param page * @param statement * @param obj * @return */ BaseResponse selectListPage(PageData page,String statement,Object obj); /** * 新增数据 * @param entity * @return :插入成功，返回新增的 */ int insert(T entity); /** * 批量插入:数据表的主键id是UUID生成的，自增长的会提示id不为空，报错 * @param list * @return */ int insert(List<T> list); /** * 修改数据 * @param entity * @return */ int update(T entity); /** * 批量修改数据 * @param list * @return */ int update(List<T> list); /** * 单条删除 * @param id * @return */ int delete(String id); /** * 批量删除 * @param ids * @return */ int delete(String[] ids); }
BaseDaoImpl
package com.core.dao; import java.util.List; import org.apache.ibatis.session.SqlSession; import org.mybatis.spring.SqlSessionTemplate; import org.mybatis.spring.support.SqlSessionDaoSupport; import org.springframework.beans.factory.annotation.Autowired; import com.core.dao.common.BaseResponse; import com.core.dao.common.BasicExecuteEnum; import com.core.dao.common.PageData; import com.github.pagehelper.PageHelper; import com.github.pagehelper.PageInfo; /** * 公共方法实现类 * @author shb * */ public class BaseDaoImpl<T> extends SqlSessionDaoSupport implements BaseDao<T> { @Override public T selectById(String id) { return getSqlSession().selectOne(getStatement(BasicExecuteEnum.SELECT_BYID.id), id); } @Override public List<T> selectList(Object obj) { return getSqlSession().selectList(getStatement(BasicExecuteEnum.SELECT_LIST.id), obj); } @Override public List<T> selectList(String statement, Object obj) { return getSqlSession().selectList(getStatement(statement), obj); } @Override public BaseResponse selectListPage(PageData page, Object obj) { BaseResponse response = new BaseResponse(); PageHelper.startPage(page.getPage(), page.getNumber(), true); List<Object> list = getSqlSession().selectList(getStatement(BasicExecuteEnum.SELECT_LIST.id), obj); response.setObj(list); response.setPageInfo(new PageInfo<Object>(list)); return response; } @Override public BaseResponse selectListPage(PageData page, String statement, Object obj) { BaseResponse response = new BaseResponse(); PageHelper.startPage(page.getPage(), page.getNumber(), true); List<Object> list = getSqlSession().selectList(getStatement(statement), obj); response.setObj(list); response.setPageInfo(new PageInfo<Object>(list)); return response; } @Override public int insert(T entity) { return getSqlSession().insert(getStatement(BasicExecuteEnum.INSERT.id), entity); } @Override public int insert(List<T> list) { return getSqlSession().insert(getStatement(BasicExecuteEnum.INSERT_BATCH.id), list); } @Override public int update(T entity) { return getSqlSession().update(getStatement(BasicExecuteEnum.UPDATE.id), entity); } @Override public int update(List<T> list) { int size = 0; for(T obj:list){ int length = getSqlSession().update(getStatement(BasicExecuteEnum.UPDATE.id),obj); if(length != 0) size++; } return size; } @Override public int delete(String id) { return getSqlSession().delete(getStatement(BasicExecuteEnum.INSERT.id), id); } @Override public int delete(String[] ids) { int size = 0; for(String id:ids){ int length = getSqlSession().delete(getStatement(BasicExecuteEnum.INSERT.id), id); if(length != 0 ) size++; } return size; } /** * 注入SqlSessionTemplate实例(要求Spring中进行SqlSessionTemplate的配置).<br/> * 可以调用getSqlSession()完成数据库操作. */ // public SqlSessionTemplate sqlSessionTemplate; // @Autowired // protected SqlSessionFactory sqlSessionFactory; // public SqlSessionTemplate getSessionTemplate() { // return getSqlSession(); // } @Autowired public void setSessionTemplate(SqlSessionTemplate sqlSessionTemplate) { super.setSqlSessionTemplate(sqlSessionTemplate); } public SqlSession getSqlSession() { return super.getSqlSession(); } // public SqlSessionFactory getSqlSessionFactory(SqlSessionFactory sqlSessionFactory) { // return sqlSessionFactory; // } // // @Override // public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) { // this.sqlSessionFactory = sqlSessionFactory; // } /** * 获取类的全路径，拼接 sqlId的Mapper的访问路径 * 公共方法无法获取指定的类全路径，即无法使用namespace的完全限定名 * @param sqlId * @return */ public String getStatement(String sqlId) { String name = this.getClass().getName(); StringBuffer sb = new StringBuffer().append(name).append(".").append(sqlId); return sb.toString(); } }
