首先加入连依赖：
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.6</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>新建工具类：
package utils;
import com.jeesite.modules.ejserver.impl.EjImpl;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
public class LisDBUtil {
private static ComboPooledDataSource dataSource;
/**
* 连接XXX库并设置自动提交为否
*/
public static Connection getLisConnection(){
Connection conn = null;
try{
dataSource = new ComboPooledDataSource();
dataSource.setUser("lis");
//dataSource.setPassword("1");
dataSource.setPassword("oracle");
dataSource.setJdbcUrl("jdbc:oracle:thin:@192.168.40.7:1521:orcl");
dataSource.setDriverClass("oracle.jdbc.driver.OracleDriver");
//dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/pjxt?useUnicode=true&characterEncoding=utf-8");
//dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setMinPoolSize(2);
dataSource.setAcquireIncrement(5);
dataSource.setMaxPoolSize(20);
conn = dataSource.getConnection();
conn.setAutoCommit(false);//设置自动提交为否
System.out.println("连接成功！");
}catch (Exception e){
e.printStackTrace();
}
return conn;
}
/**
* 提交数据
* @throws Exception
*/
public static void commit(Connection conn) throws Exception {
if(null!=conn){
conn.commit();//提交
}
if (null!=conn){
conn.close();//关闭
}
}
/**
* 回滚
* @throws Exception
*/
public static void rollback( Connection conn) throws Exception {
if (null!=conn){
conn.rollback();//LIS数据回滚
}
if (null!=conn){
conn.close();
}
}
public static void connColse(Connection conn){
if(null!=conn){
try{
conn.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
}
}使用实例：
package com.jeesite.modules.ejserver.impl;
import com.jeesite.modules.ejserver.Ej;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.dom4j.*;
import org.dom4j.io.SAXReader;
import org.springframework.stereotype.Service;
import utils.LisDBUtil;
import javax.jws.WebMethod;
import javax.jws.WebService;
import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.*;
@WebService
@Service
public class EjImpl implements Ej {
QueryRunner queryRunner = new QueryRunner();
/**
*通过“条码号”【参数：医院条码】从该接口获取LIS的病人信息和医嘱信息（XML文档字符串）
* @param HospSampleID 条码号”【参数：医院条码】
* @return
*/
@WebMethod
public String GetLisRequest(String HospSampleID) {
String xmlStr = null;
String sql = "select a.bqtmbh Lis_Barcode," +
" a.brid pat_id ," +
" decode(a.mzh,'',a.tjh,a.mzh)pat_no," +
" a.xm pat_name," +
" a.ch pat_bedno," +
" a.cysj blood_time," +
" a.xb pat_sex," +
" a.csrq pat_birthday," +
" a.nn pat_age," +
" decode(sign(trunc(months_between(sysdate, a.csrq)/12)-15),1,4,5)pat_ageunit," +
" a.kxmksmc dept_name," +
" a.ysxm doctor_name," +
" a.zd clinical_diag," +
" c.mc samp_name," +
" b.zhxmid lis_item_code," +
" d.zhxmmc lis_item_name," +
" e.jyxm lis_subitem_code," +
" f.xmmc lis_subitem_name " +
" from lis_jyd a left join lis_jydzhxm b on a.jydid=b.jydid " +
" left join lis_bblx c on b.bblxid=c.bblxid join lis_zhxm d on b.zhxmid=d.zhxmid" +
" left join lis_zhxmpz e on e.zhxmid=b.zhxmid " +
" left join lis_jyxm f on e.jyxm=f.jyxmid " +
" where a.bqtmbh='"+HospSampleID+"' " +
" order by b.zhxmid";
Connection conn = LisDBUtil.getLisConnection();
List<Map<String, Object>> result = null;
Map<String,Object> rowMap = new LinkedHashMap<>();
List<Map<String, Object>> lisItemList = null;
try{
result = queryRunner.query(conn,sql, new MapListHandler());
Document document = null;
if(result.size() != 0){
document = DocumentHelper.createDocument();
Element Data = document.addElement("Data");
Element Data_Row = Data.addElement("Data_Row");
rowMap.put("Lis_Barcode",result.get(0).get("Lis_Barcode"));
rowMap.put("pat_id",result.get(0).get("pat_id"));
rowMap.put("pat_no",result.get(0).get("pat_no"));
rowMap.put("blood_time",result.get(0).get("blood_time"));
rowMap.put("pat_name",result.get(0).get("pat_name"));
rowMap.put("pat_bedno",result.get(0).get("pat_bedno"));
rowMap.put("pat_sex",result.get(0).get("pat_sex"));
rowMap.put("pat_birthday",result.get(0).get("pat_birthday"));
rowMap.put("pat_age",result.get(0).get("pat_age"));
rowMap.put("pat_ageunit",result.get(0).get("pat_ageunit"));
rowMap.put("dept_name",result.get(0).get("dept_name"));
rowMap.put("doctor_name",result.get(0).get("doctor_name"));
rowMap.put("clinical_diag",result.get(0).get("clinical_diag"));
rowMap.put("samp_name",result.get(0).get("samp_name"));
for(String key:rowMap.keySet()){//创建病人信息标签并赋值
if(null!=rowMap.get(key) && (!"".equals(rowMap.get(key))) ){
Data_Row.addElement(key).addText(rowMap.get(key).toString());
}else{
Data_Row.addElement(key);
}
}
Element LisItems = null;
Element SubItems = null;
for(int i=0;i<result.size();i++ ){
if(i==0 || !result.get(i).get("lis_item_code").equals(result.get(i-1).get("lis_item_code")) ){//创建组合项目标签并赋值
LisItems = Data_Row.addElement("LisItems");
LisItems.addElement("lis_item_code").addText(result.get(i).get("lis_item_code").toString());
LisItems.addElement("lis_item_name").addText(result.get(i).get("lis_item_name").toString());
SubItems = LisItems.addElement("SubItems");
SubItems.addElement("lis_subitem_code").addText(result.get(i).get("lis_subitem_code").toString());
SubItems.addElement("lis_subitem_name").addText(result.get(i).get("lis_subitem_name").toString());
}else{
SubItems = LisItems.addElement("SubItems");
SubItems.addElement("lis_subitem_code").addText(result.get(i).get("lis_subitem_code").toString());
SubItems.addElement("lis_subitem_name").addText(result.get(i).get("lis_subitem_name").toString());
}
}
xmlStr = document.asXML();
}else{
document = DocumentHelper.createDocument();
Element response = document.addElement("response");
response.addElement("code").addText("1");
response.addElement("message").addText("未找到该标本信息");
xmlStr = document.asXML();
}
}catch (Exception e){
Document document = DocumentHelper.createDocument();
Element response = document.addElement("response");
response.addElement("code").addText("1");
response.addElement("message").addText(e.getMessage());
xmlStr = document.asXML();
}finally {
LisDBUtil.connColse(conn);
}
return xmlStr;
}
/**
* 通过该接口发送 确认获取（LIS的病人信息和医嘱信息）成功的信息,然后lis设置jydzhxm的status=1表示该组合项目已被金域接收
* @param HospSampleID 条码号”【参数：医院条码】
* @param ItemCode 需确认的组合项目代码 可不传或多个（以逗号隔开）
* @return
*/
@WebMethod
public String AffirmRequest(String HospSampleID, String... ItemCode) {
String sql = "";
String xmlStr = null;
Connection conn = null;
if(ItemCode.length==0){
sql += "update lis_jydzhxm set status=1 where jydid in(select jydid from lis_jyd where bqtmbh = ? ) ";
}else{
String[] itemCodeArray = ItemCode;
sql +=" update lis_jydzhxm set status=1 where jydid in(select jydid from lis_jyd where bqtmbh = ? ) and zhxmid in(";
for(int i=0;i<itemCodeArray.length;i++ ){
if(i==itemCodeArray.length-1){
sql += itemCodeArray[i];
}else{
sql += itemCodeArray[i]+",";
}
}
sql +=")";
}
Document document = null;
try{
conn = LisDBUtil.getLisConnection();
queryRunner.update(conn, sql,HospSampleID );//更新
LisDBUtil.commit(conn);//oracle提交
document = DocumentHelper.createDocument();
Element response = document.addElement("response");
response.addElement("code").addText("0");
response.addElement("message");
xmlStr = document.asXML();
}catch(Exception e){
e.printStackTrace();
try{
LisDBUtil.rollback(conn);
}catch(Exception ee){
ee.printStackTrace();
}finally {
document = DocumentHelper.createDocument();
Element response = document.addElement("response");
response.addElement("code").addText("1");
response.addElement("message").addText("更新数据出错"+e.getMessage());
xmlStr = document.asXML();
}
}finally {
LisDBUtil.connColse(conn);
}
return xmlStr;
}
/**
* 通过该接口回传 结果和报告单
* @param
* @return
*/
@WebMethod
public String UploadLisRepData(String xml) {
String xmlStr=null;
Connection conn = null;
SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
try{
// xml = "<?xml version=\"1.0\" encoding=\"utf-8\"?><Report_Result><Report_Info><ext_lab_code>kingmed</ext_lab_code><lis_Barcode>0000000103</lis_Barcode><ext_Barcode>20190219083</ext_Barcode><ext_checkItem/><pat_name>zz</pat_name><pat_age/><pat_height/><pat_wight/><pat_pre_week/><pat_id>1009</pat_id><pat_bedNo/><pat_tel/><pat_sex>男</pat_sex><pat_birthday>1999-02-18</pat_birthday><pat_ori_name>OP</pat_ori_name><sam_name>血清</sam_name><sam_state/><doctor_name>张云锋</doctor_name><dept_name>体检科</dept_name><clinical_diag/><SampleNumber>0000000103</SampleNumber><blood_time/><ext_check_ID/><ext_receive_time>2019-02-19 10:37:21</ext_receive_time><ext_check_time>2019-02-19 11:06:24</ext_check_time><ext_first_audit_time>2019-02-19 11:13:36</ext_first_audit_time><ext_second_audit_time>2019-02-19 11:15:01</ext_second_audit_time><ext_upload_time/><ext_report_suggestion/><ext_report_remark/><ext_checker>陈燕华</ext_checker><ext_first_audit>陈燕华</ext_first_audit><ext_second_audit>杨双燕</ext_second_audit><ext_intstrmt_name/><ext_lab_name/><ext_report_type>1</ext_report_type><ext_report_code>FZ0000797710185</ext_report_code><requestno/><result_info><result_seq>1</result_seq><ext_combine_code>ZH00913</ext_combine_code><ext_combine_name>甲功五项</ext_combine_name><ext_item_code>41098</ext_item_code><ext_item_name>总三碘甲状腺原氨酸(TT3)</ext_item_name><result>8.00</result><result_unit>nmol/L</result_unit><result_flag>↑</result_flag><result_reference>0.92-2.79</result_reference><result_date>2019-02-19 11:15:01</result_date><result_intstrmt_name/><result_department>15甲功岗位</result_department><result_test_method>化学发光法</result_test_method><result_suggestion/><result_remark/><lis_combine_code>285</lis_combine_code><lis_combine_name>甲功五项</lis_combine_name><lis_item_code>475</lis_item_code><lis_item_name>总三碘甲状腺原氨酸(TT3)，化学发光法</lis_item_name><isreimbu/><reimbudesc/><isdelayed/><delayeddesc/></result_info><result_info><result_seq>2</result_seq><ext_combine_code>ZH00913</ext_combine_code><ext_combine_name>甲功五项</ext_combine_name><ext_item_code>41099</ext_item_code><ext_item_name>总甲状腺素(TT4)</ext_item_name><result>8.00</result><result_unit>nmol/L</result_unit><result_flag>↓</result_flag><result_reference>58.05-140.61</result_reference><result_date>2019-02-19 11:15:01</result_date><result_intstrmt_name/><result_department>15甲功岗位</result_department><result_test_method>化学发光法</result_test_method><result_suggestion/><result_remark/><lis_combine_code>285</lis_combine_code><lis_combine_name>甲功五项</lis_combine_name><lis_item_code>472</lis_item_code><lis_item_name>总甲状腺素(TT4)，化学发光法</lis_item_name><isreimbu/><reimbudesc/><isdelayed/><delayeddesc/></result_info><result_info><result_seq>3</result_seq><ext_combine_code>ZH00913</ext_combine_code><ext_combine_name>甲功五项</ext_combine_name><ext_item_code>41100</ext_item_code><ext_item_name>超敏促甲状腺素(TSH)</ext_item_name><result>3.000</result><result_unit>uIU/ml</result_unit><result_flag/><result_reference>≥18岁||0.550-4.780&12岁-18岁||0.510-4.940&2岁-11岁||0.640-6.270</result_reference><result_date>2019-02-19 11:15:00</result_date><result_intstrmt_name/><result_department>15甲功岗位</result_department><result_test_method>化学发光法</result_test_method><result_suggestion/><result_remark/><lis_combine_code>285</lis_combine_code><lis_combine_name>甲功五项</lis_combine_name><lis_item_code>478</lis_item_code><lis_item_name>超敏促甲状腺素(TSH)，化学发光法</lis_item_name><isreimbu/><reimbudesc/><isdelayed/><delayeddesc/></result_info><result_info><result_seq>4</result_seq><ext_combine_code>ZH00913</ext_combine_code><ext_combine_name>甲功五项</ext_combine_name><ext_item_code>41101</ext_item_code><ext_item_name>游离三碘甲状腺原氨酸(FT3)</ext_item_name><result>6.30</result><result_unit>pmol/L</result_unit><result_flag/><result_reference>3.54-6.47</result_reference><result_date>2019-02-19 11:15:01</result_date><result_intstrmt_name/><result_department>15甲功岗位</result_department><result_test_method>化学发光法</result_test_method><result_suggestion/><result_remark/><lis_combine_code>285</lis_combine_code><lis_combine_name>甲功五项</lis_combine_name><lis_item_code>473</lis_item_code><lis_item_name>游离三碘甲状腺原氨酸(FT3)，化学发光法</lis_item_name><isreimbu/><reimbudesc/><isdelayed/><delayeddesc/></result_info><result_info><result_seq>5</result_seq><ext_combine_code>ZH00913</ext_combine_code><ext_combine_name>甲功五项</ext_combine_name><ext_item_code>41102</ext_item_code><ext_item_name>游离甲状腺素(FT4)</ext_item_name><result>18.00</result><result_unit>pmol/L</result_unit><result_flag/><result_reference>11.50-22.70</result_reference><result_date>2019-02-19 11:15:01</result_date><result_intstrmt_name/><result_department>15甲功岗位</result_department><result_test_method>化学发光法</result_test_method><result_suggestion/><result_remark/><lis_combine_code>285</lis_combine_code><lis_combine_name>甲功五项</lis_combine_name><lis_item_code>474</lis_item_code><lis_item_name>游离甲状腺素(FT4)，化学发光法</lis_item_name><isreimbu/><reimbudesc/><isdelayed/><delayeddesc/></result_info><report_pic><pic_content/><pic_name/><pic_seq>1</pic_seq></report_pic></Report_Info></Report_Result>";
//xml ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Report_Result><Report_Info><ext_lab_code>kingmed</ext_lab_code><lis_Barcode>0000000409</lis_Barcode><ext_Barcode>20190219083</ext_Barcode><ext_checkItem/><pat_name>赵靴</pat_name><pat_age/><pat_height/><pat_wight/><pat_pre_week/><pat_id>944</pat_id><pat_bedNo/><pat_tel/><pat_sex>男</pat_sex><pat_birthday>1999-02-18</pat_birthday><pat_ori_name>OP</pat_ori_name><sam_name>血清</sam_name><sam_state/><doctor_name>张云锋</doctor_name><dept_name>体检科</dept_name><clinical_diag/><SampleNumber>0000000409</SampleNumber><blood_time/><ext_check_ID/><ext_receive_time>2019-02-23 22:11:21</ext_receive_time><ext_check_time>2019-02-23 22:11:24</ext_check_time><ext_first_audit_time>2019-02-23 22:11:36</ext_first_audit_time><ext_second_audit_time>2019-02-23 22:11:01</ext_second_audit_time><ext_upload_time/><ext_report_suggestion/><ext_report_remark/><ext_checker>陈燕华</ext_checker><ext_first_audit>陈燕华</ext_first_audit><ext_second_audit>杨双燕</ext_second_audit><ext_intstrmt_name/><ext_lab_name/><ext_report_type>1</ext_report_type><ext_report_code>FZ0000797710185</ext_report_code><requestno/><result_info><result_seq>1</result_seq><ext_combine_code>ZH00913</ext_combine_code><ext_combine_name>甲功五项</ext_combine_name><ext_item_code>41098</ext_item_code><ext_item_name>总三碘甲状腺原氨酸(TT3)</ext_item_name><result>8.00</result><result_unit>nmol/L</result_unit><result_flag>↑</result_flag><result_reference>0.92-2.79</result_reference><result_date>2019-02-19 11:15:01</result_date><result_intstrmt_name/><result_department>15甲功岗位</result_department><result_test_method>化学发光法</result_test_method><result_suggestion/><result_remark/><lis_combine_code>285</lis_combine_code><lis_combine_name>甲功五项</lis_combine_name><lis_item_code>475</lis_item_code><lis_item_name>总三碘甲状腺原氨酸(TT3)，化学发光法</lis_item_name><isreimbu/><reimbudesc/><isdelayed/><delayeddesc/></result_info><result_info><result_seq>2</result_seq><ext_combine_code>ZH00913</ext_combine_code><ext_combine_name>甲功五项</ext_combine_name><ext_item_code>41099</ext_item_code><ext_item_name>总甲状腺素(TT4)</ext_item_name><result>8.00</result><result_unit>nmol/L</result_unit><result_flag>↓</result_flag><result_reference>58.05-140.61</result_reference><result_date>2019-02-19 11:15:01</result_date><result_intstrmt_name/><result_department>15甲功岗位</result_department><result_test_method>化学发光法</result_test_method><result_suggestion/><result_remark/><lis_combine_code>285</lis_combine_code><lis_combine_name>甲功五项</lis_combine_name><lis_item_code>472</lis_item_code><lis_item_name>总甲状腺素(TT4)，化学发光法</lis_item_name><isreimbu/><reimbudesc/><isdelayed/><delayeddesc/></result_info><result_info><result_seq>3</result_seq><ext_combine_code>ZH00913</ext_combine_code><ext_combine_name>甲功五项</ext_combine_name><ext_item_code>41100</ext_item_code><ext_item_name>超敏促甲状腺素(TSH)</ext_item_name><result>3.000</result><result_unit>uIU/ml</result_unit><result_flag/><result_reference>≥18岁||0.550-4.780&12岁-18岁||0.510-4.940&2岁-11岁||0.640-6.270</result_reference><result_date>2019-02-19 11:15:00</result_date><result_intstrmt_name/><result_department>15甲功岗位</result_department><result_test_method>化学发光法</result_test_method><result_suggestion/><result_remark/><lis_combine_code>285</lis_combine_code><lis_combine_name>甲功五项</lis_combine_name><lis_item_code>478</lis_item_code><lis_item_name>超敏促甲状腺素(TSH)，化学发光法</lis_item_name><isreimbu/><reimbudesc/><isdelayed/><delayeddesc/></result_info><result_info><result_seq>4</result_seq><ext_combine_code>ZH00913</ext_combine_code><ext_combine_name>甲功五项</ext_combine_name><ext_item_code>41101</ext_item_code><ext_item_name>游离三碘甲状腺原氨酸(FT3)</ext_item_name><result>6.30</result><result_unit>pmol/L</result_unit><result_flag/><result_reference>3.54-6.47</result_reference><result_date>2019-02-19 11:15:01</result_date><result_intstrmt_name/><result_department>15甲功岗位</result_department><result_test_method>化学发光法</result_test_method><result_suggestion/><result_remark/><lis_combine_code>285</lis_combine_code><lis_combine_name>甲功五项</lis_combine_name><lis_item_code>473</lis_item_code><lis_item_name>游离三碘甲状腺原氨酸(FT3)，化学发光法</lis_item_name><isreimbu/><reimbudesc/><isdelayed/><delayeddesc/></result_info><result_info><result_seq>5</result_seq><ext_combine_code>ZH00913</ext_combine_code><ext_combine_name>甲功五项</ext_combine_name><ext_item_code>41102</ext_item_code><ext_item_name>游离甲状腺素(FT4)</ext_item_name><result>18.00</result><result_unit>pmol/L</result_unit><result_flag/><result_reference>11.50-22.70</result_reference><result_date>2019-02-19 11:15:01</result_date><result_intstrmt_name/><result_department>15甲功岗位</result_department><result_test_method>化学发光法</result_test_method><result_suggestion/><result_remark/><lis_combine_code>285</lis_combine_code><lis_combine_name>甲功五项</lis_combine_name><lis_item_code>474</lis_item_code><lis_item_name>游离甲状腺素(FT4)，化学发光法</lis_item_name><isreimbu/><reimbudesc/><isdelayed/><delayeddesc/></result_info><report_pic><pic_content/><pic_name/><pic_seq>1</pic_seq></report_pic></Report_Info></Report_Result>";
SAXReader saxReader = new SAXReader();
//Document document = saxReader.read(new File("./src/contact.xml"));
Document doc = saxReader.read(new ByteArrayInputStream(xml.getBytes("UTF-8")));//xml串第一行不能有空格，否则报错
Element rootEle = doc.getRootElement();// 获取根节点
Map map = new HashMap();
List<Map> resultInfoList = new ArrayList<>();
List<Map> reortPicList = new ArrayList<>();
List<Element> Report_Results = rootEle.elements();//获取 根节点（即Report_Result结点）下的元素
for (Element e1 : Report_Results){
List<Element> Report_Infos = e1.elements();//获取 结点Report_Info下的元素
for (Element e2 : Report_Infos) {
if("result_info".equals(e2.getName())){
Map resultInfoMap = new HashMap();
List<Element> result_info = e2.elements();//获取 结点result_info下的元素
for(Element e3:result_info){
resultInfoMap.put(e3.getName(), e3.getText());
}
resultInfoList.add(resultInfoMap);
}else if("report_pic".equals(e2.getName())){
Map reortPicMap = new HashMap();
List<Element> report_pics = e2.elements();//获取 结点report_pic下的元素
for(Element e4:report_pics){
reortPicMap.put(e4.getName(), e4.getText());
}
reortPicList.add(reortPicMap);
}else {
map.put(e2.getName(), e2.getText());
}
}
System.out.println("========================================解析成功！===========================================================");
}
conn = LisDBUtil.getLisConnection();
for(Map e:reortPicList){//保存图片信息
queryRunner.update(conn, "insert into report_pic values(?,?,?,?,?,?)",
null,//id 会自增 故传null
map.get("lis_Barcode"),map.get("ext_Barcode"),
e.get("pic_seq"),
e.get("pic_name"),
e.get("pic_content")
);
}
for(Map e:resultInfoList){//保存报告单相关信息
queryRunner.update(conn,
"insert into report_info values(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?)",
//id会自增 故传null
null,map.get("lis_Barcode"),map.get("ext_Barcode"),new Timestamp(sd.parse(map.get("ext_receive_time").toString()).getTime()),new Timestamp(sd.parse(map.get("ext_second_audit_time").toString()).getTime()),
map.get("ext_checker"),map.get("ext_first_audit"),map.get("ext_second_audit"),map.get("ext_report_code"),e.get("result_seq"),
e.get("ext_combine_code"),e.get("ext_combine_name"),e.get("ext_item_code"),e.get("ext_item_name"),e.get("result"),
e.get("result_unit"),e.get("result_flag"),e.get("result_reference"),new Timestamp(sd.parse(e.get("result_date").toString()).getTime()),e.get("result_intstrmt_name"),
e.get("result_department"),e.get("result_test_method"),e.get("result_suggestion"),e.get("result_remark"),e.get("lis_combine_code"),
e.get("lis_combine_name"),e.get("lis_item_code"),e.get("lis_item_name"),e.get("isreimbu"),e.get("reimbudesc"),
e.get("isdelayed"),e.get("delayeddesc"),e.get("lowrange"),e.get("highrange")
);
String sql = "update lis_ybxmjyjgmx set ckqj='" +e.get("result_reference")+
"',xmmc='" +e.get("lis_item_name")+
"',jyz='" +e.get("result")+
"',wsbz='" +e.get("result_flag")+
"',jyff='" +e.get("result_test_method")+
"' where jyjgid=(select a.jydid from lis_jyd a join lis_jydzhxm b on a.jydid=b.jydid where a.bqtmbh='"+map.get("lis_Barcode")+"'and b.zhxmid='"+e.get("lis_combine_code")+"') and jyxmid='"+e.get("lis_item_code")+"'";
queryRunner.update(conn, sql);
String upJydZTSql = "update lis_jyd set zt='2'where jydid=(select a.jydid from lis_jyd a join lis_jydzhxm b on a.jydid=b.jydid where a.bqtmbh='"+map.get("lis_Barcode")+"'and b.zhxmid='"+e.get("lis_combine_code")+"')";
queryRunner.update(conn, upJydZTSql);
}
LisDBUtil.commit(conn);//oracle提交
Document document = DocumentHelper.createDocument();
Element response = document.addElement("response");
response.addElement("code").addText("0");
response.addElement("message");
xmlStr = document.asXML();
}catch(Exception e){
e.printStackTrace();
try {
LisDBUtil.rollback(conn);
}catch (Exception ee){
ee.printStackTrace();
}finally {
Document document = DocumentHelper.createDocument();
Element response = document.addElement("response");
response.addElement("code").addText("1");
response.addElement("message").addText("报告单解析或保存出错"+e.getMessage());
xmlStr = document.asXML();
}
}finally {
LisDBUtil.connColse(conn);
}
return xmlStr;
}
}在上方的使用实例代码中 因为id会自增所以参数传入null就可以；设置id主键自增：
--建表
CREATE TABLE "REPORT_PIC"
( "ID" NUMBER NOT NULL ENABLE,
"LIS_BARCODE" VARCHAR2(200) NOT NULL ENABLE,
"EXT_BARCODE" VARCHAR2(200),
"PIC_SEQ" NUMBER,
"PIC_NAME" VARCHAR2(200),
"PIC_CONTENT" VARCHAR2(200)
)
--设置主键
ALTER TABLE "REPORT_PIC" ADD CONSTRAINT "PK_REPORT_PIC" PRIMARY KEY ("ID")
--建序列
create sequence SEQ_REPORT_PIC
minvalue 1
maxvalue 100000000000000000000
start with 141
increment by 1
cache 20;
--建触发器(在建好序列的基础上)
create or replace trigger "TRI_REPORT_PIC"
before insert on report_pic
for each row
begin
select SEQ_report_pic.nextval into :new.id from dual;
end;在上方的工具类中，没有设计成单例，每次连接数据库时候都会new 一个ComboPooledDataSource，会导致连接池越滚越多，达到oracle最大限度时候就会连不上oracle数据库，现改成如下单例模式：
package utils;
import com.jeesite.modules.ejserver.impl.EjImpl;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
public class LisDBUtil {
private static LisDBUtil instance;
//c3p0被称为数据库连接池，用来管理数据库的连接
//c3p0连接池的ComboPooledDataSource类
private ComboPooledDataSource dataSource;
private LisDBUtil()throws Exception{
dataSource = new ComboPooledDataSource();
dataSource.setUser("lis");
//dataSource.setPassword("1");
dataSource.setPassword("oracle");
dataSource.setJdbcUrl("jdbc:oracle:thin:@192.168.40.7:1521:orcl");
//dataSource.setJdbcUrl("jdbc:oracle:thin:@127.0.0.1:1521:system");
dataSource.setDriverClass("oracle.jdbc.driver.OracleDriver");
//dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/pjxt?useUnicode=true&characterEncoding=utf-8");
//dataSource.setDriverClass("com.mysql.jdbc.Driver");
dataSource.setMinPoolSize(5);//设置连接池的最小连接数
dataSource.setMaxPoolSize(50);//设置连接池的最大连接数
dataSource.setInitialPoolSize(10);//初始化时获取的连接数，取值应在minPoolSize与maxPoolSize之间。Default: 3
dataSource.setMaxIdleTime(10);//最大空闲时间,10秒内未使用则连接被丢弃。若为0则永不丢弃。Default: 0
dataSource.setAcquireIncrement(5);//当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3
dataSource.setCheckoutTimeout(10000);//连接池用完时客户调用getConnection()后等待获取连接的时间，单位：毫秒
dataSource.setBreakAfterAcquireFailure(false);//为true会导致连接池占满后不提供服务。所以必须为false
dataSource.setIdleConnectionTestPeriod(30);//每30秒检查一次空闲连接，加快释放连接。
}
public static final LisDBUtil getInstance () {
if (instance == null) {
try {
instance = new LisDBUtil();
} catch (Exception e) {
e.printStackTrace();
}
}
return instance;
}
//返回一个连接
public synchronized final Connection getConnection () {
Connection conn = null;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false);//设置自动提交为否
System.out.println("连接成功！");
return conn;
} catch (Exception e) {
e.printStackTrace();
}
return null;
}
/**
* 提交数据
* @throws Exception
*/
public static void commit(Connection conn) throws Exception {
if(null!=conn){
conn.commit();//提交
}
if (null!=conn){
conn.close();//关闭，其实连接并未真正的关闭，而是放回连接池中
}
}
/**
* 回滚
* @throws Exception
*/
public static void rollback( Connection conn) throws Exception {
if (null!=conn){
conn.rollback();//LIS数据回滚
}
if (null!=conn){
conn.close();
}
}
public static void connColse(Connection conn){
if(null!=conn){
try{
conn.close();
}catch(Exception ex){
ex.printStackTrace();
}
}
}
public static void main(String[] args) {
EjImpl ejImpl = new EjImpl();
//String xmlStr = ejImpl.GetLisRequest("0000000103");
//String mxlStr = ejImpl.AffirmRequest("0000000099");
// ejImpl.UploadLisRepData("");
System.out.println();
}
}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
