扩展Myabatis Pagehelper支持多数据源的分页插件

2019独角兽企业重金招聘Python工程师标准>>>

http://git.oschina.net/free/Mybatis_PageHelper/blob/master/wikis/Important.markdown

结合spring使用mybatis pagehelper只能配置一个bean，当一个项目使用多种数据库时就比较纠结了，下面是我参考spring支持多数据源的思想封装一个支持多数据源的PageHelper。

实现一个mybatis插件，参考mybatis pagehelper，实现根据不同的数据源切换使用不同的pagehelper。

@SuppressWarnings({"rawtypes", "unchecked"})
@Intercepts(@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}))
public class CustomPageHelper implements Interceptor {

    private PageHelper mysqlPageHelper = new PageHelper();

    private PageHelper oraclePageHelper = new PageHelper();

    private PageHelper postgresqlPageHelper = new PageHelper();

    private Map<Object, PageHelper> targetPageHelper = new HashMap<>();

    private PageHelper defaultPageHelper;


    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        return determinePageHelper().intercept(invocation);
    }

    @Override
    public Object plugin(Object target) {
        /*return determinePageHelper().plugin(target);*/
        //determinePageHelper();
        if (target instanceof Executor) {
            return Plugin.wrap(target, this);
        } else {
            return target;
        }
    }

    @Override
    public void setProperties(Properties properties) {
        targetPageHelper.put(Dialect.mysql.name(), mysqlPageHelper);
        targetPageHelper.put(Dialect.oracle.name(), oraclePageHelper);
        targetPageHelper.put(Dialect.postgresql.name(), postgresqlPageHelper);
        //数据库方言
        String dialect = properties.getProperty("dialect");
        if(Dialect.oracle.equals(Dialect.valueOf(dialect.toLowerCase()))) {
            defaultPageHelper = oraclePageHelper;
        } else if(Dialect.postgresql.equals(Dialect.valueOf(dialect.toLowerCase()))) {
            defaultPageHelper = postgresqlPageHelper;
        } else {
            defaultPageHelper = mysqlPageHelper;
        }

        properties.put("dialect", Dialect.mysql.name());
        mysqlPageHelper.setProperties(properties);

        properties.put("dialect", Dialect.oracle.name());
        oraclePageHelper.setProperties(properties);

        properties.put("dialect", Dialect.postgresql.name());
        postgresqlPageHelper.setProperties(properties);

        properties.put("dialect", dialect);
    }

    private PageHelper determinePageHelper() {
        String pageType = PageHelperHolder.getPagerType();
        PageHelper pageHelper = targetPageHelper.get(pageType);
        if (pageHelper != null) {
            return pageHelper;
        } else {
            return defaultPageHelper;
        }
    }
}

    <!-- Oracle配置数据源-->
    <bean id="oracleDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
          destroy-method="close">
        <property name="driverClass" value="${oracle.jdbc.driver}"/>
        <property name="jdbcUrl" value="${oracle.jdbc.url}"/>
        <property name="user" value="${oracle.jdbc.user}"/>
        <property name="password" value="${oracle.jdbc.password}"/>
        <property name="minPoolSize" value="${oracle.jdbc.minPoolSize}"/>
        <property name="maxPoolSize" value="${oracle.jdbc.maxPoolSize}"/>
        <property name="initialPoolSize" value="${oracle.jdbc.initialPoolSize}"/>
        <property name="maxIdleTime" value="${oracle.jdbc.maxIdleTime}"/>
        <property name="acquireIncrement" value="${oracle.jdbc.acquireIncrement}"/>
        <property name="acquireRetryAttempts" value="${oracle.jdbc.acquireRetryAttempts}"/>
        <property name="acquireRetryDelay" value="${oracle.jdbc.acquireRetryDelay}"/>
        <property name="testConnectionOnCheckin" value="${oracle.jdbc.testConnectionOnCheckin}"/>
        <property name="automaticTestTable" value="${oracle.jdbc.automaticTestTable}"/>
        <property name="idleConnectionTestPeriod" value="${oracle.jdbc.idleConnectionTestPeriod}"/>
        <property name="checkoutTimeout" value="${oracle.jdbc.checkoutTimeout}"/>
    </bean>

    <!-- Mysql配置数据源-->
    <bean id="mysqlDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"
          destroy-method="close">
        <property name="driverClass" value="${mysql.jdbc.driver}"/>
        <property name="jdbcUrl" value="${mysql.jdbc.url}"/>
        <property name="user" value="${mysql.jdbc.user}"/>
        <property name="password" value="${mysql.jdbc.password}"/>
        <property name="minPoolSize" value="${mysql.jdbc.minPoolSize}"/>
        <property name="maxPoolSize" value="${mysql.jdbc.maxPoolSize}"/>
        <property name="initialPoolSize" value="${mysql.jdbc.initialPoolSize}"/>
        <property name="maxIdleTime" value="${mysql.jdbc.maxIdleTime}"/>
        <property name="acquireIncrement" value="${mysql.jdbc.acquireIncrement}"/>
        <property name="acquireRetryAttempts" value="${mysql.jdbc.acquireRetryAttempts}"/>
        <property name="acquireRetryDelay" value="${mysql.jdbc.acquireRetryDelay}"/>
        <property name="testConnectionOnCheckin" value="${mysql.jdbc.testConnectionOnCheckin}"/>
        <property name="automaticTestTable" value="${mysql.jdbc.automaticTestTable}"/>
        <property name="idleConnectionTestPeriod" value="${mysql.jdbc.idleConnectionTestPeriod}"/>
        <property name="checkoutTimeout" value="${mysql.jdbc.checkoutTimeout}"/>
    </bean>

    <bean id="pgDataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
        <!-- 基本属性 url、user、password -->
        <property name="driverClassName" value="${pg.db.drivername}"/>
        <property name="url" value="${pg.db.url}" />
        <property name="username" value="${pg.db.username}" />
        <property name="password" value="${pg.db.password}" />
        <property name="dbType" value="postgresql" />

        <!-- 配置初始化大小、最小、最大 -->
        <property name="initialSize" value="1" />
        <property name="minIdle" value="1" />
        <property name="maxActive" value="50" />

        <!-- 配置获取连接等待超时的时间 -->
        <property name="maxWait" value="60000" />

        <!-- 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒 -->
        <property name="timeBetweenEvictionRunsMillis" value="60000" />

        <!-- 配置一个连接在池中最小生存的时间，单位是毫秒 -->
        <property name="minEvictableIdleTimeMillis" value="300000" />

        <property name="validationQuery" value="SELECT 1" />
        <property name="testWhileIdle" value="true" />
        <property name="testOnBorrow" value="false" />
        <property name="testOnReturn" value="true" />

        <!-- 打开PSCache，并且指定每个连接上PSCache的大小 -->
        <property name="poolPreparedStatements" value="true" />
        <property name="maxPoolPreparedStatementPerConnectionSize" value="20" />

        <!-- 配置监控统计拦截的filters -->
        <property name="filters" value="stat" />
    </bean>

    <bean id="multipleDataSource" class="com.common.support.MultipleDataSource">
        <property name="defaultTargetDataSource" ref="mysqlDataSource"/>
        <property name="targetDataSources">
            <map>
                <entry key="mySqlDataSource" value-ref="mysqlDataSource"/>
                <entry key="passSqlDataSource" value-ref="passsqlDataSource"/>
                <entry key="oracleDataSource" value-ref="oracleDataSource"/>
                <entry key="greenplumDataSource" value-ref="greenplumDataSource"/>
            </map>
        </property>
    </bean>

    <!-- 配置sql会话工厂：SqlSessionFactoryBean -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="multipleDataSource" />
        <!--<property name="dataSource" ref="oracleDataSource"/>-->
        <property name="plugins">
            <array>
                <bean class="com.common.support.CustomPageHelper" scope="singleton">
                    <property name="properties">
                        <value>dialect=mysql</value>
                    </property>
                </bean>
            </array>
        </property>
    </bean>

