Just as a Connection object creates Statement and PreparedStatement objects, it also creates CallableStatement objects which will be used to execute the database stored procedure.
Create a CallableStatement object:
Assume, the following Oracle stored procedures need to be executed:
CREATE OR REPLACE PROCEDURE getEmpName (EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) ASBEGIN SELECT first INTO EMP_FIRST FROM Employees WHERE ID = EMP_ID;END;
Note: The above has written Oracle stored procedures, but we are using MySQL database, writing the same stored procedures for MySQL as follows, creating 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 ;
There are three types of parameters: IN, OUT and INOUT. PreparedStatement object only uses IN parameter. The CallableStatement object can use all three.
Here are each definition:
The following code snippet shows how to instantiate a CallableStatement object based on the above stored procedure using the Connection.prepareCall() method:
CallableStatement cstmt = null;try { String SQL = "{call getEmpName (?, ?)}"; cstmt = conn.prepareCall (SQL); . . .}catch (SQLException e) { . . .} finally { . . .}The SQL of the String variable represents a stored procedure, using parameter placeholders.
Using a CallableStatement object is using a PreparedStatement object. The value must be bound to all parameters before executing the statement, otherwise a SQLException will be received.
If there is an IN parameter, just follow the same rules and tricks that apply to the PreparedStatement object; use the setXXX() method corresponding to the Java data type to be bound.
When using OUT and INOUT parameters, registerOutParameter() with the additional CallableStatement method must be used. registerOutParameter() method JDBC data type bound to the stored procedure returned by the data type.
Once the stored procedure is called, the value is retrieved using the output parameters of the getXXX() method. This method casts SQL type values to retrieve Java data types.
Close the CallableStatement object:
Just as closing other Statement objects, the CallableStatement object should also 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 will close the CallableStatement object as well. However, the CallableStatement object should always be explicitly closed to ensure correct clearance.
CallableStatement cstmt = null;try { String SQL = "{call getEmpName (?, ?)}"; cstmt = conn.prepareCall (SQL); . . .}catch (SQLException e) { . . .} finally { cstmt.close();} PS: CallableStatement object instance
Here is an example of a MySQL stored procedure using CallableStatement along with the following getEmpName():
Please make sure that the stored procedure has been created in the EMP database. It can be done 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 ;
This script code has been written based on the installation of the environment and database in the previous chapter.
Copy the following example JDBCExample.java, compile and run as shown below:
//STEP 1. Import required packagesimport 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 JDBCExampleNow 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 ZaidGoodbye!
JDBC's SQL escape syntax:
Escape syntax enables the flexibility of using certain features of the database by using standard JDBC methods and properties.
The general SQL escape syntax format is as follows:
{keyword 'parameters'}Here are the following that will be found very useful while doing so in JDBC programming:
d, t, ts keywords:
They help determine date, time and time stamp 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 to the target database. Implementation example:
{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 objectstmt = conn.createStatement();//Insert data ==> ID, First Name, Last Name, DOBString sql="INSERT INTO STUDENTS VALUES" + "(100,'Zara','Ali', {d '2001-12-16'})";stmt.executeUpdate(sql);Likewise, one of the following two syntaxes can be used, whether t or ts:
{t 'hh:mm:ss'}where hh=hours, mm=minutes, ss=seconds. Using this syntax {t '13:30:29'} is 1:30 pm 29 pm.
{ts 'yyyy-mm-dd hh:mm:ss'}These are the two syntaxes 'd' and 't' above to represent the timestamp combination syntax.
escape keywords:
This keyword identifies the escaped character used in the LIKE clause. Useful when using SQL wildcard % where zero or more characters are matched. For example:
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 keywords:
This keyword means using scalar functions in 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 11, the length of the string 'Hello World'. .
call keywords:
This keyword is used to call stored procedures. For example, for a stored procedure, you need 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 keywords:
This keyword is used to represent an external join. The syntax is as follows:
{oj outer-join}External join table = {LEFT| RIGHT| FULL} External join{Table|External join} search criteria. For example:
String sql = "SELECT Employees FROM {oj ThisTable RIGHT OUTER JOIN ThatTable on id = '100'}";stmt.execute(sql);