Druid 是阿里巴巴一个开源项目,之前(hikari 出现之前)一直被认为是Java语言中各方面最好的数据库连接池,并且Druid还能够提供强大的监控和扩展功能,备受软件开发人员推崇。本文主要介绍springboot整合druid流程,及多数据配置。
一、pom引入druid依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.1.10</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.10</version>
</dependency>
二、properties或yml文件配置数据源信息(SQLServer、mySQL多数据源)
## SQLServer数据源信息
spring.datasource.druid.sqlserver.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.sqlserver.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.druid.sqlserver.url=jdbc:sqlserver://192.168.1.8:1433;DatabaseName=myDB;useUnicode=true;characterEncoding=utf8;characterSetResults=utf8;allowMultiQueries=true;multiStatementAllow=true
spring.datasource.druid.sqlserver.username=sa
spring.datasource.druid.sqlserver.password=DBadmin
spring.datasource.druid.sqlserver.initial-size=5
spring.datasource.druid.sqlserver.min-idle=5
spring.datasource.druid.sqlserver.max-active=300
spring.datasource.druid.sqlserver.max-wait=30000
spring.datasource.druid.sqlserver.time-between-eviction-runs-millis=60000
spring.datasource.druid.sqlserver.min-evictable-idle-time-millis=30000
spring.datasource.druid.sqlserver.validation-query=SELECT 1
spring.datasource.druid.sqlserver.test-while-idle=true
spring.datasource.druid.sqlserver.test-on-borrow=false
spring.datasource.druid.sqlserver.test-on-return=false
spring.datasource.druid.sqlserver.pool-prepared-statements=true
spring.datasource.druid.sqlserver.max-pool-prepared-statement-per-connection-size=20
spring.datasource.druid.sqlserver.filters=stat
spring.datasource.druid.sqlserver.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
spring.datasource.druid.sqlserver.use-global-data-source-stat=true
## mySql数据源信息
spring.datasource.druid.mysql.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.mysql.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.mysql.url=jdbc:sqlserver://192.168.1.8:3306;DatabaseName=myDB;useUnicode=true;characterEncoding=utf8;characterSetResults=utf8;allowMultiQueries=true;multiStatementAllow=true
spring.datasource.druid.mysql.username=sa
spring.datasource.druid.mysql.password=DBadmin
spring.datasource.druid.mysql.initial-size=5
spring.datasource.druid.mysql.min-idle=5
spring.datasource.druid.mysql.max-active=300
spring.datasource.druid.mysql.max-wait=30000
spring.datasource.druid.mysql.time-between-eviction-runs-millis=60000
spring.datasource.druid.mysql.min-evictable-idle-time-millis=30000
spring.datasource.druid.mysql.validation-query=SELECT 1
spring.datasource.druid.mysql.test-while-idle=true
spring.datasource.druid.mysql.test-on-borrow=false
spring.datasource.druid.mysql.test-on-return=false
spring.datasource.druid.mysql.pool-prepared-statements=true
spring.datasource.druid.mysql.max-pool-prepared-statement-per-connection-size=20
spring.datasource.druid.mysql.filters=stat
spring.datasource.druid.mysql.connection-properties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
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-size | preparedStatement大小 |
filters | 拦截filter:监控SQL、防火墙或日志等 |
connection-properties | 打开mergeSql功能,记录慢SQL |
use-global-data-source-stat | 合并多个datasource监控数据 |
…… | …… |
三、SQLServerSettings.java:属性配置文件(以SQLServer为例)
package com.example.demo.config;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.stereotype.Component;
@Component
//properties文件中获取SQLServer属性信息
@ConfigurationProperties(prefix = "spring.datasource.druid.sqlserver")
public class SQLServerSettings {
private String type;
private String driverClassName;
private String url;
private String username;
private String password;
private Integer initialSize;
private Integer minIdle;
private Integer maxActive;
private Long maxWait;
private Long timeBetweenEvictionRunsMillis;
private Long minEvictableIdleTimeMillis;
private String validationQuery;
private boolean testWhileIdle;
private boolean testOnBorrow;
private boolean testOnReturn;
private boolean poolPreparedStatements;
private Integer maxPoolPreparedStatementPerConnectionSize;
private String filters;
private String connectionProperties;
private boolean useGlobalDataSourceStat;
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public String getUrl() {
return url;
}
public void setUrl(String url) {
this.url = url;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public Integer getInitialSize() {
return initialSize;
}
public void setInitialSize(Integer initialSize) {
this.initialSize = initialSize;
}
public Integer getMinIdle() {
return minIdle;
}
public void setMinIdle(Integer minIdle) {
this.minIdle = minIdle;
}
public Integer getMaxActive() {
return maxActive;
}
public void setMaxActive(Integer maxActive) {
this.maxActive = maxActive;
}
public Long getMaxWait() {
return maxWait;
}
public void setMaxWait(Long maxWait) {
this.maxWait = maxWait;
}
public Long getTimeBetweenEvictionRunsMillis() {
return timeBetweenEvictionRunsMillis;
}
public void setTimeBetweenEvictionRunsMillis(Long timeBetweenEvictionRunsMillis) {
this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
}
public Long getMinEvictableIdleTimeMillis() {
return minEvictableIdleTimeMillis;
}
public void setMinEvictableIdleTimeMillis(Long minEvictableIdleTimeMillis) {
this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
}
public String getValidationQuery() {
return validationQuery;
}
public void setValidationQuery(String validationQuery) {
this.validationQuery = validationQuery;
}
public boolean isTestWhileIdle() {
return testWhileIdle;
}
public void setTestWhileIdle(boolean testWhileIdle) {
this.testWhileIdle = testWhileIdle;
}
public boolean isTestOnBorrow() {
return testOnBorrow;
}
public void setTestOnBorrow(boolean testOnBorrow) {
this.testOnBorrow = testOnBorrow;
}
public boolean isTestOnReturn() {
return testOnReturn;
}
public void setTestOnReturn(boolean testOnReturn) {
this.testOnReturn = testOnReturn;
}
public boolean isPoolPreparedStatements() {
return poolPreparedStatements;
}
public void setPoolPreparedStatements(boolean poolPreparedStatements) {
this.poolPreparedStatements = poolPreparedStatements;
}
public Integer getMaxPoolPreparedStatementPerConnectionSize() {
return maxPoolPreparedStatementPerConnectionSize;
}
public void setMaxPoolPreparedStatementPerConnectionSize(Integer maxPoolPreparedStatementPerConnectionSize) {
this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
}
public String getFilters() {
return filters;
}
public void setFilters(String filters) {
this.filters = filters;
}
public String getConnectionProperties() {
return connectionProperties;
}
public void setConnectionProperties(String connectionProperties) {
this.connectionProperties = connectionProperties;
}
public boolean isUseGlobalDataSourceStat() {
return useGlobalDataSourceStat;
}
public void setUseGlobalDataSourceStat(boolean useGlobalDataSourceStat) {
this.useGlobalDataSourceStat = useGlobalDataSourceStat;
}
}
四、SQLServerConfig.java:数据源配置信息(以SQLServer为例)
package com.example.demo.config;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
@Configuration
//扫描注入SQLServer数据访问层DAO
@MapperScan(basePackages = "com.example.demo.dao.sqlServerDao", sqlSessionTemplateRef = "sqlServerSqlSessionTemplate")
public class SQLServerConfig {
@Autowired
private SQLServerSettings sqlServerSettings;
@Bean(name = "sqlServerDataSource")
@ConfigurationProperties("spring.datasource.druid.sqlserver.*")
public DataSource DataSource(DataSourceProperties properties) throws Exception{
DruidDataSource dataSource = new DruidDataSource();
dataSource.setDriverClassName(sqlServerSettings.getDriverClassName());
dataSource.setUrl(sqlServerSettings.getUrl());
dataSource.setUsername(sqlServerSettings.getUsername());
dataSource.setPassword(sqlServerSettings.getPassword());
dataSource.setInitialSize(sqlServerSettings.getInitialSize());
dataSource.setMinIdle(sqlServerSettings.getMinIdle());
dataSource.setMaxActive(sqlServerSettings.getMaxActive());
dataSource.setMaxWait(sqlServerSettings.getMaxWait());
dataSource.setTimeBetweenEvictionRunsMillis(sqlServerSettings.getTimeBetweenEvictionRunsMillis());
dataSource.setMinEvictableIdleTimeMillis(sqlServerSettings.getMinEvictableIdleTimeMillis());
String validationQuery = sqlServerSettings.getValidationQuery();
if (validationQuery != null && !"".equals(validationQuery)) {
dataSource.setValidationQuery(validationQuery);
}
dataSource.setTestWhileIdle(sqlServerSettings.isTestWhileIdle());
dataSource.setTestOnBorrow(sqlServerSettings.isTestOnBorrow());
dataSource.setTestOnReturn(sqlServerSettings.isTestOnReturn());
if(sqlServerSettings.isPoolPreparedStatements()){
dataSource.setMaxPoolPreparedStatementPerConnectionSize(sqlServerSettings.getMaxPoolPreparedStatementPerConnectionSize());
}
dataSource.setFilters(sqlServerSettings.getFilters());
String connectionPropertiesStr = sqlServerSettings.getConnectionProperties();
if(connectionPropertiesStr != null && !"".equals(connectionPropertiesStr)){
Properties connectProperties = new Properties();
String[] propertiesList = connectionPropertiesStr.split(";");
for(String propertiesTmp:propertiesList){
String[] obj = propertiesTmp.split("=");
String key = obj[0];
String value = obj[1];
connectProperties.put(key,value);
}
dataSource.setConnectProperties(connectProperties);
}
dataSource.setUseGlobalDataSourceStat(sqlServerSettings.isUseGlobalDataSourceStat());
return dataSource;
}
@Bean(name="sqlServerSqlSessionFactroy")
public SqlSessionFactory sqlServerSqlSessionFactroy(@Qualifier("sqlServerDataSource") DataSource dataSource) throws Exception{
SqlSessionFactoryBean factoryBean=new SqlSessionFactoryBean();
factoryBean.setDataSource(dataSource);
factoryBean.setConfigLocation(new ClassPathResource("mybatis/mybatis-config.xml"));
factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis/*/*.xml"));
return factoryBean.getObject();
}
@Primary
@Bean(name="sqlServerSqlSessionTemplate")
public SqlSessionTemplate sqlServerSqlSessionTemplate(@Qualifier("sqlServerSqlSessionFactroy") SqlSessionFactory sqlSessionFactory) throws Exception{
return new SqlSessionTemplate(sqlSessionFactory);
}
@Bean
//druid监控配置
public ServletRegistrationBean<StatViewServlet> druidServlet() {
ServletRegistrationBean<StatViewServlet> servletRegistrationBean = new ServletRegistrationBean<StatViewServlet>(new StatViewServlet(), "/druid/*");
//白名单(多个IP逗号分隔)
servletRegistrationBean.addInitParameter("allow", "");
//黑名单(多个IP逗号分隔,如果黑名单deny与白名单allow同时存在,deny优先于allow)
servletRegistrationBean.addInitParameter("deny", "");
//用户名
servletRegistrationBean.addInitParameter("loginUsername", "admin");
//密码
servletRegistrationBean.addInitParameter("loginPassword", "admin");
//是否可以重置Druid监控计数器
servletRegistrationBean.addInitParameter("resetEnable", "true");
return servletRegistrationBean ;
}
}
五、SQLServerDaoSupport.java:数据访问层DAO实现类
package com.example.demo.dao.sqlServerDao;
import javax.annotation.Resource;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.stereotype.Repository;
@Repository("sqlServerDao")
public class SQLServerDaoSupport implements SQLServerDao {
@Resource(name = "sqlServerSqlSessionTemplate")
private SqlSessionTemplate sqlServerSqlSessionTemplate;
/**
* 保存对象
* @param str
* @param obj
* @return
* @throws Exception
*/
public Object save(String str, Object obj) throws Exception {
return sqlServerSqlSessionTemplate.insert(str, obj);
}
/**
* 修改对象
* @param str
* @param obj
* @return
* @throws Exception
*/
public Object update(String str, Object obj) throws Exception {
return sqlServerSqlSessionTemplate.update(str, obj);
}
/**
* 删除对象
* @param str
* @param obj
* @return
* @throws Exception
*/
public Object delete(String str, Object obj) throws Exception {
return sqlServerSqlSessionTemplate.delete(str, obj);
}
/**
* 查找对象
* @param str
* @param obj
* @return
* @throws Exception
*/
public Object findForObject(String str, Object obj) throws Exception {
return sqlServerSqlSessionTemplate.selectOne(str, obj);
}
/**
* 查找对象
* @param str
* @param obj
* @return
* @throws Exception
*/
public Object findForList(String str, Object obj) throws Exception {
return sqlServerSqlSessionTemplate.selectList(str, obj);
}
}
mySql数据源配置类似,不再赘述。再来看一下Service怎样使用:
package com.example.demo.service;
import java.util.HashMap;
import java.util.List;
import javax.annotation.Resource;
import org.springframework.stereotype.Service;
import com.example.demo.dao.sqlServerDao.SQLServerDao;
import com.example.demo.dao.mySqlDao.MySqlDao;
import com.example.demo.entity.User;
@Service
public class UserService {
@Resource(name = "sqlServerDao")
private SQLServerDao sqlServerDao;
@Resource(name = "mySqlDao")
private MySqlDao mySqlDao;
//获取SQLServer数据信息
public List<User> getUserList(HashMap<String, Object> paramMap) throws Exception {
return (List<User>) sqlServerDao.findForList("UserMapper.getUserList", paramMap);
}
//获取MySql数据信息
public List<User> getUserList(HashMap<String, Object> paramMap) throws Exception {
return (List<User>) mySqlDao.findForList("UserMapper.getUserList", paramMap);
}
}
Druid监控信息:
以上,即是springboot整合druid,并实现多数据源的配置过程, 除此之外,我们还可以使用动态数据源配置的方式达到类似功能,文章所介绍的只是一种相对而言逻辑简单、结构清晰的多数据源配置方式。