kettle 更新/插入组件 JAVA API

package com.example.fg.kettle;

import org.pentaho.di.core.KettleEnvironment;
import org.pentaho.di.core.database.DatabaseMeta;
import org.pentaho.di.core.exception.KettleException;
import org.pentaho.di.core.logging.StepLogTable;
import org.pentaho.di.core.variables.VariableSpace;
import org.pentaho.di.core.variables.Variables;
import org.pentaho.di.repository.kdr.KettleDatabaseRepository;
import org.pentaho.di.repository.kdr.KettleDatabaseRepositoryMeta;
import org.pentaho.di.trans.Trans;
import org.pentaho.di.trans.TransHopMeta;
import org.pentaho.di.trans.TransMeta;
import org.pentaho.di.trans.step.StepIOMetaInterface;
import org.pentaho.di.trans.step.StepMeta;
import org.pentaho.di.trans.steps.mergerows.MergeRowsMeta;
import org.pentaho.di.trans.steps.synchronizeaftermerge.SynchronizeAfterMergeMeta;
import org.pentaho.di.trans.steps.tableinput.TableInputMeta;
import org.owasp.encoder.Encode;

/**

• @author cgm

• @date 2019年7月8日
  /
  public class KettleTranSynchronizeAfterMerge {
  /*

  • 两个库中的表名
    */
    public static String bjdt_tablename = "xy_social_security";
    public static String kettle_tablename = "xy_social_security_qfzx_test";
    public static String kettle_log = "t_lzfx_data_log";

  public static void main(String[] args) {
  try {
  KettleEnvironment.init();
  TransMeta transMeta = new TransMeta();
  transMeta.setName("转换名称");

       DatabaseMeta sourceDatabaseMeta = new DatabaseMeta("bjdt", "mysql", "Native(JDBC)", "127.0.0.1", "test?useSSL=false&serverTimezone=Asia/Shanghai", "3306", "root", "toor@1234");
       transMeta.addDatabase(sourceDatabaseMeta);
       DatabaseMeta targetDatabaseMeta = new DatabaseMeta("kettle", "mysql", "Native(JDBC)", "127.0.0.1", "test?useSSL=false&serverTimezone=Asia/Shanghai", "3306", "root", "toor@1234");
       transMeta.addDatabase(targetDatabaseMeta);
       VariableSpace space = new Variables();
       space.setVariable("kettle_log","bjdt");
       space.initializeVariablesFrom(null);
       StepLogTable stepLogTable = StepLogTable.getDefault(space,transMeta);
       stepLogTable.setConnectionName("bjdt");
       stepLogTable.setTableName(kettle_log);
       transMeta.setStepLogTable(stepLogTable);
  
       TableInputMeta oldTableInput = new TableInputMeta();
       DatabaseMeta database_bjdt = transMeta.findDatabase("bjdt");
       oldTableInput.setDatabaseMeta(database_bjdt);
       String old_select_sql = "SELECT * FROM "+bjdt_tablename + " order by id" ;
       oldTableInput.setSQL(old_select_sql);
  
       StepMeta oldTableInputMetaStep = new StepMeta("INPUTTABLE_"+bjdt_tablename,oldTableInput);
       transMeta.addStep(oldTableInputMetaStep);
  
       TableInputMeta newTableInput = new TableInputMeta();
       DatabaseMeta database_kettle = transMeta.findDatabase("kettle");
       newTableInput.setDatabaseMeta(database_kettle);
       String new_select_sql = "SELECT * FROM "+kettle_tablename + " order by id" ;
       newTableInput.setSQL(new_select_sql);
  
       StepMeta newTableInputMetaStep = new StepMeta("INPUTTABLE_"+kettle_tablename,newTableInput);
       transMeta.addStep(newTableInputMetaStep);
  
       String[] columns = new String[]{"id", "eid", "name", "report_year", "report_name", "report_date", "bq_shengyubx_je", "dw_je_display", "dw_yanglaobx_je", "bq_shiyebx_je", "dw_yiliaobx_je", "shiyebx_num", "dw_yanglaobx_js", "dw_gongshangbx_js", "yiliaobx_num", "dw_shengyubx_je", "dw_js_display", "dw_shengyubx_js", "bq_yiliaobx_je", "bq_gongshangbx_je", "dw_gongshangbx_je", "shengyubx_num", "dw_shiyebx_js", "dw_shiyebx_je", "bq_je_display", "gongshangbx_num", "yanglaobx_num", "bq_yanglaobx_je", "dw_yiliaobx_js", "currency", "created_time", "row_update_time", "local_row_update_time"};
  
       MergeRowsMeta mergeRowsMeta = new MergeRowsMeta();
       StepIOMetaInterface stepIOMeta = mergeRowsMeta.getStepIOMeta();
       stepIOMeta.getInfoStreams().get(0).setStepMeta(newTableInputMetaStep);
       stepIOMeta.getInfoStreams().get(1).setStepMeta(oldTableInputMetaStep);
       mergeRowsMeta.setFlagField("bz"); //设置标志字段
       mergeRowsMeta.setKeyFields(new String[]{"ID"});
       mergeRowsMeta.setValueFields(columns);
       StepMeta mergeStepMeta = new StepMeta("合并记录", mergeRowsMeta);
       transMeta.addStep(mergeStepMeta);
  
       transMeta.addTransHop(new TransHopMeta(oldTableInputMetaStep, mergeStepMeta));
       transMeta.addTransHop(new TransHopMeta(newTableInputMetaStep, mergeStepMeta));
  
       SynchronizeAfterMergeMeta synchronizeAfterMergeMeta = new SynchronizeAfterMergeMeta();
       synchronizeAfterMergeMeta.setCommitSize(10000);
       synchronizeAfterMergeMeta.setDatabaseMeta(database_kettle);
       synchronizeAfterMergeMeta.setSchemaName("");
       synchronizeAfterMergeMeta.setTableName(kettle_tablename);
       synchronizeAfterMergeMeta.setUseBatchUpdate(true);
       synchronizeAfterMergeMeta.setKeyLookup(new String[]{"id"});
       synchronizeAfterMergeMeta.setKeyStream(new String[]{"id"});
       synchronizeAfterMergeMeta.setKeyStream2(new String[]{""});
       synchronizeAfterMergeMeta.setKeyCondition(new String[]{"="});
  
       Boolean[] updateOrNot = {false,true,true,true,true};
       synchronizeAfterMergeMeta.setUpdateLookup(columns);
       synchronizeAfterMergeMeta.setUpdateStream(columns);
       synchronizeAfterMergeMeta.setUpdate(updateOrNot);
  
       synchronizeAfterMergeMeta.setOperationOrderField("bz");
       synchronizeAfterMergeMeta.setOrderInsert("new");
       synchronizeAfterMergeMeta.setOrderUpdate("changed");
       synchronizeAfterMergeMeta.setOrderDelete("deleted");
       StepMeta synStepMeta = new StepMeta("数据同步", synchronizeAfterMergeMeta);
       transMeta.addStep(synStepMeta);
  
       transMeta.addTransHop(new TransHopMeta(mergeStepMeta,synStepMeta));
  
       Trans trans = new Trans(transMeta);
  
       trans.execute(null);
       trans.waitUntilFinished();
       if ( trans.getErrors() > 0 )
       {
           throw new RuntimeException( "There were errors during transformation execution." );
       }
       System.out.println("***********the end************");
   } catch (Exception e) {
       e.printStackTrace();
   }
  

  }
  }