Springboot+MyBatis+Druid+Pagehelper双数据源配置范例
application.yml(properties)
特别说明：主数据源的分页插件单独配置，走自动部署，从数据源需要自己配置分页插件，都按照从的配置方式，主分页不可用，从可用，最后尝试出该种配置方式是都可用的
# primary数据库(master)分页插件 pagehelper: helperDialect: com.github.pagehelper.dialect.helper.SqlServerDialect offsetAsPageNum: true # master数据源 master: datasource: driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver url: jdbc:sqlserver://localhost:1433;DatabaseName=master username: abc password: UoZHn0nefR2GEc4kK18OXKlwjaQTTkUJly9j72i+ShNM4M+AN8fy3V8KO9anKn4KxvXrXS1c1qHQ2fQG+rIPKQ== initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true connection-properties: druid.stat.mergeSql:true;druid.stat.slowSqlMillis:5000 # slave数据源 slave: datasource: driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver url: jdbc:sqlserver://localhost;DatabaseName=slave username: abc password: UoZHn0nefR2GEc4kK18OXKlwjaQTTkUJly9j72i+ShNM4M+AN8fy3V8KO9anKn4KxvXrXS1c1qHQ2fQG+rIPKQ== initialSize: 5 minIdle: 5 maxActive: 20 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis: 300000 validationQuery: SELECT 1 testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true connection-properties: druid.stat.mergeSql:true;druid.stat.slowSqlMillis:5000 # 分页插件(slave) pagehelper: helperDialect: com.github.pagehelper.dialect.helper.SqlServerDialect offsetAsPageNum: true
JavaConfig
两个java类，除了SIGN值不同其他完全相同，即通过扫描的包区分不同数据库;
特别说明：@Primary注解只能加到一个类上，另外一个不加，否则报错
master
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
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 com.alibaba.druid.pool.DruidDataSource;
/**
* @author IceWee
* @Description master数据库配置，所有需要访问master数据库的DAO（以及mapper）都需要定义在com.demo.master.**.dao下
* @version
*/
@Configuration
@MapperScan(basePackages = MasterDataSourceConfig.PACKAGE, sqlSessionFactoryRef = MasterDataSourceConfig.SQL_SESSION_FACTORY)
public class MasterDataSourceConfig {
public static final String SIGN = "master"; // 唯一标识
public static final String DATASOURCE = SIGN + "DataSource";
public static final String TRANSACTION_MANAGER = SIGN + "TransactionManager";
public static final String SQL_SESSION_FACTORY = SIGN + "SqlSessionFactory";
public static final String PACKAGE = "com.demo." + SIGN + ".**.dao";
public static final String MAPPER_LOCATION = "classpath:com/demo/" + SIGN + "/**/*.xml";
@Value("${" + SIGN + ".datasource.url}")
private String url;
@Value("${" + SIGN + ".datasource.username}")
private String user;
@Value("${" + SIGN + ".datasource.password}")
private String password;
@Value("${" + SIGN + ".datasource.driver-class-name}")
private String driverClass;
@Value("${" + SIGN + ".datasource.initialSize}")
private int initialSize;
@Value("${" + SIGN + ".datasource.minIdle}")
private int minIdle;
@Value("${" + SIGN + ".datasource.maxActive}")
private int maxActive;
@Value("${" + SIGN + ".datasource.maxWait}")
private int maxWait;
@Value("${" + SIGN + ".datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${" + SIGN + ".datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${" + SIGN + ".datasource.validationQuery}")
private String validationQuery;
@Value("${" + SIGN + ".datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${" + SIGN + ".datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${" + SIGN + ".datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${" + SIGN + ".datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${" + SIGN + ".datasource.connection-properties}")
private String connectionProperties;
@Primary
@Bean(name = DATASOURCE)
public DataSource dataSource() throws Exception {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenConnectErrorMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setConnectionProperties(connectionProperties);
return dataSource;
}
@Primary
@Bean(name = TRANSACTION_MANAGER)
public DataSourceTransactionManager transactionManager() throws Exception {
return new DataSourceTransactionManager(dataSource());
}
@Primary
@Bean(name = SQL_SESSION_FACTORY)
public SqlSessionFactory sqlSessionFactory(@Qualifier(DATASOURCE) DataSource dataSource) throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
slave
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import com.alibaba.druid.pool.DruidDataSource;
import com.github.pagehelper.PageInterceptor;
/**
* @author IceWee
* @Description slave数据库配置，所有需要访问slave数据库的DAO（以及mapper）都需要定义在com.demo.slave.**.dao下
* @version
*/
@Configuration
@MapperScan(basePackages = SlaveDataSourceConfig.PACKAGE, sqlSessionFactoryRef = SlaveDataSourceConfig.SQL_SESSION_FACTORY)
public class SlaveDataSourceConfig {
public static final String SIGN = "slave"; // 唯一标识
public static final String DATASOURCE = SIGN + "DataSource";
public static final String TRANSACTION_MANAGER = SIGN + "TransactionManager";
public static final String SQL_SESSION_FACTORY = SIGN + "SqlSessionFactory";
public static final String PACKAGE = "com.demo." + SIGN + ".**.dao";
public static final String MAPPER_LOCATION = "classpath:com/demo/" + SIGN + "/**/*.xml";
@Value("${" + SIGN + ".datasource.url}")
private String url;
@Value("${" + SIGN + ".datasource.username}")
private String user;
@Value("${" + SIGN + ".datasource.password}")
private String password;
@Value("${" + SIGN + ".datasource.driver-class-name}")
private String driverClass;
@Value("${" + SIGN + ".datasource.initialSize}")
private int initialSize;
@Value("${" + SIGN + ".datasource.minIdle}")
private int minIdle;
@Value("${" + SIGN + ".datasource.maxActive}")
private int maxActive;
@Value("${" + SIGN + ".datasource.maxWait}")
private int maxWait;
@Value("${" + SIGN + ".datasource.timeBetweenEvictionRunsMillis}")
private int timeBetweenEvictionRunsMillis;
@Value("${" + SIGN + ".datasource.minEvictableIdleTimeMillis}")
private int minEvictableIdleTimeMillis;
@Value("${" + SIGN + ".datasource.validationQuery}")
private String validationQuery;
@Value("${" + SIGN + ".datasource.testWhileIdle}")
private boolean testWhileIdle;
@Value("${" + SIGN + ".datasource.testOnBorrow}")
private boolean testOnBorrow;
@Value("${" + SIGN + ".datasource.testOnReturn}")
private boolean testOnReturn;
@Value("${" + SIGN + ".datasource.poolPreparedStatements}")
private boolean poolPreparedStatements;
@Value("${" + SIGN + ".datasource.connection-properties}")
private String connectionProperties;
@Value("${" + SIGN + ".pagehelper.offsetAsPageNum}")
private String offsetAsPageNum;
@Value("${" + SIGN + ".pagehelper.helperDialect}")
private String helperDialect;
@Bean(name = DATASOURCE)
public DataSource dataSource() throws Exception {
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(driverClass);
dataSource.setUrl(url);
dataSource.setUsername(user);
dataSource.setPassword(password);
dataSource.setInitialSize(initialSize);
dataSource.setMinIdle(minIdle);
dataSource.setMaxActive(maxActive);
dataSource.setMaxWait(maxWait);
dataSource.setTimeBetweenConnectErrorMillis(timeBetweenEvictionRunsMillis);
dataSource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
dataSource.setValidationQuery(validationQuery);
dataSource.setTestWhileIdle(testWhileIdle);
dataSource.setTestOnBorrow(testOnBorrow);
dataSource.setTestOnReturn(testOnReturn);
dataSource.setPoolPreparedStatements(poolPreparedStatements);
dataSource.setConnectionProperties(connectionProperties);
return dataSource;
}
@Bean(name = TRANSACTION_MANAGER)
public DataSourceTransactionManager transactionManager() throws Exception {
return new DataSourceTransactionManager(dataSource());
}
@Bean(name = SQL_SESSION_FACTORY)
public SqlSessionFactory sqlSessionFactory(@Qualifier(DATASOURCE) DataSource dataSource) throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataSource);
// 分页拦截器-begin
PageInterceptor interceptor = new PageInterceptor();
Properties properties = new Properties();
properties.setProperty("helperDialect", helperDialect);
properties.setProperty("offsetAsPageNum", offsetAsPageNum);
interceptor.setProperties(properties);
sessionFactory.getObject().getConfiguration().addInterceptor(interceptor);
// 分页拦截器-end
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(MAPPER_LOCATION));
return sessionFactory.getObject();
}
}
