Capturing ResultSet with executeQuery()

JDBC for MySQL - Herong's Tutorial Examples

∟Capturing ResultSet with executeQuery()

This section describes how to capture ResultSet objects by calling executeQuery() to execute the CALL statement.

There are two ways to execute the CALL statement enclosed in the CallableStatement object:

rs.executeUpdate() - Execute the CALL statement without collecting any returning ResultSet. Use this method when the stored procedure is not returning any result sets or you want to ignore them.
res = rs.executQuery() - Execute the CALL statement and collect the returning ResultSet objects. Use this method when the stored procedure is returning result sets and you want to collect them.

Here is a sample program that show you how to collect the returning ResultSet of the HelloWorld() procedure:

/* MySqlCallExecuteQuery.java
 * Copyright (c) HerongYang.com. All Rights Reserved.
 */
import java.sql.*;
public class MySqlCallExecuteQuery {
  public static void main(String [] args) {
    Connection con = null;
    try {
      com.mysql.cj.jdbc.MysqlDataSource ds
        = new com.mysql.cj.jdbc.MysqlDataSource();
      ds.setServerName("localhost");
      ds.setPortNumber(3306);
      ds.setDatabaseName("HerongDB");
      ds.setUser("Herong");
      ds.setPassword("TopSecret");
      ds.setServerTimezone(java.util.TimeZone.getDefault().getID());
      con = ds.getConnection();

// Create CallableStatement
      CallableStatement cs = con.prepareCall("CALL HelloWorld()");

// Execute the call statement and collect the ResultSet
      ResultSet res = cs.executeQuery();

// Retrieving the result
      res.next();
      System.out.println("Result: "+res.getString("Message"));

// Close resource
      cs.close();

      con.close();
    } catch (Exception e) {
      System.err.println("Exception: "+e.getMessage());
      e.printStackTrace();
    }
  }
}

This time the result of the query "SELECT 'Hello world!' AS Message;" in HelloWorld() procedure was successfully collected:

herong> java -cp .:mysql-connector-java.jar \
   MySqlCallExecuteQuery.java

Result: Hello world!

Table of Contents

About This Book

JDBC (Java Database Connectivity) Introduction

JDK (Java SE) Installation

MySQL Installation on Windows

MySQL JDBC Driver (MySQL Connector/J)

MySQL - PreparedStatement

MySQL - Reference Implementation of JdbcRowSet

►MySQL - JBDC CallableStatement

Overview of CallableStatement Objects

"CREATE PROCEDURE" - Creating a Simple Procedure

Creating Procedures with IN and OUT Parameters

Creating Procedures with INOUT Parameters

Creating Procedures with Multiple Queries

Creating CallableStatement Objects with prepareCall()

►Capturing ResultSet with executeQuery()

Creating CallableStatement Objects with Parameters

Common Errors with CallableStatement Parameters

Creating CallableStatement Objects with INOUT Parameters

Retrieving Multiple ResultSet Objects

Executing Stored Procedures without Permission

getProcedures() - Listing Stored Procedures

MySQL CLOB (Character Large Object) - TEXT

MySQL BLOB (Binary Large Object) - BLOB

Using Connection Pool with JDBC

Archived Tutorials

References

Full Version in PDF/EPUB