最近在做项目期间,oracle存储过程需要得比较多,在网上找了很多关于jfinal调用oracle的存储过程方法，感觉都不是很好，借鉴大牛们的思想，自己写了一个较为通用的ICallback接口实现，以下是代码:
public class TestICallback implements ICallback {
private String sql;
private Object[] paras;
private String proc_name;
private List<Record> ParamAttrs;
private Kv result = Kv.create();
private Kv result_index = Kv.create();
private Kv result_name = Kv.create();
public TestICallback(String proc_name, Kv proc_param) {
this.sql = "{call " + Db.getSqlPara(proc_name, proc_param).getSql() + " }";
this.paras = Db.getSqlPara(proc_name, proc_param).getPara();
this.proc_name = getProcName(Db.getSqlPara(proc_name, proc_param).getSql());
this.ParamAttrs = getParamAttrs(this.proc_name);
}
@SuppressWarnings("unchecked")
public Object call(Connection conn) throws SQLException {
if (ParamAttrs.size() == paras.length) {
CallableStatement proc = conn.prepareCall(sql);
for (int i = 0; i < paras.length; i++) {
if (ParamAttrs.get(i).getStr("IN_OUT").equals("IN")) {
proc.setObject(i + 1, paras[i]);
} else {
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("CHAR")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.CHAR);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("DATE")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.DATE);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("FLOAT")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.FLOAT);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("LONG")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.NUMBER);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("NCHAR")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.NCHAR);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("NUMBER")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.NUMBER);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("NVARCHAR2")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.NVARCHAR);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("VARCHAR2")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.NVARCHAR);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("REF")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.REF);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("REF CURSOR")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.CURSOR);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("TABLE")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.PLSQL_INDEX_TABLE);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("TIME")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.TIME);
}
if (ParamAttrs.get(i).getStr("DATA_TYPE").equals("TIMESTAMP")) {
proc.registerOutParameter(i + 1, oracle.jdbc.OracleTypes.TIMESTAMP);
}
result_index.set(result_index.size(), i + 1);
result_name.set(result_name.size(), ParamAttrs.get(i).get("ARGUMENT_NAME"));
}
}
proc.execute();
for (int j = 0; j < result_index.size(); j++) {
if (ParamAttrs.get(result_index.getInt(j) - 1).getStr("DATA_TYPE").equals("REF CURSOR")) {
List<Record> results = new ArrayList<Record>();
ResultSet rs = null;
ResultSetMetaData rsmd = null;
rs = (ResultSet) proc.getObject(result_index.getInt(j));
if (rs != null) {
rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
String[] labelNames = new String[columnCount + 1];
int[] types = new int[columnCount + 1];
buildLabelNamesAndTypes(rsmd, labelNames, types);
while (rs.next()) {
Record record = new Record();
CPI.setColumnsMap(record, DbKit.getConfig().getContainerFactory().getColumnsMap());
Map<String, Object> columns = record.getColumns();
for (int i=1; i<=columnCount; i++) {
Object value;
if (types[i] < Types.DATE) {
value = rs.getObject(i);
} else {
if (types[i] == Types.TIMESTAMP) {
value = rs.getTimestamp(i);
} else if (types[i] == Types.DATE) {
value = rs.getDate(i);
} else if (types[i] == Types.CLOB) {
value = ModelBuilder.me.handleClob(rs.getClob(i));
} else if (types[i] == Types.NCLOB) {
value = ModelBuilder.me.handleClob(rs.getNClob(i));
} else if (types[i] == Types.BLOB) {
value = ModelBuilder.me.handleBlob(rs.getBlob(i));
} else {
value = rs.getObject(i);
}
}
columns.put(labelNames[i], value);
}
results.add(record);
}
}
result.set(result_name.get(j),results);
} else {
result.set(result_name.get(j), proc.getObject(result_index.getInt(j)));
}
}
return proc;
} else {
return null;
}
}
public Kv getResult() {
return result;
}
private String getProcName(String str) {
if (str.contains("(")) {
str = str.substring(0, str.indexOf("(")).trim();
} else {
str = str.trim();
}
return str;
}
/* 由于是oracle 打算采用这种方式获取参数与顺序
#sql("getParamAttr")
SELECT DD.POSITION,
DD.DATA_TYPE,
dd.argument_name,
DD.in_out
FROM ALL_ARGUMENTS dd
where upper(dd.object_name) = upper(#para(procName))
or upper(DD.OWNER||'.'||dd.object_name) = upper(#para(procName))
order by dd.position
#end
*/
private List<Record> getParamAttrs(String procName) {
return Db.find(Db.getSqlPara("getParamAttr", Kv.by("procName", procName)));
}
public void buildLabelNamesAndTypes(ResultSetMetaData rsmd, String[] labelNames, int[] types) throws SQLException {
for (int i=1; i<labelNames.length; i++) {
labelNames[i] = rsmd.getColumnLabel(i);
types[i] = rsmd.getColumnType(i);
}
}
}调用的时候，我用的是:
public class TextExec {
public static Object execute(String proc_name,Kv proc_param){
TestICallback callback = new TestICallback(proc_name,proc_param);
Db.execute(callback);
return callback.getResult();
}
}取数据的时候可以如下取数据:
/*
#sql("TestSql")
blade.pro_test(#para(i_id),#para(s_value),#para(o_id),#para(o_lst))
#end
*/
public class TestController extends Controller {
public void index() {
Kv con = Kv.by("i_id", "333").set("s_value", "lsadf").set("o_id", new Object()).set("o_lst", new Object());
Kv result = (Kv) TextExec.execute("TestSql", con);
@SuppressWarnings("unchecked")
List<Record> records = (List<Record>) (result.containsKey("MYCUR") ? result.get("MYCUR") : null);
setAttr("Users", records);
render(new XmlRender("users.xml"));
}
}虽然不完善，但是勉强够用，权当备忘了。
