前言
终于到了数据库、配置中心和spring整合了。
ps:通常有难度和容易错的都会标注草稿—因为不把踩过的坑说出来是。。欸,是不会记得的。
配置中心与配置bean
添加相关依赖以及添加配置文件
根模块添加:
目标子模块添加依赖:
在配置中心后台添加对应的设置信息
最终成品如下:
添加对应配置类
在plugins config文件夹下面添加数据库的配置文件:
注意,这次我们来试一试xxl-conf的第二种属性映射方式:
代码:
package net.w2p.local.plugins.config;
import com.xxl.conf.core.annotation.XxlConf;
/***
*
* 数据库配置文件,配置内容有:
* druid.driverClassName=org.postgresql.Driver
* druid.jdbcUrl = jdbc:postgresql://localhost:5432/postgres
* druid.username = postgres
* druid.password = 123456
* druid.initialSize=5
* druid.minIdle=2
* druid.maxActive=50
* druid.maxWait=180000
* druid.timeBetweenEvictionRunsMillis=60000
* druid.minEvictableIdleTimeMillis=300000
* druid.validationQuery=SELECT 'x'
* druid.testWhileIdle=true
* druid.testOnBorrow=true
* druid.testOnReturn=false
* druid.poolPreparedStatements=false
* druid.maxPoolPreparedStatementPerConnectionSize=20
* druid.filters=wall,stat
* druid.removeAbandoned=false
* #还是加一个连接的租期吧,就是说60s内必须close---前提是运行自动关闭已经设置了
* druid.removeAbandonedTimeout = 60
*
* ***/
public class DbConf {
private static final String conf_prefix ="file-server.db.";
@XxlConf(conf_prefix +"driverClassName")
public String driverClassName = "org.postgresql.Driver";
@XxlConf(conf_prefix+"jdbcUrl")
public String jdbcUrl = "jdbc:postgresql://localhost:5432/postgres";
@XxlConf(conf_prefix+"username")
public String username = "postgres";
@XxlConf(conf_prefix+"password")
public String password = "123456";
@XxlConf(conf_prefix+"initialSize")
public Integer initialSize = 5;
@XxlConf(conf_prefix+"minIdle")
public Integer minIdle = 2;
@XxlConf(conf_prefix+"maxActive")
public Integer maxActive = 50;
@XxlConf(conf_prefix+"maxWait")
public Integer maxWait = 180000;
@XxlConf(conf_prefix+"timeBetweenEvictionRunsMillis")
public Integer timeBetweenEvictionRunsMillis = 60000;
@XxlConf(conf_prefix+"minEvictableIdleTimeMillis")
public Integer minEvictableIdleTimeMillis = 300000;
@XxlConf(conf_prefix+"validationQuery")
public String validationQuery = "SELECT 'x'";
@XxlConf(conf_prefix+"testWhileIdle")
public Boolean testWhileIdle = true;
@XxlConf(conf_prefix+"testOnBorrow")
public Boolean testOnBorrow = false;
@XxlConf(conf_prefix+"testOnReturn")
public Boolean testOnReturn = false;
@XxlConf(conf_prefix+"poolPreparedStatements")
public Boolean poolPreparedStatements = false;
@XxlConf(conf_prefix+"maxPoolPreparedStatementPerConnectionSize")
public Integer maxPoolPreparedStatementPerConnectionSize = 20;
@XxlConf(conf_prefix+"filters")
public String filters = "wall,stat";
@XxlConf(conf_prefix+"removeAbandoned")
public Boolean removeAbandoned = false;
//还是加一个连接的租期吧,就是说60s内必须close---前提是运行自动关闭已经设置了
@XxlConf(conf_prefix+"removeAbandonedTimeout")
public Integer removeAbandonedTimeout = 60;
public String getDriverClassName() {
return driverClassName;
}
public void setDriverClassName(String driverClassName) {
this.driverClassName = driverClassName;
}
public String getJdbcUrl() {
return jdbcUrl;
}
public void setJdbcUrl(String jdbcUrl) {
this.jdbcUrl = jdbcUrl;
}
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 Integer getInitialSize() {
return initialSize;
}
public void setInitialSize(Integer initialSize) {
this.initialSize = initialSize;
}
public Integer getMinIdle() {
return minIdle;
}
public void setMinIdle(Integer minIdle) {
this.minIdle = minIdle;
}
public Integer getMaxActive() {
return maxActive;
}
public void setMaxActive(Integer maxActive) {
this.maxActive = maxActive;
}
public Integer getMaxWait() {
return maxWait;
}
public void setMaxWait(Integer maxWait) {
this.maxWait = maxWait;
}
public Integer getTimeBetweenEvictionRunsMillis() {
return timeBetweenEvictionRunsMillis;
}
public void setTimeBetweenEvictionRunsMillis(Integer timeBetweenEvictionRunsMillis) {
this.timeBetweenEvictionRunsMillis = timeBetweenEvictionRunsMillis;
}
public Integer getMinEvictableIdleTimeMillis() {
return minEvictableIdleTimeMillis;
}
public void setMinEvictableIdleTimeMillis(Integer minEvictableIdleTimeMillis) {
this.minEvictableIdleTimeMillis = minEvictableIdleTimeMillis;
}
public String getValidationQuery() {
return validationQuery;
}
public void setValidationQuery(String validationQuery) {
this.validationQuery = validationQuery;
}
public Boolean getTestWhileIdle() {
return testWhileIdle;
}
public void setTestWhileIdle(Boolean testWhileIdle) {
this.testWhileIdle = testWhileIdle;
}
public Boolean getTestOnBorrow() {
return testOnBorrow;
}
public void setTestOnBorrow(Boolean testOnBorrow) {
this.testOnBorrow = testOnBorrow;
}
public Boolean getTestOnReturn() {
return testOnReturn;
}
public void setTestOnReturn(Boolean testOnReturn) {
this.testOnReturn = testOnReturn;
}
public Boolean getPoolPreparedStatements() {
return poolPreparedStatements;
}
public void setPoolPreparedStatements(Boolean poolPreparedStatements) {
this.poolPreparedStatements = poolPreparedStatements;
}
public Integer getMaxPoolPreparedStatementPerConnectionSize() {
return maxPoolPreparedStatementPerConnectionSize;
}
public void setMaxPoolPreparedStatementPerConnectionSize(Integer maxPoolPreparedStatementPerConnectionSize) {
this.maxPoolPreparedStatementPerConnectionSize = maxPoolPreparedStatementPerConnectionSize;
}
public String getFilters() {
return filters;
}
public void setFilters(String filters) {
this.filters = filters;
}
public Boolean getRemoveAbandoned() {
return removeAbandoned;
}
public void setRemoveAbandoned(Boolean removeAbandoned) {
this.removeAbandoned = removeAbandoned;
}
public Integer getRemoveAbandonedTimeout() {
return removeAbandonedTimeout;
}
public void setRemoveAbandonedTimeout(Integer removeAbandonedTimeout) {
this.removeAbandonedTimeout = removeAbandonedTimeout;
}
}
配置类注册
在applicationContext-XxlConf.xml中配置托管的bean。
代码
<!-- ********************************* druid&数据库 配置 ********************************* -->
<bean id="dbConf" class="net.w2p.local.plugins.config.DbConf">
</bean>
测试是否正常运行
然后编写测试程序查看dbconf的获取情况并执行:
代码:
@Resource
DbConf dbConf;
@Test
public void printDb(){
System.out.println(JSONObject.toJSONString(dbConf));
}
可见,执行成功,数据库获取配置成功。
数据源配置
这一小节用到了下面的文章,请适当查阅:
在Spring中使用@Configuration注解加载JavaConfig配置
Spring Boot 自定义数据源 DruidDataSource
spring bean加载顺序解决办法笔记
Druid连接池基本配置
Spring3.1完全基于注解配置@Configuration类中@Autowire无法注入问题解决
spring中bean配置和bean注入
首先在plugins下面添加beanconfig,用java类来做配置:
代码如下:
package net.w2p.local.plugins.BeanConfiguration;
import com.alibaba.druid.pool.DruidDataSource;
import net.w2p.local.plugins.config.DbConf;
import org.springframework.beans.factory.annotation.Autowire;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.DependsOn;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.sql.SQLException;
/**
* Druid监控web配置
*
* @author jinxiaoxin
*
*/
/*******/
@DependsOn(value = "dbConf")
@Configuration
public class DruidConfig {
@Resource
DbConf dbConf;
@Bean(autowire= Autowire.BY_TYPE)
public DataSource druidDataSource() {
if(dbConf==null){
System.out.println("欸???数据库配置没有读取成功!!!!");
}
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(dbConf.getDriverClassName());
druidDataSource.setUrl(dbConf.getJdbcUrl());
druidDataSource.setUsername(dbConf.getUsername());
druidDataSource.setPassword(dbConf.getPassword());
druidDataSource.setInitialSize(dbConf.getInitialSize());
druidDataSource.setMaxActive(dbConf.getMaxActive());
druidDataSource.setMaxWait(dbConf.getMaxWait());
druidDataSource
.setTimeBetweenEvictionRunsMillis(dbConf.getTimeBetweenEvictionRunsMillis());
druidDataSource
.setMinEvictableIdleTimeMillis(dbConf.getMinEvictableIdleTimeMillis());
druidDataSource.setValidationQuery(dbConf.getValidationQuery());
druidDataSource.setTestWhileIdle(dbConf.getTestWhileIdle());
druidDataSource.setTestOnBorrow(dbConf.getTestOnBorrow());
druidDataSource.setTestOnReturn(dbConf.getTestOnReturn());
druidDataSource.setPoolPreparedStatements(dbConf.getPoolPreparedStatements());
druidDataSource
.setMaxPoolPreparedStatementPerConnectionSize(dbConf.
getMaxPoolPreparedStatementPerConnectionSize());
druidDataSource.setConnectionProperties("");
try {
druidDataSource.setFilters(dbConf.getFilters());
} catch (SQLException e) {
e.printStackTrace();
}
return druidDataSource;
}
}
然后在applicationContext.xml里面的componentScan要加上这个路径:
代码如下:
<!--
net.w2p.local.plugins.utils =》 redis,数据库连接池等插件所在
net.w2p.local.Shared.mybatis.TypeHandlers =》 自定义mybatis数据类型转换,譬如,数组类型,布尔值类型等。
net.w2p.local.plugins.BeanConfiguration=》自定义的java类作为configuration的方法,包含了数据库datasource源
-->
<context:component-scan
base-package="
net.w2p.local.plugins.utils,
net.w2p.Shared.mybatis.TypeHandlers,
net.w2p.local.plugins.BeanConfiguration
"/>
好了,编写测试用例来看看是不是能够正常运行:
代码:
@Resource
DataSource dataSource;
@Test
public void tryTestDataSourceConfig() throws Exception {
Connection cnn=dataSource.getConnection();
cnn.close();
}
好了,现在运行一下:
出错了,问题是:
Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'druidDataSource' defined in net.w2p.local.plugins.BeanConfiguration.DruidConfig: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'druidDataSource' threw exception; nested exception is java.lang.NullPointerException
好了,跟我一步一步调试一下程序吧。。
本人有点怀疑是dbConf没有注入,any way,开始调试:
1、
查看dbConf的内容,欸,已经有数据了,那么dbConf无问题。那没办法了,全部都丧心病狂打断点,哪里断了就哪里有问题。
2、
经过反复比较,发现:
每次运行到setMaxActive以后就跳出来了,一看,maxActive是null,这一步就错了。。。
好了,核查原因。
结果发现配置中心里面没有配置:
疏忽了。
加上配置:
然后执行,
好,datasource成功加载了。
配置JdbcTemplate等数据连接对象
一般来说,网上大部分教程都是用xml进行配置的,譬如:
但我们反其道而行之,就是喜欢写代码,用代码来配置,在DruidConfig文件里面进行配置,例如:
代码:
/***分别生成jdbcTemplate以及NamedParameterJdbcTemplate两个不同的数据库连接对象***/
/***注意,这里要有参数表示注入的是我们之前初始化的datasource***/
@Bean(name="jdbcTemplate")
@Autowired
public JdbcTemplate jdbcTemplate(@Qualifier("dataSource")DataSource dataSource){
return new JdbcTemplate(dataSource);
}
@Bean(name="namedParameterJdbcTemplate")
@Autowired
public NamedParameterJdbcTemplate namedParameterJdbcTemplate(@Qualifier("dataSource")DataSource dataSource){
return new NamedParameterJdbcTemplate(dataSource);
}
测试
编写测试代码:
@Autowired
JdbcTemplate jdbcTemplate;
@Autowired
NamedParameterJdbcTemplate template;
@Test
public void testJdbcTemplate(){
List objs=jdbcTemplate.queryForList("select 1");
System.out.println(JSONObject.toJSONString(objs));
List objs2=template.queryForList("select now()",new HashMap<>());
System.out.println(JSONObject.toJSONString(objs2));
}
进行测试:
好了,测试成功,成功配置数据源以及数据访问对象。