springboot 中 druid+jpa+MYSQL数据库配置

48 阅读 0 评论 32 点赞

我是靠谱客的博主伶俐百合，最近开发中收集的这篇文章主要介绍springboot 中 druid+jpa+MYSQL数据库配置，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

Druid来自于阿里的一个开源连接池能够提供强大的监控和扩展功能，Spring Boot默认不支持Druid和jpa，需要引入依赖。

1、引入依赖包

<!--druid-->
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.22</version>
</dependency>
<!--jpa-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

2、配置application.properties

#druid配置-MYSQL
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test1?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&allowPublicKeyRetrieval=true
spring.datasource.username=root
spring.datasource.password=123456
# 初始化大小，最小，最大
spring.datasource.initialSize=5
spring.datasource.maxActive=20
spring.datasource.minIdle=5
# 配置获取连接等待超时的时间
spring.datasource.max-wait=60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
spring.datasource.time-between-eviction-runs-millis=60000
# 配置一个连接在池中最小生存的时间，单位是毫秒
spring.datasource.min-evictable-idle-time-millis=300000
#检测连接是否有效的sql,要求是一个查询语句，常用select 'x'.如果validationQuery为null,testOnBorrow,testOnBorrow,testOnReturn,testWhileIdle都不会起作用。这个可以不配置
spring.datasource.validation-query=SELECT 'x'
#检测连接是否有效的超时时间。
spring.datasource.validation-query-timeout=60000
spring.datasource.test-while-idle=true
spring.datasource.test-on-borrow=false
spring.datasource.test-on-return=false
# 打开PSCache，并且指定每个连接上PSCache的大小
spring.datasource.pool-prepared-statements=true
spring.datasource.max-pool-prepared-statement-per-connection-size=20
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙，#别名方式，扩展插件，监控统计用的filter:stat，日志用的filter:log4j，防御sql注入的filter:wall
spring.datasource.filters=stat,wall,slf4j

3、Druid配置信息定制

@Configuration
public class DruidConfig {
@Autowired
private DruidDataSourceProperties properties;
@Bean(name = "druidDataSource", initMethod = "init", destroyMethod = "close")
@Qualifier("druidDataSource")
public DataSource dataSource() throws Exception {
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setUrl(properties.getUrl());
druidDataSource.setUsername(properties.getUsername());
druidDataSource.setPassword(properties.getPassword());
druidDataSource.setDriverClassName(properties.getDriverClassName());
druidDataSource.setInitialSize(properties.getInitialSize());
druidDataSource.setMaxActive(properties.getMaxActive());
druidDataSource.setMinIdle(properties.getMinIdle());
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());
druidDataSource.setFilters(properties.getFilters());
try {
if (null != druidDataSource) {
druidDataSource.setFilters("wall,stat");
druidDataSource.setUseGlobalDataSourceStat(true);
//
Properties properties = new Properties();
//
properties.setProperty("decrypt", "true");
//
druidDataSource.setConnectProperties(properties);
druidDataSource.init();
}
} catch (Exception e) {
throw new RuntimeException(
"load datasource error, dbProperties is :", e);
}
return druidDataSource;
}
}

3、获取Properties中配置信息


