Details the use of stored procedures and SQL escape syntax for the Java JDBC API

Just as a Connection object creates a Statement and a PreparedStatement object, it also creates a CallableStatement object that will be used to perform the call to the database stored procedure.

Create a CallableStatement object:
Suppose that the following Oracle stored procedures need to be executed:

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 Oracle stored procedure has been written above, but we are using MySQL database, write the same stored procedure for MySQL as follows, create it 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 ;

The three types of parameters are IN, OUT and INOUT. The PreparedStatement object USES only the IN parameter. The CallableStatement object can use all three.

Here are each definition:

IN: its value is an unknown parameter when the SQL statement is created. Bind the value to the parameter of the setXXX () method. OUT: its value is the parameter supplied by the SQL statement it returns. You retrieve the value from the getXXX () method of the OUT parameter. INOUT: provides parameters for both input and output values. Bind the variable of the setXXX () method and retrieve the value with the getXXX () method.

The following code snippet shows how to instantiate a CallableStatement object based on the stored procedure above using the connection.preparecall () method:

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

The SQL for the String variable represents the stored procedure, using parameter placeholders.

Using a CallableStatement object is to use a PreparedStatement object. The value must be bound to all parameters before the statement is executed, otherwise you will receive a SQLException.

If there is an IN parameter, just follow the same rules and techniques that apply to a PreparedStatement object; Use the setXXX () method corresponding to the Java data type to be bound.

When the OUT and INOUT parameters are used, the registerOutParameter () of the additional CallableStatement method must be taken. The registerOutParameter () method JDBC data type is bound to the stored procedure return of the data type.

Once the stored procedure is invoked, the value is retrieved with the output parameter of the getXXX () method. This method projects SQL type values to retrieve Java data types.

Close the CallableStatement object:
Just as other Statement objects are closed, the CallableStatement object should be closed for the same reason.

A simple call to the close () method will do the job. If the Connection object is closed, first it closes the CallableStatement object. However, the CallableStatement object should always be explicitly closed to ensure proper cleanup.

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

PS: instance of a CallableStatement object
Here is an example of a MySQL stored procedure using a CallableStatement along with the following getEmpName() :

Make sure the stored procedure has been created in the EMP database. You can do this using the MySQL query browser.

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 ;

Based on the installation of the environment and database in the previous section, this sample code has been written.

Copy the following example of jdbcexample.java, compile and run as follows:

//STEP 1. Import required packages
import java.sql.*;

public class JDBCExample {
  // JDBC driver name and database URL
  static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 
  static final String DB_URL = "jdbc:mysql://localhost/EMP";

  // Database credentials
  static final String USER = "username";
  static final String PASS = "password";
  
  public static void main(String[] args) {
  Connection conn = null;
  CallableStatement stmt = null;
  try{
   //STEP 2: Register JDBC driver
   Class.forName("com.mysql.jdbc.Driver");

   //STEP 3: Open a connection
   System.out.println("Connecting to database...");
   conn = DriverManager.getConnection(DB_URL,USER,PASS);

   //STEP 4: Execute a query
   System.out.println("Creating statement...");
   String sql = "{call getEmpName (?, ?)}";
   stmt = conn.prepareCall(sql);
   
   //Bind IN parameter first, then bind OUT parameter
   int empID = 102;
   stmt.setInt(1, empID); // This would set ID as 102
   // Because second parameter is OUT so register it
   stmt.registerOutParameter(2, java.sql.Types.VARCHAR);
   
   //Use execute method to run stored procedure.
   System.out.println("Executing stored procedure..." );
   stmt.execute();

   //Retrieve employee name with getXXX method
   String empName = stmt.getString(2);
   System.out.println("Emp Name with ID:" + 
        empID + " is " + empName);
   stmt.close();
   conn.close();
  }catch(SQLException se){
   //Handle errors for JDBC
   se.printStackTrace();
  }catch(Exception e){
   //Handle errors for Class.forName
   e.printStackTrace();
  }finally{
   //finally block used to close resources
   try{
     if(stmt!=null)
      stmt.close();
   }catch(SQLException se2){
   }// nothing we can do
   try{
     if(conn!=null)
      conn.close();
   }catch(SQLException se){
     se.printStackTrace();
   }//end finally try
  }//end try
  System.out.println("Goodbye!");
}//end main
}//end JDBCExample

Now compile the above example as follows:

C:>javac JDBCExample.java

When running JDBCExample, it produces the following results:

C:>java JDBCExample

Connecting to database...
Creating statement...
Executing stored procedure...
Emp Name with ID:102 is Zaid
Goodbye!

SQL escape syntax for JDBC:
Escape syntax enables the use of some of the flexibility of the database that is not available through the use of standard JDBC methods and properties.

The general SQL escape syntax format is as follows:

{keyword 'parameters'}

Here are the escape sequences of JDBC programming that you will find very useful for doing this:

D, t, ts
They help determine the date, time, and timestamp text. As you know, no two database management systems are based on time and date in the same way. This escape syntax tells the driver the format, date, or time to render in the target database. Implementation examples:

{d 'yyyy-mm-dd'}

Where yyyy= year, mm = month, DD = day. Using this syntax {d '2009-09-03'} is March 9, 2009.

Here is a simple example of how to insert a date 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);

Again, you can use one of two grammars, whether t or ts:

{t 'hh:mm:ss'}

Where hh= hours, mm= minutes, ss= seconds. Using this syntax {t '13:30:29'} is 1:30pm 29 seconds.

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

Here are the two grammars 'd' and '  't' for timestamp combination syntax.

Escape keyword:
This keyword identifies the escape character used in the LIKE clause. Useful when using the SQL wildcard %, where zero or more characters are matched. Such as:

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

If you use the backslash character () as the escape character, you must also use two backslash characters in the Java string literal, because the backslash is also a Java escape character.

Fn key words:
This keyword represents the use of scalar functions in a DBMS. For example, you can use the SQL length function to calculate the length of a GE string:

{fn length('Hello World')}

This will return the length of the 11 string 'Hello World'. .

Key words:
This keyword is used to invoke the stored procedure. For example, for a stored procedure that requires an IN parameter, use the following syntax:

{call my_procedure(?)};

For a stored procedure, an IN parameter is required and an OUT parameter is returned, using the following syntax:

{? = call my_procedure(?)};

Oj key words:
This keyword is used to represent an outer join. The syntax is as follows:

{oj outer-join}

Outer join table ={LEFT| RIGHT| FULL} search criteria for outer join {table | outer join}. Such as:

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