Java execution of stored procedures and functions (web basic learning notes 14), stored procedure learning notes

Java execution of stored procedures and functions (web basic learning notes 14), stored procedure learning notes
I. Overview

To execute the stored procedure, we should use the CallableStatement interface.

The CallableStatement interface inherits from the PreparedStatement interface. Therefore, the CallableStatement Interface contains all the methods defined by the Statement interface and the PreparedStatement interface, but not all methods are used. The main methods used are as follows:

Common CallableStatement methods:

Return type	Method Signature	Description
Boolean	Execute ()	Execute the SQL statement. If the first result is a ResultSet pair Returns true if the first result is an update count or no If there is a result, false is returned.
Void	RegisterOutParameter (int parameterIndex, int sqlType)	In order, parameterIndex registers the OUT parameter The JDBC Type is sqlType, and sqlType is a constant in the Types class.
Type	GetType (int parameterIndex)	Obtain the value of the specified JDBC parameter based on the parameter number. First The parameter is 1, and the second parameter is 2.

We can use the execute () method to execute the stored procedure. CallableStatement provides a unified standard form for all databases to call stored procedures. Therefore, you will see that the syntax for calling stored procedures using execute () is different from that in Oracle.

To obtain the returned values of stored procedures or functions, we need to use the registerOutParameter () method to register the returned parameters as the JDBC Type. The first parameter of the registerOutParameter () method is the serial number of the parameter. The first parameter is 1, the second parameter is 2, and so on. The second parameter requires an int value to mark the JDBC Type. We can set this parameter using constants in the java. SQL. Types class. Such as VARCHAR and DOUBLE. If the type is not enough, you can also find the appropriate type constant from the driver of a specific database. If a stored procedure or function has a return value, this method must be called. Otherwise, the returned value cannot be obtained, and an exception may occur.

The CallableStatement interface defines many get methods to obtain the values returned by stored procedures. You can use different get methods, such as getInt () and getString (), depending on the value type () and getDouble.
Let's take a look at the syntax format of executing stored procedures and functions using the CallableStatement interface.

Stored procedure: {call <procedure-name> [(<arg1>, <arg2>,...)]}
Function :{? = Call <procedure-name> [(<arg1>, <arg2>,...)]}

If you want to call a stored procedure, use the first syntax, that is, the syntax with no question mark at the beginning. The call is followed by the process name,
If no parameter exists, parentheses can be omitted.

If you want to call a function, use the second syntax with a question mark and an equal sign at the beginning. In fact, this question mark is a placeholder. This question mark is always the first placeholder to call the function. The other part has the same syntax as the process.

Ii. CallableStatement executes the Stored Procedure 2.1 and creates a base class