最后定义一个PageHelperHolder，在数据源切换时把当前使用的数据库类型放进来以为Pagehelper动态切换数据源。

public final class PageHelperHolder {
//    public enum PagerType {
//        MySql, Oracle
//    }
    private static final ThreadLocal<String> contextHolder = new ThreadLocal<>();

    public static void setPagerType(Dialect Dialect) {
        contextHolder.set(Dialect.name());
    }

    public static String getPagerType() {
        return contextHolder.get();
    }

    public static void clearPaerType() {
        contextHolder.remove();
    }
}

自定义注解，用来加到mapper上面，aop拦截mybatis mapper方法时根据注解来判断数据源类型从而决定用哪个分页

@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.METHOD})
@Documented
public @interface DataSourceSelector {
    DataSourceType value();
}

public enum DataSourceType {
    MYSQL_DATASOURCE("mySqlDataSource", Dialect.mysql),
    ORACLE_DATASOURCE("oracleDataSource", Dialect.oracle),
    GREENPLUM_DATASOURCE("pgDataSource", Dialect.postgresql),

    private String type;
    private Dialect dialect;

    DataSourceType(String type, Dialect dialect) {
        this.type = type;
        this.dialect = dialect;
    }

    public String getType() {
        return type;
    }

    public Dialect getDialect() {
        return dialect;
    }

