spring boot +mybatis+druid 多数据源配置
因为我的工程需要在两个数据库中操作数据，所以要配置两个数据库，我这里没有数据源没有什么主从之分，只是配合多数据源必须要指定一个主数据源，所以我就把
操作相对要对的那个数据库设置为主数据（dataBase1 库）源
数据源配置
###################本地应用配置#################
spring:
application:
name: test
datasource:
url:
dataBase1: jdbc:mysql://192.168.666666.66:3306/dataBase1
dataBase2: jdbc:mysql://192.168.66666.67:3306/dataBase2?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
username:
dataBase1: dataBase1
dataBase2: dataBase2
password:
dataBase1: dataBase1
dataBase2: dataBase2
driver: com.mysql.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
initialSize: 5
minIdle: 5
maxActive: 20
maxWait: 10000
timeBetweenEvictionRunsMillis: 60000
minEvictableIdleTimeMillis: 300000
validationQuery: SELECT 1 FROM DUAL
testWhileIdle: true
testOnBorrow: true
testOnReturn: false
poolPreparedStatements: true
maxOpenPreparedStatementConnectionSize: 20
filters: stat,wall,log4j
connectionProperties: druid.stat.mergeSql:true;druid.stat.slowSqlMillis:5000
数据源抽象类（因为数据库的配置属性太多了，所以把通用的数据放到这个抽象类中，默认的配置也是主数据源）
package so.dian.device.common.config;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Configuration;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
@Configuration
public abstract class AbstractDBConfig {
private static Logger log = LoggerFactory.getLogger(AbstractDBConfig.class);
private String driver;
private String url;
private String username;
private String password;
private int maxActive;
private int maxWait;
private int initialSize;
private int minEvictableIdleTimeMillis;
private int minIdle;
private String validationQuery;
private boolean testWhileIdle;
private boolean testOnBorrow;
private boolean testOnReturn;
private String connectionProperties;
private String filters;
private long timeBetweenEvictionRunsMillis;
private boolean poolPreparedStatements;
private int maxOpenPreparedStatementConnectionSize;
private String mapperLocations;
@Value("${maxOpenPreparedStatementConnectionSize}")
public int getMaxOpenPreparedStatementConnectionSize() {
return maxOpenPreparedStatementConnectionSize;
}
public void setMaxOpenPreparedStatementConnectionSize(int maxOpenPreparedStatementConnectionSize) {
this.maxOpenPreparedStatementConnectionSize = maxOpenPreparedStatementConnectionSize;
}
public String getMapperLocations() {
return mapperLocations;
}
@Value("${mybatis.configuration.mapper-locations.dataBase1}")
public void setMapperLocations(String mapperLocations) {
this.mapperLocations = mapperLocations;
}
public String getFilters() {
return filters;
}
public String getDriver() {
return driver;
}
@Value("${spring.datasource.driver}")
public void setDriver(String driver) {
this.driver = driver;
}
public String getUrl() {
return url;
}
@Value("${spring.datasource.url.dataBase1}")
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
@Value("${spring.datasource.username.dataBase1}")
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
@Value("${spring.datasource.password.dataBase1}")
public void setPassword(String password) {
this.password = password;
}
public int getMaxActive() {
return maxActive;
}
@Value("${spring.datasource.maxActive}")
public void setMaxActive(int maxActive) {
this.maxActive = maxActive;
}
public int getMaxWait() {
return maxWait;
}
@Value("${spring.datasource.maxWait}")
public void setMaxWait(int maxWait) {
this.maxWait = maxWait;
}
public int getInitialSize() {
return initialSize;
}
@Value("${spring.datasource.initialSize}")
public void setInitialSize(int initialSize) {
this.initialSize = initialSize;
}
public int getMinEvictableIdleTimeMillis() {
return minEvictableIdleTimeMillis;
}
@Value("${spring.datasource.minEvictableIdleTimeMillis}")
public void setMinEvictableIdleTimeMillis(int minEvictableIdleTimeMillis) {
this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
}
public int getMinIdle() {
return minIdle;
}
@Value("${spring.datasource.minIdle}")
public void setMinIdle(int minIdle) {
this.minIdle = minIdle;
}
public String getValidationQuery() {
return validationQuery;
}
@Value("${spring.datasource.validationQuery}")
public void setValidationQuery(String validationQuery) {
this.validationQuery = validationQuery;
}
public boolean isTestWhileIdle() {
return testWhileIdle;
}
@Value("${spring.datasource.testWhileIdle}")
public void setTestWhileIdle(boolean testWhileIdle) {
this.testWhileIdle = testWhileIdle;
}
public boolean isTestOnBorrow() {
return testOnBorrow;
}
@Value("${spring.datasource.testOnBorrow}")
public void setTestOnBorrow(boolean testOnBorrow) {
this.testOnBorrow = testOnBorrow;
}
public boolean isTestOnReturn() {
return testOnReturn;
}
@Value("${spring.datasource.testOnReturn}")
public void setTestOnReturn(boolean testOnReturn) {
this.testOnReturn = testOnReturn;
}
public String getConnectionProperties() {
return connectionProperties;
}
@Value("${spring.datasource.connectionProperties}")
public void setConnectionProperties(String connectionProperties) {
this.connectionProperties = connectionProperties;
}
@Value("${spring.datasource.filters}")
public void setFilters(String filters) {
this.filters = filters;
}
public long getTimeBetweenEvictionRunsMillis() {
return timeBetweenEvictionRunsMillis;
}
@Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
public void setTimeBetweenEvictionRunsMillis(long timeBetweenEvictionRunsMillis) {
this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
}
public boolean isPoolPreparedStatements() {
return poolPreparedStatements;
}
@Value("${spring.datasource.poolPreparedStatements}")
public void setPoolPreparedStatements(boolean poolPreparedStatements) {
this.poolPreparedStatements = poolPreparedStatements;
}
public int getmaxOpenPreparedStatementConnectionSize() {
return maxOpenPreparedStatementConnectionSize;
}
@Value("${spring.datasource.maxOpenPreparedStatementConnectionSize}")
public void setmaxOpenPreparedStatementConnectionSize(int maxOpenPreparedStatementConnectionSize) {
this.maxOpenPreparedStatementConnectionSize = maxOpenPreparedStatementConnectionSize;
}
/**
* AbstractDBConfig.java
* @Description:自定义数据源
* @return
* @throws Exception
* @date 2018年1月22日 下午5:26:25
* @author ningque
*
*/
public abstract DataSource dataSource()throws Exception;
/**
*
* AbstractDBConfig.java
* @Description:自定义事物
* @return
* @throws Exception
* @date 2018年1月22日 下午5:26:38
* @author ningque
*
*/
public abstract DataSourceTransactionManager transactionManager()throws Exception;
/**
*
* AbstractDBConfig.java
* @Description:自定义session
* @param dataSource
* @return
* @throws Exception
* @date 2018年1月22日 下午5:27:41
* @author ningque
*
*/
public abstract SqlSessionFactory sqlSessionFactory( DataSource dataSource)throws Exception;
/**
*
* AbstractDBConfig.java
* @Description:配置druid 监控
* @return
* @date 2018年1月22日 下午5:28:01
* @author ningque
*
*/
public abstract ServletRegistrationBean druidServlet();
public abstract FilterRegistrationBean filterRegistrationBean();
}
主数据源类
package so.dian.device.common.config;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.cloud.context.config.annotation.RefreshScope;
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;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
@MapperScan(basePackages="so.dian.device.dao.dataBase1",sqlSessionFactoryRef = "dataBase1SqlSessionFactory")
public class dataBase1DruidDBConfig extends AbstractDBConfig {
private static Logger log = LoggerFactory.getLogger(dataBase1DruidDBConfig.class);
private String url;
private String mapperLocations;
public String userName;
public String passWord;
private String typeAliasesPackage;
public String getTypeAliasesPackage() {
return typeAliasesPackage;
}
@Value("${mybatis.type-aliases-package.dataBase1}")
public void setTypeAliasesPackage(String typeAliasesPackage) {
this.typeAliasesPackage = typeAliasesPackage;
}
public String getUserName() {
return userName;
}
@Value("${spring.datasource.username.dataBase1}")
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
@Value("${spring.datasource.password.dataBase1}")
public void setPassWord(String passWord) {
this.passWord = passWord;
}
public String getUrl() {
return url;
}
@Value("${spring.datasource.url.dataBase1}")
public void setUrl(String url) {
this.url = url;
}
public String getMapperLocations() {
return mapperLocations;
}
@Value("${mybatis.mapper-locations.dataBase1}")
public void setMapperLocations(String mapperLocations) {
this.mapperLocations = mapperLocations;
}
@Bean(name = "dataBase1DataSource")
@Primary
public DataSource dataSource() throws SQLException {
log.info("---------------开始初始化 dataBase1DataSource！！");
DruidDataSource druidDataSource = new DruidDataSource();
log.info("driver -->:" + getDriver() + ", url -->:" + url);
druidDataSource.setDriverClassName(getDriver());
druidDataSource.setUrl(getUrl());
druidDataSource.setUsername(getUsername());
druidDataSource.setPassword(getPassword());
druidDataSource.setMaxActive(getMaxActive());
druidDataSource.setInitialSize(getInitialSize());
druidDataSource.setMinIdle(getMinIdle());
druidDataSource.setMaxWait(getMaxWait());
druidDataSource.setMinEvictableIdleTimeMillis(getMinEvictableIdleTimeMillis());
druidDataSource.setValidationQuery(getValidationQuery());
druidDataSource.setTestWhileIdle(isTestWhileIdle());
druidDataSource.setTestOnBorrow(isTestOnBorrow());
druidDataSource.setTestOnReturn(isTestOnReturn());
druidDataSource.setConnectionProperties(getConnectionProperties());
druidDataSource.setFilters(getFilters());
druidDataSource.setTimeBetweenEvictionRunsMillis(getTimeBetweenEvictionRunsMillis());
druidDataSource.setPoolPreparedStatements(isPoolPreparedStatements());
druidDataSource.setMaxOpenPreparedStatements(getMaxOpenPreparedStatementConnectionSize());
return druidDataSource;
}
@Bean(name = "dataBase1TransactionManager")
@Primary
public DataSourceTransactionManager transactionManager()throws Exception {
return new DataSourceTransactionManager(dataBase1PrimaryDataSource());
}
@Bean(name = "dataBase1SqlSessionFactory")
@Primary
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataBase1DataSource") DataSource dataBase1DataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataBase1DataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(getMapperLocations()));
sessionFactory.setTypeAliasesPackage(typeAliasesPackage);
return sessionFactory.getObject();
}
@Bean
@RefreshScope
public ServletRegistrationBean druidServlet() {
log.info("---------------开始初始化 ServletRegistrationBean！！");
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
reg.addInitParameter("loginUsername", "hello");
reg.addInitParameter("loginPassword", "hello123");
return reg;
}
@Bean
@RefreshScope
public FilterRegistrationBean filterRegistrationBean() {
log.info("---------------开始初始化 FilterRegistrationBean！！");
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
filterRegistrationBean.addInitParameter("profileEnable", "true");
filterRegistrationBean.addInitParameter("principalCookieName", "USER_COOKIE");
filterRegistrationBean.addInitParameter("principalSessionName", "USER_SESSION");
return filterRegistrationBean;
}
}
从数据源配置
package so.dian.device.common.config;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.dataBase2.context.config.annotation.RefreshScope;
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.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
@MapperScan(basePackages="so.dian.device.dao.dataBase2",sqlSessionFactoryRef = "dataBase2SqlSessionFactory")
public classdataBase2DruidDBConfig extends AbstractDBConfig {
private static Logger log = LoggerFactory.getLogger(dataBase2DruidDBConfig.class);
private String url;
public String userName;
public String passWord;
private String mapperLocations;
private String typeAliasesPackage;
public String getTypeAliasesPackage() {
return typeAliasesPackage;
}
@Value("${mybatis.type-aliases-package.dataBase2}")
public void setTypeAliasesPackage(String typeAliasesPackage) {
this.typeAliasesPackage = typeAliasesPackage;
}
public String getUserName() {
return userName;
}
@Value("${spring.datasource.username.dataBase2}")
public void setUserName(String userName) {
this.userName = userName;
}
public String getPassWord() {
return passWord;
}
@Value("${spring.datasource.password.dataBase2}")
public void setPassWord(String passWord) {
this.passWord = passWord;
}
public String getUrl() {
return url;
}
@Value("${spring.datasource.url.dataBase2}")
public void setUrl(String url) {
this.url = url;
}
public String getMapperLocations() {
return mapperLocations;
}
@Value("${mybatis.mapper-locations.dataBase2}")
public void setMapperLocations(String mapperLocations) {
this.mapperLocations = mapperLocations;
}
@Bean(name = "dataBase2DataSource")
public DataSourcedataBase2 dataSource() throws SQLException {
log.info("---------------开始初始化dataBase2DataSource！！");
DruidDataSource druidDataSource = new DruidDataSource();
log.info("driver -->:" + getDriver() + ", url -->:" + url);
druidDataSource.setDriverClassName(getDriver());
druidDataSource.setUrl(url);
druidDataSource.setUsername(userName);
druidDataSource.setPassword(passWord);
druidDataSource.setMaxActive(getMaxActive());
druidDataSource.setInitialSize(getInitialSize());
druidDataSource.setMinIdle(getMinIdle());
druidDataSource.setMaxWait(getMaxWait());
druidDataSource.setMinEvictableIdleTimeMillis(getMinEvictableIdleTimeMillis());
druidDataSource.setValidationQuery(getValidationQuery());
druidDataSource.setTestWhileIdle(isTestWhileIdle());
druidDataSource.setTestOnBorrow(isTestOnBorrow());
druidDataSource.setTestOnReturn(isTestOnReturn());
druidDataSource.setConnectionProperties(getConnectionProperties());
druidDataSource.setFilters(getFilters());
druidDataSource.setTimeBetweenEvictionRunsMillis(getTimeBetweenEvictionRunsMillis());
druidDataSource.setPoolPreparedStatements(isPoolPreparedStatements());
druidDataSource.setMaxOpenPreparedStatements(getMaxOpenPreparedStatementConnectionSize());
return druidDataSource;
}
@Bean(name = "dataBase2TransactionManager")
public DataSourceTransactionManager transactionManager()throws Exception {
return new DataSourceTransactionManager(dataBase2DataSource());
}
@Bean(name = "dataBase2SqlSessionFactory")
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataBase2DataSource") DataSource dataBase2DataSource)
throws Exception {
final SqlSessionFactoryBean sessionFactory = new SqlSessionFactoryBean();
sessionFactory.setDataSource(dataBase2DataSource);
sessionFactory.setMapperLocations(new PathMatchingResourcePatternResolver().getResources(mapperLocations));
sessionFactory.setTypeAliasesPackage(getTypeAliasesPackage());
return sessionFactory.getObject();
}
@Bean
@RefreshScope
public ServletRegistrationBean druidServlet() {
log.info("---------------开始初始化 ServletRegistrationBean！！");
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
reg.addInitParameter("loginUsername", "fac");
reg.addInitParameter("loginPassword", "r5bk2fey");
return reg;
}
@Bean
@RefreshScope
public FilterRegistrationBean filterRegistrationBean() {
log.info("---------------开始初始化 FilterRegistrationBean！！");
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
filterRegistrationBean.addInitParameter("profileEnable", "true");
filterRegistrationBean.addInitParameter("principalCookieName", "USER_COOKIE");
filterRegistrationBean.addInitParameter("principalSessionName", "USER_SESSION");
return filterRegistrationBean;
}
}
ok 启动工程
[INFO] - ---------------开始初始化 dataBase1DataSource！！
2018-01-22 16:48:34.830 [main] [config.DataBase1DruidDBConfig.dataBase1PrimaryDataSource] [INFO] - driver -->:com.mysql.jdbc.Driver, url -->:jdbc:mysql://192.168.166.66:3306/dataBase1
2018-01-22 16:48:36.462 [main] [org.springframework.beans.factory.annotation.AutowiredAnnotationBeanPostProcessor$3.doWith] [WARN] - Autowired annotation should only be used on methods with parameters: public int config.AbstractDBConfig.getMaxOpenPreparedStatementConnectionSize()
2018-01-22 16:48:36.541 [main] [config.DataBase1DruidDBConfig.dataBase1DataSource] [INFO] - ---------------开始初始化 dataBase2DataSource！！
2018-01-22 16:48:36.542 [main] [config.DataBase2DruidDBConfig.dataBase2DataSource] [INFO] - driver -->:com.mysql.jdbc.Driver, url -->:jdbc:mysql://192.168.166.67:3306/dataBase2?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true
2018-01-22 16:48:37.706 [main] [com.netflix.config.sources.URLConfigurationSource.<init>] [WARN] - No URLs will be polled as dynamic configuration sources.
2018-01-22 16:48:37.706 [main] [com.netflix.config.sources.URLConfigurationSource.<init>] [INFO] - To enable URLs as dynamic configuration sources, define Sy
druid 页面
|
* 用户名 |
dataBase1 |
指定建立连接时使用的用户名 |
|
* 连接地址 |
jdbc:mysql://jdbc:mysql://192.168.166.66:3306/dataBase1 |
jdbc连接字符串 |
|
* 数据库类型 |
mysql |
数据库类型 |
|
* 驱动类名 |
com.mysql.jdbc.Driver |
jdbc驱动的类名 |
|
* filter类名 |
com.alibaba.druid.filter.stat.StatFilter,com.alibaba.druid.wall.WallFilter,com.alibaba.druid.filter.logging.Log4jFilter |
|
|
* 用户名 |
dataBase2 |
指定建立连接时使用的用户名 |
|
* 连接地址 |
jdbc:mysql://192.168.166.67:3306/dataBase2?useUnicode=true&characterEncoding=utf-8&allowMultiQueries=true |
jdbc连接字符串 |
|
* 数据库类型 |
mysql |
数据库类型 |
|
* 驱动类名 |
com.mysql.jdbc.Driver |
jdbc驱动的类名 |
|
* filter类名 |
com.alibaba.druid.filter.stat.StatFilter,com.alibaba.druid.wall.WallFilter,com.alibaba.druid.filter.logging.Log4jFilter |
|
