这边业务有个需求，通过后端请求，执行Hologres端的sql并返回数据
根据官方文档，除了标准JDBC连接外，还可以通过Druid进行连接池配置。因此，基于Druid连接池2+DbUtils进行脚手架搭建便成为我的最佳选择

pom

hologres用的就是postgresql的java驱动，同时还需引入druid和dbutils的依赖，其他依赖来自于业务所需的微服务开发。

    <dependencies>
        <!-- SpringCloud Alibaba Nacos -->
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-nacos-discovery</artifactId>
        </dependency>

        <!-- SpringCloud Alibaba Nacos Config -->
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-nacos-config</artifactId>
        </dependency>

        <!-- SpringCloud Alibaba Sentinel -->
        <dependency>
            <groupId>com.alibaba.cloud</groupId>
            <artifactId>spring-cloud-starter-alibaba-sentinel</artifactId>
        </dependency>

        <!-- SpringBoot Web -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <!-- SpringBoot Actuator -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-actuator</artifactId>
        </dependency>

        <!-- lombak-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>io.springfox</groupId>
            <artifactId>springfox-core</artifactId>
            <version>3.0.0</version>
            <scope>compile</scope>
        </dependency>

        <!-- swagger-->
        <dependency>
            <groupId>cn.com.ruijie.rycem</groupId>
            <artifactId>rycem-common_swagger</artifactId>
        </dependency>

        <!-- hologres-->
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
        </dependency>
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.7</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.2.15</version>
        </dependency>

        <!-- aop-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-aop</artifactId>
        </dependency>
    </dependencies>

bootstrap.yml

# Tomcat
server:
  port: 9109
  servlet:
    context-path: /rycem/api/analysis

# hologres只能在ECS环境下连接上，这里我使用了本地隧穿，所以ip填的127.0.0.1
hologres:
  url: "jdbc:postgresql://${hologres.ip}:${hologres.port}/${hologres.db}?user=${hologres.access_id}&password=${hologres.access_key}&ApplicationName=${hologres.application_name}&reWriteBatchedInserts=true"
  ip: "127.0.0.1"
  port: "port"
  db: "db"
  access_id: "access_id"
  access_key: "access_key"
  application_name: "application_name"


