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.

© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.