欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

JDBC调用PKG  

程序员文章站 2022-03-25 21:34:10
...
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;
}
});
}
}