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-sizepreparedStatement大小
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,并实现多数据源的配置过程, 除此之外,我们还可以使用动态数据源配置的方式达到类似功能,文章所介绍的只是一种相对而言逻辑简单、结构清晰的多数据源配置方式。

Logo

为开发者提供学习成长、分享交流、生态实践、资源工具等服务,帮助开发者快速成长。

更多推荐