Maven Mybatis JTA SprintBoot分布式事物管理，拦截器管理事物

最新推荐文章于 2022-03-18 19:33:34 发布

冰雪Love齐迹

最新推荐文章于 2022-03-18 19:33:34 发布

阅读量316

点赞数

分类专栏： springboot相关学习文章标签： jta maven springboot mybatis

本文链接：https://blog.csdn.net/liqi_q/article/details/80499709

版权

springboot相关学习专栏收录该内容

13 篇文章 0 订阅

订阅专栏

如果要源码参考请访问：https://download.csdn.net/download/liqi_q/10445505

java中分布式事物JTA是比较常用的第三方服务，下面我是使用的Atomikos ，分布式事物主要是2.5之前和之后使用上有明显的区别，Springboot项目在配置数据源的时候多数据源的情况下项目启动要指定一个默认的数据源及@Primary，2.5版本之前使用的时候如果说处理两个业务方法，方法一是@Primary数据源在前的话，方法二失败了他会回滚数据，反之，如果是方法二在前@Primary再后面的话，方法二的数据不会回滚。

下面我贴出来我的数据源的配置application.properties文件内容

server.port: 9991
# car\u6570\u636E\u6E90\u914D\u7F6E
spring.datasource.pro.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.pro.driverClassName=com.mysql.jdbc.Driver
spring.datasource.pro.url=jdbc:mysql://127.0.0.1:3306/jta_pro?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
spring.datasource.pro.username=root
spring.datasource.pro.password=root
spring.datasource.pro.initialSize=5
spring.datasource.pro.minIdle=5
spring.datasource.pro.maxActive=20
spring.datasource.pro.maxWait=60000
spring.datasource.pro.timeBetweenEvictionRunsMillis=60000
spring.datasource.pro.minEvictableIdleTimeMillis=300000
spring.datasource.pro.validationQuery=SELECT 1 FROM DUAL
spring.datasource.pro.testWhileIdle=true
spring.datasource.pro.testOnBorrow=false
spring.datasource.pro.testOnReturn=false
spring.datasource.pro.poolPreparedStatements=true
spring.datasource.pro.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.pro.filters=stat,wall,log4j
spring.datasource.pro.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

# car\u6570\u636E\u6E90\u914D\u7F6E
spring.datasource.dev.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.dev.driverClassName=com.mysql.jdbc.Driver
spring.datasource.dev.url=jdbc:mysql://127.0.0.1:3306/jta_dev?autoReconnect=true&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
spring.datasource.dev.username=root
spring.datasource.dev.password=root
spring.datasource.dev.initialSize=5
spring.datasource.dev.minIdle=5
spring.datasource.dev.maxActive=20
spring.datasource.dev.maxWait=60000
spring.datasource.dev.timeBetweenEvictionRunsMillis=60000
spring.datasource.dev.minEvictableIdleTimeMillis=300000
spring.datasource.dev.validationQuery=SELECT 1 FROM DUAL
spring.datasource.dev.testWhileIdle=true
spring.datasource.dev.testOnBorrow=false
spring.datasource.dev.testOnReturn=false
spring.datasource.dev.poolPreparedStatements=true
spring.datasource.dev.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.dev.filters=stat,wall,log4j
spring.datasource.dev.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
#模板引擎
freemarker:
    #关闭缓存
    cache: false
    request-context-attribute: request
    #模板加载的位置
    template-loader-path: classpath:/templates
    #前缀
    suffix: .htm
    #后缀
    prefix: /htm/

配置文件要有对应的映射类和数据源配置

DataSourceDevConfig

package com.boot.mvn.bootmvn.configs.datasources;

import com.alibaba.druid.pool.xa.DruidXADataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.interceptor.TransactionInterceptor;

import javax.sql.DataSource;
import java.util.Properties;

/**
 * Created by Administrator on 2018/5/29.
 */
