java中调用oracle存储过程

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<Object> params = new ArrayList<Object>();
private List<Boolean> inout = new ArrayList<Boolean>();

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<size(); 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<size(); 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<size(); 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<Object> getParams() {
return params;
}
public void setParams(List<Object> 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<String, Object> 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<String, Object> 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;
}