1.pom.xml <!-- 阿里数据连接池 --> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.20</version> </dependency>
<!-- MySql --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <!-- Oracle --> <dependency> <groupId>com.oracle</groupId> <artifactId>ojdbc6</artifactId> <version>11.2.0.3</version> </dependency>
<dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency>
2.application.properties
#MySql 数据源配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.name=druidDataSource spring.datasource.druid.url=jdbc:mysql://127.0.0.1:3306/mango?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8 spring.datasource.druid.username=root spring.datasource.druid.password=root spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
#Oracle 数据源配置
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource spring.datasource.name=druidDataSource
spring.datasource.druid.driver-class-name=oracle.jdbc.OracleDriver spring.datasource.druid.url=jdbc:oracle:thin:@127.0.0.1:1521:orcl spring.datasource.druid.username=news spring.datasource.druid.password=news_1
#监控配置
# 初始化大小 spring.datasource.druid.initial-size=1 #最小连接数 spring.datasource.druid.min-idle=1 #最大连接数 spring.datasource.druid.max-active=20 # 配置获取连接等待超时的时间 spring.datasource.druid.max-wait=60000 # 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒 spring.datasource.druid.time-between-eviction-runs-millis=60000 # 配置一个连接在池中最小生存的时间,单位是毫秒 spring.datasource.druid.min-evictable-idle-time-millis=300000 spring.datasource.druid.validation-query=SELECT 1 FROM DUAL spring.datasource.druid.test-while-idle=true spring.datasource.druid.test-on-borrow=false spring.datasource.druid.test-on-return=false # 打开PSCache,并且指定每个连接上PSCache的大小 spring.datasource.druid.pool-prepared-statements=50 spring.datasource.druid.max-pool-prepared-statement-per-connection-size=20 # 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙 spring.datasource.druid.filters=stat,wall,config
#新建文件 DruidConfig、DruidDataSourcePropertiesConfig
1)数据源监控属性配置 和 上面的监控配置有关 DruidDataSourcePropertiesConfig
import lombok.Data; import org.springframework.boot.context.properties.ConfigurationProperties; /** * 数据源监控属性 配置 * * @author lhh * @Date 2019/10/26 22:03 */ @ConfigurationProperties(prefix = "spring.datasource.druid") @Data public class DruidDataSourcePropertiesConfig { private String driverClassName; private String url; private String username; private String password; private int initialSize; private int minIdle; private int 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 int maxPoolPreparedStatementPerConnectionSize; private String filters; }
2)注入属性和配置连接池 DruidConfig
import com.alibaba.druid.pool.DruidDataSource; import com.alibaba.druid.support.http.StatViewServlet; import com.alibaba.druid.support.http.WebStatFilter; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean; import org.springframework.boot.context.properties.EnableConfigurationProperties; import org.springframework.boot.web.servlet.FilterRegistrationBean; import org.springframework.boot.web.servlet.ServletRegistrationBean; import org.springframework.context.annotation.Bean; import org.springframework.context.annotation.Configuration; import javax.servlet.Servlet; import javax.sql.DataSource; import java.sql.SQLException; /** * 数据源监控 配置Servlet 和 Filter * 主要是注入属性和配置连接池 黑白名单,监控管理后台账号密码等配置 * * 访问: http://localhost:8080/druid/login.html * * @author lhh * @Date 2019/10/26 22:43 */ @Configuration @EnableConfigurationProperties({DruidDataSourcePropertiesConfig.class}) public class DruidConfig { @Autowired private DruidDataSourcePropertiesConfig properties; @Bean @ConditionalOnMissingBean public DataSource druidDataSource() { DruidDataSource druidDataSource = new DruidDataSource(); druidDataSource.setDriverClassName(properties.getDriverClassName()); druidDataSource.setUrl(properties.getUrl()); druidDataSource.setUsername(properties.getUsername()); druidDataSource.setPassword(properties.getPassword()); druidDataSource.setInitialSize(properties.getInitialSize()); druidDataSource.setMinIdle(properties.getMinIdle()); druidDataSource.setMaxActive(properties.getMaxActive()); druidDataSource.setMaxWait(properties.getMaxWait()); druidDataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis()); druidDataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis()); druidDataSource.setValidationQuery(properties.getValidationQuery()); druidDataSource.setTestWhileIdle(properties.isTestWhileIdle()); druidDataSource.setTestOnBorrow(properties.isTestOnBorrow()); druidDataSource.setTestOnReturn(properties.isTestOnReturn()); druidDataSource.setPoolPreparedStatements(properties.isPoolPreparedStatements()); druidDataSource.setMaxPoolPreparedStatementPerConnectionSize(properties.getMaxPoolPreparedStatementPerConnectionSize()); try { druidDataSource.setFilters(properties.getFilters()); druidDataSource.init(); } catch (SQLException e) { e.printStackTrace(); } return druidDataSource; } /** * 注册Servlet信息, 配置监控视图 * 相当于Web Servlet配置 * * @return */ @Bean @ConditionalOnMissingBean public ServletRegistrationBean<Servlet> druidServlet() { ServletRegistrationBean<Servlet> servletRegistrationBean = new ServletRegistrationBean<Servlet>(new StatViewServlet(), "/druid/*"); //白名单: servletRegistrationBean.addInitParameter("allow", "127.0.0.1"); //IP黑名单 (存在共同时,deny优先于allow) : 如果满足deny的话提示:Sorry, you are not permitted to view this page. servletRegistrationBean.addInitParameter("deny", ""); //登录查看信息的账号密码, 用于登录Druid监控后台 servletRegistrationBean.addInitParameter("loginUsername", "admin"); servletRegistrationBean.addInitParameter("loginPassword", "admin"); //是否能够重置数据. servletRegistrationBean.addInitParameter("resetEnable", "true"); return servletRegistrationBean; } /** * 注册Filter信息, 监控拦截器 * 相当于Web Filter配置 * * @return */ @Bean @ConditionalOnMissingBean public FilterRegistrationBean filterRegistrationBean() { FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(); filterRegistrationBean.setFilter(new WebStatFilter()); filterRegistrationBean.addUrlPatterns("/*"); filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"); return filterRegistrationBean; } }
查看监控视图
启动应用,访问: http://localhost:8080/druid/login.html, 进入Druid监控后台页面。
遇到的问题
Caused by: java.lang.IllegalStateException: dbType not support : null, url null //非法语句异常
原先填的:
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/mango?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8 spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver
修改之后就没有报此错了:
spring.datasource.druid.url=jdbc:mysql://127.0.0.1:3306/mango?useUnicode=true&zeroDateTimeBehavior=convertToNull&autoReconnect=true&characterEncoding=utf-8 spring.datasource.druid.username=root spring.datasource.druid.password=root spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
分析为什么:debug 测试 是因为 DruidDataSource 注入属性url,username,password,driver-class-name为空导致的问题。
更多资料参考:
Druid: https://github.com/alibaba/druid
监控配置 Druid Spring Starter: https://github.com/alibaba/druid/tree/master/druid-spring-boot-starter
官方文档 https://github.com/alibaba/druid/wiki
其它人博客:https://www.cnblogs.com/niejunlei/p/5977895.html