JAVA calls database Stored Procedures

The following describes how JAVA calls the ORACLE database stored procedure.

ConnUtils Connection Tool class: used to obtain connections and release resources

Package com. ljq. test;

Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. ResultSet;
Import java. SQL. SQLException;
Import java. SQL. Statement;

/**
* Connection Tool
*
* The ConnUtils class declared as a final class indicates that this class cannot be inherited.
*
* @ Author jiqinlin
*
*/
Public final class ConnUtils {
Private static String url = "jdbc: oracle: thin: @ localhost: 1521: orcl ";
Private static String user = "test ";
Private static String password = "test ";

/**
* It indicates that you can only access this type through static or Singleton mode.
*/
Private ConnUtils (){
}

// Register the driver (only once)
Static {
Try {
Class. forName ("oracle. jdbc. driver. OracleDriver ");
} Catch (ClassNotFoundException e ){
Throw new ExceptionInInitializerError (e );
}
}

/**
* Get the Connection object
*
* @ Return
* @ Throws SQLException
*/
Public static Connection getConnection () throws SQLException {
Return DriverManager. getConnection (url, user, password );
}

/**
* Release resources
*
* @ Param rs
* @ Param st
* @ Param conn
*/
Public static void free (ResultSet rs, Statement st, Connection conn ){
Try {
If (rs! = Null)
Rs. close ();
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
Try {
If (st! = Null)
St. close ();
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
If (conn! = Null)
Try {
Conn. close ();
} Catch (SQLException e ){
E. printStackTrace ();
}
}
}
}
}

Code used to create a stored procedure for the output parameter:

-- Output parameter Stored Procedure
Create or replace procedure stu_proc (v_name OUT VARCHAR2)
BEGIN
SELECT o. sname INTO v_name FROM student o where o. id = 2;
END;

Use java to call the stored procedure for bringing out parameters

Package com. ljq. test;

Import java. SQL. CallableStatement;
Import java. SQL. Connection;
Import java. SQL. SQLException;
Import java. SQL. Types;

Public class ProceTest {

Public static void main (String [] args) throws Exception {
Connection conn = null;
CallableStatement statement = null;
String SQL = "{call stu_proc (?)} ";
Try {
Conn = ConnUtils. getConnection ();
Statement = conn. prepareCall (SQL );
Statement. registerOutParameter (1, Types. VARCHAR );
Statement.exe cuteUpdate ();
// Output: lisi
String sname = statement. getString (1 );
System. out. println (sname );
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
ConnUtils. free (null, statement, conn );
}
}

}

Create a stored procedure code with Input and Output Parameters

-- Stored procedure with Input and Output Parameters
Create or replace procedure stu_proc (v_id in number, v_name OUT VARCHAR2)
BEGIN
SELECT o. sname INTO v_name FROM student o where o. id = v_id;
END;

Use JAVA to call the stored procedure with Input and Output Parameters

Package com. ljq. test;

Import java. SQL. CallableStatement;
Import java. SQL. Connection;
Import java. SQL. SQLException;
Import java. SQL. Types;

Public class ProceTest {

Public static void main (String [] args) throws Exception {
Connection conn = null;
CallableStatement statement = null;
String SQL = "{call stu_proc (?, ?)} ";
Try {
Conn = ConnUtils. getConnection ();
Statement = conn. prepareCall (SQL );
Statement. setInt (1, 1 );
Statement. registerOutParameter (2, Types. VARCHAR );
Statement.exe cuteUpdate ();
// Output: zhangsan
String sname = statement. getString (2 );
System. out. println (sname );
} Catch (SQLException e ){
E. printStackTrace ();
} Finally {
ConnUtils. free (null, statement, conn );
}
}

}