spring:
  # 数据源配置
  datasource:
    type: com.alibaba.druid.pool.DruidDataSource
    driver-class-name: org.postgresql.Driver
    url: ${hologres.url}
    # 连接池配置
    druid:
      # 初始化大小，最小，最大连接数
      initial-size: 5
      min-idle: 10
      max-active: 20
      # 配置获取连接等待超时的时间
      max-wait: 60000
      # 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位毫秒
      time-between-eviction-runs-millis: 2000
      # 配置一个连接在池中最小生存时间
      min-evictable-idle-time-millis: 600000
      max-evictable-idle-time-millis: 900000
      # 用来测试连接是否可用的SQL语句
      validation-query: SELECT 1
      # 应用向连接池申请连接，并且testOnBorrow为false时，连接池将会判断连接是否处于空闲状态，如果是，则验证这条连接是否可用
      test-while-idle: true
      # 如果为true，默认是false，应用向连接池申请连接时，连接池会判断这条连接是否是可用的
      test-on-borrow: false
      # 如果为true（默认false），当应用使用完连接，连接池回收连接的时候会判断该连接是否还可用
      test-on-return: false
      phy-max-use-count: 100000
      # 连接池中的minIdle数量以内的连接，空闲时间超过minEvictableIdleTimeMillis，则会执行keepAlive操作
      keepAlive: true
      # Spring 监控，利用aop 对指定接口的执行时间，jdbc数进行记录，需要引入spring-boot-starter-aop的maven依赖
      aop-patterns: "com.rycem.analysis.controller"
      ########### 启用内置过滤器（第一个 stat必须，否则监控不到SQL）##########
      filters: stat,wall,log4j2
      # 自己配置监控统计拦截的filter
      filter:
        # 开启druiddatasource的状态监控
        stat:
          enabled: true
          db-type: postgresql
          # 开启慢sql监控，超过2s 就认为是慢sql，记录到日志中
          log-slow-sql: true
          slow-sql-millis: 2000
        # 日志监控，使用slf4j 进行日志输出
        slf4j:
          enabled: true
          statement-log-error-enabled: true
          statement-create-after-log-enabled: false
          statement-close-after-log-enabled: false
          result-set-open-after-log-enabled: false
          result-set-close-after-log-enabled: false
      ########## 配置WebStatFilter，用于采集web关联监控的数据 ##########
      web-stat-filter:
        enabled: true                   # 启动 StatFilter
        url-pattern: /*                 # 过滤所有url
        exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*" # 排除一些不必要的url
        session-stat-enable: true       # 开启session统计功能
        session-stat-max-count: 1000    # session的最大个数,默认100
      ########## 配置StatViewServlet（监控页面），用于展示Druid的统计信息 ##########
      stat-view-servlet:
        enabled: true              # 启用StatViewServlet
        url-pattern: /druid/*      # 访问内置监控页面的路径，内置监控页面的首页是/druid/index.html
        reset-enable: false        # 不允许清空统计数据,重新计算
        login-username: xxx       # 配置监控页面访问帐号
        login-password: xxx       # 配置监控页面访问密码
        allow: 127.0.0.1           # 允许访问的地址，如果allow没有配置或者为空，则允许所有访问
        deny:

  application:
    # 应用名称
    name: rycem-analysis
  profiles:
    # 环境配置
    active: dev
  cloud:
    nacos:
      discovery:
        # 服务注册地址
        server-addr: 127.0.0.1:8848
      config:
        username: xxx
        password: xxx
        # 配置中心地址
        server-addr: 127.0.0.1:8848
        # 配置文件格式
        file-extension: yml
        # 共享配置
        shared-configs:
          - application-${spring.profiles.active}.${spring.cloud.nacos.config.file-extension}
          - ${spring.application.name}-${spring.profiles.active}.${spring.cloud.nacos.config.file-extension}

配置类

自动配置类（可选）

由于该微服务会读取nacos的配置（必须使用），其中配置中心有这个一句：

spring:
  autoconfigure:
    exclude: com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure

该配置会让该微服务的Druid监控页面无法开启

因此本地执行创建了一个自动配置类，写法其实就是com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceAutoConfigure，照抄过来的。

package com.rycem.analysis.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.spring.boot.autoconfigure.DruidDataSourceWrapper;
import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.spring.boot.autoconfigure.stat.DruidFilterConfiguration;
import com.alibaba.druid.spring.boot.autoconfigure.stat.DruidSpringAopConfiguration;
import com.alibaba.druid.spring.boot.autoconfigure.stat.DruidStatViewServletConfiguration;
import com.alibaba.druid.spring.boot.autoconfigure.stat.DruidWebStatFilterConfiguration;
import lombok.extern.slf4j.Slf4j;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.boot.autoconfigure.condition.ConditionalOnClass;
import org.springframework.boot.autoconfigure.condition.ConditionalOnMissingBean;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.EnableConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Import;

import javax.sql.DataSource;

/**
 * @ClassName DruidDataSourceAutoConfigure
 * @Description TODO
 * @Author 陈铭
 * @Date 12:25 2023/4/18
 * @Version 1.0
 **/
@Slf4j
@Configuration
@ConditionalOnClass(DruidDataSource.class)
@AutoConfigureBefore(DataSourceAutoConfiguration.class)
@EnableConfigurationProperties({DruidStatProperties.class, DataSourceProperties.class})
@Import({DruidSpringAopConfiguration.class,
        DruidStatViewServletConfiguration.class,
        DruidWebStatFilterConfiguration.class,
        DruidFilterConfiguration.class})
public class DruidDataSourceAutoConfigure {

    @SuppressWarnings("all")
    @Bean(initMethod = "init")
    @ConditionalOnMissingBean
    public DataSource dataSource() {
        log.info("初始化DruidDataSource");
        return new DruidDataSourceWrapper();
    }
}

去除Druid的广告

package com.rycem.analysis.config;

import com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties;
import com.alibaba.druid.util.Utils;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.boot.autoconfigure.condition.ConditionalOnWebApplication;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.servlet.*;
import java.io.IOException;

/**
 * @ClassName RemoveDruidAdConfig
 * @Description TODO
 * @Author 陈铭
 * @Date 12:55 2023/4/18
 * @Version 1.0
 **/

@SuppressWarnings("all")
@Configuration
@ConditionalOnWebApplication
@AutoConfigureAfter(DruidDataSourceAutoConfigure.class)
@ConditionalOnProperty(name = "spring.datasource.druid.stat-view-servlet.enabled",
        havingValue = "true", matchIfMissing = true)