@Configuration
@MapperScan(basePackages = {"com.boot.mvn.mapper.dev"}, sqlSessionTemplateRef = "sqlSessionTemplateDev") // 扫描dao或mapper接口
public class DataSourceDevConfig {
    @Primary
    @Bean(name = "dataSourceDev")
    public DataSource dataSourceDev(DataSourceDevProperties dataSourceDevProperties){
        DruidXADataSource dataSource = new DruidXADataSource();
        BeanUtils.copyProperties(dataSourceDevProperties,dataSource);
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(dataSource);
        xaDataSource.setUniqueResourceName("dataSourceDev");
        return xaDataSource;
    }


    @Bean(name = "sqlSessionFactoryDev")
    public SqlSessionFactory sqlSessionFactoryDev(@Qualifier("dataSourceDev") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setTypeAliasesPackage("com.boot.mvn.dev.entity");
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        bean.setConfigLocation(resolver.getResource("classpath:mybatis-config.xml"));
        bean.setMapperLocations(resolver.getResources("classpath:/mappers/dev/*Mapper.xml"));
        return bean.getObject();
    }

    @Bean(name = "sqlSessionTemplateDev")
    public SqlSessionTemplate sqlSessionTemplateDev(@Qualifier("sqlSessionFactoryDev") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    @Bean(name = "txDevAdvice")
    public TransactionInterceptor getDevAdvisor(DataSourceDevProperties dataSourceDevProperties) throws Exception {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSourceDev(dataSourceDevProperties));
        Properties properties = new Properties();
        properties.setProperty("get*", "PROPAGATION_REQUIRED,-Exception,readOnly");
        properties.setProperty("add*", "PROPAGATION_REQUIRED");
        properties.setProperty("save*", "PROPAGATION_REQUIRED");
        properties.setProperty("insert*", "PROPAGATION_REQUIRED");
        properties.setProperty("update*", "PROPAGATION_REQUIRED");
        properties.setProperty("delete*", "PROPAGATION_REQUIRED");
        TransactionInterceptor tsi = new TransactionInterceptor(transactionManager,properties);
        return tsi;
    }
    @Bean
    public BeanNameAutoProxyCreator txDevProxy() {
        BeanNameAutoProxyCreator creator = new BeanNameAutoProxyCreator();
        creator.setInterceptorNames("txDevAdvice");
        creator.setBeanNames("*Service", "*ServiceImpl");
        creator.setProxyTargetClass(true);
        return creator;
    }
}

DataSourceDevProperties

package com.boot.mvn.bootmvn.configs.datasources;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * Created by Administrator on 2018/5/29.
 */
@Component //自动注入
@ConfigurationProperties(prefix = "spring.datasource.dev")
public class DataSourceDevProperties {

    private String type;
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private int maxWait;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private String filters;
    private String connectionProperties;

    public void setType(String type) {
        this.type = type;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public void setMaxWait(int maxWait) {
        this.maxWait = maxWait;
    }

    public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public void setTestWhileIdle(boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testOnBorrow = testOnBorrow;
    }

    public void setTestOnReturn(boolean testOnReturn) {
        this.testOnReturn = testOnReturn;
    }

    public void setPoolPreparedStatements(boolean poolPreparedStatements) {
        this.poolPreparedStatements = poolPreparedStatements;
    }

    public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    }

    public void setFilters(String filters) {
        this.filters = filters;
    }

    public void setConnectionProperties(String connectionProperties) {
        this.connectionProperties = connectionProperties;
    }

