devbox
不正经
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

springboot整合druid(多数据源配置)_druid多数据源配置 spring boot

作者：不正经 | 2024-03-08 15:26:55

赞

踩

druid多数据源配置 spring boot

Druid 是阿里巴巴一个开源项目，之前(hikari 出现之前)一直被认为是Java语言中各方面最好的数据库连接池，并且Druid还能够提供强大的监控和扩展功能，备受软件开发人员推崇。本文主要介绍springboot整合druid流程，及多数据配置。

一、pom引入druid依赖

  1.  
  2. <dependency>
  3. 	<groupId>com.alibaba</groupId>
  4. 	<artifactId>druid-spring-boot-starter</artifactId>
  5. 	<version>1.1.10</version>
  6. </dependency>
  7. <dependency>
  8. 	<groupId>com.alibaba</groupId>
  9. 	<artifactId>druid</artifactId>
  10. 	<version>1.1.10</version>
  11. </dependency>

二、properties或yml文件配置数据源信息(SQLServer、mySQL多数据源)

  1. ## SQLServer数据源信息
  2. spring.datasource.druid.sqlserver.type=com.alibaba.druid.pool.DruidDataSource
  3. spring.datasource.druid.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
  4. spring.datasource.druid.sqlserver.url=jdbc:sqlserver://192.168.1.8:1433;DatabaseName=myDB;useUnicode=true;characterEncoding=utf8;characterSetResults=utf8;allowMultiQueries=true;multiStatementAllow=true    
  5. spring.datasource.druid.sqlserver.username=sa
  6. spring.datasource.druid.sqlserver.password=DBadmin
  7. spring.datasource.druid.sqlserver.initial-size=5
  8. spring.datasource.druid.sqlserver.min-idle=5
  9. spring.datasource.druid.sqlserver.max-active=300
  10. spring.datasource.druid.sqlserver.max-wait=30000
  11. spring.datasource.druid.sqlserver.time-between-eviction-runs-millis=60000
  12. spring.datasource.druid.sqlserver.min-evictable-idle-time-millis=30000
  13. spring.datasource.druid.sqlserver.validation-query=SELECT 1
  14. spring.datasource.druid.sqlserver.test-while-idle=true
  15. spring.datasource.druid.sqlserver.test-on-borrow=false
  16. spring.datasource.druid.sqlserver.test-on-return=false
  17. spring.datasource.druid.sqlserver.pool-prepared-statements=true
  18. spring.datasource.druid.sqlserver.max-pool-prepared-statement-per-connection-size=20
  19. spring.datasource.druid.sqlserver.filters=stat
  20. spring.datasource.druid.sqlserver.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
  21. spring.datasource.druid.sqlserver.use-global-data-source-stat=true
  22.  
  23. ## mySql数据源信息
  24. spring.datasource.druid.mysql.type=com.alibaba.druid.pool.DruidDataSource
  25. spring.datasource.druid.mysql.driver-class-name=com.mysql.jdbc.Driver
  26. spring.datasource.druid.mysql.url=jdbc:sqlserver://192.168.1.8:3306;DatabaseName=myDB;useUnicode=true;characterEncoding=utf8;characterSetResults=utf8;allowMultiQueries=true;multiStatementAllow=true    
  27. spring.datasource.druid.mysql.username=sa
  28. spring.datasource.druid.mysql.password=DBadmin
  29. spring.datasource.druid.mysql.initial-size=5
  30. spring.datasource.druid.mysql.min-idle=5
  31. spring.datasource.druid.mysql.max-active=300
  32. spring.datasource.druid.mysql.max-wait=30000
  33. spring.datasource.druid.mysql.time-between-eviction-runs-millis=60000
  34. spring.datasource.druid.mysql.min-evictable-idle-time-millis=30000
  35. spring.datasource.druid.mysql.validation-query=SELECT 1
  36. spring.datasource.druid.mysql.test-while-idle=true
  37. spring.datasource.druid.mysql.test-on-borrow=false
  38. spring.datasource.druid.mysql.test-on-return=false
  39. spring.datasource.druid.mysql.pool-prepared-statements=true
  40. spring.datasource.druid.mysql.max-pool-prepared-statement-per-connection-size=20
  41. spring.datasource.druid.mysql.filters=stat
  42. spring.datasource.druid.mysql.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
  43. spring.datasource.druid.mysql.use-global-data-source-stat=true

常用属性参数信息：

