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.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.Record;
import com.huaweicloud.dws.client.model.TableName;
import com.huaweicloud.dws.client.model.TableSchema;
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.util.ArrayList;
import java.util.BitSet;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;
import static java.lang.String.format;
/**
* @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);
}
/**
* 根据表名获取表结构定义
*/
public static TableSchema getTableSchema(Connection conn, TableName tableName, List uniqueKeys) throws DwsClientException {
// 如果用户制定了唯一索引那么使用唯一索引替代主键，否则获取表中主键
Set primaryKeys = new HashSet<>();
if (uniqueKeys != null) {
primaryKeys.addAll(uniqueKeys);
} 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(column.getName()));
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);
}
/**
* 获取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 -> !uniqueKeyFields.contains(c))
.map(f -> IdentifierUtil.quoteIdentifier(f) + "=EXCLUDED." + IdentifierUtil.quoteIdentifier(f))
.collect(Collectors.joining(", "));
conflictSql = String.format(" DO UPDATE SET %s ", updateClause);
}
return getInsertIntoStatement(tableName, insertFieldNames)
+ " ON CONFLICT ("
+ uniqueColumns
+ ")"
+ conflictSql;
}
/**
* 获取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, column.getType());
}
}
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 {
if (obj instanceof Number) {
ps.setObject(index, new Time(((Number) obj).longValue()), column.getType());
} else if (obj instanceof String) {
try {
ps.setObject(index, new Time(Long.parseLong((String) obj)), column.getType());
} catch (NumberFormatException e) {
if (DATE_0000.equals(obj)) {
ps.setObject(index, new Time(0), column.getType());
} else {
ps.setObject(index, obj, column.getType());
}
}
} else {
ps.setObject(index, obj, column.getType());
}
}
/**
* 日期类型参数设置
*/
public static void setDate(PreparedStatement ps, int index, Object obj, Column column) throws SQLException {
if (obj instanceof Number) {
ps.setObject(
index,
new java.sql.Date(((Number) obj).longValue()),
column.getType());
} else if (obj instanceof String) {
try {
ps.setObject(index, new java.sql.Date(Long.parseLong((String) obj)), column.getType());
} catch (NumberFormatException e) {
if (DATE_0000.equals(obj)) {
ps.setObject(index, new java.sql.Date(0), column.getType());
} else {
ps.setObject(index, obj, column.getType());
}
}
} else {
ps.setObject(index, obj, column.getType());
}
}
/**
* 处理时间戳字段
*/
public static void setTimeStamp(PreparedStatement ps, int index, Object obj, Column column) throws SQLException {
if (obj instanceof Number) {
ps.setObject(index, new Timestamp(((Number) obj).longValue()),
column.getType());
} else if (obj instanceof String) {
try {
ps.setObject(index, new Timestamp(Long.parseLong((String) obj)), column.getType());
} catch (NumberFormatException e) {
if (DATE_0000.equals(obj)) {
ps.setObject(index, new Timestamp(0), column.getType());
} else {
ps.setObject(index, obj, column.getType());
}
}
} else {
ps.setObject(index, obj, column.getType());
}
}
/**
* 获取删除SQL
*/
public static String getDeleteStatement(String tableName, List conditionFields) {
String conditionClause = conditionFields.stream()
.map(f -> 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 -> format("%s = ?", IdentifierUtil.quoteIdentifier(f)))
.collect(Collectors.joining(" AND "));
String setSql = updateFieldNames.stream().filter(c -> !uniqueKeyFields.contains(c)).map(f -> 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 -> format("o.%s = t.%s", f, f))
.collect(Collectors.joining(" AND "));
String setSql = updateFieldNames.stream().filter(c -> !uniqueKeyFields.contains(c)).map(f -> 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 ";
}
/**
*
* 构建成copy的buffer
*/
public static String buildCopyBuffer(List records, DwsConfig config) {
if (records == null || records.isEmpty()) {
return null;
}
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(EOL);
}
return copyBuffer.toString();
}
/**
* 获取copy sql
* @return
*/
public static String getCopyFromStdinStatement(String tableName, List fieldNames) {
String fieldNamesString = fieldNames.stream()
.map(IdentifierUtil::quoteIdentifier)
.collect(Collectors.joining(", "));
return "COPY " +
tableName + "(" + fieldNamesString + ")" +
" FROM STDIN DELIMITER " +
"'" + DELIMITER + "'" +
" ENCODING" +
" 'UTF8' NULL 'null'" +
" eol" +
" '" + EOL + "'";
}
public static long executeCopy(BaseConnection connection, String sql, String copyBuffer) throws Exception {
CopyManager perform = new CopyManager(connection);
StringReader reader = new StringReader(copyBuffer);
long result = perform.copyIn(sql, reader);
reader.close();
return result;
}
/**
* 获取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);
if (strategy == ConflictStrategy.INSERT_OR_IGNORE) {
mergeSql.append(" WHERE 1 > 1 ");
}
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";
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);
}
}
upsertFromSql.append(" ON CONFLICT ( ")
.append(uniqueColumns).append(")")
.append(conflictSql);
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 -> 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()));
PreparedStatement statement = connection.prepareStatement(sql);
long starAddBatch = System.currentTimeMillis();
for (Record record : records) {
LogUtil.withLogData(config, schema.getTableName(), () -> log.info("add batch data = {}", RecordUtil.toMap(record)));
for (int i = 0; i < keys.size(); i++) {
String field = keys.get(i);
JdbcUtil.fillPreparedStatement(statement, i + 1, record.getValue(field), schema.getColumn(field));
}
statement.addBatch();
}
LogUtil.withLogSwitch(config, () -> log.info("add batch time = {}, sql = {}", System.currentTimeMillis() - starAddBatch, sql));
long starExecute = System.currentTimeMillis();
statement.executeBatch();
LogUtil.withLogSwitch(config, () -> log.info("execute batch = {}", System.currentTimeMillis() - starExecute));
statement.close();
}
public static void executeSql(Connection connection, String sql) throws SQLException {
try (Statement statement = connection.createStatement()) {
statement.execute(sql);
}
}
}
© 2015 - 2025 Weber Informatics LLC | Privacy Policy