Recent Pos

The JDBC CallableStatement Object

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmail

callable statement object jdbc
 
It is used for creating/executing database stored procedure and function.
 

Database stored procedure:

⇒ It is a procedure (sequence of operations) compiled and saved in the database for later execution.

⇒ Oracle support PL/SQL for creating stored procedures. In oracle we can create the stored procedure in the form of procedure or function.
 
# Creating procedure in Oracle DB:

⇒ The following is the system for creating procedure in Oracle database server:

 
params: We can have one or more parameters here separated using ‘,’ (comma).

⇒ each parameter declaration should be in the following format:

<name>[<type>]<datatype>

<type>: Can be any one of the following.
IN: This type of parameter is used to take the input into the procedure.

OUT: This type of parameter is used to send the output data from the procedure.

INOUT: This is used as an IN and as well as OUT parameter combined.

Note: In case if we don’t specify the <type> it defaults to IN.
 
Example:

 

 
While compiling the procedure, if any error comes then we can use the following command to show the error:

 
-We can run the above code in the SQL prompt of Oracle SQL*Plus editor to compile the procedure.
 
Q. How can we execute procedure from Java application using JDBC?

We use CallableStatement to do this.
 
Q. How to use CallableStatement?
The following 5 steps are involved in doing this:

step 1: Creating the CallableStatement.

step 2: Set the values to IN parameters.

step 3: Register the OUT parameter.

step 4: Execute the statement.

step 5: Read the OUT parameter.

⇒ We use the following method of Connection object to create the CallableStatement object:

 
⇒ The statement string is of the following syntax (For procedure):

–for no parameters

 
–for parameters

Note: The ? marks (parameter) here are given with index based on the position from left to right similar to PreparedStatement.

⇒ The index starts with 1
 
step 1: creating the CallableStatement:

 
step 2: Setting the values for IN parameters:

⇒ The CallableStatement extends PreparedStatement

⇒ In the PreparedStatement we observed the setXxx methods for setting the parameter values.

⇒ The same methods are used here with CallableStatement to get the value for IN parameters.
 
step 3: Register the OUT parameters:

 
⇒ In case of working with OUT parameter we need to declare the variables in the database server before calling the procedure.

⇒ The CallableStatement supports the following method to configure the OUT parameter so that JDBC can declare the variable and further read it for the result.

⇒ The following method of CallableStatement object is used to do this:

The java.sql.Types class declares static constants for representing the various database datatypes.

Types.INTEGER: Specifies the integer type, i.e. number in case of Oracle and int in case of MySQL.

Types.DOUBLE: Specifies the double type.

⇒ Similarly we have for all the datatypes.
 

step 4: Executing the Statement:

-The following method of CallableStatement is used to execute the statement.

 
step 5: Reading the OUT parameter values:

⇒ We can use the getXxx() methods of CallableStatement to do this:

⇒ Similarly for all datatypes.
 
Example:

//We want to execute the createEmp procedure

 
CallableStatementExInsert.java

 
 
<-Previous: Working with java.sql.Date type using JDBC | | Next->Working With JDBC CallableStatement IN and OUT Parameters

No Comments

Post a Comment