如何在Source Transformer中一次性生成多个CSV文件？

我在Mirth Connect的Source Transformer中尝试通过SQL查询生成CSV文件，步骤如下：

• 从ORDER表中根据指定OrderID获取Placer_Order_Numbers值；
• 对每个Placer_Order_Numbers，查询ORDER_DETAILS表获取ORDER_DETAIL_ID、Patient_Last_Name、Patient_First_Name，并为查询结果生成CSV；
• 将CSV存入channelMap，传递到目标端写入SFTP位置。

但每次执行通道时，生成的CSV只包含while循环中最后一次查询返回的行。我的代码如下：

try
{
    dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://**.**.**.**:****/dbname','user','password');
   
    getPlacerOrderNumber = dbConnMirth.executeCachedQuery(" SELECT * PLACER_ORDER_NUMBER FROM ORDER WHERE ORDER_ID IN (11055,11054,11053); ");
    
    if (getPlacerOrderNumber.size() > 0){
        
        while(getPlacerOrderNumber.next()){
            
            var resultOrderDetailsQuery = "SELECT ORDER_DETAIL_ID,PATIENT_LAST_NAME, PATIENT_FIRST_NAME FROM ORDER_DETAILS WHERE ORDER_DETAILS_PLACER_ORDER_NUMBER = '" + placerOrderNumber + "'; ";
            var resultOrderDetailsQueryResult = dbConn.executeCachedQuery(resultOrderDetailsQuery);
            
            if (resultOrderDetailsQueryResult.size() > 0){
                resultOrderDetailsQueryResult.next();
                
                var orderDetailId = resultOrderDetailsQueryResult.getInt(1);
                var patientFirst = resultOrderDetailsQueryResult.getString(4);
                var patientLast = resultOrderDetailsQueryResult.getString(3);
                var csvName = patientLast + "_" + patientFirst + ".csv";
                
                channelMap.put('csvName', csvName);
                
                csv = 'Order Detail Id' + delimiter + 'Patient Last Name' + delimiter + 'Patient First Name' + delimiter +'\n';
                csv = csv + orderDetailId + delimiter+ patientLast + delimiter + patientFirst +'\n';
            }
            channelMap.put('csv', csv);
        }
    }
}

问题根源 #

1. CSV变量被循环重置：每次循环直接用csv = ...赋值，覆盖了之前累积的内容，最终只保留最后一次循环的结果。
2. 未处理多行查询结果：仅调用resultOrderDetailsQueryResult.next()一次，只获取ORDER_DETAILS的第一行数据，忽略其余行。
3. 变量缺失/错误：placerOrderNumber未从结果集取值，导致查询条件无效；delimiter未定义，CSV分隔符失效；patientFirst取了索引4，但查询只返回3个字段，会报错。
4. channelMap赋值时机错误：每次循环覆盖csv和csvName的channelMap值，最终仅保留最后一次循环的结果。

修正后的代码（合并为单个CSV场景） #

try {
    // 初始化数据库连接
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://**.**.**.**:****/dbname','user','password');
    // 定义CSV分隔符
    var delimiter = ',';
    // 初始化CSV表头，放在循环外避免重复写入
    var csv = 'Order Detail Id' + delimiter + 'Patient Last Name' + delimiter + 'Patient First Name' + '\n';
    
    // 修正SQL：去掉多余的*，ORDER是关键字需加方括号
    var getPlacerOrderNumber = dbConn.executeCachedQuery("SELECT PLACER_ORDER_NUMBER FROM [ORDER] WHERE ORDER_ID IN (11055,11054,11053);");
    
    if (getPlacerOrderNumber.size() > 0){
        while(getPlacerOrderNumber.next()){
            // 从结果集获取当前Placer_Order_Number
            var placerOrderNumber = getPlacerOrderNumber.getString('PLACER_ORDER_NUMBER');
            
            // 用参数化查询避免SQL注入，同时简化语法
            var resultOrderDetailsQueryResult = dbConn.executeCachedQuery(
                "SELECT ORDER_DETAIL_ID, PATIENT_LAST_NAME, PATIENT_FIRST_NAME FROM ORDER_DETAILS WHERE ORDER_DETAILS_PLACER_ORDER_NUMBER = ?;",
                [placerOrderNumber]
            );
            
            // 遍历当前Placer_Order_Number对应的所有明细行
            while(resultOrderDetailsQueryResult.next()){
                var orderDetailId = resultOrderDetailsQueryResult.getInt('ORDER_DETAIL_ID');
                var patientLast = resultOrderDetailsQueryResult.getString('PATIENT_LAST_NAME');
                var patientFirst = resultOrderDetailsQueryResult.getString('PATIENT_FIRST_NAME');
                
                // 用+=追加行内容，而非覆盖
                csv += orderDetailId + delimiter + patientLast + delimiter + patientFirst + '\n';
            }
        }
    }
    
    // 循环结束后统一存入channelMap，避免被覆盖
    channelMap.put('csv', csv);
    channelMap.put('csvName', 'combined_orders.csv');
} catch(e) {
    // 捕获异常并记录，避免通道中断
    logger.error('生成CSV失败: ' + e.getMessage());
    throw e;
} finally {
    // 确保数据库连接关闭，避免资源泄漏
    if (dbConn) {
        dbConn.close();
    }
}

