package cn.net.communion.dbdatasync.dbhelper.impl;
|
|
import cn.net.communion.dbdatasync.dbhelper.DbHelper;
|
import cn.net.communion.dbdatasync.entity.JobInfo;
|
import org.apache.log4j.Logger;
|
|
import java.sql.Connection;
|
import java.sql.PreparedStatement;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.List;
|
|
public class PostgreSql implements DbHelper {
|
private Logger logger = Logger.getLogger(PostgreSql.class);
|
|
@Override
|
public String assembleSQL(String srcSql, Connection conn, JobInfo jobInfo) throws SQLException {
|
String[] fields = jobInfo.getDestTableFields().split(",");
|
String destTable = jobInfo.getDestTable();
|
|
PreparedStatement pst = conn.prepareStatement(srcSql);
|
ResultSet rs = pst.executeQuery();
|
StringBuilder sql = new StringBuilder();
|
sql.append("INSERT INTO ").append(destTable).append(" (").append(jobInfo.getDestTableFields()).append(") VALUES ");
|
|
long count = 0;
|
while (rs.next()) {
|
sql.append("(");
|
for (int index = 0; index < fields.length; index++) {
|
String value = rs.getString(fields[index]);
|
if (value == null) {
|
sql.append("NULL"); // 显式处理 NULL 值
|
} else {
|
sql.append("'").append(value).append("'"); // 字符串值加引号
|
}
|
if (index < fields.length - 1) {
|
sql.append(", ");
|
}
|
}
|
sql.append("),");
|
count++;
|
}
|
|
if (rs != null) {
|
rs.close();
|
}
|
if (pst != null) {
|
pst.close();
|
}
|
|
if (count > 0) {
|
sql = new StringBuilder(sql.substring(0, sql.length() - 1)); // 删除最后一个逗号
|
return sql.toString();
|
}
|
return null;
|
}
|
|
|
|
@Override
|
public void executeSQL(String sql, Connection conn) throws SQLException {
|
logger.debug("Executing SQL: " + sql);
|
try (PreparedStatement pst = conn.prepareStatement(sql)) {
|
pst.execute();
|
conn.commit();
|
}
|
}
|
|
// 辅助方法：设置参数
|
private void setParameters(PreparedStatement pst, List<Object> params) throws SQLException {
|
for (int i = 0; i < params.size(); i++) {
|
pst.setObject(i + 1, params.get(i));
|
}
|
}
|
|
// 辅助方法：序列化 SQL 和参数
|
private String serializeSqlWithParams(List<String> sqlStatements, List<List<Object>> paramLists) {
|
StringBuilder result = new StringBuilder();
|
for (int i = 0; i < sqlStatements.size(); i++) {
|
result.append(sqlStatements.get(i)).append("; ");
|
}
|
return result.toString();
|
}
|
}
|
