Cliché: The JDBC Series stored procedure CallableStatement (created and used)

Objective

recently in the study of the MyBatis framework, since the framework is based on JDBC and wants to understand and learn mybatis well, it is necessary to have a deeper understanding of JDBC. So the JDBC this thing turned out, the same cliché, a good summary, as their own notes, but also to the reader a reference ~ ~ ~

This article describes JDBC support for stored procedures by Creating stored procedures using JDBC and using the JDBC call stored procedure . In this article, we will create a basic table of jobs that can represent job information under the Oracle database, and then perform various operations on the jobs table through stored procedures. Table Jobs's build statement is as follows:

--Create tablecreate table JOBS (  job_id     VARCHAR2 () not NULL,  job_title  VARCHAR2 (+),  Min_ Salary Number (6),  max_salary number (6));--add comments to the table comment on table jobs is  ' post information table ';--add comme  NTS to the columns comment in column jobs.job_id is  ' job ID ', Comment on column jobs.job_title is  ' job name '; Comment on Column jobs.min_salary  is ' minimum remuneration '; Comment on column jobs.max_salary  is ' maximum remuneration ';--create/recreate primary, Unique and FOREIGN KEY constraints ALTER TABLE JOBS  add constraint pk_job_id primary key (job_id);

JDBC Creating stored Procedures

Using a database to manipulate the database requires three steps: Execute the create stored procedure statement -- compile the stored procedure---> call the stored procedure .

For example, we create a stored procedure that adds a record to table jobs and calls it to execute the following code on the database:

--1. Creating a stored procedure create OR REPLACE PROCEDURE insert_jobs_proc (    input_job_id in Varchar2,input_job_title in Varchar2,input _min_salary in Number,input_max_salary in number) Asbegininsert into jobs (job_id,job_title,min_salary,max_salary) VALUES (input_job_id,input_job_title,input_min_salary,input_max_salary); END insert_jobs_proc;--2. Compile stored procedure compile;--3. Using Stored procedures call Insert_jobs_proc (' ad_pres ', ' president ', 20080,40000);

Since the code above is essentially SQL code, you can use JDBC to step through the above SQL code (although using JDBC creation does not need to call compile for compilation, JDBC automatically compiles the database):

