在项目开发中,我们经常用到存储过程来实现某些业务功能:
下面是简单的后台java 代码调用 oracle 11g 或12c数据库的中存储过程的方法
本人用到如下的 方法:
第一种
String stmt = "BEGIN XxXX(?,?,?,?); END;";
java.sql.CallableStatement st = null;
String returnString = "";
try {
st = am.getDBTransaction().createCallableStatement(stmt, 0);
st.setString(1, startNj);
st.setString(2, endNj);
st.setString(3, (new SimpleDateFormat("yyyy-MM-dd HH:mm:ss")).format(new Date()));
st.registerOutParameter(4, Types.VARCHAR);
st.executeUpdate();
returnString = st.getString(4);
if (returnString != null && !returnString.contains(endNj)) { //未查到数据。
JSFUtils.addFacesErrorMessage(returnString);
am.getDBTransaction().rollback();
go = "cancel";
} else {
am.getDBTransaction().commit();
go = "ok";
}
} catch (java.sql.SQLException s) {
am.getDBTransaction().rollback();
s.printStackTrace();
throw new oracle.jbo.JboException(s);
} finally {
try {
if (st != null) {
st.close();
}
} catch (java.sql.SQLException s2) {
throw new oracle.jbo.JboException(s2);
}
}
第二种:
(XXXAMImpl) am = (XXXAMImpl) AMUtils.getAm(XXX);
DBTransaction db = am.getDBTransaction();
Statement st = db.createStatement(0);
Connection conn = null;
try {
conn = st.getConnection(); //获取数据库的链接
//调用存储过程
String proname = proname = "{ call XXXX(?,?,?,?,?,?)}";
CallableStatement stmt = conn.prepareCall(proname);
// 装订数组
stmt.setObject(1, xx1);
stmt.setObject(2, xx2);
stmt.setObject(3, xx3);
stmt.setObject(4, xx4);
stmt.setObject(5, xx5);
stmt.setObject(6, xx6);
stmt.execute();
} catch (SQLException e) {
am.getTransaction().rollback();
JSFUtils.addFacesInformationMessage("出现异常!");
} finally {
if (conn != null) {
conn.close();
}
if (st != null) {
st.close();
}
}