JDBC调用PKG

package com.ejintai.vms.integration.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import oracle.jdbc.internal.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.nativejdbc.NativeJdbcExtractor;
import org.springframework.orm.ibatis.SqlMapClientCallback;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.support.TransactionSynchronizationManager ;

import com.ejintai.fa.app.integration.BaseDAO;
import com.ejintai.fa.app.integration.DAOException;
import com.ejintai.fa.app.integration.SAOException;
import com.ejintai.vms.common.Constants;
import com.ejintai.vms.dto.card.WebVmsPrnInfoDTO;
import com.ejintai.vms.dto.util.RollBackException;
import com.ejintai.vms.dto.vch.WebVchPrnUploadDTO;
import com.ejintai.vms.dto.vch.WebVmsAppObjInfoDTO;
import com.ejintai.vms.dto.vch.WebVmsWareuserPrnDTO;
import com.ejintai.vms.integration.dao.CardDAO;
import com.ibatis.sqlmap.client.SqlMapExecutor;

@Repository
public class CardDAOImpl extends BaseDAO implements CardDAO {

@Autowired
private NativeJdbcExtractor nativeJdbcExtractor;

@Override
public void confimSale(Map<String, Object> paraMap) throws RollBackException {
CallableStatement proc = null;
String resultMsg = null;
String bachtNO = null;
Connection tempConn = null;
        Connection conn = null;
try {
logger.info("调用【PKG_VCH_INTERFACE_COMMON.P_CONFIM_SALE】存储过程 开始");
tempConn = DataSourceUtils.getConnection(super
.getDataSource());
conn = nativeJdbcExtractor.getNativeConnection(tempConn);
ARRAY retArray = this.getRetDetailArray("TYPE_RETURN_PRN_NO",
"TYPE_RETURN_PRN_NO_ARRAY", paraMap, conn);
proc = conn
.prepareCall("call PKG_VCH_INTERFACE_COMMON.P_CONFIM_SALE( ?, ? ,?, ? ,?, ? ,?, ? ,?, ? ,?, ? )");

proc.setString(1, paraMap.get("p_app_no").toString());
proc.setString(2, paraMap.get("p_vch_type").toString());
proc.setString(3, paraMap.get("p_oper_dpt_cde").toString());
proc.setString(4, paraMap.get("p_app_cnm").toString());
proc.setString(5, paraMap.get("p_app_cnm_ch").toString());
proc.setString(6, paraMap.get("p_app_obj").toString());
proc.setArray(7, retArray);
proc.setString(8, paraMap.get("p_oper_code").toString());
proc.setString(9, paraMap.get("p_nCount").toString());
proc.setString(10, paraMap.get("p_ProsCde").toString());
proc.registerOutParameter(11, OracleTypes.NVARCHAR);
proc.registerOutParameter(12, OracleTypes.NVARCHAR);

proc.execute();
String a = proc.getString(11);

logger.info("调用【PKG_VCH_INTERFACE_COMMON.P_CONFIM_SALE】存储过程 成功");
} catch (Exception e) {
e.getMessage();
logger.error(resultMsg, e);
try {
throw new SAOException(
"调用存储过程【PKG_VCH_INTERFACE_COMMON.P_CONFIM_SALE】出错"+e.getMessage()
, e);
} catch (SAOException e1) {
e1.printStackTrace();
throw new RollBackException(
"调用存储过程【PKG_VCH_INTERFACE_COMMON.P_CONFIM_SALE】出错"
+ e.getMessage());
}
} finally {
if (proc != null) {
try {
proc.close();
proc = null;
} catch (Exception e) {
logger.error("close CallableStatement failed.", e);
}
}

if(TransactionSynchronizationManager.isSynchronizationActive())
            {
                // 说明有事务，不关闭
            }
            else
            {
                if(tempConn != null)
                {
                    try
                    {
                        tempConn.close() ;
                    }
                    catch(SQLException e)
                    {
                        e.printStackTrace();
                        throw new RollBackException(
                                "调用【PKG_VCH_INTERFACE_COMMON.P_CONFIM_SALE】存储过程连接关闭异常："
                                        + e.getMessage());
                    }
                }
            }
}
}

private ARRAY getRetDetailArray(String ora_type, String ora_array,
Map<String, Object> paraMap, Connection oracleconn)
throws SQLException, DAOException {
ARRAY list = null;
logger.info("开始组装自定义结构化参数。");
if (!paraMap.isEmpty()) {
List<Map<String, String>> prnInfoList = (List<Map<String, String>>) paraMap
.get("prnInfo");
StructDescriptor structdesc = new StructDescriptor(ora_type,
oracleconn);
STRUCT[] structs = new STRUCT[prnInfoList.size()];
for (int i = 0; i < prnInfoList.size(); i++) {
Object[] result = new Object[6];
Map<String, String> resMap = prnInfoList.get(i);
result[0] = resMap.get("cBgnPrnNo"); // 起始卡号
result[1] = resMap.get("cEndPrnNo"); // 终止卡号
result[2] = 0; // 金额
result[3] = 0; // 份数
result[4] = null; // 单证类型（不在此作用）
result[5] = null; // 申领单号（不在此作用）
structs[i] = new STRUCT(structdesc, oracleconn, result);
}
ArrayDescriptor arraydesc = new ArrayDescriptor(ora_array,
oracleconn);
list = new ARRAY(arraydesc, oracleconn, structs);
}
return list;
}

@Override
public void dealPrnNoByExSalePassDate(Map<String, Object> map)
throws RollBackException {
CallableStatement proc = null;
String resultMsg = null;
String bachtNO = null;
Connection tempConn = null;
        Connection conn = null;
try {
logger.info("调用【PKG_VCH_INTERFACE_COMMON.P_DEAL_CREATSALE】存储过程 开始");
tempConn = DataSourceUtils.getConnection(super
.getDataSource());
conn = nativeJdbcExtractor.getNativeConnection(tempConn);
ARRAY retArray = this.getRetSaleArray("TYPE_RETURN_PRN_NO",
"TYPE_RETURN_PRN_NO_ARRAY", map, conn);
proc = conn
.prepareCall("call PKG_VCH_INTERFACE_COMMON.P_DEAL_CREATSALE( ?, ? ,?)");

proc.setArray(1, retArray);
proc.setString(2, map.get("operCde").toString());
proc.registerOutParameter(3, OracleTypes.NVARCHAR);

proc.execute();

resultMsg = proc.getString(3);
if(resultMsg != null){
throw new RollBackException(
"调用存储过程【PKG_VCH_INTERFACE_COMMON.P_DEAL_CREATSALE】出错:"
+ resultMsg);
}

logger.info("调用【PKG_VCH_INTERFACE_COMMON.P_DEAL_CREATSALE】存储过程 成功");
} catch (Exception e) {
logger.error(resultMsg, e);
try {
throw new SAOException(
"调用存储过程【PKG_VCH_INTERFACE_COMMON.P_DEAL_CREATSALE】出错"
+ resultMsg, e);
} catch (SAOException e1) {
e1.printStackTrace();
throw new RollBackException(
"调用存储过程【PKG_VCH_INTERFACE_COMMON.P_DEAL_CREATSALE】出错"
+ resultMsg);
}
} finally {
if (proc != null) {
try {
proc.close();
proc = null;
} catch (Exception e) {
logger.error("close CallableStatement failed.", e);
}
}
if(TransactionSynchronizationManager.isSynchronizationActive())
            {
                // 说明有事务，不关闭
            }
            else
            {
                if(tempConn != null)
                {
                    try
                    {
                        tempConn.close() ;
                    }
                    catch(SQLException e)
                    {
                        e.printStackTrace();
                        throw new RollBackException(
                                "调用【PKG_VCH_INTERFACE_COMMON.P_DEAL_CREATSALE】存储过程连接关闭异常："
                                        + e.getMessage());
                    }
                }
            }
}
}

private ARRAY getRetSaleArray(String ora_type, String ora_array,
Map<String, Object> paraMap, Connection oracleconn)
throws SQLException, DAOException {
ARRAY list = null;
logger.info("开始组装自定义结构化参数。");
if (!paraMap.isEmpty()) {
List<WebVmsPrnInfoDTO> dtoList = (List<WebVmsPrnInfoDTO>) paraMap
.get("dtoList");
StructDescriptor structdesc = new StructDescriptor(ora_type,
oracleconn);
STRUCT[] structs = new STRUCT[dtoList.size()];
for (int i = 0; i < dtoList.size(); i++) {
Object[] result = new Object[6];
WebVmsPrnInfoDTO dto = dtoList.get(i);
result[0] = dto.getcBgnPrnNo(); // 起始卡号
result[1] = dto.getcEndPrnNo(); // 终止卡号
result[2] = 0; // 金额
result[3] = 0; // 份数
result[4] = dto.getcVchType(); // 单证类型
result[5] = dto.getcAppNo(); // 申领单号
structs[i] = new STRUCT(structdesc, oracleconn, result);
}
ArrayDescriptor arraydesc = new ArrayDescriptor(ora_array,
oracleconn);
list = new ARRAY(arraydesc, oracleconn, structs);
}
return list;
}

@Override
public List<WebVmsAppObjInfoDTO> findAgentInfoByOrg(String orgCde)
throws RollBackException {
CallableStatement proc = null;
java.sql.ResultSet rs = null;
Connection tempConn = null;
        Connection conn = null;
List<WebVmsAppObjInfoDTO> list = new ArrayList<WebVmsAppObjInfoDTO>();
try {
logger.info("调用【PKG_VCH_COMMON.GETPTLLISTBYDPTCDELIST】存储过程 开始");
tempConn = DataSourceUtils.getConnection(super
.getDataSource());
conn = nativeJdbcExtractor.getNativeConnection(tempConn);
proc = conn
.prepareCall("call PKG_VCH_COMMON.GETPTLLISTBYDPTCDELIST( ?, ? )");

proc.setString(1, orgCde);
proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
proc.execute();

rs = (java.sql.ResultSet) proc.getObject(2);

while (rs.next()) {
WebVmsAppObjInfoDTO dto = new WebVmsAppObjInfoDTO();
if (StringUtils.isNotEmpty(rs.getString("CPrxCde"))) {
dto.setcObjCde(rs.getString("CPrxCde"));
}
if (StringUtils.isNotEmpty(rs.getString("CPrxNme"))) {
dto.setCObjNme(rs.getString("CPrxNme"));
}
list.add(dto);
}
if (rs != null) {
rs.close();
}

logger.info("调用【PKG_VCH_COMMON.GETPTLLISTBYDPTCDELIST】存储过程 成功");
} catch (Exception e) {
try {
throw new SAOException(
"调用存储过程【PKG_VCH_COMMON.GETPTLLISTBYDPTCDELIST】出错", e);
} catch (SAOException e1) {
e1.printStackTrace();
throw new RollBackException(
"调用存储过程【PKG_VCH_COMMON.GETPTLLISTBYDPTCDELIST】出错");
}
} finally {
if (proc != null) {
try {
proc.close();
proc = null;
} catch (Exception e) {
logger.error("close CallableStatement failed.", e);
}
}
if(TransactionSynchronizationManager.isSynchronizationActive())
            {
                // 说明有事务，不关闭
            }
            else
            {
                if(tempConn != null)
                {
                    try
                    {
                        tempConn.close() ;
                    }
                    catch(SQLException e)
                    {
                        e.printStackTrace();
                        throw new RollBackException(
                                "调用【PKG_VCH_COMMON.GETPTLLISTBYDPTCDELIST】存储过程连接关闭异常："
                                        + e.getMessage());
                    }
                }
            }
}
return list;
}

/* (non-Javadoc)
* @see com.ejintai.vms.integration.dao.CardDAO#lockNotifyData()
*/
@Override
public String lockNotifyData(String capptype ) {
String executorId = (String)this.getSqlMapClientTemplate().queryForObject("queryExecutorId");
Map<String,String> paraMap = new HashMap<String,String>();
paraMap.put("executorId", executorId);
paraMap.put("capptype", capptype);
this.getSqlMapClientTemplate().update("lockNotifyData", paraMap);
return executorId;
}

/*
* 批量插入
* (non-Javadoc)
* @see com.ejintai.vms.integration.dao.CardDAO#insertProWareuserInfo(java.util.List)
*/
public void insertProWareuserInfo(final List<WebVmsWareuserPrnDTO> list){
this.getSqlMapClientTemplate().execute(new SqlMapClientCallback<Long>() {
/* (non-Javadoc)
* @see org.springframework.orm.ibatis.SqlMapClientCallback#doInSqlMapClient(com.ibatis.sqlmap.client.SqlMapExecutor)
*/
public Long doInSqlMapClient(SqlMapExecutor executor)
throws SQLException {
executor.startBatch();
long batch = 0;
for(WebVmsWareuserPrnDTO dto: list){
getSqlMapClientTemplate().insert("insertProWareuserInfo", dto);
batch++;
if(batch == Constants.BATCH_COUNT) {
executor.executeBatch();
batch = 0;
}
}
executor.executeBatch();
return 0L;
}
});
}

/* (non-Javadoc)
* @see com.ejintai.vms.integration.dao.CardDAO#deleteUploadData(java.util.List)
*/
@Override
public void deleteUploadData(final List<WebVchPrnUploadDTO> list) {
this.getSqlMapClientTemplate().execute(new SqlMapClientCallback<Long>() {
/* (non-Javadoc)
* @see org.springframework.orm.ibatis.SqlMapClientCallback#doInSqlMapClient(com.ibatis.sqlmap.client.SqlMapExecutor)
*/
public Long doInSqlMapClient(SqlMapExecutor executor)
throws SQLException {
executor.startBatch();
long batch = 0;
for(WebVchPrnUploadDTO dto: list){
if("2".equals(dto.getCuploadstatus())){
getSqlMapClientTemplate().insert("deleteUploadData", dto);
batch++;
if(batch == Constants.BATCH_COUNT) {
executor.executeBatch();
batch = 0;
}
}
}
executor.executeBatch();
return 0L;
}
});
}

/* (non-Javadoc)
* @see com.ejintai.vms.integration.dao.CardDAO#insertUploadData(java.util.List)
*/
@Override
public void insertUploadData(final List<WebVchPrnUploadDTO> list) {
this.getSqlMapClientTemplate().execute(new SqlMapClientCallback<Long>() {
/* (non-Javadoc)
* @see org.springframework.orm.ibatis.SqlMapClientCallback#doInSqlMapClient(com.ibatis.sqlmap.client.SqlMapExecutor)
*/
public Long doInSqlMapClient(SqlMapExecutor executor)
throws SQLException {
executor.startBatch();
long batch = 0;
for(WebVchPrnUploadDTO dto: list){
if("2".equals(dto.getCuploadstatus())){
getSqlMapClientTemplate().insert("insertUploadData", dto);
batch++;
if(batch == Constants.BATCH_COUNT) {
executor.executeBatch();
batch = 0;
}
}
}
executor.executeBatch();
return 0L;
}
});
}

/* (non-Javadoc)
* @see com.ejintai.vms.integration.dao.CardDAO#updateUploadData(java.util.List)
*/
@Override
public void updateUploadData(final List<WebVchPrnUploadDTO> list) {
this.getSqlMapClientTemplate().execute(new SqlMapClientCallback<Long>() {
/* (non-Javadoc)
* @see org.springframework.orm.ibatis.SqlMapClientCallback#doInSqlMapClient(com.ibatis.sqlmap.client.SqlMapExecutor)
*/
public Long doInSqlMapClient(SqlMapExecutor executor)
throws SQLException {
executor.startBatch();
long batch = 0;
for(WebVchPrnUploadDTO dto: list){
getSqlMapClientTemplate().insert("updateUploadData", dto);
batch++;
if(batch == Constants.BATCH_COUNT) {
executor.executeBatch();
batch = 0;
}
}
executor.executeBatch();
return 0L;
}
});
}
}