Java JDBC - Executing Query

In this post we will discuss about executing queries using JDBC. In previous post we already have gone through a sample code for making connections and executing queries in Java using JDBC. For executing queries we had used Statement interface for executing queries, there are some other ways as well for executing queries. Below is the list of options that we have in Java JDBC for executing queries.


1. Statement Interface
We should use object of Statement interface when we want to execute static SQL query. In other words when our query do not want parameter values at runtime then we should use this interface.

2. PreparedStatment Interface
If our query wants parameter at runtime, in other words if our query is dynamic, then we should use object of PreparedStatment interface. We can set parameter values at runtime using PreparedStatement interface.

3. CallableStatement Interface
When we want to execute stored procedures in through our code then we should use object of CallableStatement interface.

Now let us discuss about these interfaces in little bit details.

Note : Examples below will be using an object of Connection for creating objects of above interfaces, so we will not see code in examples below for creating connections with databases. 

1. Statement Interface

Below is the sample code for creating Statement objects.

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

After creating the object of Statement interface, we can call below methods depending on our need.

ResultSet executeQuery(String sql) :

When we execute a SELECT query a result set is returned, if we want to iterate through that result set we can call executeQuery method of Statement interface which returns an object of ResultSet interface, that can be use to iterate through the result returned by query.

int executeUpdate(String sql) :

If we want to get number of rows impacted after executing a DML statement (INSERT, DELETE, UPDATE)  then we can call executeUpdate method of Statement interface. This method will return number of rows impacted by the query.

boolean execute(String sql) :

If we want to execute DDL statement (CREATE, DROP, ALTER)  then we should use this method, it returns true if ResultSet object can be retrieved after executing the query otherwise it will return false. 

When we finish our work with Statement object, it is important for us to close that object in order to save database resources. If we close Connection object first then it will automatically close the Statement object, but still I will prefer to close Statement object explicitly. 

2. PreparedStatement Interface

If we have dynamic SQL query which needs parameter at runtime then Statement interface will not be able to handle those queries, for these type of queries we use PreparedStatement interface. 

PreparedStatement interface extends Statement interface, it has added advantages and functionality over Statement interface. 

For example we want to get employee record of an employee whose id is provided at runtime, then we will use PreparedStatement as shown below :

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

In our query, for parameters that we want to input at runtime is specified by ? in query.  For each ? in query we have to set values using setXXX(int index, XXX value) method of PreparedStatement object, where XXX  is data type of value you want to set and index is the position of ? for which you want to set value. Some of the examples are shown below :

setInt(int index, int value) : This method is used for setting integer values in our query.
setString(int index, String value) : This method is used for setting String values.

Here is the complete list of methods available in PreparedStatment interface.


3. CallableStatement Interface
If we want to call stored procedures and functions from our code then we should use CallableStatement interface. Similar to PreparedStatement and Statement interfaces, Connection object is used to create objects of CallableStatement interface.

Suppose we have following procedure in our MySQL database :

DELIMITER $$

DROP PROCEDURE IF EXISTS `MATH`.`sum` $$
CREATE PROCEDURE `MATH`.`sum` 
   (IN NUM1 INT,IN NUM2 INT, OUT SUM INT)
BEGIN
   SELECT NUM1+NUM2 INTO SUM   
END $$

DELIMITER ;

We can call above procedure from code as shown below :

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

The way we are setting value for ? in PreparedStatement, we have to use same way for passing IN parameters to CallableStatement, but for OUT and INOUT parameters we have call an additional method of CallableStatement i.e. registerOutParameter(). While calling registerOutParameter() we need to make sure the data type of the variable that we are registering should be same as the procedure is expected to return.
When execution of CallableStatement is completed we can retrieve value of OUT and INOUT parameter with appropriate getXXX() method.

Note : It is very important to close resources when we are finish with them, for efficient programming. Therefore in finally block always remember to close objects of Statement, PreparedStatement and CallableStatement by calling close() method.

In subsequent topics we will discuss about functions of ResultSet, PreparedStatement and Other interfaces that help us to write an efficient program. 

Share: