在java中调用存储过程与调用function函数类似但不相同
1.调存储过程
有几个参数,用几个占位符在存储过程的()中
public static ArrayList Prc_Page(PageInfo page) {
ArrayList list = new ArrayList();
Map mp;
Session s = null;
Connection conn = null;
ResultSet rs = null;
CallableStatement proc = null;
try {
s = HibernateSessionFactory.getSession();
conn = s.connection();
proc = conn.prepareCall("{call pages.prc_page(?,?,?,?,?,?,?,?,?)}");
proc.setString(1, page.getP_tableName());
proc.setString(2, page.getP_strWhere());
proc.setString(3, page.getP_orderColumn());
proc.setString(4, page.getP_orderStyle());
proc.setInt(5, page.getP_curPage());
proc.setInt(6, page.getP_pageSize());
proc.registerOutParameter(7, OracleTypes.NUMBER);
proc.registerOutParameter(8, OracleTypes.NUMBER);
proc.registerOutParameter(9, OracleTypes.CURSOR);
proc.execute();
// page.setP_totalRecords(proc.getInt("p_totalRecords"));
// page.setP_totalPages(proc.getInt("p_totalPages"));
// list = (ArrayList) proc.getObject("v_cur");
page.setP_totalRecords(proc.getInt(7));
page.setP_totalPages(proc.getInt(8));
rs = ((OracleCallableStatement) proc).getCursor(9); // 得到输出结果集参数
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
while (rs.next()) {
mp = new HashMap(numberOfColumns);
for (int r = 1; r < numberOfColumns; r++) {
mp.put(rsmd.getColumnName(r), rs.getObject(r));
}
list.add(mp);
}
return list;
} catch (SQLException ex) {
ex.printStackTrace();
return list;
} catch (Exception ex2) {
ex2.printStackTrace();
return list;
} finally {
try {
if (proc != null) {
proc.close();
}
if (rs != null) {
rs.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException ex1) {
ex1.printStackTrace();
}
}
}
2.调用函数
第一个参数为游标,用占位符代替,?= 而且,call 必须小写,不能为CALL ,,函数()中的占位符表示的是函数所需要传入的参数
public String getQynsxx(int start, int limit, Map<String, Object> param) {
String pBA_ID = (String) param.get("pBA_ID");
String pFRDM = (String) param.get("pFRDM");
String prkrq_q = (String) param.get("prkrq_q");
String prkrq_z = (String) param.get("prkrq_z");
String pzsxm_dm = (String) param.get("pzsxm_dm");
System.out.println("---------日期起------"+prkrq_q.toString()+"----------------");
System.out.println("--------日期止-------"+prkrq_z.toString()+"----------------");
System.out.println("--------BA_ID-------"+pBA_ID+"----------------");
System.out.println("--------法人代码-------"+pFRDM+"----------------");
System.out.println("--------征收项目代码-------"+pzsxm_dm+"----------------");
Connection conn = null;
ResultSet rs = null;
CallableStatement proc = null;
try {
//获得连接
Class.forName("oracle.jdbc.driver.OracleDriver");
conn= dao.getMyDataSource().getConnection();
proc = conn.prepareCall("{? = call FUC_SWN_GET_QYNSXX(?,?,?,?,?)}");
proc.registerOutParameter(1, OracleTypes.CURSOR);
proc.setString(2, pBA_ID);//BA_ID
proc.setString(3, pFRDM);
proc.setString(4, prkrq_q);
proc.setString(5, prkrq_z);
proc.setString(6, pzsxm_dm);
proc.execute();
// page.setP_totalRecords(proc.getInt("p_totalRecords"));
// page.setP_totalPages(proc.getInt("p_totalPages"));
// list = (ArrayList) proc.getObject("v_cur");
// String testSql = proc.getString(5);
rs=(ResultSet)proc.getObject(1);
int i =0;
StringBuffer result = new StringBuffer();
StringBuffer rjson = new StringBuffer();
while(rs.next()){
i++;
// System.out.println(rs.getString("FRDM")+"---"+rs.getString("TYSHXYDM")+"--"+rs.getString("QYMC")+"--"+rs.getString("QYZT"));
rjson.append("{");
rjson.append("\"ND\":\""+rs.getInt("ND")+"\",");
rjson.append("\"NSE\":\""+rs.getString("NSE")+"\",");
rjson.append("\"ZSXM\":\""+rs.getString("ZSXM")+"\"");
rjson.append("},");
}
if(i!=0){
rjson.deleteCharAt(rjson.length()-1);
}
result.append("{\"totalcount\":\""+i+"\",\"list\":[");
result.append(rjson);
result.append("]}");
return result.toString();
}
catch(Exception e)
{
e.printStackTrace();
}
finally
{
if (proc != null) {
try
{
proc.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
if (conn != null) {
try
{
conn.close();
} catch (SQLException e)
{
e.printStackTrace();
}
}
}
return "wrong";
}