public class RemoveDruidAdConfig {

    /**
     * 方法名: removeDruidAdFilterRegistrationBean
     * 方法描述 除去页面底部的广告
     *
     * @param properties com.alibaba.druid.spring.boot.autoconfigure.properties.DruidStatProperties
     * @return org.springframework.boot.web.servlet.FilterRegistrationBean
     */
    @Bean
    public FilterRegistrationBean removeDruidAdFilterRegistrationBean(DruidStatProperties properties) {

        // 获取web监控页面的参数
        DruidStatProperties.StatViewServlet config = properties.getStatViewServlet();
        // 提取common.js的配置路径
        String pattern = config.getUrlPattern() != null ? config.getUrlPattern() : "/druid/*";
        String commonJsPattern = pattern.replaceAll("\\*", "js/common.js");

        final String filePath = "support/http/resources/js/common.js";

        //创建filter进行过滤
        Filter filter = new Filter() {
            @Override
            public void init(FilterConfig filterConfig) throws ServletException {
            }

            @Override
            public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain) throws IOException, ServletException {
                chain.doFilter(request, response);
                // 重置缓冲区，响应头不会被重置
                response.resetBuffer();
                // 获取common.js
                String text = Utils.readFromResource(filePath);
                // 正则替换banner, 除去底部的广告信息
                text = text.replaceAll("<a.*?banner\"></a><br/>", "");
                text = text.replaceAll("powered.*?shrek.wang</a>", "");
                response.getWriter().write(text);
            }

            @Override
            public void destroy() {
            }
        };
        FilterRegistrationBean registrationBean = new FilterRegistrationBean();
        registrationBean.setFilter(filter);
        registrationBean.addUrlPatterns(commonJsPattern);
        return registrationBean;
    }
}

工具类

在service层调用工具类方法进行查询就好了

package com.rycem.analysis.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.rycem.analysis.except.AnalysisSqlException;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.springframework.stereotype.Component;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * @ClassName SqlCofig
 * @Description TODO
 * @Author 陈铭
 * @Date 9:39 2023/4/18
 * @Version 1.0
 **/
@Component
@Slf4j
@RequiredArgsConstructor
public class SqlUtil {

    private final DruidDataSource druidDataSource;

    private QueryRunner getQueryRunner() {
        return new QueryRunner(druidDataSource);
    }

    public List<Object[]> queryList(String requestId, String sql) {
        QueryRunner queryRunner = getQueryRunner();
        List<Object[]> result;
        try {
            result = queryRunner.query(sql, new ArrayListHandler());
        } catch (SQLException e) {
            log.error("【执行sql异常】==>"
                    .concat(" requestId:").concat(requestId)
                    .concat(" sql:").concat(sql)
            );
            throw new AnalysisSqlException(requestId, "【执行sql异常】");
        }
        return result;
    }

    public List<Map<String, Object>> queryMap(String requestId, String sql) {
        QueryRunner queryRunner = getQueryRunner();
        List<Map<String, Object>> result;
        try {
            result = queryRunner.query(sql, new MapListHandler());
        } catch (SQLException e) {
            log.error("【执行sql异常】==>"
                    .concat(" requestId:").concat(requestId)
                    .concat(" sql:").concat(sql)
            );
            throw new AnalysisSqlException(requestId, "【执行sql异常】");
        }
        return result;
    }

    public <T> T queryBean(String requestId, String sql, Class<T> t) {
        QueryRunner queryRunner = getQueryRunner();
        T result = null;
        try {
            result = queryRunner.query(sql, new BeanHandler<T>((Class<? extends T>) t.getClass()));
        } catch (SQLException e) {
            log.error("【执行sql异常】==>"
                    .concat(" requestId:").concat(requestId)
                    .concat(" sql:").concat(sql)
            );
            throw new AnalysisSqlException(requestId, "【执行sql异常】");
        }
        return result;
    }

}

测试

随便写一个接口，用工具类去执行sql，访问：
http://127.0.0.1:9109/rycem/api/analysis/druid/sql.html

目录CONTENT

Hologres后端SQL开发脚手架（Druid连接池2+DbUtils）

pom

bootstrap.yml

配置类

自动配置类（可选）

去除Druid的广告

工具类

测试

评论区