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();
	}

}

posted on 2017-10-20 13:56 IceWee 阅读(379) 评论(0) 编辑收藏举报

刷新页面返回顶部

ice world

Springboot+MyBatis+Druid+Pagehelper双数据源配置范例

Springboot+MyBatis+Druid+Pagehelper双数据源配置范例

application.yml(properties)

JavaConfig

master

slave

导航

公告