JDBC PS creating ResultSet

Creating ResultSet with PreparedStatement object methods?

  • All the prepareStatement() 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 database table through the ResultSet.
    Which are:
    Methods in Connection interface Description
    PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency) When you use this method, the returned PreparedStatement object can be used to query for a ResultSet (using the executeQuery() method) that can be updateable and can be scrollable.
    PreparedStatement prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) This will create a PreparedStatement 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 PreparedStatement object you have to decide what kind of connection you want. That is done by the 2., 3., and 4. parameters of the prepareStatement() method.
  • As the first parameter to the prepareStatement() method always is the SQL to be executed , the second parameter is about reflection on changes made by other updating the DB table and it is also about the type of Scrolling in the ResultSet.
    Optional constant for the 2. parameter in the prepareStatement() method is:
    Constants in ResultSet interface
    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 Third parameter to the prepareStatement() method is about access posibilities of the DB table through the created ResultSet.
    Optional constant for the 3. parameter in the prepareStatement() 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 Fourth parameter to the prepareStatement() method is about keeping a created 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 PreparedStatement object with one of the prepareStatement() 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 PreparedStatement setXXX() methods.
  • The last ting to do is to retrieve the ResultSet with the method:
    Method in PreparedStatement interface Description
    ResultSet executeQuery() Executing SQL statement in this PreparedStatement object and returns a ResultSet object that is generated based on the SQL statement.
    Example code:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class Main {
     public static void main(String[] args) {
      Connection conn = null;
      PreparedStatement 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);
       // Create a  PreparedStatement for a ResultSet that is
       // sensitive for other users updating of tables in the database and
       // the ResultSet is updatable for a selected table in the database.
       // Select all columns from DB table Carpark where regNo
       // is "XY34567"
       stmt = conn.prepareStatement("select * from Carpark " +
               " where regNo=?", ResultSet.TYPE_SCROLL_SENSITIVE,
               ResultSet.CONCUR_UPDATABLE);
       // set the regnNo in the Where clause to be "XY34567"
       stmt.setString(1, "XY34567");
       // Gets the ResultSet from the PreparedStatement object
       ResultSet resultSet = stmt.executeQuery();
       // ...
       // ... codes to handle the ResultSet
       // ...
      } catch (ClassNotFoundException ex) {
        Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
      } catch (SQLException e) {
        e.printStackTrace();
      } finally {
        try {
         // Close the Statement, which also close the ResultSet
         stmt.close();
         conn.close();
       } catch (Exception xe) {
         xe.printStackTrace();
       }
      }
     }
    }
© 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.