    public String getType() {
        return type;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public String getUrl() {
        return url;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public int getInitialSize() {
        return initialSize;
    }

    public int getMinIdle() {
        return minIdle;
    }

    public int getMaxActive() {
        return maxActive;
    }

    public int getMaxWait() {
        return maxWait;
    }

    public int getTimeBetweenEvictionRunsMillis() {
        return timeBetweenEvictionRunsMillis;
    }

    public int getMinEvictableIdleTimeMillis() {
        return minEvictableIdleTimeMillis;
    }

    public String getValidationQuery() {
        return validationQuery;
    }

    public boolean isTestWhileIdle() {
        return testWhileIdle;
    }

    public boolean isTestOnBorrow() {
        return testOnBorrow;
    }

    public boolean isTestOnReturn() {
        return testOnReturn;
    }

    public boolean isPoolPreparedStatements() {
        return poolPreparedStatements;
    }

    public int getMaxPoolPreparedStatementPerConnectionSize() {
        return maxPoolPreparedStatementPerConnectionSize;
    }

    public String getFilters() {
        return filters;
    }

    public String getConnectionProperties() {
        return connectionProperties;
    }
}

DataSourceProConfig

package com.boot.mvn.bootmvn.configs.datasources;

import com.alibaba.druid.pool.xa.DruidXADataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.aop.framework.autoproxy.BeanNameAutoProxyCreator;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.jta.atomikos.AtomikosDataSourceBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.interceptor.TransactionInterceptor;

import javax.sql.DataSource;
import java.util.Properties;

/**
 * Created by Administrator on 2018/5/29.
 */
@Configuration
@MapperScan(basePackages = {"com.boot.mvn.mapper.pro"}, sqlSessionTemplateRef = "sqlSessionTemplatePro") // 扫描dao或mapper接口
public class DataSourceProConfig {
    @Bean(name = "dataSourcePro")
    public DataSource dataSourcePro(DataSourceProProperties dataSourceProProperties){
        DruidXADataSource dataSource = new DruidXADataSource();
        BeanUtils.copyProperties(dataSourceProProperties,dataSource);
        AtomikosDataSourceBean xaDataSource = new AtomikosDataSourceBean();
        xaDataSource.setXaDataSource(dataSource);
        xaDataSource.setUniqueResourceName("dataSourcePro");
        return xaDataSource;
    }

    @Bean(name = "sqlSessionFactoryPro")
    public SqlSessionFactory sqlSessionFactoryPro(@Qualifier("dataSourcePro") DataSource dataSource)throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setTypeAliasesPackage("com.boot.mvn.dev.entity");
        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        bean.setConfigLocation(resolver.getResource("classpath:mybatis-config.xml"));
        bean.setMapperLocations(resolver.getResources("classpath:/mappers/pro/*Mapper.xml"));
        return bean.getObject();
    }

    @Bean(name = "sqlSessionTemplatePro")
    public SqlSessionTemplate sqlSessionTemplatePro(@Qualifier("sqlSessionFactoryPro") SqlSessionFactory sqlSessionFactory) throws Exception {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
    @Bean(name = "txProAdvice")
    public TransactionInterceptor getProAdvisor(DataSourceProProperties dataSourceProProperties) throws Exception {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSourcePro(dataSourceProProperties));
        Properties properties = new Properties();
        properties.setProperty("get*", "PROPAGATION_REQUIRED,-Exception,readOnly");
        properties.setProperty("add*", "PROPAGATION_REQUIRED");
        properties.setProperty("save*", "PROPAGATION_REQUIRED");
        properties.setProperty("insert*", "PROPAGATION_REQUIRED");
        properties.setProperty("update*", "PROPAGATION_REQUIRED");
        properties.setProperty("delete*", "PROPAGATION_REQUIRED");
        TransactionInterceptor tsi = new TransactionInterceptor(transactionManager,properties);
        return tsi;
    }
    @Bean
    public BeanNameAutoProxyCreator txProProxy() {
        BeanNameAutoProxyCreator creator = new BeanNameAutoProxyCreator();
        creator.setInterceptorNames("txProAdvice");
        creator.setBeanNames("*Service", "*ServiceImpl");
        creator.setProxyTargetClass(true);
        return creator;
    }
}

DataSourceProProperties

package com.boot.mvn.bootmvn.configs.datasources;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;

/**
 * Created by Administrator on 2018/5/29.
 */
@Component //自动注入
@ConfigurationProperties(prefix = "spring.datasource.pro")
public class DataSourceProProperties {

