Java JDBC CallableStatement

Introduction to Java JDBC CallableStatement

The CallableStatement is used to call SQL procedure. The syntax for SQL string is:

"{call procedureName(?...)}"

We copy the syntax of the TestQuery() here:

mysql> delimiter $$ 
mysql> create procedure TestQuery(String varchar(50)) 
       begin 
       select * from Customers
       where FirstName = String; 
       end$$ 
mysql> delimiter ;

As we can see, the parameter name is String, we have to remember that. This String parameter will be passed to the SQL query with the setString() method.

We can use setString(index, string) as the previous example. But in this example, we use the parameter name:

import java.sql.*;
class Test {
  static public void main(String[] args)
      throws SQLException {
    var dbUrl = "jdbc:mysql://localhost/test?"
        + "user=testuser&password=Test@12345";
    var conn = DriverManager.getConnection(dbUrl);
    //create a SQL call to TestQuery procedure
    //the parameter will be in the brackets
    var sql = "{call TestQuery(?)}";
    //Create a CallableStatement that using 
    //the sql string
    CallableStatement call = conn.prepareCall(sql);
    //pass the parameter for TestQuery procedure
    //name of the TestQuery, which is String
    call.setString("String", "Tom");
    //get the ResultSet
    ResultSet set = call.executeQuery();
    //iterate the table and output the rows
    while (set.next()){
      System.out.println(
        "Id:" + set.getInt("CustomerId") +
        " FirstName:" + set.getString("FirstName")
        + " LastName:" + set.getString("LastName")
      );
    }
  }
}

Run the code and we have:

Id:1 FirstName:Tom LastName:Rex

Using try-with-resource with CallableStatement

In our examples above, we never closed the resource. Now we will do it properly:

import java.sql.*;
class Test {
  static public void main(String[] args){
    var dbUrl = "jdbc:mysql://localhost/test?"
        + "user=testuser&password=Test@12345";
    var query = "select * from Test";
    try(
        //using the try-resource block
      var c = DriverManager.getConnection(dbUrl);
      var ps = c.prepareStatement(query)
    ){
      ResultSet set = ps.executeQuery();
      while (set.next()){
        System.out.println(
            "Id: " + set.getInt("Id") + " - " +
                "Name: " + set.getString("Name")
        );
      }
      //close the ResultSet
      set.close();
    }
    catch (SQLException e){}
  }
}

One note, in the older JDBC version, we have to declare the jdbc Driver class with the static method Class.forName(), MySQL Driver class full name is:

“com.mysql.cj.jdbc.Driver”

The line of code is:

Class.forName("com.mysql.cj.jdbc.Driver");

We will need to declare this before we call the DriverManager’s getConnection() method. From JDBC version 4, this is not needed anymore.