Hello everyone, I have recently encountered a rather difficult problem, which is to implement update and insert, which means that when executing SQL, update and insert
SQL syntax does have this function on duplicate key update but this kind of requirement is primary key or unique index
Too limited, my business is relatively complicated, so I can’t use sql, so after thinking for a long time, I decided to use java to generate xml files first, and then call kettle to put the files in to implement update and insert. The xml generated here is not displayed because it is not difficult, and the kettle code is called directly (basically, Baidu does not say that xml or ktr are called, but a bunch of calls to kjb)
@Override
public void kettle(Integer projectId) {
logger_info.info("Start inserting");
try {
KettleUtil.runKettleTransfer(null, kettleXml + projectId + ".xml");
//KettleUtil.runKettleTransfer(null, "D:/" + projectId + ".xml");
} catch (Exception e) {
logger_info.info("insert/update failed");
XxlJobHelper. handleFail();
e.printStackTrace();
}
logger_info.info("insert/update successfully");
XxlJobHelper. handleSuccess();
}
This is the parameters of the business layer. See your own definition. KettleUtil.runKettleTransfer(null, “D:/” + projectId + “.xml”);
The first pass is null and the second is the address where you put the xml/ktr file
I directly encapsulate the relevant implementation code into a tool class
<kettle.version>7.1.0.0-12</kettle.version>
<!-- kettle -->
<dependency>
<groupId>pentaho-kettle</groupId>
<artifactId>kettle-engine</artifactId>
<version>${kettle.version}</version>
<exclusions>
<exclusion>
<groupId>*</groupId>
<artifactId>*</artifactId>
</exclusion>
</exclusions>
</dependency>
<dependency>
<groupId>rhino</groupId>
<artifactId>js</artifactId>
<version>1.7R3</version>
</dependency>
<dependency>
<groupId>javax.mail</groupId>
<artifactId>mail</artifactId>
<version>1.4.7</version>
</dependency>
<dependency>
<groupId>pentaho-kettle</groupId>
<artifactId>kettle-core</artifactId>
<version>${kettle.version}</version>
<exclusions>
<exclusion>
<groupId>jug-lgpl</groupId>
<artifactId>jug-lgpl</artifactId>
</exclusion>
<exclusion>
<groupId>secondstring</groupId>
<artifactId>secondstring</artifactId>
</exclusion>
<exclusion>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
</exclusion>
<exclusion>
<artifactId>xercesImpl</artifactId>
<groupId>xerces</groupId>
</exclusion>
<exclusion>
<groupId>org.apache.xmlgraphics</groupId>
<artifactId>batik-js</artifactId>
</exclusion>
</exclusions>
</dependency>
/**
* @author Liu
* @data 2023/05/23
* @des kettle tool class Realize the ktr file of java calling kettle
*/
public class KettleUtil {
protected static final Logger logger_info = LoggerFactory. getLogger(KettleUtil. class);
/**
* Execute the job
*
* @param initKettleParam
* @param kjbFilePath
* @return
*/
public static boolean runKettleJob(Map<String, String> initKettleParam, String kjbFilePath) {
String uuid = UUID. randomUUID(). toString();
logger_info.info("ExecKettleUtil@runKettleJob:" + uuid + " {kjbFilePath:" + kjbFilePath + "}");
try {
KettleEnvironment.init();
// Initialize the job path
JobMeta jobMeta = new JobMeta(kjbFilePath, null);
Job job = new Job(null, jobMeta);
// Initialize the job parameters, get the parameter value in the script: ${variableName}
if (initKettleParam != null) {
for (String variableName : initKettleParam. keySet()) {
job.setVariable(variableName, initKettleParam.get(variableName));
}
}
job. start();
job. waitUntilFinished();
if (job. getErrors() > 0) {
logger_info.info("ExecKettleUtil@runKettleJob:" + uuid + "Execution failed");
} else {
logger_info.info("ExecKettleUtil@runKettleJob:" + uuid + " executed successfully");
}
return true;
} catch (Exception e) {
logger_info.error("ExecKettleUtil@runKettleJob:" + uuid, e);
return false;
}
}
/**
* Perform conversion
* @param initKettleParam
* @param ktrFilePath
* @return
*/
public static boolean runKettleTransfer(Map<String, String> initKettleParam, String ktrFilePath) {
Trans trans = null;
String uuid = UUID. randomUUID(). toString();
logger_info.info("ExecKettleUtil@runKettleTransfer:" + uuid + " {ktrFilePath:" + ktrFilePath + "}");
try {
// initialization
KettleEnvironment.init();
EnvUtil. environmentInit();
TransMeta transMeta = new TransMeta(ktrFilePath);
// convert
trans = new Trans(transMeta);
// Initialize the trans parameter, get the parameter value in the script: ${variableName}
if (initKettleParam != null) {
for (String variableName : initKettleParam. keySet()) {
trans.setVariable(variableName, initKettleParam.get(variableName));
}
}
// perform the conversion
trans. execute(null);
// Wait for the conversion to complete
trans.waitUntilFinished();
if (trans. getErrors() > 0) {
logger_info.info("ExecKettleUtil@runKettleTransfer:" + uuid + "Execution failed");
} else {
logger_info.info("ExecKettleUtil@runKettleTransfer:" + uuid + " Execution succeeded");
}
return true;
} catch (Exception e) {
logger_info.error("ExecKettleUtil@runKettleTransfer:" + uuid, e);
return false;
}
}
}
Creation is not easy, I hope everyone will like it