这边业务有个需求，通过后端请求，执行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
评论区