Two examples of Java calling stored procedures and call function functions in Oracle

Calling a stored procedure in Java is similar to calling a function function but not the same

1. Adjust the stored procedure

There are several parameters, with several placeholders in the () of the stored procedure

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); Get output Result set parameter
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 function

The first argument is a cursor, replaced by a placeholder. = Furthermore, call must be lowercase and cannot be called, and the placeholder in the function () represents the parameters that the function needs to pass in

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 ("---------date------" +prkrq_q.tostring () + "----------------");
System.out.println ("--------date-------" +prkrq_z.tostring () + "----------------");
System.out.println ("--------ba_id-------" +pba_id+ "----------------");
SYSTEM.OUT.PRINTLN ("--------Legal person code-------" +pfrdm+ "----------------");
System.out.println ("--------Levy Project Code-------" +pzsxm_dm+ "----------------");
Connection conn = null;
ResultSet rs = null;
callablestatement proc = null;
try {
Get a connection
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";
}