一个基本开发框架的整合演化之路--8、整合数据库-postgresql
前言
绝大部分项目都需要数据库，下面就spring+xxl-conf配置中心+postgresql做一下整合。
ps：本次整合不包括mybatis，因为项目不需要orm。
初始化配置中心的数据
实际上手动输入数据是很容易出错的，而且有三个环境，test、ppe、product，手滑了输错一个配置，到时候错问题，报null，正式环境都挂了谁能负起这种责任，下面是一份用来初始化各个环境下面配置的脚本：
-- 直接用脚本导入数据库默认配置，方便省事。
-- 小技巧，如果是手动添加数据，那么可以执行这个sql从数据库中直接查找需要的数据，生成需要的数据拼接字符串：
-- with t1 as(
-- select concat("key",'->',"title",'->',value,'$$') as str from xxl_conf_node where env='test' and "key" like 'file-server.redis.%')
-- select string_agg(str,'') from t1;
create or replace function "initDbConfig"(
in envName varchar,
in para_appname varchar
)
returns varchar
as $BODY$
declare _defaultValues varchar;
declare _envName varchar;
declare _appname varchar;
declare _prefix varchar;
declare strArrays varchar[];
declare arrItemLv1 varchar;
declare tempArrSubItem varchar;
declare valArrs varchar[];
declare item_attr varchar;
declare item_title varchar;
declare item_val varchar;
begin
if envName <> 'test' and envName<> 'ppe' and envName<> 'product' then
raise notice '环境变量异常，只能为test、ppe以及product其中一个。';
return '环境变量异常，只能为test、ppe以及product其中一个。';
end if;
_appname:=para_appname;
_prefix:=concat(_appname,'.db.','');
_defaultValues:=
'driverClassName->数据库驱动，譬如：org.postgresql.Driver->org.postgresql.Driver$$' ||
'filters->需要的过滤器,常用的过滤器有: 监控统计用的filter:stat,日志用的filter:log4j ->wall,stat$$' ||
'initialSize->初始化连接数量->5$$' ||
'jdbcUrl->访问地址，例如：jdbc:postgresql://localhost:5432/postgres->jdbc:postgresql://localhost:5432/postgres$$' ||
'maxActive->数据库最大并发连接数->50$$' ||
'maxIdle->最大空闲连接数【已废弃】->0$$' ||
'maxPoolPreparedStatementPerConnectionSize->平均每个数据库连接最大缓存预查询的最大数量->20$$' ||
'maxWait->配置获取连接等待超时的时间【毫秒为单位】->180000$$' ||
'minEvictableIdleTimeMillis->配置一个连接在池中最小生存的时间，单位是毫秒->500$$' ||
'minIdle->最小空闲连接数->0$$' ||
'password->数据库访问密码->123456$$' ||
'poolPreparedStatements->是否打开PSCache，为true时候要指定每个连接上PSCache的大小->true$$' ||
'removeAbandoned->超过时间限制是否回收->true$$' ||
'removeAbandonedTimeout->只有在允许超时回收时候此参数才有意思，表示超过多少秒了要回收【单位：秒】->60$$' ||
'testOnBorrow->申请连接时执行validationQuery检测连接是否有效，配置为true会降低性能->false$$' ||
'testOnReturn->归还连接时执行validationQuery检测连接是否有效，配置为true会降低性能->false$$' ||
'testWhileIdle->申请连接的时候检测->true$$' ||
'timeBetweenEvictionRunsMillis->配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒->60000$$' ||
'username->数据库访问账号->postgres$$' ||
'validationQuery->用来检测连接是否有效的sql，要求是一个查询语句->select ''x''$$'
;
strArrays:=string_to_array(_defaultValues,'$$');
_envName:=envName;
insert into xxl_conf_project ("appname", title) values (_appname,_appname) on conflict ("appname") do nothing;
<<loop4BigArray>>
foreach arrItemLv1 in array strArrays
loop
if char_length(arrItemLv1) < 1 then
raise notice '空字符串无须处理';
continue ;
end if;
valArrs:=string_to_array(arrItemLv1,'->');
item_attr:=valArrs[1];
item_title:=valArrs[2];
item_val:=valArrs[3];
raise notice '属性名称：%,描述：%,当前值：%',item_attr,item_title,item_val;
raise notice '开始添加记录';
insert into xxl_conf_node("env","key","appname","title","value")
values (_envName,concat(_prefix,item_attr),_appname,item_title,item_val)
on conflict ("env","key") do nothing ;
end loop loop4BigArray;
return envName||'环境下的'||_appName||'配置成功';
end;
$BODY$ language plpgsql volatile ;
-- 记住执行下面方法分别添加三个环境下的默认数据。
-- select "initDbConfig"('test','file-server');
-- select "initDbConfig"('ppe','file-server');
-- select "initDbConfig"('product','file-server');
用工具远程登录配置中心的数据库，直接执行即可：
select "initDbConfig"('test','file-server');
select "initDbConfig"('ppe','file-server');
select "initDbConfig"('product','file-server');
编写配置类以及对应插件
请放到WebExt下面
package net.w2p.WebExt.config;
/***
*
* 数据库配置文件，配置内容有：
* 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 {
public String driverClassName = "org.postgresql.Driver";
public String jdbcUrl = "jdbc:postgresql://localhost:5432/postgres";
public String username = "postgres";
public String password = "123456";
public Integer initialSize = 5;
public Integer minIdle = 2;
public Integer maxActive = 50;
public Integer maxWait = 180000;
public Integer timeBetweenEvictionRunsMillis = 60000;
public Integer minEvictableIdleTimeMillis = 300000;
public String validationQuery = "SELECT 'x'";
public Boolean testWhileIdle = true;
public Boolean testOnBorrow = false;
public Boolean testOnReturn = false;
public Boolean poolPreparedStatements = false;
public Integer maxPoolPreparedStatementPerConnectionSize = 20;
public String filters = "wall,stat";
public Boolean removeAbandoned = false;
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;
}
}
使用java代码整合到spring中进行设置
在FileServerWebApp的BeanConfiguration下面添加：
package net.w2p.local.plugins.BeanConfiguration;
import com.alibaba.druid.pool.DruidDataSource;
import com.xxl.conf.core.XxlConfClient;
import net.w2p.WebExt.config.DbConf;
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.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import javax.sql.DataSource;
import java.sql.SQLException;
/****druid数据库连接池配置****/
@Configuration
public class DruidConfiguration {
@Bean(name="dbConf")
public DbConf dbConf(){
final String VarPrefix ="file-server.db.";
DbConf dbConf=new DbConf();
dbConf.driverClassName=XxlConfClient.get(VarPrefix+"driverClassName");
dbConf.jdbcUrl=XxlConfClient.get(VarPrefix+"jdbcUrl");
dbConf.username=XxlConfClient.get(VarPrefix+"username");
dbConf.password=XxlConfClient.get(VarPrefix+"password");
dbConf.initialSize=XxlConfClient.getInt(VarPrefix+"initialSize");
dbConf.minIdle=XxlConfClient.getInt(VarPrefix+"minIdle");
dbConf.maxActive=XxlConfClient.getInt(VarPrefix+"maxActive");
dbConf.maxWait=XxlConfClient.getInt(VarPrefix+"maxWait");
dbConf.timeBetweenEvictionRunsMillis=XxlConfClient.getInt(VarPrefix+"timeBetweenEvictionRunsMillis");
dbConf.minEvictableIdleTimeMillis=XxlConfClient.getInt(VarPrefix+"minEvictableIdleTimeMillis");
dbConf.validationQuery=XxlConfClient.get(VarPrefix+"validationQuery");
dbConf.testWhileIdle=XxlConfClient.getBoolean(VarPrefix+"testWhileIdle");
dbConf.testOnBorrow=XxlConfClient.getBoolean(VarPrefix+"testOnBorrow");
dbConf.testOnReturn=XxlConfClient.getBoolean(VarPrefix+"testOnReturn");
dbConf.poolPreparedStatements=XxlConfClient.getBoolean(VarPrefix+"poolPreparedStatements");
dbConf.maxPoolPreparedStatementPerConnectionSize=XxlConfClient.getInt(VarPrefix+"maxPoolPreparedStatementPerConnectionSize");
dbConf.filters=XxlConfClient.get(VarPrefix+"filters");
dbConf.removeAbandoned=XxlConfClient.getBoolean(VarPrefix+"removeAbandoned");
//还是加一个连接的租期吧，就是说60s内必须close---前提是运行自动关闭已经设置了
dbConf.removeAbandonedTimeout=XxlConfClient.getInt(VarPrefix+"removeAbandonedTimeout");
return dbConf;
}
/***数据连接***/
@Bean(name = "dataSource")
@Autowired
public DataSource druidDataSource(@Qualifier("dbConf")DbConf dbConf) {
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;
}
/***分别生成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);
}
}
代码里面有dbConf的注册，也有dataSource的暴露，更同时将两种jdbc连接托管到spring中。
进行测试
好了，无论是dbConf的获取还是jdbcTemplate执行sql都通过了。