kettle9.0 动态表 根据条件查询批量更新或者插入

一、整体流程

 1、先查询表的字段信息

（1）查询表的结构信息

SELECT
-- 拼接所有字段名（按ORDINAL_POSITION排序，逗号分隔）
GROUP_CONCAT(COLUMN_NAME ORDER BY ORDINAL_POSITION SEPARATOR ',') AS FIELD_LIST,
-- 取主键字段（无主键时返回空，需确保表有主键）
MAX(CASE WHEN COLUMN_KEY = 'PRI' THEN COLUMN_NAME ELSE NULL END) AS PK_FIELD
FROM information_schema.columns
WHERE table_schema = '${SOURCE_DB_NAME}'
AND table_name = '${TABLE_NAME}';

 2、查询同步记录

（2）根据条件查询符合条件的记录信息

SELECT
*,
'${TABLE_NAME}' AS TABLE_NAME,
'${FIELD_LIST}' AS FIELD_LIST,
'${PK_FIELD}' AS PK_FIELD,
'${COMPANY_ID}' AS company_id
FROM `${TABLE_NAME}`
WHERE gmt_modified >= DATE_SUB(NOW(), INTERVAL 3 MONTH)

（3）生成sql,进行sql执行

 （4）java代码

 

public boolean processRow(StepMetaInterface smi, StepDataInterface sdi) throws KettleException {

Object[] rowData = getRow();
if (rowData == null) {
setOutputDone();
return false;
}

String fieldList = getVariable("FIELD_LIST", "");
String pkField = getVariable("PK_FIELD", "id");
String companyId = getVariable("COMPANY_ID", "");

String tableName = getVariable("TABLE_NAME", "");

String[] sourceFields = fieldList.trim().isEmpty() ? new String[0] : fieldList.split(",");

boolean hasCompanyId = false;
for (String f : sourceFields) {
if ("company_id".equalsIgnoreCase(f.trim())) {
hasCompanyId = true;
break;
}
}

StringBuilder fields = new StringBuilder();
StringBuilder values = new StringBuilder();
StringBuilder updates = new StringBuilder();

// 处理源字段
for (String fieldName : sourceFields) {
fieldName = fieldName.trim();
if (fieldName.isEmpty()) continue;

int idx = getInputRowMeta().indexOfValue(fieldName);
Object val = (idx >= 0) ? rowData[idx] : null;
ValueMetaInterface meta = (idx >= 0) ? getInputRowMeta().getValueMeta(idx) : null;

fields.append("`").append(fieldName).append("`");

if (val == null) {
values.append("NULL");
} else if (meta != null && (meta.isNumeric() || meta.isInteger() || meta.isBigNumber())) {
values.append(val.toString());
} else if (meta != null && meta.isBoolean()) {
boolean b = "1".equals(val.toString()) || "true".equalsIgnoreCase(val.toString());
values.append(b ? "1" : "0");
} else {
String str = val.toString().replace("\\", "\\\\").replace("'", "''");
values.append("'").append(str).append("'");
}

if (!fieldName.equalsIgnoreCase(pkField)) {
updates.append("`").append(fieldName).append("` = VALUES(`").append(fieldName).append("`)");
}

// 只要后面还有字段（包括可能的 company_id），就加逗号
fields.append(",");
values.append(",");
if (!fieldName.equalsIgnoreCase(pkField)) {
updates.append(",");
}
}

// === 关键修复：处理 company_id ===
if (!hasCompanyId) {
// 先去掉末尾多余的逗号（如果有的话）
if (fields.length() > 0 && fields.charAt(fields.length() - 1) == ',') {
fields.setLength(fields.length() - 1);
}
if (values.length() > 0 && values.charAt(values.length() - 1) == ',') {
values.setLength(values.length() - 1);
}
if (updates.length() > 0 && updates.charAt(updates.length() - 1) == ',') {
updates.setLength(updates.length() - 1);
}

// 添加 company_id
if (fields.length() > 0) {
fields.append(",`company_id`");
if (companyId == null || companyId.trim().isEmpty()) {
values.append(",NULL");
} else {
String escaped = companyId.replace("\\", "\\\\").replace("'", "''");
values.append(",'").append(escaped).append("'");
}
} else {
fields.append("`company_id`");
if (companyId == null || companyId.trim().isEmpty()) {
values.append("NULL");
} else {
String escaped = companyId.replace("\\", "\\\\").replace("'", "''");
values.append("'").append(escaped).append("'");
}
}

// 更新子句
if (!"company_id".equalsIgnoreCase(pkField)) {
if (updates.length() > 0) {
updates.append(",`company_id` = VALUES(`company_id`)");
} else {
updates.append("`company_id` = VALUES(`company_id`)");
}
}
} else {
// 如果已有 company_id，确保移除末尾逗号
if (fields.length() > 0 && fields.charAt(fields.length() - 1) == ',') fields.setLength(fields.length() - 1);
if (values.length() > 0 && values.charAt(values.length() - 1) == ',') values.setLength(values.length() - 1);
if (updates.length() > 0 && updates.charAt(updates.length() - 1) == ',') updates.setLength(updates.length() - 1);
}

String sql = "INSERT INTO `"+tableName+"` (" + fields.toString() + ") VALUES (" + values.toString() + ") ON DUPLICATE KEY UPDATE " + updates.toString();

Object[] outputRow = createOutputRow(rowData, data.outputRowMeta.size());
get(Fields.Out, "dynamic_sql").setValue(outputRow, sql);
putRow(data.outputRowMeta, outputRow);
return true;
}