    private String type;
    private String driverClassName;
    private String url;
    private String username;
    private String password;
    private int initialSize;
    private int minIdle;
    private int maxActive;
    private int maxWait;
    private int timeBetweenEvictionRunsMillis;
    private int minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private int maxPoolPreparedStatementPerConnectionSize;
    private String filters;
    private String connectionProperties;

    public void setType(String type) {
        this.type = type;
    }

    public void setDriverClassName(String driverClassName) {
        this.driverClassName = driverClassName;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public void setInitialSize(int initialSize) {
        this.initialSize = initialSize;
    }

    public void setMinIdle(int minIdle) {
        this.minIdle = minIdle;
    }

    public void setMaxActive(int maxActive) {
        this.maxActive = maxActive;
    }

    public void setMaxWait(int maxWait) {
        this.maxWait = maxWait;
    }

    public void setTimeBetweenEvictionRunsMillis(int timeBetweenEvictionRunsMillis) {
        this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
    }

    public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
        this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
    }

    public void setValidationQuery(String validationQuery) {
        this.validationQuery = validationQuery;
    }

    public void setTestWhileIdle(boolean testWhileIdle) {
        this.testWhileIdle = testWhileIdle;
    }

    public void setTestOnBorrow(boolean testOnBorrow) {
        this.testOnBorrow = testOnBorrow;
    }

    public void setTestOnReturn(boolean testOnReturn) {
        this.testOnReturn = testOnReturn;
    }

    public void setPoolPreparedStatements(boolean poolPreparedStatements) {
        this.poolPreparedStatements = poolPreparedStatements;
    }

    public void setMaxPoolPreparedStatementPerConnectionSize(int maxPoolPreparedStatementPerConnectionSize) {
        this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
    }

    public void setFilters(String filters) {
        this.filters = filters;
    }

    public void setConnectionProperties(String connectionProperties) {
        this.connectionProperties = connectionProperties;
    }

    public String getType() {
        return type;
    }

    public String getDriverClassName() {
        return driverClassName;
    }

    public String getUrl() {
        return url;
    }

    public String getUsername() {
        return username;
    }

    public String getPassword() {
        return password;
    }

    public int getInitialSize() {
        return initialSize;
    }

    public int getMinIdle() {
        return minIdle;
    }

    public int getMaxActive() {
        return maxActive;
    }

    public int getMaxWait() {
        return maxWait;
    }

    public int getTimeBetweenEvictionRunsMillis() {
        return timeBetweenEvictionRunsMillis;
    }

    public int getMinEvictableIdleTimeMillis() {
        return minEvictableIdleTimeMillis;
    }

    public String getValidationQuery() {
        return validationQuery;
    }

    public boolean isTestWhileIdle() {
        return testWhileIdle;
    }

    public boolean isTestOnBorrow() {
        return testOnBorrow;
    }

    public boolean isTestOnReturn() {
        return testOnReturn;
    }

    public boolean isPoolPreparedStatements() {
        return poolPreparedStatements;
    }

    public int getMaxPoolPreparedStatementPerConnectionSize() {
        return maxPoolPreparedStatementPerConnectionSize;
    }

    public String getFilters() {
        return filters;
    }

    public String getConnectionProperties() {
        return connectionProperties;
    }
}

通过上面的配置之后就可使分布式事物了，分布式事物使用有两种方式，一种是在方法或者类上面添加注解的方式，另一种是配置拦截器的方式实现。

方式一：@Transactional

方式二：

