JDBC Stored Procedures

While learningJDBC-statementIn addition to learning how to use stored procedures in JDBC, we also learned how to use JDBC SQL escape syntax in this chapter. This chapter is similar to the previous one, but it will provide you with more information about JDBC SQL escape syntax.

Just like the Connection object creates Statement and PreparedStatement objects, it also creates CallableStatement objects, which are used to execute calls to database stored procedures.

Create CallableStatement object

Assuming you need to execute the following Oracle stored procedure-

CREATE OR REPLACE PROCEDURE getEmpName　
　　　(EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
　　　SELECT first INTO EMP_FIRST
　　　FROM Employees
　　　WHERE ID = EMP_ID;
END;

NOTE: The stored procedure above has been written for Oracle, but we are using MySQL database, so let's write the same stored procedure for MySQL as follows, so that it can be created in the EMP database-

DELIMITER $$
DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$
CREATE PROCEDURE `EMP`.`getEmpName`　
　　　(IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
　　　SELECT first INTO EMP_FIRST
　　　FROM Employees
　　　WHERE ID = EMP_ID;
END $$
DELIMITER ;

There are three types of parameters: IN, OUT, and INOUT. PreparedStatement objects only use IN parameters. CallableStatement objects can use all three.

This is the definition of each-

The following code snippet shows how to instantiate an object using this method based on the previous stored procedure-Connection.prepareCall()CallableStatement

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

The string variable SQL represents the stored procedure with parameter placeholders.

Using CallableStatement objects is very similar to using PreparedStatement objects. You must bind values to all parameters before executing the statement; otherwise, you will receive an SQLException.

If there are IN parameters, just follow the same rules and techniques applied to PreparedStatement objects; use the setXXX() method corresponding to the bound Java data type.

When using OUT and INOUT parameters, you must use the additional CallableStatement method registerOutParameter(). The registerOutParameter() method binds the JDBC data type to the expected data type returned by the stored procedure.

After calling the stored procedure, you can use the appropriate getXXX() method to retrieve values from the OUT parameters. This method converts the SQL type values retrieved to Java data types.

Closing CallableStatement object

Just like closing other Statement objects, for the same reason, you should also close the CallableStatement object.

It is enough to call the close() method simply. If the Connection object is closed first, it will also close the CallableStatement object. However, you should always explicitly close the CallableStatement object to ensure proper cleanup.

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

We studied inIn the callable example codeMore details were studied.

JDBC SQL escape syntax

Escape syntax allows you to flexibly use database-specific features that cannot be used with standard JDBC methods and properties.
The general SQL escape syntax format is as follows-

{keyword 'parameters'}

Below are the following escape sequences that you will find very useful when performing JDBC programming-

d, t, ts keywords

They can help identify text for dates, times, and timestamps. As you know, no two dbms represent time and date in the same way. This escape syntax tells the driver to present dates or times in the format of the target database. For example:

{d 'yyyy-mm-dd'}

where yyyy = year, mm = month; dd = day. Use this syntax {d'2009-09-03}' is2009Year3Month9Day.

This is a simple example showing how to insert a date into a table-

//Create a Statement object
stmt = conn.createStatement();
//Insert data ==> ID, First Name, Last Name, DOB
String sql="INSERT INTO STUDENTS VALUES"　+
　　　　　　　　　　　　　"(100,'Zara','Ali', {d '2001-12-16'})";
stmt.executeUpdate(sql);

Similarly, you can use one of the following two syntaxes:torts：

{t 'hh:mm:ss'}

where hh = hour; mm = minute; ss = second. Use this syntax {t '13：30：29}' is1:30:29 PM.

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

This is a combined syntax of the above two grammars, where 'd' and 't' represent timestamps.

escape keyword

This keyword identifies the escape character used in the LIKE clause. It is very useful when using SQL wildcard %, which matches zero or more characters. For example:

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

If the backslash character (\) is used as an escape character, then you must also use two backslash characters in the Java String literal, because the backslash is also a Java escape character.

fn keyword

This keyword represents the scalar function used in the DBMS. For example, you can use SQL functionslengthto get the length of the string-

{fn length('Hello World')}

This will return11i.e., the length of the string "Hello World".

call keyword

This keyword is used to call stored procedures. For example, for stored procedures that require IN parameters, use the following syntax-

{call my_procedure(?)};

For stored procedures that require IN parameters and return OUT parameters, use the following syntax-

{? = call my_procedure(?)};

oj keyword

This keyword is used to represent outer join. Syntax is as follows-

{oj outer-join}

Among them, outer join = table {LEFT | RIGHT | FULL} OUTER JOIN {table | search condition. For example-

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