属性说明
type连接池类型
driver-class-name数据库驱动类型
url数据库连接地址
username数据库账号
password数据库密码
initial-size初始化连接数
min-idle最小活跃连接数
max-active最大活跃连接数
max-wait连接最大等待(超时)时间(毫秒)
time-between-eviction-runs-millis检测连接并进行回收处理的时间间隔数(毫秒)
min-evictable-idle-time-millis连接在数据池中最短生存时间(毫秒)
validation-query测试验证连接
test-while-idle取用连接时，通过与回收时间比较检测是否有效
test-on-borrow取用连接时，检测是否有效
test-on-return归还连接时，检测是否有效
pool-prepared-statements是否缓存preparedStatement
max-pool-prepared-statement-per-connection-sizepreparedStatement大小
filters拦截filter：监控SQL、防火墙或日志等
connection-properties打开mergeSql功能，记录慢SQL
use-global-data-source-stat合并多个datasource监控数据
…………

三、SQLServerSettings.java：属性配置文件(以SQLServer为例)

  1. package com.example.demo.config;
  2.  
  3. import org.springframework.boot.context.properties.ConfigurationProperties;
  4. import org.springframework.stereotype.Component;
  5.  
  6. @Component
  7. //properties文件中获取SQLServer属性信息
  8. @ConfigurationProperties(prefix = "spring.datasource.druid.sqlserver")
  9. public class SQLServerSettings {
  10. 	private String type;
  11. 	private String driverClassName;
  12. 	private String url;
  13. 	private String username;
  14. 	private String password;
  15. 	private Integer initialSize;
  16. 	private Integer minIdle;
  17. 	private Integer maxActive;
  18. 	private Long maxWait;
  19. 	private Long timeBetweenEvictionRunsMillis;
  20. 	private Long minEvictableIdleTimeMillis;
  21. 	private String validationQuery;
  22. 	private boolean testWhileIdle;
  23. 	private boolean testOnBorrow;
  24. 	private boolean testOnReturn;
  25. 	private boolean poolPreparedStatements;
  26. 	private Integer maxPoolPreparedStatementPerConnectionSize;
  27. 	private String filters;
  28. 	private String connectionProperties;
  29. 	private boolean useGlobalDataSourceStat;
  30.  
  31. 	public String getType() {
  32. 		return type;
  33. 	}
  34.  
  35. 	public void setType(String type) {
  36. 		this.type = type;
  37. 	}
  38.  
  39. 	public String getDriverClassName() {
  40. 		return driverClassName;
  41. 	}
  42.  
  43. 	public void setDriverClassName(String driverClassName) {
  44. 		this.driverClassName = driverClassName;
  45. 	}
  46.  
  47. 	public String getUrl() {
  48. 		return url;
  49. 	}
  50.  
  51. 	public void setUrl(String url) {
  52. 		this.url = url;
  53. 	}
  54.  
  55. 	public String getUsername() {
  56. 		return username;
  57. 	}
  58.  
  59. 	public void setUsername(String username) {
  60. 		this.username = username;
  61. 	}
  62.  
  63. 	public String getPassword() {
  64. 		return password;
  65. 	}
  66.  
  67. 	public void setPassword(String password) {
  68. 		this.password = password;
  69. 	}
  70.  
  71. 	public Integer getInitialSize() {
  72. 		return initialSize;
  73. 	}
  74.  
  75. 	public void setInitialSize(Integer initialSize) {
  76. 		this.initialSize = initialSize;
  77. 	}
  78.  
  79. 	public Integer getMinIdle() {
  80. 		return minIdle;
  81. 	}
  82.  
  83. 	public void setMinIdle(Integer minIdle) {
  84. 		this.minIdle = minIdle;
  85. 	}
  86.  
  87. 	public Integer getMaxActive() {
  88. 		return maxActive;
  89. 	}
  90.  
  91. 	public void setMaxActive(Integer maxActive) {
  92. 		this.maxActive = maxActive;
  93. 	}
  94.  
  95. 	public Long getMaxWait() {
  96. 		return maxWait;
  97. 	}
  98.  
  99. 	public void setMaxWait(Long maxWait) {
  100. 		this.maxWait = maxWait;
  101. 	}
  102.  
  103. 	public Long getTimeBetweenEvictionRunsMillis() {
  104. 		return timeBetweenEvictionRunsMillis;
  105. 	}
  106.  
  107. 	public void setTimeBetweenEvictionRunsMillis(Long timeBetweenEvictionRunsMillis) {
  108. 		this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
  109. 	}
  110.  
  111. 	public Long getMinEvictableIdleTimeMillis() {
  112. 		return minEvictableIdleTimeMillis;
  113. 	}
  114.  
  115. 	public void setMinEvictableIdleTimeMillis(Long minEvictableIdleTimeMillis) {
  116. 		this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
  117. 	}
  118.  
  119. 	public String getValidationQuery() {
  120. 		return validationQuery;
  121. 	}
  122.  
  123. 	public void setValidationQuery(String validationQuery) {
  124. 		this.validationQuery = validationQuery;
  125. 	}
  126.  
  127. 	public boolean isTestWhileIdle() {
  128. 		return testWhileIdle;
  129. 	}
  130.  
  131. 	public void setTestWhileIdle(boolean testWhileIdle) {
  132. 		this.testWhileIdle = testWhileIdle;
  133. 	}
  134.  
  135. 	public boolean isTestOnBorrow() {
  136. 		return testOnBorrow;
  137. 	}
  138.  
  139. 	public void setTestOnBorrow(boolean testOnBorrow) {
  140. 		this.testOnBorrow = testOnBorrow;
  141. 	}
  142.  
  143. 	public boolean isTestOnReturn() {
  144. 		return testOnReturn;
  145. 	}
  146.  
  147. 	public void setTestOnReturn(boolean testOnReturn) {
  148. 		this.testOnReturn = testOnReturn;
  149. 	}
  150.  
  151. 	public boolean isPoolPreparedStatements() {
  152. 		return poolPreparedStatements;
  153. 	}
  154.  
  155. 	public void setPoolPreparedStatements(boolean poolPreparedStatements) {
  156. 		this.poolPreparedStatements = poolPreparedStatements;
  157. 	}
  158.  
  159. 	public Integer getMaxPoolPreparedStatementPerConnectionSize() {
  160. 		return maxPoolPreparedStatementPerConnectionSize;
  161. 	}
  162.  
  163. 	public void setMaxPoolPreparedStatementPerConnectionSize(Integer maxPoolPreparedStatementPerConnectionSize) {
  164. 		this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
  165. 	}
  166.  
  167. 	public String getFilters() {
  168. 		return filters;
  169. 	}
  170.  
  171. 	public void setFilters(String filters) {
  172. 		this.filters = filters;
  173. 	}
  174.  
  175. 	public String getConnectionProperties() {
  176. 		return connectionProperties;
  177. 	}
  178.  
  179. 	public void setConnectionProperties(String connectionProperties) {
  180. 		this.connectionProperties = connectionProperties;
  181. 	}
  182.  
  183. 	public boolean isUseGlobalDataSourceStat() {
  184. 		return useGlobalDataSourceStat;
  185. 	}
  186.  
  187. 	public void setUseGlobalDataSourceStat(boolean useGlobalDataSourceStat) {
  188. 		this.useGlobalDataSourceStat = useGlobalDataSourceStat;
  189. 	}
  190. }

