Detailed Java JDBC API stored procedures and the use of SQL escape syntax _java

Just as a Connection object creates statement and PreparedStatement objects, it also creates CallableStatement objects that will be used to execute calls to the database stored procedures.

To create a CallableStatement object:
assume that you need to perform the following Oracle stored procedures:

CREATE OR REPLACE PROCEDURE getempname 
  (emp_id in number, emp_first out VARCHAR) as
BEGIN
  SELECT P_first from
  Employees
  WHERE ID = emp_id;
End;

NOTE: Oracle stored procedures have been written above, but we are using the MySQL database to write the same stored procedure 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 in Emp_first from
  Employees
  WHERE ID = emp_i D;
End $$

DELIMITER;

Three kinds of parameters are: In,out and inout. The PreparedStatement object uses the in parameter only. The CallableStatement object can use all three.

Here is each definition:

• In: Its value is an unknown parameter when the SQL statement is created. Bind the value to the parameters of the Setxxx () method.
• Out: Its value is the parameter provided by the SQL statement it returns. You retrieve a 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 fragment shows how to use the Connection.preparecall () method to instantiate a CallableStatement object based on the stored procedure described above:

CallableStatement cstmt = null;
try {
  String SQL = ' {call Getempname (?,?)} ';
  cstmt = Conn.preparecall (SQL);
  . . .
}
catch (SQLException e) {
  ...
}
Finally {
  ...
}

A string variable's SQL represents a stored procedure, using a parameter placeholder.

Use the CallableStatement object to use the PreparedStatement object. You must bind the value to all parameters before executing the statement, or you will receive a sqlexception.

If you have the in parameter, just follow the same rules and techniques as applicable to the PreparedStatement object, and use the Setxxx () method that corresponds to the Java data type you want to bind.

When the out and InOut parameters are used, the Registeroutparameter () of the additional CallableStatement method must be used. The Registeroutparameter () method JDBC data type is bound to the data type's stored procedure return.

Once the stored procedure is invoked, the value is retrieved with the output parameter of the GetXXX () method. This method casts a value of SQL type to the Java data type.

Close the CallableStatement object:
Just as you close other statement objects, you should also close the CallableStatement object for the same reason.

A simple call to the close () method will do the job. If you close the Connection object first it closes the CallableStatement object as well. 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 ();
}

Ps:callablestatement Object Instance
The following are examples of MySQL stored procedures that take advantage of CallableStatement together with the following Getempname ():

Make sure that the stored procedure is already created in the EMP database. You can use the MySQL query browser to complete it.

DELIMITER $$

DROP PROCEDURE IF EXISTS ' emp '. ' Getempname ' $$
CREATE PROCEDURE ' emp '. ' Getempname ' 
  (in Emp_ ID INT, out emp_first VARCHAR (255))
BEGIN
  SELECT in Emp_first from
  Employees
  WHERE ID = emp_i D;
End $$

DELIMITER;

This example code has been written based on the installation of the environment and the database in the previous section.

Copy Jdbcexample.java in the following example, 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.Driv 
  ER ";

  Static final String Db_url = "Jdbc:mysql://localhost/emp";
  The 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-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 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 you run 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 flexibility to use certain features of a database by using standard JDBC methods and properties.

The general SQL escape syntax format is as follows:

{keyword ' parameters '}

Here are some of the following, which will find very useful, and thus do the escape sequence of JDBC programming:

D, t, TS Keywords:
they help to determine the date, time and time stamp text. As you know, no two database management systems are the same based on time and date. This escape syntax tells the driver to render the format, date, or time of the target database. Implementation example:

{d ' yyyy-mm-dd '}

Which yyyy= year, MM = month, DD = day. The use of 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, name, last name, DOB
String sql= "INSERT into STUDENTS VALUES" +
       "(M, ' Zara ', ' Ali ', {d ' 2001-12-16 '}) ";

Stmt.executeupdate (SQL);

Similarly, you can use one of the following two syntaxes, either T or TS:

{t ' hh:mm:ss '}

Which hh= hours, mm= minutes, ss= seconds. Use this syntax {t ' 13:30:29 '} is 1 o'clock in the afternoon 30 minutes 29 seconds.

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

This is the two syntax ' d ' and ' t ' to denote the timestamp binding syntax.

Escape keyword:
This keyword identifies the escape character used in the LIKE clause. Useful uses the SQL wildcard character%, which matches 0 or more character characters. 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 the two backslash characters in the Java string literal, because the backslash is also a Java escape character.

fn Keyword:
this keyword represents the use of scalar functions in the DBMS. For example, you can use the SQL length function to calculate the length of the GE string:

{fn Length (' Hello World ')}

This will return 11, the length of the string ' Hello world '.

Call Keyword:
This keyword is used to invoke a stored procedure. For example, if you need an in parameter for a stored procedure, 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 Keyword:
This keyword is used to represent an outer join. The syntax is as follows:

{OJ Outer-join}

Outer JOIN Table ={left| Right| Full} outer joins {table | outer join} 's search criteria. For example:

String sql = "Select Employees from 
       {OJ thistable right
       OUTER JOIN thattable on id = ' n '}";
Stmt.execute (SQL);