Kettle集成到SpringBoot项目案例

拼搏现实的明天。 2021-12-15 00:45 2219阅读 0赞

### 一、案例关键代码结构 ###

![20190704104349182.png][]

说明：转换ktr脚本和作业kjb脚本由可视化kettle先编写好

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3htMzkzMzkyNjI1_size_16_color_FFFFFF_t_70][]

简单案例可参考：[使用Kettle将SQL Server数据批量导入到MySql][Kettle_SQL Server_MySql]

kettle用到的部分包依赖（可从kettle客户端lib包拷贝，可自行打到自己maven仓库，因是测试案例本人是本地引用）：

![20191202141645775.png][]

<dependency>
    		    <groupId>com.jcraft</groupId>
    		    <artifactId>jsch</artifactId>
    		    <version>0.1.46</version>
    		</dependency>
    		
    
    		<dependency> 
    			<groupId>org.pentaho.di</groupId> 
    			<artifactId>kettle-core</artifactId> 
    			<version>8.2.0.0-342</version> 
    			<scope>system</scope> 
    			<systemPath>${project.basedir}/lib/kettle-core-8.2.0.0-342.jar</systemPath> 
    		</dependency>
    		<dependency> 
    			<groupId>org.pentaho.di</groupId> 
    			<artifactId>kettle-engine</artifactId> 
    			<version>8.2.0.0-342</version> 
    			<scope>system</scope> 
    			<systemPath>${project.basedir}/lib/kettle-engine-8.2.0.0-342.jar</systemPath> 
    		</dependency>
    		
    		<dependency> 
    			<groupId>org.pentaho.metastore</groupId> 
    			<artifactId>metastore</artifactId> 
    			<version>8.2.0.0-342</version> 
    			<scope>system</scope> 
    			<systemPath>${project.basedir}/lib/metastore-8.2.0.0-342.jar</systemPath> 
    		</dependency>
    		
    		<dependency> 
    			<groupId>org.apache.commons</groupId> 
    			<artifactId>commons-vfs2</artifactId> 
    			<version>2.2</version> 
    			<scope>system</scope> 
    			<systemPath>${project.basedir}/lib/commons-vfs2-2.2.jar</systemPath> 
    		</dependency>
    		
    		<dependency> 
    			<groupId>net.sourceforge</groupId> 
    			<artifactId>jtds</artifactId> 
    			<version>1.3.1</version> 
    			<scope>system</scope> 
    			<systemPath>${project.basedir}/lib/jtds-1.3.1.jar</systemPath> 
    		</dependency>
    		
    		<dependency> 
    			<groupId>org.apache.commons</groupId> 
    			<artifactId>commons-logging</artifactId> 
    			<version>1.1.3</version> 
    			<scope>system</scope> 
    			<systemPath>${project.basedir}/lib/commons-logging-1.1.3.jar</systemPath> 
    		</dependency>
    		
    		<dependency> 
    			<groupId>org.apache.commons</groupId> 
    			<artifactId>commons-io</artifactId> 
    			<version>2.2</version> 
    			<scope>system</scope> 
    			<systemPath>${project.basedir}/lib/commons-io-2.2.jar</systemPath> 
    		</dependency>
    		
    		<dependency> 
    			<groupId>oracle</groupId>
    			<artifactId>ojdbc8</artifactId>
    			<version>8.0</version>
    			<scope>system</scope>
    			<systemPath>${project.basedir}/lib/ojdbc8-8.0.jar</systemPath>
    		</dependency>
    
    		<dependency>
    			<groupId>com.ibm</groupId>
    			<artifactId>db2jcc</artifactId>
    			<version>1.0</version>
    			<scope>system</scope>
    			<systemPath>${project.basedir}/lib/db2jcc.jar</systemPath>
    		</dependency>
    
    		<dependency>
    			<groupId>com.ibm</groupId>
    			<artifactId>db2jcc4</artifactId>
    			<version>1.0</version>
    			<scope>system</scope>
    			<systemPath>${project.basedir}/lib/db2jcc4.jar</systemPath>
    		</dependency>

### 二、关键代码说明 ###

1）、配置文件application.properties添加