 @Bean(name = "txProAdvice")
    public TransactionInterceptor getProAdvisor(DataSourceProProperties dataSourceProProperties) throws Exception {
        DataSourceTransactionManager transactionManager = new DataSourceTransactionManager(dataSourcePro(dataSourceProProperties));
        Properties properties = new Properties();
        properties.setProperty("get*", "PROPAGATION_REQUIRED,-Exception,readOnly");
        properties.setProperty("add*", "PROPAGATION_REQUIRED");
        properties.setProperty("save*", "PROPAGATION_REQUIRED");
        properties.setProperty("insert*", "PROPAGATION_REQUIRED");
        properties.setProperty("update*", "PROPAGATION_REQUIRED");
        properties.setProperty("delete*", "PROPAGATION_REQUIRED");
        TransactionInterceptor tsi = new TransactionInterceptor(transactionManager,properties);
        return tsi;
    }
    @Bean
    public BeanNameAutoProxyCreator txProProxy() {
        BeanNameAutoProxyCreator creator = new BeanNameAutoProxyCreator();
        creator.setInterceptorNames("txProAdvice");
        creator.setBeanNames("*Service", "*ServiceImpl");
        creator.setProxyTargetClass(true);
        return creator;
    }

指定mybatis相关的配置分页插件和xml存放路径

@Bean(name = "sqlSessionFactoryPro")
public SqlSessionFactory sqlSessionFactoryPro(@Qualifier("dataSourcePro") DataSource dataSource)throws Exception {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(dataSource);
    bean.setTypeAliasesPackage("com.boot.mvn.dev.entity");
    PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    bean.setConfigLocation(resolver.getResource("classpath:mybatis-config.xml"));
    bean.setMapperLocations(resolver.getResources("classpath:/mappers/pro/*Mapper.xml"));
    return bean.getObject();
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="cacheEnabled" value="false"/>
        <setting name="mapUnderscoreToCamelCase" value="true" />
        <setting name="jdbcTypeForNull" value="NULL"/>
    </settings>
    <typeHandlers>
        <!--<typeHandler handler="com.pn.core.utils.persistence.BoolCharTypeHandler" javaType="Boolean" jdbcType="CHAR"/>-->
    </typeHandlers>
    <plugins>
        <plugin interceptor="com.boot.mvn.bootmvn.configs.mybatis.MybatisPaginationInterceptor">
            <property name="dialectClass" value="com.boot.mvn.bootmvn.configs.mybatis.MySQLDialect"/>
        </plugin>
    </plugins>
</configuration>

package com.boot.mvn.bootmvn.configs.mybatis;

import com.boot.mvn.bootmvn.configs.model.PageResult;
import org.apache.ibatis.binding.MapperMethod.ParamMap;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.parameter.ParameterHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.MappedStatement.Builder;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.scripting.defaults.DefaultParameterHandler;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.data.domain.Pageable;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;

/**
 * 分页插件，采用spring-data-common中的 Pageable 和 Page 对象作为输入和输出。
 * 只要方法参数中包含 Pageable 参数，则自动分页。
 * 
 * 方言类和部分逻辑来自： https://github.com/miemiedev/mybatis-paginator
 * 
 * @author lewis
 *
 */
@Intercepts({ @Signature(type = Executor.class, method = "query", args =
{ MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class } )})
public class MybatisPaginationInterceptor implements Interceptor {
   
   private static Logger logger = LoggerFactory.getLogger(MybatisPaginationInterceptor.class);
   
   static int MAPPED_STATEMENT_INDEX = 0;
   static int PARAMETER_INDEX = 1;
   static int ROWBOUNDS_INDEX = 2;
   static int RESULT_HANDLER_INDEX = 3;
   
   
   private Dialect dialect;

   @Override
   public Object intercept(Invocation inv) throws Throwable {
      
        final Object[] queryArgs = inv.getArgs();
      
        // 查找方法参数中的 分页请求对象
        Pageable pageRequest = this.findPageableObject(queryArgs[PARAMETER_INDEX]);
        
      // 如果需要分页
      if(pageRequest != null) {
         
           final MappedStatement ms = (MappedStatement)queryArgs[MAPPED_STATEMENT_INDEX];
           final Object parameter = queryArgs[PARAMETER_INDEX];
         
         final BoundSql boundSql = ms.getBoundSql(parameter);
         
         final StringBuffer bufferSql = new StringBuffer(boundSql.getSql().trim());
         if(bufferSql.lastIndexOf(";") == bufferSql.length()-1){
               bufferSql.deleteCharAt(bufferSql.length()-1);
           }
           

           String sql = bufferSql.toString();

           // 1. 搞定分页 记录总数          
         int total = this.queryTotal(sql, ms, boundSql);

         if(pageRequest.getSort() != null) {
            sql = dialect.getSortString(sql, pageRequest.getSort().iterator());
         }
         // 2. 搞定limit 查询
         // 2.1 获取分页SQL，并完成参数准备
         String limitSql = dialect.getLimitString(sql, pageRequest.getOffset(), pageRequest.getPageSize());
         
         queryArgs[ROWBOUNDS_INDEX] = new RowBounds(RowBounds.NO_ROW_OFFSET, RowBounds.NO_ROW_LIMIT);
         queryArgs[MAPPED_STATEMENT_INDEX] = copyFromNewSql(ms, boundSql, limitSql);
         
         // 2.2 继续执行生育步骤，获取查询结果
         Object ret = inv.proceed();
         
         // 3. 组成分页对象
//       Page<?> pi = new PageImpl<Object>((List) ret, pageRequest, total);
         //TODO dubbo 序列化问题

         PageResult<?> pi = new PageResult<Object>((List) ret, pageRequest.getPageSize(), total);
         
         // 4. MyBatis 需要返回一个List对象，这里只是满足MyBatis而作的临时包装
//       List<Page<?>> tmp = new ArrayList<Page<?>>(1);
         List<PageResult<?>> tmp = new ArrayList<PageResult<?>>(1);

         tmp.add(pi);
         return tmp;
      }
 
      return inv.proceed();
      
   }
   
   /**
    * 在方法参数中查找 分页请求对象
    * @param params Mapper接口方法中的参数对象
    * @return
    */
   private Pageable findPageableObject(Object params) {
      
      if (params == null) {
         return null;
      }
            
      if(Pageable.class.isAssignableFrom(params.getClass())) {
         return (Pageable) params;
      }
      
      else if (params instanceof ParamMap) {
         ParamMap<Object> paramMap = (ParamMap<Object>) params;
         for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
            Object paramValue = entry.getValue();
            
            if(Pageable.class.isAssignableFrom(paramValue.getClass())) {
               return (Pageable) paramValue;
            }  
         }
      }
      
      return null;
   }

   @Override
   public Object plugin(Object target) {
//    return Plugin.wrap(target, this);
      if (Executor.class.isAssignableFrom(target.getClass())) {
            return Plugin.wrap(target, this);
        }
      
        return target;
   }

   @Override
   public void setProperties(Properties p) {
      String dialectClass = p.getProperty("dialectClass");
      
      try {
            setDialect((Dialect) Class.forName(dialectClass).newInstance());
      } catch (Exception e) {
         throw new RuntimeException("cannot create dialect instance by dialectClass:"+dialectClass,e);
      }
      
   }
   
   /**
    * 查询总记录数
    * @param sql
    * @param mappedStatement
    * @param boundSql
    * @return
    */
   private int queryTotal(String sql, MappedStatement mappedStatement,
            BoundSql boundSql) {
      
      Connection connection = null;
        PreparedStatement countStmt = null;
        ResultSet rs = null;
        try {

           connection = mappedStatement.getConfiguration().getEnvironment().getDataSource().getConnection();
           
          String countSql = this.dialect.getCountString(sql);
       
            countStmt = connection.prepareStatement(countSql);
            BoundSql countBoundSql = new BoundSql(mappedStatement.getConfiguration(), countSql,
                    boundSql.getParameterMappings(), boundSql.getParameterObject());
            
            setParameters(countStmt, mappedStatement, countBoundSql, boundSql.getParameterObject());
            
            
            
            rs = countStmt.executeQuery();
            int totalCount = 0;
            if (rs.next()) {
                totalCount = rs.getInt(1);
                if(rs.next()){  //如果记录集中的条数不止为1条  则需要返回总的行数
                   rs.last();
                   totalCount = rs.getRow();
            }
            }

            return totalCount;
        } catch (SQLException e) {
            logger.error("Ignore this exception", e);
         logger.info(e.getMessage());
        } finally {
            try {
            if(rs != null)
                   rs.close();
            } catch (SQLException e) {
                logger.error("Ignore this exception", e);
            }
            try {
            if(countStmt != null)
                   countStmt.close();
            if(connection != null)
               connection.close();
            } catch (SQLException e) {
                logger.error("Ignore this exception", e);
            }
        }
      
      return 0;
   }
   
   /**
     * 对SQL参数(?)设值
     * 
     * @param ps
     * @param mappedStatement
     * @param boundSql
     * @param parameterObject
     * @throws SQLException
     */
    private void setParameters(PreparedStatement ps, MappedStatement mappedStatement, BoundSql boundSql,
                               Object parameterObject) throws SQLException {
        ParameterHandler parameterHandler = new DefaultParameterHandler(mappedStatement, parameterObject, boundSql);
        parameterHandler.setParameters(ps);
    }

   public Dialect getDialect() {
      return dialect;
   }

   public void setDialect(Dialect dialect) {
      this.dialect = dialect;
   }

   private MappedStatement copyFromNewSql(MappedStatement ms,
                                           BoundSql boundSql, String sql) {
      BoundSql newBoundSql = copyFromBoundSql(ms, boundSql, sql);
      return copyFromMappedStatement(ms, new BoundSqlSqlSource(newBoundSql));
   }
   
   public static class BoundSqlSqlSource implements SqlSource {
      BoundSql boundSql;
      public BoundSqlSqlSource(BoundSql boundSql) {
         this.boundSql = boundSql;
      }
      public BoundSql getBoundSql(Object parameterObject) {
         return boundSql;
      }
   }
   
   private BoundSql copyFromBoundSql(MappedStatement ms, BoundSql boundSql,
                                      String sql) {
      BoundSql newBoundSql = new BoundSql(ms.getConfiguration(),sql, boundSql.getParameterMappings(), boundSql.getParameterObject());
      for (ParameterMapping mapping : boundSql.getParameterMappings()) {
          String prop = mapping.getProperty();
          if (boundSql.hasAdditionalParameter(prop)) {
              newBoundSql.setAdditionalParameter(prop, boundSql.getAdditionalParameter(prop));
          }
      }
      return newBoundSql;
   }
   
   //see: MapperBuilderAssistant
   private MappedStatement copyFromMappedStatement(MappedStatement ms, SqlSource newSqlSource) {
      Builder builder = new Builder(ms.getConfiguration(),ms.getId(),newSqlSource,ms.getSqlCommandType());
      
      builder.resource(ms.getResource());
      builder.fetchSize(ms.getFetchSize());
      builder.statementType(ms.getStatementType());
      builder.keyGenerator(ms.getKeyGenerator());
      if(ms.getKeyProperties() != null && ms.getKeyProperties().length !=0){
            StringBuffer keyProperties = new StringBuffer();
            for(String keyProperty : ms.getKeyProperties()){
                keyProperties.append(keyProperty).append(",");
            }
            keyProperties.delete(keyProperties.length()-1, keyProperties.length());
         builder.keyProperty(keyProperties.toString());
      }
      
      //setStatementTimeout()
      builder.timeout(ms.getTimeout());
      
      //setStatementResultMap()
      builder.parameterMap(ms.getParameterMap());
      
      //setStatementResultMap()
        builder.resultMaps(ms.getResultMaps());
      builder.resultSetType(ms.getResultSetType());
       
      //setStatementCache()
      builder.cache(ms.getCache());
      builder.flushCacheRequired(ms.isFlushCacheRequired());
      builder.useCache(ms.isUseCache());
      
      return builder.build();
   }
}

package com.boot.mvn.bootmvn.configs.mybatis;

public class MySQLDialect extends Dialect{

