一个基本开发框架的整合演化之路--8、整合数据库-postgresql

最新推荐文章于 2022-04-21 08:23:52 发布

码农下的天桥

最新推荐文章于 2022-04-21 08:23:52 发布

阅读量266

点赞数

文章标签： spring xxl-conf postgresql

本文链接：https://blog.csdn.net/cdnight/article/details/86663314

版权

前言

绝大部分项目都需要数据库，下面就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中。

进行测试

测试代码：

    @Resource
    DbConf dbConf;

    @Test
    public void printDb(){
        System.out.println(JSONObject.toJSONString(dbConf));
    }



    @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));
    }

测试结果：
在这里插入图片描述

在这里插入图片描述

好了，无论是dbConf的获取还是jdbcTemplate执行sql都通过了。

码农下的天桥

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一个基本开发框架的整合演化之路--8、整合数据库-postgresql

前言绝大部分项目都需要数据库，下面就spring+xxl-conf配置中心+postgresql做一下整合。ps：本次整合不包括mybatis，因为项目不需要orm。初始化配置中心的数据实际上手动输入数据是很容易出错的，而且有三个环境，test、ppe、product，手滑了输错一个配置，到时候错问题，报null，正式环境都挂了谁能负起这种责任，下面是一份用来初始化各个环境下面配置的脚本：...
复制链接

扫一扫