    public static DataSourceType getType(String type) {
        for(DataSourceType dataSourceType : DataSourceType.values()) {
            if(dataSourceType.type.equals(type)) {
                return dataSourceType;
            }
        }
        return null;
    }
}

下面是spring aop拦截mapper的重点代码片段

@Aspect
public class MultipleDataSourceAspectAdvice {    
    @Around("execution(* com.mapper.*.*(..))")
    public Object doAround(ProceedingJoinPoint jp) throws Throwable {
        return DataSourceHolder.getDynaDataSource(jp);
    }
}

    //做mapper使用数据源类型缓存，不用每次都要从注解中读取 
    private transient static Map<Class, String> dataSourceHolder = new HashMap<>();
    public static Object getDynaDataSource(ProceedingJoinPoint jp) throws Throwable {
        Object result = null;
        Class targetClass = jp.getThis().getClass();
        try {
            log.debug("MultipleDataSourceAspectAdvice: {}, {}", jp.getThis(), jp.getTarget());
            //获取我们写的mapper类，以便读取到类上面的注解
            Class[] interfaces = targetClass.getInterfaces();
            if (interfaces != null && interfaces.length > 0) {
                targetClass = interfaces[0];
            }
            String targetDataSource = dataSourceHolder.get(targetClass);
            boolean isHold = false;
            if (StringUtils.isBlank(targetDataSource)) {
                //读取注解上声明要使用的数据源类型
                DataSourceSelector dss = (DataSourceSelector) targetClass.getAnnotation(DataSourceSelector.class);
                if (dss != null) {
                    targetDataSource = dss.value().getType();
                }
            } else {
                isHold = true;
            }

            if (StringUtils.isNotBlank(targetDataSource)) {
                DataSourceType dataSourceType = DataSourceType.getType(targetDataSource);
                if(dataSourceType != null) {
                    DataSourceHolder.setCustomerType(dataSourceType.getType());
                    PageHelperHolder.setPagerType(dataSourceType.getDialect());
                    if (!isHold) {
                        dataSourceHolder.put(targetClass, targetDataSource);
                    }
                } else {
                    log.warn("{}-{} not found available dataSourceType, use default.", targetClass, targetDataSource);
                }
            }
            log.debug("{}: {}", targetClass, dataSourceHolder.get(targetClass));
            result = jp.proceed();
        } catch (Exception ex) {
            log.error("deal with dynamic datasource error. datasource: {}", DataSourceHolder.getCustomerType());
            dataSourceHolder.remove(targetClass);
            throw ex;
        } finally {
            DataSourceHolder.clearCustomerType();
            PageHelperHolder.clearPaerType();
        }

        return result;
    }

mapper类

@DataSourceSelector(DataSourceType.MYSQL_DATASOURCE)
public interface SysRoleMapper {
    ……
}

大体实现就是这样子了，要注意的是spring数据源的配置bean的id和DataSourceType中定义的名称了一致，要不然key不一样会取不到哦。

转载于:https://my.oschina.net/u/565351/blog/749662