Package com. pb. emp. dao; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. preparedStatement; import java. SQL. resultSet; import java. SQL. SQLException; import com. pb. emp. untily. configManager; public class BaseDao {protected Connection conn; protected PreparedStatement ps; protected ResultSet rs; // create a Connection public boolean getConnection () {String driver = ConfigManager. getInstance (). getStr Ing ("jdbc. driver_class "); String url = ConfigManager. getInstance (). getString ("jdbc. connection. url "); String username = ConfigManager. getInstance (). getString ("jdbc. connection. username "); String password = ConfigManager. getInstance (). getString ("jdbc. connection. password "); try {Class. forName (driver); conn = DriverManager. getConnection (url, username, password);} catch (ClassNotFoundException e) {// TODO Aut O-generated catch block e. printStackTrace (); return false;} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace (); return false;} return true;} // Add, modify, delete public int executeUpdate (String SQL, Object [] params) {getConnection (); int updateRow = 0; try {ps = conn. prepareStatement (SQL); // fill the placeholder for (int I = 0; I <params. length; I ++) {ps. setObject (I + 1, params [I]);} updateRow = ps. e XecuteUpdate ();} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace ();} return updateRow;} // query public ResultSet executeSQL (String SQL, Object [] params) {getConnection (); try {ps = conn. prepareStatement (SQL); // fill the placeholder for (int I = 0; I <params. length; I ++) {ps. setObject (I + 1, params [I]);} rs = ps.exe cuteQuery ();} catch (SQLException e) {// TODO Auto-generated catch block E. printStackTrace () ;}return rs ;}// disable the public boolean closeResource () {if (rs! = Null) {try {rs. close ();} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace (); return false ;}} if (ps! = Null) {try {ps. close ();} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace (); return false ;}} if (conn! = Null) {try {conn. close ();} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace (); return false ;}} return true ;}}

2.2. Execute a stored procedure without parameters but with returned values

You can also create a new class to inherit the above class. The stored procedure is as follows:

-- Query the NUMBER of records in the emp table create or replace procedure getEmpCount (v_count out number) asbegin select count (*) INTO v_count FROM emp; END;

Call

// Execute a stored procedure without parameters but with returned values to obtain the total number of records in the emp table public int getTotalCountProc () {// define a variable to receive the result int totalCount = 0; // declare CallableStatement object CallableStatement proc = null; String SQL = "{call getEmpCount (?)} "; Try {// establish the connection getConnection (); // CallableStatement object proc = conn. prepareCall (SQL); // register the database object data type as the type proc in java. registerOutParameter (1, Types. INTEGER); // execute proc.exe cute (); // receive the returned value totalCount = proc. getInt (1);} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace ();} return totalCount ;}

2.3. Execute the stored procedure with parameters and returned values

-- Create or replace procedure getEmpCount (v_deptno NUMBER, v_ename VARCHAR2, v_count out number) asbeginselect count (*) INTO v_count FROM empWHERE ename LIKE '%' | v_ename | '%' AND deptno = v_deptno; END;

// Execute the Stored Procedure public int getTotalCountProc1 (int deptno, String ename) with parameters returned values {// define a variable to receive the result int totalCount = 0; // declare CallableStatement object CallableStatement proc = null; String SQL = "{call getEmpCount (?,?,?)} "; // Establish the connection getConnection (); // CallableStatement object try {proc = conn. prepareCall (SQL); // set the placeholder // Object [] params = {deptno, ename}; // only set the input parameter to proc. setInt (1, deptno); proc. setString (2, ename); // proc. setInt (3, totalCount); // register the Data Type of the database object as the type in java and convert the output parameter to proc. registerOutParameter (3, Types. INTEGER); // execute proc.exe cute (); // obtain the result totalCount = proc. getInt (3);} catch (SQLException e) {// TODO Auto-generated Catch block e. printStackTrace () ;}finally {this. closeResource (); if (proc! = Null) {try {proc. close ();} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace () ;}} return totalCount ;}

2.3 execute the stored procedure whose return value is a cursor

-- Query all employee information create or replace procedure emp_cur (emp_cur OUT SYS_REFCURSOR) asbegin open emp_cur for select * FROM emp; END;

// The execution returns the cursor Stored Procedure cursor name emp_cur public List <Emp> getempProc1 () {List <Emp> emplist = new ArrayList <Emp> (); string SQL = "{call emp_cur (?)} "; // Declare CallableStatement object CallableStatement proc = null; // establish a connection getConnection (); try {// execute proc = conn. prepareCall (SQL); // The registration type is database cursor type proc. registerOutParameter (1, oracle. jdbc. oracleTypes. CURSOR); // receives the result set proc.exe cute (); // gets the result. The first object rs = (ResultSet) proc. getObject (1); while (rs. next () {int empno = rs. getInt ("empno"); String ename = rs. getString ("ename"); String job = rs. getString ("job"); int mgr = rs. getInt ("Mgr"); Date hiredate = rs. getDate ("hiredate"); double sal = rs. getDouble ("sal"); double comm = rs. getDouble ("comm"); int deptno = rs. getInt ("deptno"); // declare the Emp object Emp emp = new Emp (); // Add the obtained value to the object emp. setEmpno (empno); emp. setEname (ename); emp. setJob (job); emp. setMgr (mgr); emp. setHiredate (hiredate); emp. setSal (sal); emp. setComm (comm); emp. setDeptno (deptno); // Add the object to the set emplist. add (emp) ;}} catch (SQLExcepti On e) {// TODO Auto-generated catch block e. printStackTrace ();} finally {this. closeResource (); if (proc! = Null) {try {proc. close ();} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace () ;}} return emplist ;}

As shown above, the output parameters and results must be registered. do not register the input parameters,

However, you need to set placeholders for the input parameters.

3. Execute function 3.1. The function returns the name based on the employee id.

CREATE OR REPLACE FUNCTION getename(v_empno NUMBER)RETURN VARCHAR2ASv_ename VARCHAR2(20);BEGIN  SELECT ename INTO v_ename FROM emp WHERE empno=v_empno;  RETURN v_ename;END;

public void getenamefun(int empno){            //sql            String ename="";            String sql="{?=call getename(?)}";            CallableStatement fun=null;            getConnection();            try {                fun=conn.prepareCall(sql);                fun.setInt(2, empno);                fun.registerOutParameter(1, Types.VARCHAR);                fun.execute();                ename=fun.getString(1);                System.out.println(ename);            } catch (SQLException e) {                // TODO Auto-generated catch block                e.printStackTrace();            }                    }        

Other methods and procedures are the same, but there are multiple return value types.