Create a ResultSet

Creating ResultSet with CallableStatement object methods?

• All the prepareCall() methods in the java.sql.Connection interface can be used as a start to create a ResutSet, but only two of them gives you the opportunity to update the DB table through the ResultSet.
  Which are:

  Methods	Description
  CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency)	When you use this method, the returned CallableStatement object can be used to query for a ResultSet (This require that the database-stored procedure must return a DB table) that can be updateable and can be scrollable.
  CallableStatement prepareCall(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability)	This will create a CallableStatement object as the previous one. Use this method when you also want to decide whether you want the resultset to stay open (resultSetHoldability) after a transaction is committed. (Only available in JDBC 3.0)

• Before you creates a ResultSet with the executeQuery() method in the CallableStatement object you have to decide what kind of connection you want. That is done by the parameters of the prepareCall() method.
• The first parameter is about whether the changes made by others in the database table should be reflected in your ResultSet and about which type of ResultSet scrolling you want.
  Optional constant for the first parameter of the prepareCall() method is:

  Constants in ResultSet interface	Description
  java.sql.ResultSet.TYPE_SCROLL_SENSITIVE	This constant indicates that the ResultSet can be scrolled both forward and backward and that the ResultSet is sensitive to changes made by others.
  java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE	This constant indicates that the ResultSet can be scrolled both forward and backward and that the ResultSet is NOT sensitive to changes made by others.
  java.sql.ResultSet.TYPE_FORWARD_ONLY	This constant indicates that the ResultSet can be scrolled only forward.

• The second parameter is about the type of access the ResultSet should have to the underlying database table.
  Optional constant for the second parameter of the prepareCall() method is:

  Constants in ResultSet interface	Description
  java.sql.ResultSet.CONCUR_READ_ONLY	The constant indicating the concurrency mode for a ResultSet object that may NOT be updated.
  java.sql.ResultSet.CONCUR_UPDATABLE	The constant indicating the concurrency mode for a ResultSet object that may be updated.

• The third parameter is about keeping the ResultSet open after committing.
  There is two resultSetHoldability option to use:

  Constants in ResultSet interface	Description
  ResultSet.HOLD_CURSORS_OVER_COMMIT	The constant indicating that open ResultSet objects with this holdability will remain open when the current transaction is commited.
  ResultSet.CLOSE_CURSORS_AT_COMMIT	The constant indicating that open ResultSet objects with this holdability will be closed when the current transaction is commited.

• After you have deside which type of ResultSet you want to create you can create the CallableStatement object with one of the prepareCall() method using your SQL SELECT statement that may also contains parametric placeholders for values.
• If you have any parametric placeholders for values in your SQL statement, then it is time to set these using the CallableStatement setXXX() methods.
• The last ting to do is to retrieve the ResultSet with the method:

  Method in CallableStatement interface	Description
  ResultSet executeQuery()	Executes the SQL procedure call statement in this CallableStatement object and returns the ResultSet object returned by the query contained in the database procedure.

  Example using CallableStatement and ResultSet with a DriverManager connection:

  import java.sql.CallableStatement;
  import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  
  public class Main {
   public static void main(String[] args) {
    Connection conn = null;
    CallableStatement stmt = null;
    try {
     // Register a driver for the MySQL database
     Class.forName("com.mysql.jdbc.Driver");
     // Create a url for accessing the MySQL
     // database CarDB
     String url = "jdbc:mysql://localhost:3306/CarDB";
     // user and password to access the database
     String username = "root";
     String password = "root";
     // User the DriverManager to get a Connection to the database
     conn = DriverManager.getConnection(url, username, password);
     /*---- USING DB Defined PROCEDURE: getCarPark() ----------
      DELIMITER $$
      DROP PROCEDURE IF EXISTS `cardb`.`getCarPark`$$
      CREATE DEFINER=`root`@`localhost`
      PROCEDURE `getCarPark`(IN regInNo varchar(20))
        BEGIN
          select * from CarPark where  regNo LIKE regInNo;
        END$$
      DELIMITER ;
     ---------------------------------------------------------- */
     // Create a  CallableStatement for a ResultSet that will
     // return all the columns and rows from the dB table Carpark
     // using the procedure;  getCarPark. 
     stmt = conn.prepareCall("{ call getCarPark('%') } ");
     // Gets the ResultSet from the CallableStatement object
     ResultSet resultSet = stmt.executeQuery();
     // ...
     // ... codes to handle the ResultSet
     // ...
    } catch (ClassNotFoundException ex) {
     ex.printStackTrace();
    } catch (SQLException e) {
     e.printStackTrace();
    } finally {
     try {
      // Close the Statement, which also close the ResultSet
      stmt.close();
      conn.close();
     } catch (Exception xe) {
      xe.printStackTrace();
     }
    }
   }
  }

  Tools to try examples.

  You can download and install all software needed to try-out the examples here.