关键修正点说明 #

• CSV内容累积：将csv初始化移到循环外，用+=追加行内容，保留所有循环的结果。
• 处理多行结果：为ORDER_DETAILS的查询结果嵌套while循环，遍历所有返回行。
• 变量补全与修正：添加placerOrderNumber取值逻辑，定义delimiter，修正字段索引错误。
• 参数化查询：替换字符串拼接为参数化查询，避免SQL注入风险，同时提升代码可读性。
• 资源与异常处理：添加finally块关闭数据库连接，catch块捕获并记录异常，提升通道稳定性。

若需每个Placer_Order_Number生成独立CSV #

如果需要为每个Placer_Order_Number单独生成CSV文件，可调整为以下逻辑：

try {
    var dbConn = DatabaseConnectionFactory.createDatabaseConnection('net.sourceforge.jtds.jdbc.Driver','jdbc:jtds:sqlserver://**.**.**.**:****/dbname','user','password');
    var delimiter = ',';
    // 用数组存储多个CSV的文件名和内容
    var csvFiles = [];
    
    var getPlacerOrderNumber = dbConn.executeCachedQuery("SELECT PLACER_ORDER_NUMBER FROM [ORDER] WHERE ORDER_ID IN (11055,11054,11053);");
    
    if (getPlacerOrderNumber.size() > 0){
        while(getPlacerOrderNumber.next()){
            var placerOrderNumber = getPlacerOrderNumber.getString('PLACER_ORDER_NUMBER');
            var resultOrderDetailsQueryResult = dbConn.executeCachedQuery(
                "SELECT ORDER_DETAIL_ID, PATIENT_LAST_NAME, PATIENT_FIRST_NAME FROM ORDER_DETAILS WHERE ORDER_DETAILS_PLACER_ORDER_NUMBER = ?;",
                [placerOrderNumber]
            );
            
            // 为当前Placer_Order_Number创建独立CSV
            var currentCsv = 'Order Detail Id' + delimiter + 'Patient Last Name' + delimiter + 'Patient First Name' + '\n';
            var csvName = '';
            
            while(resultOrderDetailsQueryResult.next()){
                var orderDetailId = resultOrderDetailsQueryResult.getInt('ORDER_DETAIL_ID');
                var patientLast = resultOrderDetailsQueryResult.getString('PATIENT_LAST_NAME');
                var patientFirst = resultOrderDetailsQueryResult.getString('PATIENT_FIRST_NAME');
                
                currentCsv += orderDetailId + delimiter + patientLast + delimiter + patientFirst + '\n';
                // 用第一行数据生成文件名
                if (!csvName) {
                    csvName = patientLast + '_' + patientFirst + '.csv';
                }
            }
            
            // 将当前CSV存入数组
            csvFiles.push({name: csvName, content: currentCsv});
        }
    }
    
    // 将数组存入channelMap，后续在目标端遍历写入SFTP
    channelMap.put('csvFiles', csvFiles);
} catch(e) {
    logger.error('生成CSV失败: ' + e.getMessage());
    throw e;
} finally {
    if (dbConn) dbConn.close();
}

内容的提问来源于stack exchange，提问作者Noor