1.jdbc调用oralce自定义的数组类型并批量更新数据
首先在这里说一下目的：公司要求用jdbc插入weka分析数据后的模型来分析某条数据对结果的影响概率，并将这条概率插入到这条数据所在的表中，刚开始是一条一条的去调用数据库存储过程，效率很低。
现在需要一次性的插入100条数据或者更多，那么问题来了，如何用java识别oralce自定义的数组来完成这件事呢？过程是曲折的。
一、我在数据库中定义了一个类型和一个表数组（就是数组长度无限制的数组）。
1.定义了一个类型数据
在这一部分很重要，为了跟数据库字段进行匹配，刚开始这三个属性的类型都是varchar2，对应表中要插入的字段和唯一标识字段，但是数据就是插入不了。下文会说这个问题。
create or replace TYPE ana_type1 AS OBJECT ( result0 nvarchar2(300), result1 nvarchar2(300), uuid nvarchar2(10) );
2.定义一个表数组。
create or replace TYPE array_ana_type1 AS TABLE OF ana_type1;
二、预先写好存储过程
create or replace PROCEDURE OPS_RESULT_ANALYSIS(vv_array array_ana_type1,tableName varchar2) AS up_sql varchar2(1000):=''; -- type v_type is record(result0 varchar2(50),result1 varchar2(50),uuid varchar2(50)); -- type v_array is table of v_type; -- vv_array v_array; BEGIN -- vv_array:=v_array(); -- vv_array.extend; -- vv_array(1).result0:='0.1'; -- vv_array(1).result1:='0.2'; -- vv_array(1).uuid:='15646556'; --循环数组，更改数据库数据 delete update_sql; for i in vv_array.FIRST..vv_array.LAST loop up_sql:='update '||tableName||' set latent='''||vv_array(i).result0||''','||'latent2='''||vv_array(i).result1||''' where card_no='''||vv_array(i).uuid||''' and 1=1'; --dbms_output.put_line(up_sql); insert into update_sql values(up_sql); execute immediate up_sql; if tableName='OPR_RESULT_FR5' then up_sql:='update OPR_RESULT_FR5 set latent_fr='''||vv_array(i).result0||''','||'latent_fr2='''||vv_array(i).result1||''' where card_no='''||vv_array(i).uuid||''' and 1=1'; --dbms_output.put_line(up_sql); execute immediate up_sql; end if; end loop; commit; END;
注释掉的部分，是我刚开始时测试up _sql拼接的是否正确，自己定义一个数组，给数组赋空间，然后复制，打印sql的步骤。
sql拼接无误之后，就把完整的存储过程写好，以便java调用，在java调用的时候，出现了存储过程执行成功，但数据没有更新的问题。所以在此我就创建了一个只有一个字段varchar2的日志表用来记录java调用存储过程时，我拼接的sql是否正确，还可以检查传过来的数据是否正确（为空或者其他什么的，作用很大）。
三、写好java程序
在java部分我做的工作是：
1.定时器调用线程。
2.线程调用分析数据库数据的方法（是weka数据分析的工作，可以不用管它），数据库查到的每条数据产生两个概率，一个是事件的正概率，一个是事件的负概率，然后我们还需要每条数据的唯一标识，
这三个分别对应的变量名是result0,result1,uuid。
3.拿到这写数据后，用jdbc调用存储过程，传入数组变量，批量更新数据。（重要）
我们一步一步贴上代码！！
A、定时器部分：
package com.cmcc.task; import java.io.File; import org.apache.log4j.Logger; import org.springframework.scheduling.annotation.Scheduled; import org.springframework.stereotype.Component; import com.cmcc.dataAnalysis.CommonAnalysis; import com.cmcc.thread.ModelTestThred; /** * 数据分析定时器 * 复诊、非复诊、晶体等定时分析 * @author XWS * */ @Component public class DataAnalysisTask { private Logger LOG = Logger.getLogger(DataAnalysisTask.class); private String q1="SELECT * from (SELECT CARD_NO,SEX_CODE, PAYKIND_CODE,PACT_CODE,PACT_NAME,REGLEVL_CODE,REGLEVL_NAME,DEPT_CODE,DEPT_NAME,DOCT_CODE,DOCT_NAME,YNFR,YNSEE,IN_SOURCE,ADDRESS_CODE,ADDRESS_NAME,AGE,DIAGNOSE,PATIENT_NO FROM OPR_RESULT fo where fo.LATENT is NULL order by fo.reg_date desc) where rownum<=100"; private String tableName1="OPR_RESULT"; private String q2="SELECT * from (SELECT fo.* FROM OPR_RESULT_FR5 fo where fo.LATENT_FR is NULL order by fo.reg_date desc) where rownum<=100 and drug_percent is not null" ;//"SELECT * FROM OPR_RESULT_FR5"; where fo.LATENT_FR is NULL private String tableName2="OPR_RESULT_FR5"; private String [] model1= //删除不需要的属性集 { "CARD_NO", "ADDRESS_CODE", "DOCT_CODE", "PACT_CODE", "REGLEVL_CODE", "DEPT_CODE" }; private String [] model2= //删除不需要的属性集 { "CLINIC_CODE", "CARD_NO", "ADDRESS_CODE", "DOCT_CODE", "PACT_CODE", "REGLEVL_CODE", "DEPT_CODE", "TIMES", "LABEL2",//是否复诊预测结果 "LATENT_FR",//是否复诊预测结果 "LATENT_FR2" }; @Scheduled(cron = "0 34 10-11 * * ?") public void job1(){ LOG.info("job1的执行时间：0 0/10 0-1 * * ?"); String querySql=q1; String[] deletModel=model1; //String modelUrl="../model/randomForest.model"; String modelUrl = "../webapps/SJFX/model/randomForest.model"; //String insertsql="{call ops_result_update3(?,?,?)}"; String insertsql="{CALL OPS_RESULT_ANALYSIS(?,?)}"; ModelTestThred modelTest = new ModelTestThred(querySql,deletModel,modelUrl,insertsql,tableName1); new Thread(modelTest).start(); } @Scheduled(cron = "0 0/10 1-2 * * ?") public void job2(){ LOG.info("job2的执行时间：0 0/10 1-2 * * ?"); String querySql=q2; String[] deletModel=model2; String modelUrl="../webapps/SJFX/model/randomForestFR.model"; String insertsql="{call ops_result_update2(?,?,?)}"; ModelTestThred modelTest = new ModelTestThred(querySql,deletModel,modelUrl,insertsql,tableName2); new Thread(modelTest).start(); } }
B、线程部分
package com.cmcc.thread; import org.apache.log4j.Logger; import com.cmcc.dataAnalysis.CommonAnalysis; public class ModelTestThred implements Runnable{ private String querySql; private String[] deletModel; private String modelUrl; private String insertsql; private String tableName; private Logger LOG = Logger.getLogger(ModelTestThred.class); public ModelTestThred(String querySql, String[] deletModel, String modelUrl, String insertsql,String tableName) { this.querySql=querySql; this.deletModel=deletModel; this.modelUrl=modelUrl; this.insertsql=insertsql; this.tableName=tableName; } public ModelTestThred() { } @Override public void run() { LOG.info("线程"+Thread.currentThread().getName()+"开始分析"+modelUrl+"的数据"); CommonAnalysis.DataTestingFunction(querySql,deletModel,modelUrl,insertsql,tableName); LOG.info("end analysis "+modelUrl+" at"+System.currentTimeMillis()); } }
C、分析概率部分
package com.cmcc.dataAnalysis; import org.apache.log4j.Logger; import weka.classifiers.Classifier; import weka.core.Instances; import weka.core.converters.DatabaseLoader; /** * OPR_RESULT 和 OPR_RESULT_FR5两个表的数据的分析方法 * * @author XWS * */ public class CommonAnalysis { public static Logger LOG = Logger.getLogger(CommonAnalysis.class); public static void DataTestingFunction( String querySql, String[] deletModel, String modelUrl, String sql, String tableName) {// 预测住院结果 Classifier m_classifier; try { LOG.info("start testing at" + System.currentTimeMillis()); DatabaseLoader atf = DatabaseLoaderUtil.JDBCQuery(querySql); Instances instancesTest = atf.getDataSet(); // 读入训练文件 int dataSize = instancesTest.numInstances(); LOG.info("process data :" + dataSize); String [] uuid = new String[dataSize]; for (int i = 0; i < dataSize; i++)// 前100个测试数据，记录卡号 { uuid[i] = instancesTest.instance(i).stringValue(0); } DataFilterUtil.delAttr(deletModel, instancesTest); instancesTest.setClassIndex(instancesTest.numAttributes() - 1); m_classifier = (Classifier) weka.core.SerializationHelper .read(modelUrl); String [] result0 = new String[dataSize]; String [] result1 = new String[dataSize]; for (int i = 0; i < dataSize; i++)// 测试分类结果 { double[] result =null; try { result = m_classifier .distributionForInstance(instancesTest.instance(i)); } catch (Exception e) { System.out.println("分析概率出错！"); e.printStackTrace(); }// 输出分类结果result[0]和result[1]，其中result[0]是住院概率，result[1]是不住院概率 /*String[] result_o = new String[result.length]; for (int j = 0; j < result.length; j++) { if (result[j] < 0.001) { result_o[j] = 0 + ""; } else { result_o[j] = result[j] + ""; } }*/ result0[i]=result[0]<0.001?"0":(result[0]+"0"); result1[i]=result[1]<0.001?"0":(result[1]+"0"); //InsertResultUtil.insertResult(result_o, card_no[i], sql);// 前100个测试数据，根据其卡号，将结果插入数据库 } LOG.info("准备插入"+modelUrl+"模型分析的数据"); for(int i=0; i<dataSize;i++){ LOG.info("result0: "+result0[i]+"\tresult1: "+result1[i]); } try { InsertResultUtil.insertAnalysisResult(result0, result1, uuid, tableName,sql); //dataAnalysisDao.insertAnalysisResult(result0, result1, uuid, tableName, false); } catch (Exception e) { LOG.error(modelUrl+"数据插入时出现错误！"); e.printStackTrace(); } } catch (Exception e) { e.printStackTrace(); LOG.error(modelUrl+"数据测试时出现错误！"); } } }
D、调用jdbc部分
package com.cmcc.dataAnalysis; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.SQLException; import oracle.jdbc.OracleCallableStatement; import oracle.sql.ARRAY; import oracle.sql.ArrayDescriptor; import oracle.sql.STRUCT; import oracle.sql.StructDescriptor; import org.apache.log4j.Logger; public class InsertResultUtil { private static Logger LOG = Logger.getLogger(InsertResultUtil.class); public static void insertAnalysisResult(String[] result0, String[] result1, String[] uuid, String tableName, String sql) { Connection conn = null; CallableStatement csmt = null; try { //配置数据库 conn = JDBCUtils.getConnection(); conn.setAutoCommit(false); //创建oralce类型描述对象 StructDescriptor st = new StructDescriptor("ANA_TYPE1",conn); //创建oralce类型数组 STRUCT[] sts =new STRUCT[result0.length]; //填充自定义的数组 for(int i=0;i<result0.length;i++){ Object [] o = {result0[i],result1[i],uuid[i]}; //创建类型变量 sts[i]=new STRUCT(st,conn,o); System.out.println(i); } //创建数组描述对象 ArrayDescriptor ad = ArrayDescriptor.createDescriptor("ARRAY_ANA_TYPE1", conn); //创建数组对象 ARRAY ARRAY_ANA_TYPE1 = new ARRAY(ad, conn, sts); csmt = conn.prepareCall(sql); // 无参数无返回结果的存储过程 //添加占位符 ((OracleCallableStatement) csmt).setArray(1,ARRAY_ANA_TYPE1); csmt.setString(2,tableName); boolean falg = csmt.execute();//执行成功返回false System.out.println(falg); System.out.println(csmt.getUpdateCount()); conn.commit(); csmt.close(); conn.close(); LOG.info("分析"+tableName+"表的数据已经成功插入！"); } catch (SQLException e) { e.printStackTrace(); } } }
四、遇到的问题
途中遇到很多问题，但解决起来也不费力，但是有一个问题是这样的，程序运行的时候，在调用存储过程并更新数据的时候，什么错误都没有，数据库也没有更新；
然后我请教别人，给了我把sql插入到日志表中的建议，所以我建了一个表来存每次的更新sql，发现从java传过来的数据都是空的，所以我才知道数据并没有真正的传过来。
之后我又找到了网上的一些别人的例子，参照网页http://blog.csdn.net/hzw2312/article/details/8444462，里面有这么一段话：
最后：一定要记得导入orai18n.jar否则一遇到字符串就乱码、添加不到数据！
一些加上jar继续报错如下错误的朋友可以考虑以下解决方案：
ERROR1：Non supported character set: oracle-character-set-852
ERROR2：oracle/i18n/text/converter/CharacterConverterOGS.getInstance(I)Loracle/i18n/text/converter/CharacterConverter;
以上两个错误可以采取一个方案，就是把type的数据类型改成：nVARCHAR2，
如果坚持使用该jar的童鞋请将 nls_charset12.jar 加入到 classpath 中。在加上orai18n.jar
所以我才知道用这个oralce数组，必须要有orai18n.jar和nls_charset12.jar，我找到这两个架包添加到项目中去，但是还是在启动项目的时候就报错，查一查，有的人说是jar版本的问题，我头疼。
突然看到以上两个错误可以采取一个方案，就是把type的数据类型改成：nVARCHAR2，我就本着鱼死网破的心态堵了一把，没想到真的成功了！！！功夫不负有心人，数据库数据成功插入。
望有心人看了这篇文章有所收获！！
浙公网安备 33010602011771号