JDBC Stored Procedure

JDBC Stored Procedure

In this tutorial, we will be discussing about the JDBC Stored Procedure and the various techniques that are involved in it. This is very easy to understand as a Connection object creates the Statement and PreparedStatement objects, this also creates the CallableStatement object that would be used in order to execute a call to a database stored procedure.

Creating CallableStatement Object in Oracle

In order to create a callablestatement object in JDBC the programmer needs to execute the following Oracle stored procedure that has been depicted below:

CREATE OR REPLACE PROCEDURE getStuName 
   (STU_ID IN NUMBER, STU_FIRST OUT VARCHAR) AS
BEGIN
   SELECT first INTO STU_FIRST
   FROM Student
   WHERE ID = STU_ID;
END;

Note : Please note that the above mentioned stored procedure has been written for the Oracle and the programmer or the users have been working with the MySQL database hence, here is the code for stored procedure for MySQL that has been depicted below that is used to create it in EMP database:

Creating CallableStatement Object in Oracle

DELIMITER $$
DROP PROCEDURE IF EXISTS `STU`.`getStuName` $$
CREATE PROCEDURE `STU`.`getStuName` 
   (IN STU_ID INT, OUT STU_FIRST VARCHAR(255))
BEGIN
   SELECT first INTO STU_FIRST
   FROM Student
   WHERE ID = STU_ID;
END $$
DELIMITER;

In terms of the parameters there are generally three types of parameters that generally exists that are: IN, OUT, and INOUT. The PreparedStatement object in the JDBC only uses the IN parameter. However, the CallableStatement object can use all the three parameters.

The following code snippet that has been depicted below is to explain the procedurethat is used to employ the Connection.prepareCall() method in order to instantiate a CallableStatement object that is basically based on the preceding stored procedure, have a look at the code to get the deep understanding of the concept:

CallableStatement cstmt = null;
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

The String variable SQL that has been mentioned in the above code generally represents the stored procedure with the parameter placeholders. If the user is using the CallableStatement objects then it feels much similar to using PreparedStatement objects. The programmer must bind the values to all the parameters beforehand executing the statement, or else the programmer will receive a SQLException.

In case, the programmer has IN parameters, then it is better to just follow the same rules and techniques that generally apply to a PreparedStatement object; the user must use the setXXX() method that usually corresponds to the Java data type the user is binding.

Whenever the programmer tends to use OUT and INOUT parameters, then it is mandatory that they must employ an additional CallableStatement method, registerOutParameter(). The registerOutParameter() method is generally used to bind the JDBC data type to the data type that the stored procedure is generally expected to return.

If the programmer called their stored procedure, then the programmer generally retrieves the value from the OUT parameter with the appropriate getXXX() method. This method is generally known to cast the retrieved value of SQL type to a Java data type.

Closing CallableStatement Object

Just as the programmer close the other Statement objects, the same reason is applied to close the CallableStatement object in JDBC.

A simple call is to the close () method is enough to close the statement. In case the programmer closes the Connection object first, then it will close the CallableStatement object as well in the program. But it is recommended that the programmer should always explicitly close the CallableStatement object in order to ensure the proper cleanup.

Here is an example that will explain the concept from its root to get the correct idea of it:

CallableStatement cstmt = null;
try {
   String SQL = "{call getStuName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   cstmt.close();
}

JDBC SQL Escape Syntax

The escape syntax is generally known to deliver the flexibility to use the database specific features to the programmer or the user that are generally unavailable to the users, just by the help of standard JDBC methods and properties.

{keyword 'parameters'}

In order to use the JDBC programming there are several escape sequences that are known to be very helpful to the programmer in the process that has been explained below:

d, t, ts Keywords

These are the escape sequences that are used to identify date, time, and timestamp literals. As a matter of fact it is known that no two DBMSs represent time and date the same way. This very escape syntax dictates the driver to render the date or time in the target database's format. .

Here is an example of the same that has been depicted below:

{d 'yyyy-mm-dd'}

In the above example, yyyy is representing the year, mm is representing the month; dd is representing the date.

Here is an example that is displaying the procedure to INSERT date in a table, learn the example in order to grasp the process:

//Create a Statement object
stmt = conn.createStatement();
//Insert data ==> ID, FirstName, LastName, DOB
String sql="INSERT INTO STUDENTS VALUES" +
             "(100,'ABhi','singh', {d '2001-12-16'})";

stmt.executeUpdate(sql);

Just as the procedure to display the date the user can use one of the following two syntaxes, that is either t or ts that has been depicted below:

{t 'hh:mm:ss'}

In the above displayed syntax, hh represents the hour; mm represents the minute; ss represents the second. In order to represent timestamp, the combination of two syntax for’d’ and’t’ is used. See the below mentioned syntax to understand it well:

{ts 'yyyy-mm-dd hh:mm:ss'}

Escape Keyword

This is the keyword that is generally used to identify the escape character that is used in LIKE clauses. This keyword has been found to be useful when using the SQL wildcard % that basically matches zero or more characters. For instance, observe the below mentioned code for the explanation of the concept:

String sql = "SELECT symbol FROM MathSymbols
              WHERE symbol LIKE '\%' {escape '\'}";
stmt.execute(sql);

In case the user use the backslash character (\) as the escape character then the user also have to use two backslash characters in their Java String literal as of the fact that the backslash is also a Java escape character.

fn Keyword

This keyword is generally known to represent the scalar functions that are used in a DBMS. For instance, the programmer can use SQL function length in order to get the length of a string that has been depicted below:

{fn length('Hello World')}

call Keyword

This is the keyword that is generally used in order to call the stored procedures. For instance, for a stored procedure that is requiring an IN parameter, the following depicted syntax can be used:

{call my_procedure(?)};

And for a stored procedure that generally requires an IN parameter and return an OUT parameter, then the following syntax can be used that has been depicted below:

{? = call my_procedure(?)};

oj Keyword

This is the keyword that is used in order to signify outer joins. Here is the syntax of the same has been depicted below:

{oj outer-join}

In the above mentioned syntax the outer-join represents the table {LEFT|RIGHT|FULL} OUTERJOIN {table | outer-join} on search-condition. For instance look at the sample code mentioned below:

String sql = "SELECT STUDENTS 
              FROM {oj ThisTable RIGHT
              OUTER JOIN ThatTable on id = '100'}";
stmt.execute(sql);