1. 由于每个存储过程的参数不同，可以编写以一个类提出所有操作。
package com.lvhe.core.db;
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import com.lvhe.core.util.DateUtil;
public class PrecedureParam {
private List params = new ArrayList();
private List inout = new ArrayList();
public void push(Object obj, int index, boolean isIn) {
params.add(index, obj);
inout.add(index, isIn);
}
public int size() {
return params.size();
}
public String getContent() {
String cnt = "";
for(int i=0; i
if(i>0) {
cnt = cnt + ",";
}
cnt = cnt + "?";
}
return cnt;
}
public boolean isIn(int index) {
return inout.get(index);
}
public Object getParam(int index) {
return params.get(index);
}
public void setParams(CallableStatement call) throws SQLException {
for(int i=0; i
setParam(call, this.getParam(i), i+1, this.isIn(i));
}
}
public void execute(CallableStatement call) throws SQLException {
setParams(call);
call.execute();
getResult(call);
}
public void getResult(CallableStatement call) throws SQLException {
for(int i=0; i
if(!this.isIn(i)) {
Object obj = this.getParam(i);
if(obj instanceof String) {
params.set(i, call.getString(i+1));
}else if(obj instanceof Integer) {
params.set(i, call.getInt(i+1));
}else if(obj instanceof Date) {
params.set(i, call.getDate(i+1));
}else {
params.set(i, call.getString(i+1));
}
}
}
}
public void setParam(CallableStatement call, Object obj, int index, boolean isIn) throws SQLException {
if(isIn) {
if(obj instanceof String) {
call.setString(index, (String)obj);
}else if(obj instanceof Integer) {
Integer v = (Integer)obj;
call.setInt(index, v.intValue());
}else if(obj instanceof Timestamp) {
call.setTimestamp(index, (Timestamp)obj);
}else if(obj instanceof java.util.Date) {
java.sql.Date sqlDate = DateUtil.toSQL((java.util.Date)obj);
call.setDate(index, sqlDate);
}else if(obj instanceof Date) {
call.setDate(index, (Date) obj);
}else if(obj instanceof Long) {
call.setLong(index, (Long)obj);
}else {
call.setString(index, (String)obj);
}
}else {
if(obj instanceof String) {
call.registerOutParameter(index, java.sql.Types.VARCHAR);
}else if(obj instanceof Integer) {
call.registerOutParameter(index, java.sql.Types.INTEGER);
}else if(obj instanceof Timestamp) {
call.registerOutParameter(index, java.sql.Types.TIMESTAMP);
}else if(obj instanceof Date) {
call.registerOutParameter(index, java.sql.Types.DATE);
}else if(obj instanceof Long) {
call.registerOutParameter(index, java.sql.Types.NUMERIC);
}else {
call.registerOutParameter(index, java.sql.Types.VARCHAR);
}
}
}
public List getParams() {
return params;
}
public void setParams(List params) {
this.params = params;
}
}
2. 调用存储过程：
public void callPrecedure(String methodName, PrecedureParam param) {
Session session = getSession();
CallableStatement call = null;
Connection conn = session.connection();
try {
String preStr = "{Call "+methodName+"("+param.getContent()+")}";
call = conn.prepareCall(preStr);
param.execute(call);
param.getResult(call);
/*int i = call.getInt(13);
System.out.println(i);*/
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
call.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
3. 使用方法：其中true表示输入，false表示输出。0为整数，“”为字符串。如果直接调用存储过程序号是从1开始的，此处从0开始。
//call precedure
PrecedureParam preceParam = new PrecedureParam();
preceParam.push(GUID, 0, true);
preceParam.push(jsonObj.get("Optor"), 1, true);
preceParam.push(DateUtil.toTimestamp(jsonObj.getString("localTime")), 2, true);
preceParam.push( 0, 3, false);
preceParam.push( "", 4, false);
preceParam.push( "", 5, false);
preceParam.push( "", 6, false);
preceParam.push( "", 7, false);
preceParam.push( "", 8, false);
preceParam.push( "", 9, false);
preceParam.push( "", 10, false);
preceParam.push( "", 11, false);
preceParam.push( 0, 12, false);
gridService.callPrecedure("spfee_placeBackupFileMerge", preceParam);
int status = (int) preceParam.getParam(12);
直接调用示例：
public HashMap spfee_placeBackupLogin(String placeCode, String softNameCode,
String softVersion, Timestamp placeSystemTime, String DBType,
String DBBackupPwd,String placeIp) {
Session session = getSession();
CallableStatement call = null;
Connection conn = session.connection(); HashMap value = null; try { call = conn.prepareCall("{Call spfee_placeBackupLogin(?,?,?,?,?,?,?,?,?,?)}"); call.setString(1, placeCode); call.setString(2, softNameCode ); call.setString(3, softVersion); call.setTimestamp(4, placeSystemTime); call.setString(5, placeIp ); call.setString(6, DBType ); call.setString(7, DBBackupPwd ); call.registerOutParameter(8, java.sql.Types.NUMERIC); call.registerOutParameter(9, java.sql.Types.CHAR); call.registerOutParameter(10, java.sql.Types.VARCHAR); call.execute(); int result = call.getInt(8); String secretKey = call.getString(9) ; String gatewayUrl = call.getString(10); System.out.println(result); value = new HashMap<>(); value.put("result", result); value.put("secretKey", secretKey); value.put("gatewayUrl", gatewayUrl); } catch (SQLException e) { e.printStackTrace(); } finally { try { call.close(); } catch (SQLException e) { e.printStackTrace(); } } return value; }
