在application.yml中,我们添加DataSource的配置,如下:
spring:
datasource:
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/xxxxxxxx?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=true
username: xxxxxx
password: xxxxxx
#--------------------------
# 下面为连接池的补充设置,应用到上面所有数据源中
# 初始化大小,最小,最大
tomcat:
initial-size: 5
minIdle: 1
max-active: 50
# 配置获取连接等待超时的时间
max-wait: 60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
time-between-eviction-runs-millis: 60000
# 配置一个连接在池中最小生存的时间,单位是毫秒
min-evictable-idle-time-millis: 300000
validation-query: SELECT 1 FROM DUAL
test-while-idle: true
test-on-borrow: false
test-on-return: false
# 通过connectProperties属性来打开mergeSql功能;慢SQL记录
connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
dbcp2:
# 打开PSCache,并且指定每个连接上PSCache的大小
pool-prepared-statements: false
#spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
filters: stat,wall,log4j
# 合并多个DruidDataSource的监控数据
#spring.datasource.useGlobalDataSourceStat=true
创建一个子模块ssm.core,放一些公共的东西,然后在下面新建一个config包,新建DruidConfig.java:
@Configuration
public class DruidConfig {
@Value("${spring.datasource.url}")
private String dbUrl;
@Value("${spring.datasource.username}")
private String username;
@Value("${spring.datasource.password}")
private String password;
@Value("${spring.datasource.driver-class-name}")
private String driverClassName;
@Value("${spring.datasource.tomcat.initial-size}")
private int initialSize;
@Value("${spring.datasource.tomcat.minIdle}")
private int minIdle;
@Value("${spring.datasource.tomcat.max-active}")
private int maxActive;
@Value("${spring.datasource.tomcat.max-wait}")
private int maxWait;
@Value("${spring.datasource.tomcat.time-between-eviction-runs-millis}")
private int timeBetweenEvictionRunsMillis;
@Value("${spring.datasource.tomcat.min-evictable-idle-time-millis}")
private int minEvictableIdleTimeMillis;
@Value("${spring.datasource.tomcat.validation-query}")
private String validationQuery;
@Value("${spring.datasource.tomcat.test-while-idle}")
private boolean testWhileIdle;
@Value("${spring.datasource.tomcat.test-on-borrow}")
private boolean testOnBorrow;
@Value("${spring.datasource.tomcat.test-on-return}")
private boolean testOnReturn;
@Value("${spring.datasource.dbcp2.pool-prepared-statements}")
private boolean poolPreparedStatements;
// @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}")
// private int maxPoolPreparedStatementPerConnectionSize;
@Value("${spring.datasource.filters}")
private String filters;
@Value("{spring.datasource.tomcat.connection-properties}")
private String connectionProperties;
@Bean
@Primary
public DataSource dataSource() {
DruidDataSource datasource = new DruidDataSource();
datasource.setUrl(this.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.setValidationQuery(validationQuery);
datasource.setTestWhileIdle(testWhileIdle);
datasource.setTestOnBorrow(testOnBorrow);
datasource.setTestOnReturn(testOnReturn);
datasource.setPoolPreparedStatements(poolPreparedStatements);
// datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
try {
datasource.setFilters(filters);
} catch (SQLException e) {
}
datasource.setConnectionProperties(connectionProperties);
return datasource;
}
/**
* 注册一个StatViewServlet
* @return
*/
@Bean
public ServletRegistrationBean druidStatViewServlet(){
//org.springframework.boot.context.embedded.ServletRegistrationBean提供类的进行注册.
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(),"/druid/*");
//添加初始化参数:initParams
//白名单:
servletRegistrationBean.addInitParameter("allow","127.0.0.1");
//IP黑名单 (存在共同时,deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page.
servletRegistrationBean.addInitParameter("deny","192.168.1.73");
//登录查看信息的账号密码.
servletRegistrationBean.addInitParameter("loginUsername","admin");
servletRegistrationBean.addInitParameter("loginPassword","123456");
//是否能够重置数据.
servletRegistrationBean.addInitParameter("resetEnable","false");
return servletRegistrationBean;
}
@Bean
public StatFilter statFilter() {
StatFilter statFilter = new StatFilter();
statFilter.setLogSlowSql(true);
statFilter.setMergeSql(true);
statFilter.setSlowSqlMillis(1000);
return statFilter;
}
@Bean
public WallFilter wallFilter() {
WallFilter wallFilter = new WallFilter();
// 允许执行多条SQL
WallConfig config = new WallConfig();
config.setMultiStatementAllow(true);
wallFilter.setConfig(config);
return wallFilter;
}
}
同样,我们在ssm.center中添加ssm.core的依赖,并且在Application.java启动类上添加@Import(DruidConfig.class)的注解,接着,我们为了测试,在TestController中添加如下内容:
@Autowired
private JdbcTemplate jdbcTemplate;
@RequestMapping("hello")
public List<Map<String, Object>> hello() {
List<Map<String, Object>> list = jdbcTemplate.queryForList("SELECT user_name,password FROM et_user ", new Object[]{});
return list;
}
最后,我们运行程序,打开新页面http://localhost:8080/druid 登录账号密码,为我们在DruidConfig中设置的账号密码,选中SQL监控,然后执行http://localhost:8080/hello,我们会发现在SQL监控中会出现我们执行一次sql的记录,这样就配置成功了