目录
0. 相关文章链接
1. 开发目的
在使用SpringBoot后端开发中，我们如果需要对数据库（比如MySQL）进行增删查改，可以很方便的使用Mybatis进行操作。但是在大数据中，如果想要对数据库进行操作，就没有那么方便，虽然大数据组件中有各种连接源和写入源，比如Spark读取MySQL数据库，flink写入MySQL数据库等。但是也有很多需要操作其他数据库，或者在中途读取维度数据的情况，比如Flink将数据写入到Doris中，这样就不方便操作了，此时一个较为方便的工具类能方便很多使用。
2. 导入依赖
如下依赖是以华为GaussDBForDWS为案例
<!-- 华为云GaussDB的连接JDBC的Jar包 -->
<dependency>
<groupId>com.huaweicloud.dws</groupId>
<artifactId>huaweicloud-dws-jdbc</artifactId>
</dependency>
<!-- 日志打印的jar包 -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<scope>provided</scope>
</dependency>
<!-- json解析包，fastjson包 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
</dependency>
<!--commons-beanutils 是 Apache 开源组织提供的用于操作 JAVA BEAN 的工具包。使用 commons-beanutils，我们可以很方便的对 bean 对象的属性进行操作-->
<dependency>
<groupId>commons-beanutils</groupId>
<artifactId>commons-beanutils</artifactId>
<scope>provided</scope>
</dependency>
<!--Guava 工程包含了若干被 Google 的 Java 项目广泛依赖的核心库,方便开发-->
<dependency>
<groupId>com.google.guava</groupId>
<artifactId>guava</artifactId>
<scope>provided</scope>
</dependency>
<!-- 数据库连接池和jdbc操作模板 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
<version>${springboot.version}</version>
</dependency>
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>2.6.1</version>
</dependency>对应的版本号：
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
<scala.binary.version>2.11</scala.binary.version>
<scala.version>2.11.8</scala.version>
<flink.binary.version>1.10</flink.binary.version>
<flink.version>1.10.0</flink.version>
<alink.version>1.4.0</alink.version>
<log4j.version>1.2.17</log4j.version>
<slf4j.version>1.7.21</slf4j.version>
<mysql.version>8.0.21</mysql.version>
<fastjson.version>1.2.75</fastjson.version>
<huaweicloud.dws.jdbc.version>8.1.0</huaweicloud.dws.jdbc.version>
<commons.beanutils.version>1.9.4</commons.beanutils.version>
<guava.version>29.0-jre</guava.version>
<okhttp.version>3.6.0</okhttp.version>
<springboot.version>2.0.2.RELEASE</springboot.version>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<avro.version>1.10.0</avro.version>
</properties>3. 代码
注意：下述代码中使用了自定义的ModelUtil工具类，该工具类的具体介绍可以参考博主的另一篇博文：Flink（60）：Flink中通用ModelUtil工具类
并且下述工具类是以华为云的DWS数据库为案例编写，其他不同的数据库（比如Doris、MySQL等），可以根据实际情况具体修改SQL的拼接语法
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.google.common.base.CaseFormat;
import com.google.common.collect.Lists;
import com.zaxxer.hikari.HikariDataSource;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import java.util.*;
/**
* @date: 2021/6/9
* @author: yangshibiao
* @desc: GaussDB中DWS服务的工具类
*/
public class GaussDBUtil {
public static Logger logger = LoggerFactory.getLogger(GaussDBUtil.class);
/**
* jdbcTemplate
*/
private static JdbcTemplate jdbcTemplate;
/**
* 使用单例模式获取实时数仓的GaussDB的jdbcTemplate
*
* @return jdbcTemplate
*/
public static JdbcTemplate getJdbcTemplate() {
if (jdbcTemplate == null) {
synchronized (GaussDBUtil.class) {
if (jdbcTemplate == null) {
try {
Properties props = new Properties();
props.put("batchMode", "OFF");
HikariDataSource ds = new HikariDataSource();
ds.setDriverClassName("org.postgresql.Driver");
ds.setJdbcUrl(ModelUtil.getConfigValue("gaussdb.realtime.dw.url"));
ds.setUsername(ModelUtil.getConfigValue("gaussdb.realtime.dw.username"));
ds.setPassword(ModelUtil.getConfigValue("gaussdb.realtime.dw.password"));
ds.setMaximumPoolSize(10);
ds.setMinimumIdle(2);
ds.setDataSourceProperties(props);
jdbcTemplate = new JdbcTemplate(ds);
logger.info("使用HikariPool连接池初始化JdbcTemplate成功，其中最大连接大小为：{} , 最小连接大小为：{} ;", ds.getMaximumPoolSize(), ds.getMinimumIdle());
} catch (Exception e) {
e.printStackTrace();
throw new RuntimeException("创建GaussDB数据库的jdbcTemplate失败，抛出的异常信息为：" + e.getMessage());
}
}
}
}
return jdbcTemplate;
}
/**
* 处理传入数据中的特殊字符（例如： 单引号）
*
* @param object 传入的数据对象
* @return 返回的结果
*/
public static String disposeSpecialCharacter(Object object) {
String result = null;
if (object instanceof String) {
result = object.toString();
} else {
result = JSON.parseObject(JSON.toJSONString(object)).toString();
}
return result.replace("'", "''");
}
/**
* 如果传入的clz中的属性又包含对象，会报错，此时传入JSONObject对象即可
*
* @param sql 执行的查询语句
* @param clz 返回的数据类型
* @param underScoreToCamel 是否将下划线转换为驼峰命名法
* @param <T> 样例类
* @return 样例类集合
*/
public static <T> List<T> queryList(String sql, Class<T> clz, boolean underScoreToCamel) {
try {
List<Map<String, Object>> mapList = GaussDBUtil.getJdbcTemplate().queryForList(sql);
List<T> resultList = new ArrayList<>();
for (Map<String, Object> map : mapList) {
Set<String> keys = map.keySet();
// 当返回的结果中存在数据，通过反射将数据封装成样例类对象
T result = clz.newInstance();
for (String key : keys) {
String propertyName = underScoreToCamel ? CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, key) : key;
BeanUtils.setProperty(
result,
propertyName,
map.get(key)
);
}
resultList.add(result);
}
return resultList;
} catch (Exception exception) {
exception.printStackTrace();
throw new RuntimeException(
"\r\n从GaussDB数据库中 查询 数据失败，" +
"\r\n抛出的异常信息为：" + exception.getMessage() +
"\r\n查询的SQL为：" + sql
);
}
}
/**
* 将传入的数据插入到对应的GaussDB的表中
*
* @param tableName 表名
* @param underScoreToCamel 是否将驼峰转换为下划线
* @param object 数据对象
* INSERT INTO customer_t1 (c_customer_sk, c_first_name) VALUES (3769, 'Grace');
*/
public static void insert(String tableName, boolean underScoreToCamel, Object object) {
// 将传入的对象转换成JSONObject格式（并将其中的特殊字符进行替换）
JSONObject data = JSON.parseObject(GaussDBUtil.disposeSpecialCharacter(object));
// 从传入的数据中获取出对应的key和value，因为要一一对应，所以使用list
ArrayList<String> fieldList = Lists.newArrayList(data.keySet());
ArrayList<String> valueList = new ArrayList<>();
for (String field : fieldList) {
valueList.add(data.getString(field));
}
// 拼接SQL
StringBuilder sql = new StringBuilder();
sql.append(" INSERT INTO ").append(tableName);
sql.append(" ( ");
for (String field : fieldList) {
if (underScoreToCamel) {
sql.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, field)).append(",");
} else {
sql.append(field).append(",");
}
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" ) ");
sql.append(" values ('").append(StringUtils.join(valueList, "','")).append("')");
// 执行插入操作
try {
GaussDBUtil.getJdbcTemplate().execute(sql.toString());
} catch (Exception exception) {
exception.printStackTrace();
throw new RuntimeException(
"\r\n向GaussDB数据库中 插入 数据失败，" +
"\r\n抛出的异常信息为：" + exception.getMessage() +
"\r\n执行的SQL为：" + sql
);
}
}
/**
* 根据主键删除对应数据
* 注意：传入的字段名要和数据库中一一匹配，即数据库中有下划线，那传入的字段名也要有下划线
*
* @param tableName 表名
* @param fieldNameAndValue 更新时匹配的字段（key）和值（value）（注意：传入的字段名要和数据库中一一匹配，即数据库中有下划线，那传入的字段名也要有下划线）
* @return 删除时影响的条数
*/
public static int delete(String tableName, Map<String, Object> fieldNameAndValue) {
// 拼接SQL
StringBuilder sql = new StringBuilder();
sql.append(" delete from ").append(tableName);
if (fieldNameAndValue.size() > 0) {
sql.append(" WHERE ");
for (Map.Entry<String, Object> fieldNameAndValueEntry : fieldNameAndValue.entrySet()) {
sql
.append(fieldNameAndValueEntry.getKey())
.append(" = ")
.append("'")
.append(GaussDBUtil.disposeSpecialCharacter(fieldNameAndValueEntry.getValue()))
.append("'")
.append(" AND ");
}
sql.delete(sql.length() - 4, sql.length() - 1);
} else {
throw new RuntimeException("从GaussDB中删除数据异常，输入的删除条件没有指定字段名和对应的值，会进行全表删除， 拼接的SQL为：" + sql);
}
// 执行删除操作
try {
return GaussDBUtil.getJdbcTemplate().update(sql.toString());
} catch (Exception exception) {
exception.printStackTrace();
throw new RuntimeException(
"\r\n向GaussDB数据库中 删除 数据失败，" +
"\r\n抛出的异常信息为：" + exception.getMessage() +
"\r\n执行的SQL为：" + sql
);
}
}
/**
* 根据传入的表名、数据、字段名，删除表中对应的数据
*
* @param tableName 表名
* @param underScoreToCamel 是否将驼峰转换为下划线
* @param object 数据对象
* @param fields 更新时匹配的字段名
*/
public static int delete(String tableName, boolean underScoreToCamel, Object object, String... fields) {
// 将传入的对象转换成JSONObject格式
JSONObject data = JSON.parseObject(GaussDBUtil.disposeSpecialCharacter(object));
// 根据传入的字段，获取要更新的主键值
HashMap<String, Object> fieldNameAndValue = new HashMap<>();
for (String field : fields) {
if (underScoreToCamel) {
// data中的均为驼峰，获取数据时需要使用驼峰；但是将数据写入到fieldNameAndValue中时，需要全部转换成下划线
fieldNameAndValue.put(
field.contains("_") ? field : CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, field),
data.getString(field.contains("_") ? CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, field) : field)
);
} else {
// data中均为下划线，field中也是下划线
fieldNameAndValue.put(field, data.getString(field));
}
}
// 调用重载函数，删除数据
return GaussDBUtil.delete(tableName, fieldNameAndValue);
}
/**
* 将传入的数据 更新 到对应的GaussDB的表中
*
* @param tableName 表名
* @param underScoreToCamel 是否将驼峰转换为下划线
* @param object 数据对象（既可以包含更新的主键，也可以不包含）
* @param fieldNameAndValue 更新时匹配的字段和对应的值
* @return 返回更新的条数
*/
public static int update(String tableName, boolean underScoreToCamel, Object object, Map<String, Object> fieldNameAndValue) {
// 将传入的对象转换成JSONObject格式，并判断输入的数据是否符合更新条件
JSONObject data = JSON.parseObject(GaussDBUtil.disposeSpecialCharacter(object));
if (fieldNameAndValue == null || fieldNameAndValue.size() == 0) {
throw new RuntimeException("向GaussDB中更新数据异常，输入的更新条件没有指定数据，不能更新（这样更新会全表更新），传入的数据为：" + data);
}
// 拼接SQL
StringBuilder sql = new StringBuilder();
sql.append(" UPDATE ").append(tableName);
sql.append(" SET ");
if (underScoreToCamel) {
// 删除传入对象中要更新的数据
for (String key : fieldNameAndValue.keySet()) {
data.remove(key.contains("_") ? CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, key) : key);
}
// 拼接要更新的结果值
for (Map.Entry<String, Object> entry : data.entrySet()) {
sql
.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, entry.getKey()))
.append(" = ")
.append("'")
.append(entry.getValue())
.append("'")
.append(",");
}
sql.deleteCharAt(sql.length() - 1);
// 拼接判断条件
sql.append(" WHERE ");
for (Map.Entry<String, Object> fieldNameAndValueEntry : fieldNameAndValue.entrySet()) {
String key = fieldNameAndValueEntry.getKey();
Object value = fieldNameAndValueEntry.getValue();
sql
.append(key.contains("_") ? key : CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, key))
.append(" = ")
.append("'")
.append(value)
.append("'")
.append(" AND ");
}
} else {
// 删除传入对象中要更新的数据
for (String key : fieldNameAndValue.keySet()) {
data.remove(key);
}
// 拼接要更新的结果值
for (Map.Entry<String, Object> entry : data.entrySet()) {
sql
.append(entry.getKey())
.append(" = ")
.append("'")
.append(entry.getValue())
.append("'")
.append(",");
}
sql.deleteCharAt(sql.length() - 1);
// 拼接判断条件
sql.append(" WHERE ");
for (Map.Entry<String, Object> fieldNameAndValueEntry : fieldNameAndValue.entrySet()) {
String key = fieldNameAndValueEntry.getKey();
Object value = fieldNameAndValueEntry.getValue();
sql
.append(key)
.append(" = ")
.append("'")
.append(value)
.append("'")
.append(" AND ");
}
}
sql.delete(sql.length() - 4, sql.length() - 1);
// 执行更新操作
try {
return GaussDBUtil.getJdbcTemplate().update(sql.toString());
} catch (Exception exception) {
exception.printStackTrace();
throw new RuntimeException(
"\r\n向GaussDB数据库中 更新 数据失败，" +
"\r\n抛出的异常信息为：" + exception.getMessage() +
"\r\n执行的SQL为：" + sql
);
}
}
/**
* 将传入的数据 更新 到对应的GaussDB的表中
*
* @param tableName 表名
* @param underScoreToCamel 是否将驼峰转换为下划线
* @param object 数据对象
* @param fields 更新时匹配的字段名（如果underScoreToCamel为true，传入的字段为驼峰，如果underScoreToCamel为false，传入的字段为下划线）
* @return 返回更新的条数
*/
public static int update(String tableName, boolean underScoreToCamel, Object object, String... fields) {
// 将传入的对象转换成JSONObject格式
JSONObject data = JSON.parseObject(GaussDBUtil.disposeSpecialCharacter(object));
// 根据传入的字段，获取要更新的主键值
HashMap<String, Object> fieldNameAndValue = new HashMap<>();
for (String field : fields) {
if (underScoreToCamel) {
field = field.contains("_") ? CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, field) : field;
}
fieldNameAndValue.put(field, data.getString(field));
}
// 调用重载函数，更新数据
return GaussDBUtil.update(tableName, underScoreToCamel, object, fieldNameAndValue);
}
/**
* 将传入的数据 upsert 到对应的GaussDB的表中
*
* @param tableName 表名
* @param underScoreToCamel 是否将驼峰转换为下划线
* @param object 数据对象
* @param fields 更新时匹配的字段名（如果underScoreToCamel为true，传入的字段为驼峰，如果underScoreToCamel为false，传入的字段为下划线）
* @return 返回更改的条数
*/
public static int upsert(String tableName, boolean underScoreToCamel, Object object, String... fields) {
// 将传入的对象转换成JSONObject格式
JSONObject data = JSON.parseObject(GaussDBUtil.disposeSpecialCharacter(object));
// 根据传入的字段，获取要更新的主键值
HashMap<String, Object> fieldNameAndValue = new HashMap<>();
for (String field : fields) {
if (underScoreToCamel) {
field = field.contains("_") ? CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, field) : field;
}
fieldNameAndValue.put(field, data.getString(field));
}
// 调用重载函数，更新数据
return GaussDBUtil.upsert(tableName, underScoreToCamel, object, fieldNameAndValue);
}
/**
* 将传入的数据 upsert 到对应的GaussDB的表中
* <p>
* dws中的upsert语法：
* INSERT INTO test.reason_t1 (r_reason_desc, r_reason_sk, r_reason_id) values ('$$$$$$$$$$$$', '4', '444444444') on conflict ( r_reason_sk ) do update set r_reason_desc = EXCLUDED.r_reason_desc, r_reason_id = EXCLUDED.r_reason_id
*
* @param tableName 表名
* @param underScoreToCamel 是否将驼峰转换为下划线
* @param object 数据对象
* @param fieldNameAndValue 更新时匹配的字段名（如果underScoreToCamel为true，传入的字段为驼峰，如果underScoreToCamel为false，传入的字段为下划线）
* @return 返回更改的条数
*/
public static int upsert(String tableName, boolean underScoreToCamel, Object object, Map<String, Object> fieldNameAndValue) {
// 将传入的对象转换成JSONObject格式，并判断输入的数据是否符合更新条件
JSONObject data = JSON.parseObject(GaussDBUtil.disposeSpecialCharacter(object));
if (fieldNameAndValue == null || fieldNameAndValue.size() == 0) {
throw new RuntimeException("向GaussDB中更新数据异常，输入的更新条件没有指定数据，不能更新（这样更新会全表更新），传入的数据为：" + data);
}
// 将传入的更新匹配字段和值（即fieldNameAndValue），添加到数据对象中（即data）
for (Map.Entry<String, Object> entry : fieldNameAndValue.entrySet()) {
data.put(entry.getKey(), entry.getValue());
}
data = JSON.parseObject(GaussDBUtil.disposeSpecialCharacter(data));
// 根据所有数据（data）和需要更新的数据（fieldNameAndValue），求出更新的字段名和被更新的字段名
Set<String> updateKey = fieldNameAndValue.keySet();
Set<String> beUpdateKey = new TreeSet<>();
for (String key : data.keySet()) {
if (!updateKey.contains(key)) {
beUpdateKey.add(key);
}
}
// 拼接SQL
StringBuilder sql = new StringBuilder();
sql.append(" INSERT INTO ").append(tableName);
if (underScoreToCamel) {
sql.append(" ( ");
for (String key : data.keySet()) {
sql.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, key)).append(",");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" ) ");
sql.append(" values ");
sql.append(" ( ");
for (Object value : data.values()) {
sql
.append("'")
.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, value.toString()))
.append("'")
.append(",");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" ) ");
sql.append(" on conflict ");
sql.append(" ( ");
for (String key : updateKey) {
sql.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, key)).append(",");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" ) ");
sql.append(" do update set ");
for (String key : beUpdateKey) {
sql
.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, key))
.append(" = EXCLUDED.")
.append(CaseFormat.LOWER_CAMEL.to(CaseFormat.LOWER_UNDERSCORE, key))
.append(",");
}
sql.deleteCharAt(sql.length() - 1);
} else {
sql.append(" ( ");
for (String key : data.keySet()) {
sql.append(key).append(",");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" ) ");
sql.append(" values ");
sql.append(" ( ");
for (Object value : data.values()) {
sql
.append("'")
.append(value.toString())
.append("'")
.append(",");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" ) ");
sql.append(" on conflict ");
sql.append(" ( ");
for (String key : updateKey) {
sql.append(key).append(",");
}
sql.deleteCharAt(sql.length() - 1);
sql.append(" ) ");
sql.append(" do update set ");
for (String key : beUpdateKey) {
sql
.append(key)
.append(" = EXCLUDED.")
.append(key)
.append(",");
}
sql.deleteCharAt(sql.length() - 1);
}
// 执行upsert操作
try {
return GaussDBUtil.getJdbcTemplate().update(sql.toString());
} catch (Exception exception) {
exception.printStackTrace();
throw new RuntimeException(
"\r\n向GaussDB数据库中 upsert 数据失败，" +
"\r\n抛出的异常信息为：" + exception.getMessage() +
"\r\n执行的SQL为：" + sql
);
}
}
}
4. Hikari连接池各配置说明
# Hikari will use the above plus the following to setup connection pooling
spring.datasource.type=com.zaxxer.hikari.HikariDataSource
#最小空闲连接，默认值10，小于0或大于maximum-pool-size，都会重置为maximum-pool-size
spring.datasource.hikari.minimum-idle=5
#最大连接数，小于等于0会被重置为默认值10；大于零小于1会被重置为minimum-idle的值
spring.datasource.hikari.maximum-pool-size=15
#自动提交从池中返回的连接，默认值为true
spring.datasource.hikari.auto-commit=true
#空闲连接超时时间，默认值600000（10分钟），大于等于max-lifetime且max-lifetime>0，会被重置为0；不等于0且小于10秒，会被重置为10秒。
#只有空闲连接数大于最大连接数且空闲时间超过该值，才会被释放
spring.datasource.hikari.idle-timeout=30000
#连接池名称，默认HikariPool-1
spring.datasource.hikari.pool-name=Hikari
#连接最大存活时间.不等于0且小于30秒，会被重置为默认值30分钟.设置应该比mysql设置的超时时间短；单位ms
spring.datasource.hikari.max-lifetime=55000
#连接超时时间:毫秒，小于250毫秒，会被重置为默认值30秒
spring.datasource.hikari.connection-timeout=30000
#连接测试查询
spring.datasource.hikari.connection-test-query=SELECT 1注：其他相关文章链接由此进 -> 开发随笔文章汇总
扫一扫
817
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