@Configuration
@ConfigurationProperties(prefix = "spring.datasource")
public class DruidDataSourceProperties {
private String url;
private String username;
private String password;
private String driverClassName;
private int initialSize;
private int maxActive;
private int minIdle;
private int 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;
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 String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public int getInitialSize() {
return initialSize;
}
public void setInitialSize(int initialSize) {
this.initialSize = initialSize;
}
public int getMaxActive() {
return maxActive;
}
public void setMaxActive(int maxActive) {
this.maxActive = maxActive;
}
public int getMinIdle() {
return minIdle;
}
public void setMinIdle(int minIdle) {
this.minIdle = minIdle;
}
public int getMaxWait() {
return maxWait;
}
public void setMaxWait(int 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 int getMaxPoolPreparedStatementPerConnectionSize() {
return maxPoolPreparedStatementPerConnectionSize;
}
public void setMaxPoolPreparedStatementPerConnectionSize(
int maxPoolPreparedStatementPerConnectionSize) {
this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
}
public String getFilters() {
return filters;
}
public void setFilters(String filters) {
this.filters = filters;
}
public DruidDataSourceProperties() {
// TODO Auto-generated constructor stub
}
public DruidDataSourceProperties(String url, String username,
String password, String driverClassName, int initialSize,
int maxActive, int minIdle, int maxWait,
long timeBetweenEvictionRunsMillis,
long minEvictableIdleTimeMillis, String validationQuery,
boolean testWhileIdle, boolean testOnBorrow, boolean testOnReturn,
boolean poolPreparedStatements,
int maxPoolPreparedStatementPerConnectionSize, String filters) {
this.url = url;
this.username = username;
this.password = password;
this.driverClassName = driverClassName;
this.initialSize = initialSize;
this.maxActive = maxActive;
this.minIdle = minIdle;
this.maxWait = maxWait;
this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
this.validationQuery = validationQuery;
this.testWhileIdle = testWhileIdle;
this.testOnBorrow = testOnBorrow;
this.testOnReturn = testOnReturn;
this.poolPreparedStatements = poolPreparedStatements;
this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
this.filters = filters;
}
}

如果需要Druid的监控统计功能在配置代码中加入以下代码：

	@Bean
public ServletRegistrationBean druidServlet() {
ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
// IP白名单 (没有配置或者为空，则允许所有访问)
servletRegistrationBean.addInitParameter("allow", "127.0.0.1");
// IP黑名单(共同存在时，deny优先于allow)
//servletRegistrationBean.addInitParameter("deny", "");
//控制台管理用户
servletRegistrationBean.addInitParameter("loginUsername", "admin");
servletRegistrationBean.addInitParameter("loginPassword", "admin");
//是否能够重置数据 禁用HTML页面上的“Reset All”功能
servletRegistrationBean.addInitParameter("resetEnable", "false");
return servletRegistrationBean;
}
@Bean
public FilterRegistrationBean filterRegistrationBean() {
FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
filterRegistrationBean.addUrlPatterns("/*");
filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
return filterRegistrationBean;
}

访问地址：http://127.0.0.1:8080/druid，使用配置的账号密码登录即可查看数据源及SQL统计等监控信息。

4、jpa配置


@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "entityManagerFactory",
transactionManagerRef = "transactionManager",
basePackages = {"*.dao"})//指定需要扫描的dao所在包
public class RepositoryConfig {
@Autowired
private JpaProperties jpaProperties;
@Autowired
@Qualifier("druidDataSource")
private DataSource druidDataSource;
@Bean(name = "entityManager")
@Primary
public EntityManager entityManager(EntityManagerFactoryBuilder builder) {
return entityManagerFactory(builder).getObject().createEntityManager();
}
/**
* 指定需要扫描的实体包实现与数据库关联
* @param builder
* @return
*/
@Bean(name = "entityManagerFactory")
@Primary
public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder builder) {
return builder
.dataSource(druidDataSource)
.properties(getVendorProperties(druidDataSource))
.packages("*.entity")//指定需要扫描的entity所在包
.build();
}
/**
* 通过jpaProperties指定hibernate数据库方言以及在控制台打印sql语句
* @param dataSource
* @return
*/
private Map<String, String> getVendorProperties(DataSource dataSource) {
Map<String, String> map = jpaProperties.getProperties();
map.put("hibernate.dialect", "org.hibernate.dialect.MySQL8Dialect");
map.put("hibernate.show_sql", "true");
return map;
}
/**
* 创建事务管理
* @param builder
* @return
*/
@Bean(name = "transactionManager")
@Primary
PlatformTransactionManager transactionManager(EntityManagerFactoryBuilder builder) {
return new JpaTransactionManager(entityManagerFactory(builder).getObject());
}
}