lizihong
- 浏览: 30728 次
- 性别:
- 来自: 深圳
-
文章分类
- 全部博客 (47)
- MyEclipse集成本地Tomcat (2)
- JSP指令元素 (1)
- JSP动作元素 (1)
- JSP内置对象 (1)
- JSP和JavaBean (1)
- EL表达式 (1)
- JSTL标签库 (1)
- Servlet开发基础 (1)
- Filter开发 (1)
- Listener开发 (1)
- DAO设计模式 (1)
- MVC (1)
- JAVA面试智力题 (1)
- ORACLE 数据库 (4)
- JAVA开发应用 (1)
- web前端 (2)
- 数据库 (3)
- SVN更新的时候前面的U与G是什么意思? (1)
- oracle通过约束名查看表名的方法 (1)
- TOMCAT 配置 (1)
- 表空间使用及修改大小 (1)
- Oracle function (1)
- 数据库、DBLINK (1)
- Spring AOP (2)
最新评论
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;
}
});
}
}
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;
}
});
}
}
分享到：
发表评论
相关推荐
调用存储过程的Java API主要是通过JDBC（Java Database Connectivity），它是Java平台的标准接口，用于与各种数据库进行交互。以下是调用包含游标返回的Oracle存储过程的基本步骤： 1. **连接数据库**： 使用`...
1. **JDBC驱动**：确保项目中已经添加了Oracle的JDBC驱动（如ojdbc8.jar）。 2. **数据库连接**：配置好数据库连接信息，包括URL、用户名和密码等。 3. **存储过程/函数**：在Oracle数据库中创建并测试好存储过程或...
本文将深入探讨如何使用JDBC（Java Database Connectivity）API来调用Oracle数据库的存储过程。 首先，确保你的项目中已经包含了Oracle JDBC驱动，通常为ojdbc.jar或ojdbc6.jar。这个驱动允许Java应用程序与Oracle...
Oracle调用Java代码过程是一种将Java程序集成到Oracle数据库中的技术，主要应用于处理特定的业务逻辑，例如在本例中，是为了访问异构数据库（如DB2）的数据。以下是详细的步骤和注意事项： **步骤一：加载JDBC驱动*...
在Oracle数据库中，存储过程是预编译的SQL和PL/SQL代码集合，可以用于执行复杂的业务逻辑。本文将探讨如何创建一个存储过程，将行...同时，Java的JDBC调用使得应用程序能够方便地与数据库进行交互，获取处理后的结果。
以下是一个使用JDBC调用Oracle存储过程的示例： ```java CallableStatement cs = connection.prepareCall("{call page_pkg.get_paged_data(?,?,?)}"); cs.setInt(1, pageNum); // 设置页码 cs.setInt(2, pageSize);...
然后，通过 JDBC 操纵这些类，以 OracleObject 方式调用存储过程。在调用存储过程时，使用 ParamValuePairList 集合管理输入和输出参数。当存储过程返回 OracleObject 时，需要对原生的 JDBC 方法进行调整，因为原生...
10. **JDBC访问PL/SQL程序**：Java应用程序可以通过JDBC（Java Database Connectivity）接口调用PL/SQL过程和函数，从而实现Java与Oracle数据库之间的交互。 通过学习和掌握这些概念，开发者可以编写高效、可靠的PL...
3. **在JSP页面或Servlet中使用数据库连接**：可以在JSP页面或Servlet中调用`DBHelper.getConn()`来获取连接。例如，在Servlet中： ```java Connection conn = DBHelper.getConn(); // 使用连接进行数据库操作.....
- 示例：`CREATE OR REPLACE PACKAGE pkg_orders AS PROCEDURE process_order(order_id IN NUMBER); END;` - **实现程序包**：`CREATE OR REPLACE PACKAGE BODY <包名> AS <实现区> END;` ### 11. 触发器 - **创建...
rapid-framework是一个以spring为核心的项目脚手架(或者称为胶水框架)，框架将各个零散的框架(struts,strust2,springmvc,hibernate,ibatis,spring_jdbc,flex)搭建好，并内置一个代码生成器，辅助项目开发,可以生成...
Oracle本身并不直接支持读取Excel格式，因此可能需要借助第三方库，如Oracle的JDBC驱动（如果使用Java存储过程）或者通过操作系统命令调用像`libreoffice`或`openoffice`这样的工具来将Excel转换为CSV格式，然后用...