public static void Intest () {Connection Connection = null; Statement Statement = null; ResultSet ResultSet = null;try {class.forname ("Oracle.jdbc.driver.OracleDriver"). Newinstance ();D River Driver = Drivermanager.getdriver (URL); Properties props = new properties ();p rops.put ("user", user_name);p rops.put ("password", password); connection = Driver.connect (URL, props);//Get Statement object, where the transaction mechanism is used, if the creation of a stored procedure statement fails or the execution compile fails, fallback connection.setautocommit ( FALSE); statement = Connection.createstatement (); String procedurestring = "CREATE OR REPLACE PROCEDURE insert_jobs_proc (" + "input_job_ ID in VARCHAR2, "+" Input_job_title in VARCHAR2, "+" input_min_sal ary in number, "+" input_max_salary in number) as "+" BEG In "+" inserts into Jobs (job_id,job_title,min_salary,max_salary) VALUES (input_job_id,input_ Job_title,input_min_salAry,input_max_salary); "+" END insert_jobs_proc; "; /1 Create a stored procedure, the JDBC database compiles the stored procedure Statement.execute (procedurestring);//Success commits connection.commit ();//2. Call CallableStatement    CallableStatement = Connection.preparecall ("Call Insert_jobs_proc (?,?,?,?)");    Set in Parameter callablestatement.setstring (1, "ad_press");    Callablestatement.setstring (2, "President");    Callablestatement.setbigdecimal (3, New BigDecimal (20080));        Callablestatement.setbigdecimal (4, New BigDecimal (40000));    Callablestatement.execute (); Connection.commit ();} catch (ClassNotFoundException e) {System.out.println ("Failed to load Oracle Class! "); E.printstacktrace ();} catch (SQLException e) {try {connection.rollback ();} catch (SQLException E1) {e1.printstacktrace ();} E.printstacktrace ();} catch (Instantiationexception e) {e.printstacktrace ();} catch (Illegalaccessexception e) {e.printstacktrace ();} finally{//Use to manage links after completion, release resources, release order should be: ResultSet->statement->connectiontry {statement.close ();}catch (SQLException e) {e.printstacktrace ();} try {connection.close ();} catch (SQLException e) {e.printstacktrace ();}}}

JDBC Calls stored procedures

The basic format for calling stored procedures using JDBC is:

Call Procedure_name (Parameter1,parameter2,paramter3 ...)

There are three different types of parameters: in type, out type, and mixed type in and out:

In type: This type is used for parameters passed from outside to the stored procedure;

Out type: This type is the return value during the execution of the stored procedure;

In, out mixed type: This type is a parameter passed in and then returned.

The following four parameter types create different stored procedures and then call through JDBC:

only input in parameter, no output out parameter

The stored procedure Insert_jobs_proc shown above is an example that only the in parameter is passed in, and the reader should look at the example above.

both input in parameter and output out parameter, output is simple value (not list)

Create a stored procedure get_job_min_salary_proc, pass in the job_id of a specific post, return the minimum compensation min_salary that output this position, the corresponding SQL statement is as follows:

CREATE OR REPLACE PROCEDURE get_job_min_salary_proc (    input_job_id in varchar2,output_salary out number) as Beginselect min_salary to output_salary from jobs WHERE job_id = input_job_id; END   Get_job_min_salary_proc;

This is called in JDBC:

/* * There is an in type parameter input and an out type parameter output */public static void Inouttest () {Connection Connection = null; Statement Statement = null; ResultSet ResultSet = null;try {class.forname ("Oracle.jdbc.driver.OracleDriver"). Newinstance ();D River Driver = Drivermanager.getdriver (URL); Properties props = new properties ();p rops.put ("user", user_name);p rops.put ("password", password); connection = Driver.connect (URL, props);//Get Statement object, where the transaction mechanism is used, if the creation of a stored procedure statement fails or the execution compile fails, fallback connection.setautocommit ( FALSE); statement = Connection.createstatement (); String procedurestring = "CREATE OR REPLACE PROCEDURE get_job_min_salary_proc (" + "INP                                      ut_job_id in VARCHAR2, "+" output_salary out number) as " + "BEGIN" + "select Min_salary into output_salary from jobs WHERE job_id = input_job_id; "+" END get_job_min_salary_proc; "; /1 To create a stored procedure, the JDBC database compiles the stored procedure Statement.exEcute (procedurestring);//success is submitted Connection.commit ();//2. Create CallableStatement callablestatement callablestatement =    Connection.preparecall ("Call Get_job_min_salary_proc (?,?)");    3, set in Parameter callablestatement.setstring (1, "ad_pres");    4. Register the output parameter Callablestatement.registeroutparameter (2, types.numeric);        5. Execute statement callablestatement.execute ();    BigDecimal salary = Callablestatement.getbigdecimal (2); System.out.println (salary);} catch (ClassNotFoundException e) {System.out.println ("Failed to load Oracle Class! "); E.printstacktrace ();} catch (SQLException e) {try {connection.rollback ();} catch (SQLException E1) {e1.printstacktrace ();} E.printstacktrace ();} catch (Instantiationexception e) {e.printstacktrace ();} catch (Illegalaccessexception e) {e.printstacktrace ();} finally{//Use to manage links after completion, release resources, release order should be: ResultSet->statement->connectiontry {statement.close ();} catch ( SQLException e) {e.printstacktrace ();} try {connection.close ();} catch (SQLException e) {e.printstacktrace ();}}}

both the input in parameter and the output out parameter, the output is the list

Create a stored procedure get_min_greater_proc, enter the minimum compensation for the parameter, and return to the jobs table with minimum compensation not less than this parameter.

The corresponding SQL statements are as follows:

--Create a package that customizes a data type My_cursorcreate OR REPLACE package my_package_cursor are type my_cursor is REF cursor; END  my_package_cursor;--Creates a stored procedure that returns a set of jobs that are not less than minimum compensation in the jobs table by passing in the minimum compensation create OR REPLACE PROCEDURE Get_min_greater_proc (     input_min_salary in number,     setresult out my_package_cursor.my_cursor) asbegin OPEN Setresult for  

The JDBC calling code is as follows:

/* * has an in type parameter input and a set output of out type */public static void Inoutresultsettest () {Connection Connection = null; Statement Statement = null; ResultSet ResultSet = null;try {class.forname ("Oracle.jdbc.driver.OracleDriver"). Newinstance ();D River Driver = Drivermanager.getdriver (URL); Properties props = new properties ();p rops.put ("user", user_name);p rops.put ("password", password); connection = Driver.connect (URL, props);//1. Create callablestatement callablestatement callablestatement = Connection.preparecall ("    Call Get_min_greater_proc (?,?) ");    2, set in Parameter Callablestatement.setbigdecimal (1, New BigDecimal (20000));    3. Register the output parameter Callablestatement.registeroutparameter (2, oracletypes.cursor);    4. Execute statement callablestatement.execute (); Returns the result set ResultSet = (ResultSet) callablestatement.getobject (2);} catch (ClassNotFoundException e) {System.out.println ("Failed to load Oracle Class! "); E.printstacktrace ();} catch (SQLException e) {try {connection.rollback ();} catch (SQLException E1) {e1.printstacktrace ();} E.printstaCktrace ();} catch (Instantiationexception e) {e.printstacktrace ();} catch (Illegalaccessexception e) {e.printstacktrace ();} finally{//Use to manage links after completion, release resources, release order should be: ResultSet->statement->connectiontry {statement.close ();} catch ( SQLException e) {e.printstacktrace ();} try {connection.close ();} catch (SQLException e) {e.printstacktrace ();}}}

input and output parameters are the same ( in Out)

Create a stored procedure get_job_info , the incoming job_id returns JOB_ID, the job_id returned is the input job_id and the corresponding job_title stitching.

--Create stored procedure incoming job_id return job_idcreate OR REPLACE PROCEDURE get_job_info (io_job_id in Out     VARCHAR2) asbegin  

The corresponding JDBC code is as follows:

1. Create CallableStatement    callablestatement callablestatement = Connection.preparecall ("Call Get_job_info (?)");    2, set in Parameter    callablestatement.setstring (1, "ad_pres");    3. Register the output parameter    callablestatement.registeroutparameter (1, Types.varchar);    4. Execute statement    Callablestatement.execute ();    Returns the result    String jobId = callablestatement.getstring (1);    System.out.println (JOBID);

--------------------------------------------------------------------------------------------------------------- -------------------------

Note: This article largely draws on the four scenarios in which JDBC executes stored procedures .

Readers are welcome to follow my cliché: Other articles in the JDBC series ~ ~ ~