com.huaweicloud.dws.client.util.JdbcUtil Maven / Gradle / Ivy
package com.huaweicloud.dws.client.util;
import com.huawei.gauss200.jdbc.copy.CopyManager;
import com.huawei.gauss200.jdbc.core.BaseConnection;
import com.huawei.shade.com.alibaba.fastjson.JSONObject;
import com.huawei.shade.com.alibaba.fastjson.serializer.SerializerFeature;
import com.huaweicloud.dws.client.DwsConfig;
import com.huaweicloud.dws.client.TableConfig;
import com.huaweicloud.dws.client.exception.DwsClientException;
import com.huaweicloud.dws.client.exception.ExceptionCode;
import com.huaweicloud.dws.client.model.Column;
import com.huaweicloud.dws.client.model.ConflictStrategy;
import com.huaweicloud.dws.client.model.CopyMode;
import com.huaweicloud.dws.client.model.Record;
import com.huaweicloud.dws.client.model.TableName;
import com.huaweicloud.dws.client.model.TableSchema;
import com.huaweicloud.dws.client.types.MySqlTypeUtils;
import lombok.extern.slf4j.Slf4j;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.sql.Types;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.BitSet;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.StringJoiner;
import java.util.stream.Collectors;
/**
* @ProjectName: dws-connector
* @ClassName: JdbcUtil
* @Description: jdbc操作工具类
* @Date: 2022/12/22 14:24
* @Version: 1.0
*/
@Slf4j
public class JdbcUtil {
public static final char EOL_CHAR = 0x1F;
public static final String EOL = String.valueOf(EOL_CHAR);
public static final char DELIMITER_CHAR = 0x1E;
public static final String DELIMITER = String.valueOf(DELIMITER_CHAR);
public static TableSchema getTableSchema(Connection conn, TableName tableName) throws DwsClientException {
return getTableSchema(conn, tableName, null, false);
}
/**
* 根据表名获取表结构定义
*/
public static TableSchema getTableSchema(Connection conn, TableName tableName, List uniqueKeys, boolean caseSensitive) throws DwsClientException {
// 如果用户制定了唯一索引那么使用唯一索引替代主键，否则获取表中主键
Set primaryKeys = new HashSet<>();
if (uniqueKeys != null) {
uniqueKeys.forEach(s -> primaryKeys.add(IdentifierUtil.toLowerIfNoSensitive(s, caseSensitive)));
} else {
try (ResultSet rs = conn.getMetaData().getPrimaryKeys(null, tableName.getSchemaName(), tableName.getTableName())) {
while (rs.next()) {
primaryKeys.add(rs.getString(4));
}
} catch (Exception e) {
throw DwsClientException.fromException(e);
}
}
// 获取所有列
List columnList = new ArrayList<>();
try (ResultSet rs = conn.getMetaData().getColumns(null, tableName.getSchemaName(), tableName.getTableName(), "%")) {
while (rs.next()) {
Column column = new Column();
column.setName(rs.getString(4));
column.setType(rs.getInt(5));
column.setTypeName(rs.getString(6));
column.setPrecision(rs.getInt(7));
column.setScale(rs.getInt(9));
column.setAllowNull(rs.getInt(11) == 1);
column.setComment(rs.getString(12));
column.setDefaultValue(rs.getObject(13));
column.setPrimaryKey(primaryKeys.contains(IdentifierUtil.toLowerIfNoSensitive(column.getName(), caseSensitive)));
columnList.add(column);
}
} catch (Exception e) {
throw DwsClientException.fromException(e);
}
AssertUtil.notEmpty(columnList, new DwsClientException(ExceptionCode.TABLE_NOT_FOUND, String.format("table name %s does not exist", tableName.getFullName())));
return new TableSchema(tableName, columnList, caseSensitive);
}
/**
* 获取upsert sql模板
*/
public static String getUpsertStatement(String tableName, List insertFieldNames, List updateFieldNames, List uniqueKeyFields, ConflictStrategy strategy) {
// 没有主键时获取insert sql
if (uniqueKeyFields.isEmpty()) {
return getInsertIntoStatement(tableName, insertFieldNames);
}
String uniqueColumns = uniqueKeyFields.stream()
.map(IdentifierUtil::quoteIdentifier)
.collect(Collectors.joining(", "));
String conflictSql = " DO NOTHING";
if (strategy != ConflictStrategy.INSERT_OR_IGNORE) {
String updateClause =
updateFieldNames.stream()
.filter(c -> LocalUtil.getTableConfig().isUpdateAll() || !uniqueKeyFields.contains(c))
.map(f -> IdentifierUtil.quoteIdentifier(f) + "=EXCLUDED." + IdentifierUtil.quoteIdentifier(f))
.collect(Collectors.joining(", "));
conflictSql = String.format(" DO UPDATE SET %s ", updateClause);
}
TableConfig tableConfig = LocalUtil.getTableConfig();
Set compareField = tableConfig.getCompareField();
String whereSql = "";
if (compareField != null) {
whereSql = " WHERE " + compareField.stream()
.map(f -> IdentifierUtil.quoteIdentifier(f) + "< EXCLUDED." + IdentifierUtil.quoteIdentifier(f))
.collect(Collectors.joining(" AND "));
}
return getInsertIntoStatement(tableName, insertFieldNames)
+ " ON CONFLICT ("
+ uniqueColumns
+ ")"
+ conflictSql + whereSql;
}
/**
* 获取insert into sql
*/
public static String getInsertIntoStatement(String tableName, List fieldNames) {
String columns =
fieldNames.stream()
.map(IdentifierUtil::quoteIdentifier)
.collect(Collectors.joining(", "));
String placeholders =
fieldNames.stream().map(f -> "?").collect(Collectors.joining(", "));
return "INSERT INTO "
+ tableName
+ "("
+ columns
+ ")"
+ " VALUES ("
+ placeholders
+ ")";
}
/**
* 时间中 00的异常数据
*/
public static final String DATE_0000 = "0000-00-00 00:00:00";
/**
* Statement 设置参数
*/
public static void fillPreparedStatement(PreparedStatement ps, int index, Object obj, Column column) throws SQLException {
if (column == null) {
ps.setObject(index, obj);
return;
}
if (obj == null) {
ps.setNull(index, column.getType());
return;
}
String value = String.valueOf(obj);
switch (column.getType()) {
case Types.OTHER:
if ("varbit".equals(column.getTypeName())) {
ps.setString(index, value);
} else {
ps.setObject(index, obj, column.getType());
}
break;
case Types.LONGNVARCHAR:
case Types.VARCHAR:
case Types.CHAR:
ps.setObject(index, value, column.getType());
break;
case Types.BIT:
setBit(ps, index, obj, column, value);
break;
case Types.TIMESTAMP_WITH_TIMEZONE:
case Types.TIMESTAMP:
setTimeStamp(ps, index, obj, column);
break;
case Types.DATE:
setDate(ps, index, obj, column);
break;
case Types.TIME_WITH_TIMEZONE:
case Types.TIME:
setTime(ps, index, obj, column);
break;
default:
ps.setObject(index, obj);
}
}
public static void setBit(PreparedStatement ps, int index, Object obj, Column column, String value) throws SQLException {
if ("bit".equals(column.getTypeName())) {
if (obj instanceof Boolean) {
ps.setString(index, (Boolean) obj ? "1" : "0");
} else {
ps.setString(index, value);
}
} else {
ps.setObject(index, obj, column.getType());
}
}
/**
* 时间类型参数设置
*/
public static void setTime(PreparedStatement ps, int index, Object obj, Column column) throws SQLException {
TableConfig config = LocalUtil.getTableConfig();
if (obj instanceof Number && config.isNumberAsEpochMsForDatetime()) {
ps.setObject(index, new Time(((Number) obj).longValue()), column.getType());
} else if (obj instanceof String && config.getStringToDatetimeFormat() != null) {
try {
ps.setObject(index, new Time(getTime(obj.toString(), config.getStringToDatetimeFormat())), column.getType());
} catch (ParseException e) {
if (DATE_0000.equals(obj)) {
ps.setObject(index, new Time(0), column.getType());
} else {
ps.setObject(index, obj);
}
}
} else {
ps.setObject(index, obj);
}
}
/**
* 日期类型参数设置
*/
public static void setDate(PreparedStatement ps, int index, Object obj, Column column) throws SQLException {
TableConfig config = LocalUtil.getTableConfig();
if (obj instanceof Number && config.isNumberAsEpochMsForDatetime()) {
ps.setObject(
index,
new java.sql.Date(((Number) obj).longValue()),
column.getType());
} else if (obj instanceof String && config.getStringToDatetimeFormat() != null) {
try {
ps.setObject(index, new java.sql.Date(getTime(obj.toString(), config.getStringToDatetimeFormat())), column.getType());
} catch (ParseException e) {
if (DATE_0000.equals(obj)) {
ps.setObject(index, new java.sql.Date(0), column.getType());
} else {
ps.setObject(index, obj);
}
}
} else {
ps.setObject(index, obj);
}
}
private static long getTime(String src, String format) throws ParseException {
SimpleDateFormat dateFormat = new SimpleDateFormat(format);
return dateFormat.parse(src).getTime();
}
/**
* 处理时间戳字段
*/
public static void setTimeStamp(PreparedStatement ps, int index, Object obj, Column column) throws SQLException {
TableConfig config = LocalUtil.getTableConfig();
if (obj instanceof Number && config.isNumberAsEpochMsForDatetime()) {
ps.setObject(index, new Timestamp(((Number) obj).longValue()),
column.getType());
} else if (obj instanceof String && config.getStringToDatetimeFormat() != null) {
try {
ps.setObject(index, new Timestamp(getTime(obj.toString(), config.getStringToDatetimeFormat())), column.getType());
} catch (ParseException e) {
if (DATE_0000.equals(obj)) {
ps.setObject(index, new Timestamp(0), column.getType());
} else {
ps.setObject(index, obj);
}
}
} else {
ps.setObject(index, obj);
}
}
/**
* 获取删除SQL
*/
public static String getDeleteStatement(String tableName, List conditionFields) {
String conditionClause = conditionFields.stream()
.map(f -> String.format("%s = ?", IdentifierUtil.quoteIdentifier(f)))
.collect(Collectors.joining(" AND "));
return "DELETE FROM " + tableName + (conditionFields.isEmpty() ? "" : " WHERE " + conditionClause);
}
public static String getUpdateStatement(String tableName, List updateFieldNames, List uniqueKeyFields) {
String conditionClause = uniqueKeyFields.stream()
.map(f -> String.format("%s = ?", IdentifierUtil.quoteIdentifier(f)))
.collect(Collectors.joining(" AND "));
String setSql = updateFieldNames.stream().filter(c -> !uniqueKeyFields.contains(c)).map(f -> String.format("%s = ?", IdentifierUtil.quoteIdentifier(f)))
.collect(Collectors.joining(", "));
return "UPDATE " + tableName + " SET " + setSql + " WHERE " + conditionClause;
}
public static String getUpdateFromStatement(String tableName, String tempTableName, List updateFieldNames, List uniqueKeyFields) {
String conditionClause = uniqueKeyFields.stream()
.map(f -> String.format("o.%s = t.%s", f, f))
.collect(Collectors.joining(" AND "));
String setSql = updateFieldNames.stream().filter(c -> !uniqueKeyFields.contains(c)).map(f -> String.format("o.%s = t.%s", f, f))
.collect(Collectors.joining(", "));
return "UPDATE " + tableName + " o set " + setSql + " FROM " + tempTableName + " t WHERE " + conditionClause;
}
/**
* 获取创建临时表SQL
* @param origin 原表
* @param tempTable 临时表名称
* @return
*/
public static String getCreateTempTableSql(String origin, String tempTable) {
return "create temp table " +
tempTable +
" like " +
origin +
" including all excluding partition EXCLUDING INDEXES EXCLUDING RELOPTIONS ";
}
public static String getCreateTempTableAsSql(String origin, String tempTable) {
return "create temp table " +
tempTable +
" as select * from " +
origin +
" where 1=2 ";
}
/**
*
* 构建成copy的buffer
*/
public static String buildCopyBuffer(List records, DwsConfig config, TableConfig tableConfig) {
if (records == null || records.isEmpty()) {
return null;
}
if (tableConfig.getCopyMode() == CopyMode.DELIMITER) {
return buildStdinCopyBuffer(records, config, tableConfig.getDelimiter(), tableConfig.getEof());
}
return buildCsvCopyBuffer(records, config, tableConfig.getDelimiter());
}
public static String buildStdinCopyBuffer(List records, DwsConfig config, String delimiter, String eof) {
Record first = records.get(0);
BitSet columnBit = first.getColumnBit();
StringBuilder copyBuffer = new StringBuilder();
for (Record record : records) {
LogUtil.withLogData(config, record.getTableSchema().getTableName(),
() -> log.info("add data to copy buffer, data = {}", JSONObject.toJSONString(RecordUtil.toMap(record), SerializerFeature.WriteMapNullValue)));
columnBit.stream().forEach(idx -> {
String value = IdentifierUtil.replaceValue(String.valueOf(record.getValue(idx)));
copyBuffer.append(value);
if (!value.isEmpty() && value.charAt(value.length() - 1) == '\\') {
copyBuffer.append(" ");
}
copyBuffer.append(delimiter);
});
copyBuffer.setLength(copyBuffer.length() - 1);
copyBuffer.append(eof);
}
return copyBuffer.toString();
}
public static String buildCsvCopyBuffer(List records, DwsConfig config, String delimiter) {
Record first = records.get(0);
BitSet columnBit = first.getColumnBit();
StringBuilder copyBuffer = new StringBuilder();
for (Record record : records) {
LogUtil.withLogData(config, record.getTableSchema().getTableName(),
() -> log.info("add data to copy buffer, data = {}", JSONObject.toJSONString(RecordUtil.toMap(record), SerializerFeature.WriteMapNullValue)));
StringJoiner sj = new StringJoiner(delimiter);
columnBit.stream().forEach(idx -> {
Object objectValue = record.getValue(idx);
String value = String.valueOf(objectValue);
if (objectValue instanceof String) {
value = String.format("\"%s\"", IdentifierUtil.replaceValueForCsv(value));
}
sj.add(value);
});
copyBuffer.append(sj);
copyBuffer.append("\n");
}
return copyBuffer.toString();
}
/**
* 获取copy sql
* @return
*/
public static String getCopyFromStdinStatement(String tableName, List fieldNames, String delimiter, String eof) {
String fieldNamesString = fieldNames.stream()
.map(IdentifierUtil::quoteIdentifier)
.collect(Collectors.joining(", "));
return "COPY " +
tableName + "(" + fieldNamesString + ")" +
" FROM STDIN DELIMITER " +
"'" + delimiter + "'" +
" ENCODING" +
" 'UTF8' NULL 'null'" +
" eol" +
" '" + eof + "'";
}
public static String getCopyFromStdinStatement(String tableName, List fieldNames, TableConfig tableConfig) {
CopyMode copyMode = tableConfig.getCopyMode();
if (copyMode == CopyMode.DELIMITER) {
return getCopyFromStdinStatement(tableName, fieldNames, tableConfig.getDelimiter(), tableConfig.getEof());
}
return getCopyFromStdinAsCsvStatement(tableName, fieldNames, tableConfig.getDelimiter());
}
public static String getCopyFromStdinAsCsvStatement(String tableName, List fieldNames, String delimiter) {
String fieldNamesString = fieldNames.stream()
.map(IdentifierUtil::quoteIdentifier)
.collect(Collectors.joining(", "));
return "COPY " +
tableName + "(" + fieldNamesString + ")" +
" FROM STDIN with(format 'csv', delimiter '" + delimiter + "', null 'null') ";
}
public static void executeCopy(BaseConnection connection, String sql, String copyBuffer) throws Exception {
CopyManager perform = new CopyManager(connection);
try (StringReader reader = new StringReader(copyBuffer)) {
perform.copyIn(sql, reader);
}
}
/**
* 获取mergeSQL
*/
public static String getMergeIntoSql(String table, String fromTable, List pkNames, List updateColumns, List insertColumns, ConflictStrategy strategy) {
String uniqueColumns = pkNames.stream().map(item -> String.format("o.%s = t.%s ", item, item)).collect(Collectors.joining(" AND "));
String updateSets = updateColumns.stream().map(item -> String.format("o.%s = t.%s ", item, item)).collect(Collectors.joining(", "));
String insertFields = insertColumns.stream().map(IdentifierUtil::quoteIdentifier).collect(Collectors.joining(", "));
String insertValues = insertColumns.stream().map(key -> String.format("t.%s", key)).collect(Collectors.joining(", "));
StringBuilder mergeSql = new StringBuilder();
mergeSql.append("MERGE INTO ").append(table).append(" o \n");
mergeSql.append("USING ").append(fromTable).append(" t ");
mergeSql.append("ON (").append(uniqueColumns).append(") \n");
mergeSql.append("WHEN MATCHED THEN \n");
mergeSql.append("UPDATE SET ").append(updateSets);
TableConfig config = LocalUtil.getTableConfig();
Set compareField = config.getCompareField();
if (strategy == ConflictStrategy.INSERT_OR_IGNORE) {
mergeSql.append(" WHERE 1 > 1 ");
} else if (compareField != null) {
mergeSql.append(" WHERE ");
String whereSql = compareField.stream().map(item -> String.format("o.%s < t.%s ", item, item)).collect(Collectors.joining(" AND "));
mergeSql.append(whereSql);
}
mergeSql.append("WHEN NOT MATCHED THEN \n");
mergeSql.append("INSERT (").append(insertFields).append(") VALUES (");
mergeSql.append(insertValues).append(");");
return mergeSql.toString();
}
/**
* 获取从源表upsert sql
*/
public static String getUpsertFromSql(String table, String fromTable, List pkNames, List updateColumns, List insertColumns, ConflictStrategy strategy) {
String uniqueColumns = pkNames.stream().map(IdentifierUtil::quoteIdentifier).collect(Collectors.joining(", "));
String insertFields = insertColumns.stream().map(IdentifierUtil::quoteIdentifier).collect(Collectors.joining(", "));
StringBuilder upsertFromSql = new StringBuilder();
upsertFromSql.append("INSERT INTO ")
.append(table).append("(")
.append(insertFields)
.append(")")
.append(" SELECT ")
.append(insertFields)
.append(" FROM ")
.append(fromTable);
String conflictSql = " DO NOTHING";
TableConfig config = LocalUtil.getTableConfig();
Set compareField = config.getCompareField();
String whereSql = "";
if (strategy != ConflictStrategy.INSERT_OR_IGNORE) {
String updateClause =
updateColumns.stream()
.map(f -> IdentifierUtil.quoteIdentifier(f) + "=EXCLUDED." + IdentifierUtil.quoteIdentifier(f))
.collect(Collectors.joining(", "));
if (!updateClause.isEmpty()) {
conflictSql = String.format(" DO UPDATE SET %s ", updateClause);
}
if (compareField != null) {
whereSql = " WHERE " + compareField.stream()
.map(f -> IdentifierUtil.quoteIdentifier(f) + "< EXCLUDED." + IdentifierUtil.quoteIdentifier(f))
.collect(Collectors.joining(" AND "));
}
}
upsertFromSql.append(" ON CONFLICT ( ")
.append(uniqueColumns).append(")")
.append(conflictSql)
.append(whereSql);
return upsertFromSql.toString();
}
public static String getSelectFromStatement(
String tableName, List selectFields, List conditionFields) {
String selectExpressions =
selectFields.stream()
.map(IdentifierUtil::quoteIdentifier)
.collect(Collectors.joining(", "));
String fieldExpressions =
conditionFields.stream()
.map(f -> String.format("%s = ?", IdentifierUtil.quoteIdentifier(f)))
.collect(Collectors.joining(" AND "));
return "SELECT "
+ selectExpressions
+ " FROM "
+ tableName
+ (conditionFields.size() > 0 ? " WHERE " + fieldExpressions : "");
}
public static void executeBatchRecordSql(List records, Connection connection, TableSchema schema, List keys, String sql, DwsConfig config) throws SQLException {
if (records == null || records.isEmpty()) {
return;
}
LogUtil.withLogSwitch(config, () -> log.info("execute batch, type = {}", records.get(0).getType()));
long starAddBatch = System.currentTimeMillis();
List columnIndexList = new ArrayList<>(keys.size());
for (String key : keys) {
columnIndexList.add(new ColumnIndex(schema.getColumn(key), schema.getColumnIndex(key)));
}
try (PreparedStatement statement = connection.prepareStatement(sql)) {
for (Record record : records) {
for (int i = 0; i < columnIndexList.size(); i++) {
ColumnIndex columnIndex = columnIndexList.get(i);
JdbcUtil.fillPreparedStatement(statement, i + 1, record.getValue(columnIndex.getIndex()), columnIndex.getColumn());
}
statement.addBatch();
}
log.debug("add batch time = {}", System.currentTimeMillis() - starAddBatch);
LogUtil.withLogSwitch(config, () -> log.info("add batch time = {}, sql = {}", System.currentTimeMillis() - starAddBatch, sql));
long starExecute = System.currentTimeMillis();
statement.executeBatch();
log.debug("execute batch time = {}", System.currentTimeMillis() - starExecute);
LogUtil.withLogSwitch(config, () -> log.info("execute batch = {}", System.currentTimeMillis() - starExecute));
}
}
public static void executeSql(Connection connection, String sql) throws SQLException {
try (Statement statement = connection.createStatement()) {
statement.execute(sql);
}
}
public static String getAddColumn(Map schema, String column, String table) {
// 执行加字段SQL
StringBuilder sql = new StringBuilder(String.format("ALTER TABLE IF EXISTS %s ", table));
StringJoiner columnsJoin = new StringJoiner(",");
columnsJoin.add(String.format("ADD COLUMN \"%s\" %s", column, MySqlTypeUtils.toDataType(schema.get(column)).asSQL()));
sql.append(columnsJoin);
log.info("add column sql: {}", sql);
return sql.toString();
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy