JDBC Statements

JDBC Statements

JDBC Statements are basically an interface that is present in the Java language. This is known to deliver the methods that are used to execute the queries with the database. The statement interface in JDBC is basically a factory of ResultSet and in simple terms we can understand it as this delivers factory method in order to get the object of ResultSet. Whenever a connection is generally obtained then the user or the programmer can interact with the database.

The JDBC Statement, CallableStatement, and PreparedStatement interfaces are the ones that define the methods and the properties that generally enable the user or a programmer to send SQL or PL/SQL commands and receive data from their database. These interfaces also defines the methods that are generally known to assist the bridge data type differences that exists between Java and SQL data types basically used in a database.

Here is a table depicted below that will be explaining each of the interfaces purpose that us used to decide on the interface to use according to the condition:

1. The Statement Objects

• Creating Statement Object

There are certain things that the user can use in creating a Statement object to execute a SQL statement just by using the Connection object's createStatement( ) method.

Statement stmt = null;
try 
{
   stmt = conn.createStatement( );
   . . .
}
catch (SQLException e) 
{
   . . .
}
finally 
{
   . . .
}

After the user have created a Statement object then they can use that in order to execute an SQL statement with one of its three execute methods that are explained below with a brief description:

• boolean execute (String SQL) : This method generally returns a boolean value of true in case a ResultSet object can be retrieved, if not, then this generally returns false. This method is generally used to execute SQL DDL statements or in some cases when the user needs to use truly dynamic SQL.
• int executeUpdate (String SQL) : This method generally returns the number of rows that are affected by the execution of the SQL statement. It is also used to execute SQL statements for which the user or programmer expect to get a number of rows affected.
• ResultSet executeQuery (String SQL) : This method generally returns a ResultSet object. This method is basically used when the user expect to get a result set.

• Closing Statement Object

Just after the user close a Connection objects in order to save database resources, and this is the same reason that for which the user or the programmer should close the Statement object.

In order to close this, only a simple call to the close() method will do the most of the job. Please note that in case the user closes the Connection object first then it will also close the Statement object as well. Object In order to ensure proper cleanup, the user should always explicitly close the statement object.

Statement stmt = null;
try 
{
   stmt = conn.createStatement( );
   . . .
}
catch (SQLException e) 
{
   . . .
}
finally 
{
   stmt.close();
}

2. The PreparedStatement Objects

The PreparedStatement interface in the JDBC generally extends the Statement interface that generally provides the user some added functionality with a couple of advantages over a generic Statement object. This statement is generally known to provide the flexibility of supplying the arguments in a dynamic way.

Creating PreparedStatement Object

Generally, all the parameters that are in JDBC are represented by the ? Symbol and that is basically known as the parameter marker. The user or the programmer must supply the values for each of the parameter beforehand executing the SQL statement.

In order to hind the values together the parameters the setXXX() method is used, where XXX generally represents the Java data type of the value that the user wish to bind to the input parameter. Please note that if the programmer forgets to supply the values then a SQLException will be received.

Each of the parameter markers is generally referred by its ordinal position. And the process goes like: the first marker is used to represent position 1, the next position 2, and the list goes on. This method has various differences as compared to the Java array indices that basically starts at 0.

This is very interesting to know that all of the Statement object's methods that are used for interacting with the database (a) execute(), (b) executeQuery(), and (c) executeUpdate(), they also work with the PreparedStatement object. But in order to do that, the methods are generally modified in order to use SQL statements that can input the parameters.

PreparedStatement pstmt = null;
try {
   String SQL = "Update Employees SET age = ? WHERE id = ?";
   pstmt = conn.prepareStatement(SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

Closing PreparedStatement Object

The reason for closing the PreparedStatement object is same Just of the closing of a Statement object. In order to close the method a user will only need a simple call. In case the user decides to close the Connection object first, then it will also close the PreparedStatement object as well. But it is recommended that the user should always explicitly close the PreparedStatement object in order to ensure proper cleanup.

PreparedStatement pstmt = null;
try {
   String SQL = "Update Employees SET age = ? WHERE id = ?";
   pstmt = conn.prepareStatement(SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   pstmt.close();
}

3. The CallableStatement Objects

The CallableStatement object is basically created the same way as a Connection object creates the Statement and PreparedStatement objects. This statement is generally used to execute a call to a database stored procedure.

Creating CallableStatement Object

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 ;

Note : This is the stored procedure for MySQL

There are generally three types of parameters that exist that are IN, OUT, and INOUT. And the PreparedStatement object just only uses the IN parameter. As far as the terms of the CallableStatement object the n it can use all the three.

Here is a brief description of all the three parameters that has been depicted below:

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

The String variable SQL that has been used in the above example generally represents the stored procedure with the parameter placeholders.

The operations principle or the handling of the CallableStatement objects is basically same like that of the PreparedStatement objects. The programmer should bind the values to all the parameters beforehand executing the statement if the programmer fails to do so then they will receive an SQLException. In case the programmer have IN parameters, then it is very simple as they have to follow the same rules and techniques that apply to a PreparedStatement object.

Whenever it is implicated that a programmer have to use OUT and INOUT parameters the it is mandatory that they must employ an additional CallableStatement method, registerOutParameter(). The registerOutParameter() method is basically the method that is responsible for binding the JDBC data type to the data type that is generally the stored procedure is expected to return. Once the stored procedure is being called then the user will retrieve the value from the OUT parameter with the appropriate getXXX() method. This method is also known to casts the retrieved value of SQL type to a Java data type.

Closing CallableStatement Object

The reason for closing the CallableStatement object is actually the same that is used for various other Statement object. The programmer just have to give a simple call to the close() method in order to close it. In case the programmer happens to close the Connection object first then this will close the CallableStatement object as well. But as a matter of fact the programmer is supposed to explicitly close the CallableStatement object always in order to ensure proper cleanup.

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