/**
|
* <p>Title:</p>
|
* <p>Description:</p>
|
* <p>Copyright: Copyright (C) 2011 </p>
|
* <p>Company: VCI </p>
|
* @author Bear
|
* @time 2011-8-22
|
* @version 1.0
|
*/
|
package com.vci.server.base.persistence.dao;
|
|
import java.sql.CallableStatement;
|
import java.sql.Connection;
|
import java.sql.ResultSet;
|
import java.sql.SQLException;
|
import java.util.Iterator;
|
import java.util.LinkedHashMap;
|
|
import com.vci.common.log.ServerWithLog4j;
|
|
|
public class JDBCTemplate {
|
|
private Object res = null;
|
private Connection cn = null;
|
private ResultSet rst = null;
|
private CallableStatement cst = null;
|
private String sql = "";
|
private JDBCCallback callback = null;
|
|
/**
|
* 执行存储过程或函数
|
* @param callback 回调对象
|
* @return
|
* @throws Exception
|
*/
|
public Object run(JDBCCallback callback) throws JDBCException{
|
this.callback = callback;
|
if(callback == null){
|
return res;
|
}
|
try{
|
cn = callback.getCn();
|
if(cn == null){
|
return res;
|
}
|
sql = callback.getSql();
|
cst = cn.prepareCall(sql);
|
Object[] params = callback.getParams();
|
JDBCRunType runType = callback.getRunType();
|
int i = 1;
|
// 函数的第一个参数是返回值
|
if(runType == JDBCRunType.Function){
|
cst.registerOutParameter(1, callback.getReturnSqlType());
|
i = 2;
|
}
|
// 依次设置参数值
|
if(params != null){
|
for(Object val : params){
|
cst.setObject(i++, val);
|
}
|
}
|
int refOutParamIndex = i;
|
// 设置引用输出游标参数
|
if(runType == JDBCRunType.Procedure && callback.isHasRefCursorOut()){
|
// oracle.jdbc.OracleTypes.CURSOR = -10
|
cst.registerOutParameter(refOutParamIndex, -10);
|
// add by xchao 2012.12.13 完善存储过程对输出参数的支持
|
registerOutParameter();
|
}
|
// 数据处理
|
// 是函数时，直接取函数的返回值作为当前函数的返回值
|
if(runType == JDBCRunType.Function){
|
// add by xchao 2012.12.13 完善存储过程对输出参数的支持
|
registerOutParameter();
|
// 执行
|
rst = cst.executeQuery();
|
res = cst.getObject(1);
|
// add by xchao 2012.12.13 完善存储过程对输出参数的支持
|
readOutParamValueToMap();
|
}
|
// 是存储过程中时，如果有引用游标输出，则调用回调处理，否则
|
else if(runType == JDBCRunType.Procedure){
|
// 执行
|
cst.execute();
|
// add by xchao 2012.12.13 完善存储过程对输出参数的支持
|
readOutParamValueToMap();
|
if(callback.isHasRefCursorOut()){
|
rst = (ResultSet)cst.getObject(refOutParamIndex);
|
res = callback.execute(rst);
|
}
|
} else if(runType == JDBCRunType.SQL){
|
rst = cst.executeQuery();
|
res = callback.execute(rst);
|
}
|
// cn.commit();
|
}catch(SQLException e){
|
e.printStackTrace();
|
try {
|
cn.rollback();
|
} catch (Throwable t) {
|
}
|
ServerWithLog4j.logger.error("↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓");
|
ServerWithLog4j.logger.error(e);
|
ServerWithLog4j.logger.error(e.getClass().getName() + ":StackTrace", e);
|
ServerWithLog4j.logger.error("↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑");
|
throw new JDBCException(e);
|
}finally{
|
try{
|
if(rst != null) rst.close();
|
if(cst != null) cst.close();
|
// 根据参数选项，决定要不要关闭连接对象
|
// 如果此连接来自于与Session连接池中的Connection，则不应该关闭连接
|
// 如果此连接来自与DB创建的新连接（无连接池或不是从连接池中取出的连接），则需要即时关闭
|
// 对于在启用了Hiberate的应用中，不需要即时关闭连接
|
if(callback.isCloseConnectionInFinally()){
|
if(cn != null) cn.close();
|
}
|
}catch(SQLException e){
|
throw new RuntimeException(e);
|
}
|
}
|
return res;
|
}
|
|
/**
|
* 设置输出参数、类型
|
* @throws SQLException
|
*/
|
private void registerOutParameter() throws SQLException{
|
// add by xchao 2012.12.13 完善存储过程对输出参数的支持
|
if(callback.getOutParamTypeMaps() != null){
|
Iterator<Integer> keys = callback.getOutParamTypeMaps().keySet().iterator();
|
while(keys.hasNext()){
|
Integer key = keys.next();
|
cst.registerOutParameter(key, callback.getOutParamTypeMaps().get(key));
|
}
|
}
|
}
|
/**
|
* 读取输出参数数据值
|
* @throws SQLException
|
*/
|
private void readOutParamValueToMap() throws SQLException{
|
// add by xchao 2012.12.13 完善存储过程对输出参数的支持
|
if(callback.getOutParamTypeMaps() != null){
|
LinkedHashMap<Integer, Object> outParamValueMaps = new LinkedHashMap<Integer, Object>();
|
Iterator<Integer> keys = callback.getOutParamTypeMaps().keySet().iterator();
|
while(keys.hasNext()){
|
Integer key = keys.next();
|
Object value = cst.getObject(key);
|
outParamValueMaps.put(key, value);
|
}
|
callback.setOutParamValueMaps(outParamValueMaps);
|
}
|
}
|
}
|
