JDBC Statement, PreparedStatement and CallableStatement

Once a connection is obtained, we can interact with the database. The JDBC Statement, CallableStatement, and PreparedStatement interfaces define methods and properties that allow you to send SQL or PL/SQL commands and receive data from the database.

They also define methods that help bridge the data type differences between Java and SQL data types used in databases.

The following table summarizes the use of each interface to decide which interface to use.

Statement object

Create Statement object

Before you can use a Statement object to execute SQL statements, you need to create an SQL statement using the Connection object's createStatement() method, as shown in the following example-

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

Once a Statement object is created, you can use one of its three execution methods to execute SQL statements.

• boolean execute (String SQL): Returns a boolean value true if a ResultSet object can be retrieved; otherwise returns false. Use this method to execute SQL DDL statements when you need to use real dynamic SQL.

• int executeUpdate (String SQL): Returns the number of rows affected by the SQL statement execution. Use this method to execute SQL statements that you want to affect the number of rows, such as INSERT, UPDATE, or DELETE statements.

• ResultSet executeQuery (String SQL): Returns a ResultSet object. Use this method when you want to get a result set, just like using a SELECT statement.

Close the Statement object

Just like closing the Connection object to save database resources, for the same reason, you should also close the Statement object.

Just a simple call to the close() method is enough. If the Connection object is closed first, it will also close the Statement object. However, you should always explicitly close the Statement object to ensure proper cleanup.

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

To better understand, we recommend that you learnStatement example tutorial.

PreparedStatement object

The PreparedStatement interface extends the Statement interface, providing you with additional features and some advantages over generic Statement objects.

This statement allows you to dynamically provide parameters flexibly.

Create PreparedStatement object

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

All parameters in JDBC are represented by?symbols represent, which is called a parameter marker. You must provide a value for each parameter before executing the SQL statement.

ThesesetXXX()The method binds the value to the parameter, whereXXXIndicates the Java data type you want to bind to the input parameter value. If you forget to provide a value, an SQLException will be thrown.

Each parameter marker is referenced by its sequential position. The first marker represents the position1The second represents the position2And so on. This method is different from the method of Java array indexing, which starts from 0.

All methods of the Statement objects used for interacting with the database (a) execute (), (b) executeQuery () and (c) executeUpdate () can also be used with PreparedStatement objects. However, these methods are modified to use SQL statements that can accept parameters.

Close PreparedStatement object

Just like closing the Statement object, for the same reasons, the PreparedStatement object should also be closed.

A simple close() method call is enough. If you close the Connection object first, it will also close the PreparedStatement object. However, you should always explicitly close the PreparedStatement object 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();
}

To better understand, let's learn aboutPreparedStatement example code.

CallableStatement object

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 a CallableStatement object

Assume that 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 above stored procedure has been written for Oracle, but we are using a 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 demonstrates how to use the Connection.prepareCall() method based on the stored procedure mentioned earlier-Instantiate a CallableStatement object

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

The string variable SQL, representing a 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 is an IN parameter, 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 retrieved SQL type value to a Java data type.

Close CallableStatement Object

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

You can simply call the close() method. If you close the Connection object 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();
}

To better understand, I recommend studyingCallableStatement Example Code.