四、SQLServerConfig.java：数据源配置信息(以SQLServer为例)

  1. package com.example.demo.config;
  2.  
  3. import java.util.Properties;
  4.  
  5. import javax.sql.DataSource;
  6.  
  7. import org.apache.ibatis.session.SqlSessionFactory;
  8. import org.mybatis.spring.SqlSessionFactoryBean;
  9. import org.mybatis.spring.SqlSessionTemplate;
  10. import org.mybatis.spring.annotation.MapperScan;
  11. import org.springframework.beans.factory.annotation.Autowired;
  12. import org.springframework.beans.factory.annotation.Qualifier;
  13. import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
  14. import org.springframework.boot.context.properties.ConfigurationProperties;
  15. import org.springframework.boot.web.servlet.ServletRegistrationBean;
  16. import org.springframework.context.annotation.Bean;
  17. import org.springframework.context.annotation.Configuration;
  18. import org.springframework.context.annotation.Primary;
  19. import org.springframework.core.io.ClassPathResource;
  20. import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
  21.  
  22. import com.alibaba.druid.pool.DruidDataSource;
  23. import com.alibaba.druid.support.http.StatViewServlet;
  24.  
  25.  
  26. @Configuration
  27. //扫描注入SQLServer数据访问层DAO
  28. @MapperScan(basePackages = "com.example.demo.dao.sqlServerDao", sqlSessionTemplateRef = "sqlServerSqlSessionTemplate")
  29. public class SQLServerConfig {
  30.  
  31.     @Autowired
  32.     private SQLServerSettings sqlServerSettings;
  33.     
  34. 	@Bean(name = "sqlServerDataSource")
  35.     @ConfigurationProperties("spring.datasource.druid.sqlserver.*")
  36.     public DataSource DataSource(DataSourceProperties properties) throws Exception{
  37. 		DruidDataSource dataSource = new DruidDataSource();
  38.         dataSource.setDriverClassName(sqlServerSettings.getDriverClassName());
  39.         dataSource.setUrl(sqlServerSettings.getUrl());
  40.         dataSource.setUsername(sqlServerSettings.getUsername());
  41.         dataSource.setPassword(sqlServerSettings.getPassword());
  42.         dataSource.setInitialSize(sqlServerSettings.getInitialSize());
  43.         dataSource.setMinIdle(sqlServerSettings.getMinIdle());
  44.         dataSource.setMaxActive(sqlServerSettings.getMaxActive());
  45.         dataSource.setMaxWait(sqlServerSettings.getMaxWait());
  46.         dataSource.setTimeBetweenEvictionRunsMillis(sqlServerSettings.getTimeBetweenEvictionRunsMillis());
  47.         dataSource.setMinEvictableIdleTimeMillis(sqlServerSettings.getMinEvictableIdleTimeMillis());
  48.         String validationQuery = sqlServerSettings.getValidationQuery();
  49.         if (validationQuery != null && !"".equals(validationQuery)) {
  50.             dataSource.setValidationQuery(validationQuery);
  51.         }
  52.         dataSource.setTestWhileIdle(sqlServerSettings.isTestWhileIdle());
  53.         dataSource.setTestOnBorrow(sqlServerSettings.isTestOnBorrow());
  54.         dataSource.setTestOnReturn(sqlServerSettings.isTestOnReturn());
  55.         if(sqlServerSettings.isPoolPreparedStatements()){
  56.             dataSource.setMaxPoolPreparedStatementPerConnectionSize(sqlServerSettings.getMaxPoolPreparedStatementPerConnectionSize());
  57.         }
  58.         dataSource.setFilters(sqlServerSettings.getFilters());
  59.         String connectionPropertiesStr = sqlServerSettings.getConnectionProperties();
  60.         if(connectionPropertiesStr != null && !"".equals(connectionPropertiesStr)){
  61.             Properties connectProperties = new Properties();
  62.             String[] propertiesList = connectionPropertiesStr.split(";");
  63.             for(String propertiesTmp:propertiesList){
  64.                 String[] obj = propertiesTmp.split("=");
  65.                 String key = obj[0];
  66.                 String value = obj[1];
  67.                 connectProperties.put(key,value);
  68.             }
  69.             dataSource.setConnectProperties(connectProperties);
  70.         }
  71.         dataSource.setUseGlobalDataSourceStat(sqlServerSettings.isUseGlobalDataSourceStat());
  72.         return dataSource;
  73.     }
  74.  
  75. 	@Bean(name="sqlServerSqlSessionFactroy")
  76. 	public SqlSessionFactory sqlServerSqlSessionFactroy(@Qualifier("sqlServerDataSource") DataSource dataSource) throws Exception{
  77. 		SqlSessionFactoryBean factoryBean=new SqlSessionFactoryBean();
  78. 		factoryBean.setDataSource(dataSource);
  79. 		factoryBean.setConfigLocation(new ClassPathResource("mybatis/mybatis-config.xml"));
  80. 		factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*/*.xml"));
  81. 		return factoryBean.getObject();
  82. 	}
  83.  
  84. 	@Primary
  85. 	@Bean(name="sqlServerSqlSessionTemplate")
  86. 	public SqlSessionTemplate sqlServerSqlSessionTemplate(@Qualifier("sqlServerSqlSessionFactroy") SqlSessionFactory sqlSessionFactory) throws Exception{
  87. 		return new SqlSessionTemplate(sqlSessionFactory);
  88. 	}
  89.  
  90. 	@Bean
  91. 	//druid监控配置
  92.     public ServletRegistrationBean<StatViewServlet> druidServlet() {
  93.         ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<StatViewServlet>(new StatViewServlet(), "/druid/*");
  94.         //白名单(多个IP逗号分隔)
  95.         servletRegistrationBean.addInitParameter("allow", "");
  96.         //黑名单(多个IP逗号分隔，如果黑名单deny与白名单allow同时存在，deny优先于allow)
  97.         servletRegistrationBean.addInitParameter("deny", "");
  98.         //用户名
  99.         servletRegistrationBean.addInitParameter("loginUsername", "admin");
  100.         //密码
  101.         servletRegistrationBean.addInitParameter("loginPassword", "admin");
  102.         //是否可以重置Druid监控计数器
  103.         servletRegistrationBean.addInitParameter("resetEnable", "true");
  104.         return servletRegistrationBean ;
  105.     }
  106. }

五、SQLServerDaoSupport.java：数据访问层DAO实现类

  1. package com.example.demo.dao.sqlServerDao;
  2.  
  3. import javax.annotation.Resource;
  4. import org.mybatis.spring.SqlSessionTemplate;
  5. import org.springframework.stereotype.Repository;
  6.  
  7. @Repository("sqlServerDao")
  8. public class SQLServerDaoSupport implements SQLServerDao {
  9.  
  10. 	@Resource(name = "sqlServerSqlSessionTemplate")
  11. 	private SqlSessionTemplate sqlServerSqlSessionTemplate;
  12. 	
  13. 	/**
  14. 	 * 保存对象
  15. 	 * @param str
  16. 	 * @param obj
  17. 	 * @return
  18. 	 * @throws Exception
  19. 	 */
  20. 	public Object save(String str, Object obj) throws Exception {
  21. 		return sqlServerSqlSessionTemplate.insert(str, obj);
  22. 	}
  23. 	
  24. 	/**
  25. 	 * 修改对象
  26. 	 * @param str
  27. 	 * @param obj
  28. 	 * @return
  29. 	 * @throws Exception
  30. 	 */
  31. 	public Object update(String str, Object obj) throws Exception {
  32. 		return sqlServerSqlSessionTemplate.update(str, obj);
  33. 	}
  34. 	
  35. 	/**
  36. 	 * 删除对象 
  37. 	 * @param str
  38. 	 * @param obj
  39. 	 * @return
  40. 	 * @throws Exception
  41. 	 */
  42. 	public Object delete(String str, Object obj) throws Exception {
  43. 		return sqlServerSqlSessionTemplate.delete(str, obj);
  44. 	}
  45. 	 
  46. 	/**
  47. 	 * 查找对象
  48. 	 * @param str
  49. 	 * @param obj
  50. 	 * @return
  51. 	 * @throws Exception
  52. 	 */
  53. 	public Object findForObject(String str, Object obj) throws Exception {
  54. 		return sqlServerSqlSessionTemplate.selectOne(str, obj);
  55. 	}
  56.  
  57. 	/**
  58. 	 * 查找对象
  59. 	 * @param str
  60. 	 * @param obj
  61. 	 * @return
  62. 	 * @throws Exception
  63. 	 */
  64. 	public Object findForList(String str, Object obj) throws Exception {
  65. 		return sqlServerSqlSessionTemplate.selectList(str, obj);
  66. 	}
  67. }
  68.  
  69.

mySql数据源配置类似，不再赘述。

再来看一下Service怎样使用：

  1. package com.example.demo.service;
  2.  
  3. import java.util.HashMap;
  4. import java.util.List;
  5.  
  6. import javax.annotation.Resource;
  7.  
  8. import org.springframework.stereotype.Service;
  9.  
  10. import com.example.demo.dao.sqlServerDao.SQLServerDao;
  11. import com.example.demo.dao.mySqlDao.MySqlDao;
  12. import com.example.demo.entity.User;
  13.  
  14. @Service
  15. public class UserService {
  16. 	@Resource(name = "sqlServerDao")
  17. 	private SQLServerDao sqlServerDao;
  18. 	@Resource(name = "mySqlDao")
  19. 	private MySqlDao mySqlDao;
  20. 	
  21. 	//获取SQLServer数据信息
  22. 	public List<User> getUserList(HashMap<String, Object> paramMap) throws Exception {
  23. 		return (List<User>) sqlServerDao.findForList("UserMapper.getUserList", paramMap);
  24. 	}
  25. 	
  26. 	//获取MySql数据信息
  27. 	public List<User> getUserList(HashMap<String, Object> paramMap) throws Exception {
  28. 		return (List<User>) mySqlDao.findForList("UserMapper.getUserList", paramMap);
  29. 	}
  30. 	
  31. }

Druid监控信息：

以上，即是springboot整合druid，并实现多数据源的配置过程， 除此之外，我们还可以使用动态数据源配置的方式达到类似功能，文章所介绍的只是一种相对而言逻辑简单、结构清晰的多数据源配置方式。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/不正经/article/detail/210941
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号