本文將講述 spring boot + mybatis + druid 多資料來源配置方案。
環境
CentOs7.3 安裝 MySQL 5.7.19 二進位制版本
Github 程式碼
程式碼我已放到 Github ,匯入spring-boot-mybatis
專案
github github.com/souyunku/sp…

新增依賴
在專案中新增 mybatis,druid
依賴
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
</dependency>
省略 更多
複製程式碼
基礎資料來源
@Configuration
@EnableConfigurationProperties(DruidDbProperties.class)
@Import({DruidMonitConfig.class})
public abstract class AbstractDruidDBConfig {
private Logger logger = LoggerFactory.getLogger(AbstractDruidDBConfig.class);
@Resource
private DruidDbProperties druidDbProperties;
public DruidDataSource createDataSource(String url, String username, String password) {
if (StringUtils.isEmpty(url)) {
System.out.println(
"Your database connection pool configuration is incorrect!" + " Please check your Spring profile");
throw new ApplicationContextException("Database connection pool is not configured correctly");
}
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(url);
datasource.setUsername(username);
datasource.setPassword(password);
// datasource.setDriverClassName(
// StringUtils.isEmpty(driverClassName) ?
// druidDbProperties.getDriverClassName() : driverClassName);
datasource.setInitialSize(druidDbProperties.getInitialSize());
datasource.setMinIdle(druidDbProperties.getMinIdle());
datasource.setMaxActive(druidDbProperties.getMaxActive());
datasource.setMaxWait(druidDbProperties.getMaxWait());
datasource.setTimeBetweenEvictionRunsMillis(druidDbProperties.getTimeBetweenEvictionRunsMillis());
datasource.setMinEvictableIdleTimeMillis(druidDbProperties.getMinEvictableIdleTimeMillis());
datasource.setValidationQuery(druidDbProperties.getValidationQuery());
datasource.setTestWhileIdle(druidDbProperties.isTestWhileIdle());
datasource.setTestOnBorrow(druidDbProperties.isTestOnBorrow());
datasource.setTestOnReturn(druidDbProperties.isTestOnReturn());
try {
datasource.setFilters(druidDbProperties.getFilters());
} catch (SQLException e) {
logger.error("druid configuration initialization filter", e);
}
datasource.setConnectionProperties(druidDbProperties.getConnectionProperties());
return datasource;
}
/**
* 載入預設mybatis xml配置檔案,並初始化分頁外掛
*
* @param dataSource
* @return
* @throws Exception
*/
public SqlSessionFactory sqlSessionFactory(DataSource dataSource) throws Exception {
return createSqlSessionFactory(dataSource, "classpath:mybatis/**/*.xml");
}
/**
* 載入mybatis xml配置檔案,並初始化分頁外掛
*
* @param dataSource 資料來源
* @param mapperLocations 自定義xml配置路徑
* @return
* @throws Exception
*/
public SqlSessionFactory sqlSessionFactory(DataSource dataSource, String mapperLocations) throws Exception {
return createSqlSessionFactory(dataSource, mapperLocations);
}
private SqlSessionFactory createSqlSessionFactory(DataSource dataSource, String mapperLocations) throws Exception {
SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
sqlSessionFactoryBean.setDataSource(dataSource);
// mybatis分頁
PageHelper pageHelper = new PageHelper();
Properties props = new Properties();
props.setProperty("dialect", "mysql");
props.setProperty("reasonable", "true");
props.setProperty("supportMethodsArguments", "true");
props.setProperty("returnPageInfo", "check");
props.setProperty("params", "count=countSql");
pageHelper.setProperties(props); // 新增外掛
sqlSessionFactoryBean.setPlugins(new Interceptor[]{pageHelper});
PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
sqlSessionFactoryBean.setMapperLocations(resolver.getResources(mapperLocations));
return sqlSessionFactoryBean.getObject();
}
}
複製程式碼
Druid 監控配置
@EnableConfigurationProperties(DruidDbProperties.class)
@EnableAspectJAutoProxy(proxyTargetClass = true)
public class DruidMonitConfig {
@Resource
private DruidDbProperties druidDbProperties;
@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean reg = new ServletRegistrationBean();
reg.setServlet(new StatViewServlet());
reg.addUrlMappings("/druid/*");
if (!StringUtils.isEmpty(druidDbProperties.getAllow())) {
reg.addInitParameter("allow", druidDbProperties.getAllow()); // 白名單
}
if (!StringUtils.isEmpty(druidDbProperties.getDeny())) {
reg.addInitParameter("deny", druidDbProperties.getDeny()); // 黑名單
}
reg.addInitParameter("loginUsername", druidDbProperties.getUsername());
reg.addInitParameter("loginPassword", druidDbProperties.getPassword());
return reg;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
filterRegistrationBean.setFilter(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}
/**
* 監聽Spring 1.定義攔截器 2.定義切入點 3.定義通知類
*
* @return
*/
@Bean
public DruidStatInterceptor druidStatInterceptor() {
return new DruidStatInterceptor();
}
@Bean
public JdkRegexpMethodPointcut druidStatPointcut() {
JdkRegexpMethodPointcut druidStatPointcut = new JdkRegexpMethodPointcut();
String patterns = "io.ymq.mybatis*";
druidStatPointcut.setPatterns(patterns);
return druidStatPointcut;
}
@Bean
public Advisor druidStatAdvisor() {
return new DefaultPointcutAdvisor(druidStatPointcut(), druidStatInterceptor());
}
}
複製程式碼
Druid 監控引數
@ConfigurationProperties(prefix = "druid")
public class DruidDbProperties {
private String driverClassName = "com.mysql.jdbc.Driver";
/**
* 初始化時建立物理連線的個數。初始化發生在顯示呼叫init方法,或者第一次getConnection時
*/
private int initialSize = 10;
/**
* 最小連線池數量
*/
private int minIdle = 50;
/**
* 最大連線池數量
*/
private int maxActive = 300;
/**
* 獲取連線時最大等待時間,單位毫秒。配置了maxWait之後,預設啟用公平鎖,併發效率會有所下降,如果需要可以通過配置useUnfairLock屬性為true使用非公平鎖。
*/
private int maxWait = 60000;
/**
* 有兩個含義: 1)
* Destroy執行緒會檢測連線的間隔時間,如果連線空閒時間大於等於minEvictableIdleTimeMillis則關閉物理連線。 2)
* testWhileIdle的判斷依據,詳細看testWhileIdle屬性的說明
*/
private int timeBetweenEvictionRunsMillis = 60000;
/**
* 連線保持空閒而不被驅逐的最長時間
*/
private int minEvictableIdleTimeMillis = 3600000;
/**
* 用來檢測連線是否有效的sql,要求是一個查詢語句,常用select
* 'x'。如果validationQuery為null,testOnBorrow、testOnReturn、testWhileIdle都不會其作用。
*/
private String validationQuery = "SELECT USER()";
/**
* 建議配置為true,不影響效能,並且保證安全性。申請連線的時候檢測,如果空閒時間大於timeBetweenEvictionRunsMillis,執行validationQuery檢測連線是否有效。
*/
private boolean testWhileIdle = true;
/**
* 申請連線時執行validationQuery檢測連線是否有效,做了這個配置會降低效能。
*/
private boolean testOnBorrow = false;
/**
* 歸還連線時執行validationQuery檢測連線是否有效,做了這個配置會降低效能。
*/
private boolean testOnReturn = false;
/**
* 屬性型別是字串,通過別名的方式配置擴充套件外掛,常用的外掛有: 監控統計用的filter:stat 日誌用的filter:log4j
* 防禦sql注入的filter:wall
*/
private String filters = "mergeStat,config,wall";
private String connectionProperties;
/**
* 白名單
*/
private String allow;
/**
* 黑名單
*/
private String deny;
private String username = "admin";
private String password = "admin";
省略 get set
}
複製程式碼
配置資料來源 one
@Configuration
@EnableTransactionManagement
public class DBOneConfiguration extends AbstractDruidDBConfig {
@Value("${ymq.one.datasource.url}")
private String url;
@Value("${ymq.one.datasource.username}")
private String username;
@Value("${ymq.one.datasource.password}")
private String password;
// 註冊 datasourceOne
@Bean(name = "datasourceOne", initMethod = "init", destroyMethod = "close")
public DruidDataSource dataSource() {
return super.createDataSource(url, username, password);
}
@Bean(name = "sqlSessionFactorYmqOne")
public SqlSessionFactory sqlSessionFactory() throws Exception {
return super.sqlSessionFactory(dataSource());
}
@Bean
public PlatformTransactionManager transactionManager() throws SQLException {
return new DataSourceTransactionManager(dataSource());
}
}
複製程式碼
配置資料來源 two
@Configuration
@EnableTransactionManagement
public class DBOneConfiguration extends AbstractDruidDBConfig {
@Value("${ymq.one.datasource.url}")
private String url;
@Value("${ymq.one.datasource.username}")
private String username;
@Value("${ymq.one.datasource.password}")
private String password;
// 註冊 datasourceOne
@Bean(name = "datasourceOne", initMethod = "init", destroyMethod = "close")
public DruidDataSource dataSource() {
return super.createDataSource(url, username, password);
}
@Bean(name = "sqlSessionFactorYmqOne")
public SqlSessionFactory sqlSessionFactory() throws Exception {
return super.sqlSessionFactory(dataSource());
}
@Bean
public PlatformTransactionManager transactionManager() throws SQLException {
return new DataSourceTransactionManager(dataSource());
}
}
複製程式碼
BaseDao one
@Repository
public class YmqOneBaseDao extends BaseDao {
@Resource
public void setSqlSessionFactorYmqOne(SqlSessionFactory sqlSessionFactory) {
super.setSqlSessionFactory(sqlSessionFactory);
}
}
複製程式碼
BaseDao two
@Repository
public class YmqTwoBaseDao extends BaseDao {
@Resource
public void setSqlSessionFactorYmqTwo(SqlSessionFactory sqlSessionFactory) {
super.setSqlSessionFactory(sqlSessionFactory);
}
}
複製程式碼
測試 Controller
@RestController
public class IndexController {
private static final Logger LOG = LoggerFactory.getLogger(IndexController.class);
@Autowired
private YmqOneBaseDao ymqOneBaseDao;
@Autowired
private YmqTwoBaseDao ymqTwoBaseDao;
@RequestMapping("/")
public String index() throws Exception {
List<TestOnePo> testOnePoList = null;
testOnePoList = ymqOneBaseDao.selectList(new TestOnePo());
for (TestOnePo item : testOnePoList) {
LOG.info("資料來源 ymqOneBaseDao :查詢結果:{}", JSONObject.toJSONString(item));
}
List<TestTwoPo> testTwoPoList = null;
testTwoPoList = ymqTwoBaseDao.selectList(new TestTwoPo());
for (TestTwoPo item : testTwoPoList) {
LOG.info("資料來源 ymqTwoBaseDao:查詢結果:{}", JSONObject.toJSONString(item));
}
String onePoList = JSONObject.toJSONString(testOnePoList);
String twoPoList = JSONObject.toJSONString(testTwoPoList);
return "資料來源 ymqOneBaseDao :查詢結果:" + onePoList + "<br/> 資料來源 ymqTwoBaseDao :查詢結果:" + twoPoList;
}
}
複製程式碼
引數配置
application.properties
#############SERVER CONFIG############
spring.application.name=ymq-mybatis-spring-boot
#資料來源 one
ymq.one.datasource.url=jdbc:mysql://10.4.82.6:3306/ymq_one?useUnicode=true&characterEncoding=UTF-8
ymq.one.datasource.username=root
ymq.one.datasource.password=123456
#資料來源 two
ymq.two.datasource.url=jdbc:mysql://10.4.82.6:3306/ymq_two?useUnicode=true&characterEncoding=UTF-8
ymq.two.datasource.username=root
ymq.two.datasource.password=123456
server.port=80
server.tomcat.max-threads=1000
server.tomcat.max-connections=2000
複製程式碼
啟動服務
@SpringBootApplication
@ComponentScan(value = {"io.ymq.mybatis"})
public class Startup {
public static void main(String[] args) {
SpringApplication.run(Startup.class, args);
}
}
複製程式碼
在頁面上輸入 http://localhost/ 可以看到 Controller 執行情況:
資料來源 ymqOneBaseDao :查詢結果:[{"id":1,"name":"測試","remark":"這是測試 ymq_one 資料庫"}]
資料來源 ymqTwoBaseDao :查詢結果:[{"id":1,"name":"測試","remark":"這是測試 ymq_two 資料庫"}]
複製程式碼
在頁面上輸入 http://localhost/druid/ 可以看到監控到的sql語句執行情況:

程式碼我已放到 Github ,匯入spring-boot-mybatis
專案
github github.com/souyunku/sp…
Contact
- 作者:鵬磊
- 出處:www.ymq.io
- Email:admin@souyunku.com
- 版權歸作者所有,轉載請註明出處
- Wechat:關注公眾號,搜雲庫,專注於開發技術的研究與知識分享