#win系统
    kettle.script.path = E://kettle_test//
    #linux系统
    #kettle.script.path = /home/kettle_test/

2）、Service读取Kettle脚本关键代码

@Service
    public class KettleService {
    
        @Value("${kettle.script.path}")
        private String dirPath;
    
        /**
         * 执行ktr文件
         * @param filename
         * @param params
         * @return
         */
        public ResponseMsg<KettleResult> runKtr(String filename, Map<String, String> params) {
            final ResponseMsg<KettleResult> response = new ResponseMsg<>();
            try {
                KettleEnvironment.init();
                TransMeta tm = new TransMeta(dirPath + File.separator + filename);
                Trans trans = new Trans(tm);
                if (params != null) {
                    Iterator<Map.Entry<String, String>> entries = params.entrySet().iterator();
                    while (entries.hasNext()) {
                        Map.Entry<String, String> entry = entries.next();
                        trans.setParameterValue(entry.getKey(),entry.getValue());
                    }
                }
    
                trans.execute(null);
                trans.waitUntilFinished();
                response.setHead(ResponseHead.buildSuccessHead());
            } catch (Exception e) {
                e.printStackTrace();
                String er = e.getMessage();
                response.setHead(ResponseHead.buildFailedHead(er));
    
            }
    
            return response;
        }
    
        /**
         * 执行kjb文件
         * @param filename
         * @param params
         * @return
         */
        public ResponseMsg<KettleResult> runKjb(String filename, Map<String, String> params) {
            final ResponseMsg<KettleResult> response = new ResponseMsg<>();
            try {
                KettleEnvironment.init();
                JobMeta jm = new JobMeta(dirPath + File.separator + filename, null);
                Job job = new Job(null, jm);
                if (params != null) {
                    Iterator<Map.Entry<String, String>> entries = params.entrySet().iterator();
                    while (entries.hasNext()) {
                        Map.Entry<String, String> entry = entries.next();
                        job.setVariable(entry.getKey(), entry.getValue());
                    }
                }
    
                job.start();
                job.waitUntilFinished();
                response.setHead(ResponseHead.buildSuccessHead());
            } catch (Exception e) {
                e.printStackTrace();
                String er = e.getMessage();
                response.setHead(ResponseHead.buildFailedHead(er));
    
            }
            return response;
        }
    }

3）、Controller运用案例

执行ktr脚本

@PostMapping("patientTrans")
        public ResponseMsg<KettleResult> patientTrans (@Valid @RequestBody RequestMsg<KettleInfo> in, BindingResult result)
                throws CommonRuntimeException{
            String fileName = "patientTrans.ktr";
            ResponseMsg<KettleResult> response = this.kettleService.runKtr(fileName, null);
            return response;
        }

执行kjb脚本

@RequestMapping(value = "patientIn", method = RequestMethod.POST)
        @InParamCheck
        public ResponseMsg<KettleResult> patientIn(@Valid @RequestBody RequestMsg<KettleInfo> in, BindingResult result)
            throws CommonRuntimeException {
            String fileName = "patientIn.kjb";
            ResponseMsg<KettleResult> response = this.kettleService.runKjb(fileName, new HashMap<>());
            return response;
        }

4）、执行结果

发送测试请求

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3htMzkzMzkyNjI1_size_16_color_FFFFFF_t_70 1][]

后端执行情况

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3htMzkzMzkyNjI1_size_16_color_FFFFFF_t_70 2][]

[20190704104349182.png]: /images/20211214/c3085d89100e4cc5adf3a396e9965885.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3htMzkzMzkyNjI1_size_16_color_FFFFFF_t_70]: /images/20211214/5fc6775e9f7443028fc01a1072031ccc.png
[Kettle_SQL Server_MySql]: https://blog.csdn.net/xm393392625/article/details/90514087
[20191202141645775.png]: /images/20211214/06707d770b22400696cccb7fbd77d966.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3htMzkzMzkyNjI1_size_16_color_FFFFFF_t_70 1]: /images/20211214/b903dee260824722a40d733ad7fa0e04.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3htMzkzMzkyNjI1_size_16_color_FFFFFF_t_70 2]: /images/20211214/9053399a6f6c4617a18dce7a16d99116.png