1.Druid是Java语言中最好的数据库连接池。Druid能够提供强大的监控和扩展功能。
2.配置maven
<dependency><groupId>com.alibaba</groupId><artifactId>druid</artifactId><version>${druid-version}</version></dependency>
3.druid配置
spring:datasource:type: com.alibaba.druid.pool.DruidDataSourcedruid:driverClassName: com.mysql.cj.jdbc.Driverurl: jdbc:mysql://localhost:3306/data_one?useUnicode=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull&useSSL=falseusername: rootpassword: root# 初始化大小,最小,最大initial-size: 10max-active: 100min-idle: 10# 配置获取连接等待超时的时间max-wait: 60000# 打开PSCache,并且指定每个连接上PSCache的大小pool-prepared-statements: true# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙max-pool-prepared-statement-per-connection-size: 20# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒time-between-eviction-runs-millis: 60000# 配置一个连接在池中最小生存的时间,单位是毫秒min-evictable-idle-time-millis: 300000max-evictable-idle-time-millis: 60000validation-query: SELECT 1 FROM DUAL# validation-query-timeout: 5000test-on-borrow: falsetest-on-return: falsetest-while-idle: true# 通过connectProperties属性来打开mergeSql功能;慢SQL记录connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000#filters: #配置多个英文逗号分隔(统计,sql注入,log4j过滤)filters: stat,wallstat-view-servlet:enabled: trueurl-pattern: /druid/*
4.打开Druid的监控统计功能
Druid的监控统计功能是通过filter-chain扩展实现,如果你要打开监控统计功能,配置StatFilter
http://localhost:8007/druid/login.html
/*** 配置 Druid 监控界面*/@Beanpublic ServletRegistrationBean statViewServlet(){ServletRegistrationBean srb =new ServletRegistrationBean(new StatViewServlet(),"/druid/*");//设置控制台管理用户srb.addInitParameter("loginUsername","root");srb.addInitParameter("loginPassword","root");//是否可以重置数据srb.addInitParameter("resetEnable","false");return srb;}
5.配置过滤器
@Beanpublic FilterRegistrationBean statFilter(){//创建过滤器FilterRegistrationBean frb =new FilterRegistrationBean(new WebStatFilter());//设置过滤器过滤路径frb.addUrlPatterns("/*");//忽略过滤的形式frb.addInitParameter("exclusions","*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");return frb;}
}
6.jdbc操作
/*** JDBC操作配置*/@Bean(name = "dataOneTemplate")public JdbcTemplate jdbcTemplate (@Autowired DruidDataSource dataSource){return new JdbcTemplate(dataSource) ;}
7.Druid数据库连接池配置文件
/*** Druid数据库连接池配置文件*/
@Configuration
public class DruidConfig {private static final Logger logger = LoggerFactory.getLogger(DruidConfig.class);@Value("${spring.datasource.druid.url}")private String dbUrl;@Value("${spring.datasource.druid.username}")private String username;@Value("${spring.datasource.druid.password}")private String password;@Value("${spring.datasource.druid.driverClassName}")private String driverClassName;@Value("${spring.datasource.druid.initial-size}")private int initialSize;@Value("${spring.datasource.druid.max-active}")private int maxActive;@Value("${spring.datasource.druid.min-idle}")private int minIdle;@Value("${spring.datasource.druid.max-wait}")private int maxWait;@Value("${spring.datasource.druid.pool-prepared-statements}")private boolean poolPreparedStatements;@Value("${spring.datasource.druid.max-pool-prepared-statement-per-connection-size}")private int maxPoolPreparedStatementPerConnectionSize;@Value("${spring.datasource.druid.time-between-eviction-runs-millis}")private int timeBetweenEvictionRunsMillis;@Value("${spring.datasource.druid.min-evictable-idle-time-millis}")private int minEvictableIdleTimeMillis;@Value("${spring.datasource.druid.max-evictable-idle-time-millis}")private int maxEvictableIdleTimeMillis;@Value("${spring.datasource.druid.validation-query}")private String validationQuery;@Value("${spring.datasource.druid.test-while-idle}")private boolean testWhileIdle;@Value("${spring.datasource.druid.test-on-borrow}")private boolean testOnBorrow;@Value("${spring.datasource.druid.test-on-return}")private boolean testOnReturn;@Value("${spring.datasource.druid.filters}")private String filters;@Value("{spring.datasource.druid.connection-properties}")private String connectionProperties;/*** Druid 连接池配置*/@Bean //声明其为Bean实例public DruidDataSource dataSource() {DruidDataSource datasource = new DruidDataSource();datasource.setUrl(dbUrl);datasource.setUsername(username);datasource.setPassword(password);datasource.setDriverClassName(driverClassName);datasource.setInitialSize(initialSize);datasource.setMinIdle(minIdle);datasource.setMaxActive(maxActive);datasource.setMaxWait(maxWait);datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);datasource.setMaxEvictableIdleTimeMillis(minEvictableIdleTimeMillis);datasource.setValidationQuery(validationQuery);datasource.setTestWhileIdle(testWhileIdle);datasource.setTestOnBorrow(testOnBorrow);datasource.setTestOnReturn(testOnReturn);datasource.setPoolPreparedStatements(poolPreparedStatements);datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);try {datasource.setFilters(filters);} catch (Exception e) {logger.error("druid configuration initialization filter", e);}datasource.setConnectionProperties(connectionProperties);return datasource;}