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

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

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

1、引入依赖包

com.alibaba

druid

1.1.22

org.springframework.boot

spring-boot-starter-data-jpa

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.getInitfldSJUePUBialSize());

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;

fldSJUePUB

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 getVendorProperties(DataSource dataSource) {

Map 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());

}

}

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。