   public boolean supportsLimitOffset(){
      return true;
   }
   
    public boolean supportsLimit() {   
        return true;   
    }  
    
   public String getLimitString(String sql, int offset,String offsetPlaceholder, int limit, String limitPlaceholder) {
        if (offset > 0) {   
           return sql + " limit "+offsetPlaceholder+","+limitPlaceholder; 
        } else {   
            return sql + " limit "+limitPlaceholder;
        }  
   }   
  
}

maven项目的主要配置文件pom.xml内容如下：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
   <modelVersion>4.0.0</modelVersion>

   <groupId>com.boot.mvn</groupId>
   <artifactId>mvnboot</artifactId>
   <version>0.0.1-SNAPSHOT</version>
   <packaging>war</packaging>

   <name>mvnboot</name>
   <description>Demo project for Spring Boot</description>

   <parent>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-parent</artifactId>
      <version>1.5.6.RELEASE</version>
      <relativePath/> <!-- lookup parent from repository -->
   </parent>

   <properties>
      <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
      <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
      <java.version>1.8</java.version>
      <mybatis-spring-boot>1.2.0</mybatis-spring-boot>
      <mysql-connector>5.1.39</mysql-connector>
      <commons-lang3.version>3.4</commons-lang3.version>
      <druid>1.0.18</druid>
      <java.version>1.8</java.version>
   </properties>


   <dependencies>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-web</artifactId>
      </dependency>

      <!--添加外部tomcat启动依赖jar包-->
      <dependency>
         <groupId>org.apache.tomcat.embed</groupId>
         <artifactId>tomcat-embed-jasper</artifactId>
         <version>8.5.23</version>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-tomcat</artifactId>
         <scope>provided</scope>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-test</artifactId>
         <scope>test</scope>
      </dependency>


      <!-- Spring Boot Mybatis 依赖 -->
      <dependency>
         <groupId>org.mybatis.spring.boot</groupId>
         <artifactId>mybatis-spring-boot-starter</artifactId>
         <version>1.1.1</version>
      </dependency>

      <!-- MySQL 连接驱动依赖 -->
      <dependency>
         <groupId>mysql</groupId>
         <artifactId>mysql-connector-java</artifactId>
         <version>${mysql-connector}</version>
      </dependency>

      <!-- Druid 数据连接池依赖 -->
      <dependency>
         <groupId>com.alibaba</groupId>
         <artifactId>druid</artifactId>
         <version>${druid}</version>
      </dependency>

      <!-- 分页插件 -->
      <dependency>
         <groupId>com.github.pagehelper</groupId>
         <artifactId>pagehelper</artifactId>
         <version>4.1.6</version>
      </dependency>
      <!-- Apache工具组件 -->
      <dependency>
         <groupId>org.apache.commons</groupId>
         <artifactId>commons-lang3</artifactId>
         <version>${commons-lang3.version}</version>
      </dependency>
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-jta-atomikos</artifactId>
      </dependency>
      <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-starter-data-jpa -->
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-starter-data-jpa</artifactId>
      </dependency>
      <!-- https://mvnrepository.com/artifact/org.springframework.boot/spring-boot-autoconfigure -->
      <dependency>
         <groupId>org.springframework.boot</groupId>
         <artifactId>spring-boot-autoconfigure</artifactId>
      </dependency>




      <!-- https://mvnrepository.com/artifact/org.springframework.data/spring-data-commons -->
      <dependency>
         <groupId>org.springframework.data</groupId>
         <artifactId>spring-data-commons</artifactId>
         <version>1.13.7.RELEASE</version>
      </dependency>


   </dependencies>

   <build>
      <plugins>
         <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
         </plugin>
      </plugins>
   </build>


</project>