JDBC GS creating ResultSet

How do you create a ResultSet using a Statement object?

  • All the createStatement() 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
    Statement createStatement (int resultSetType, int resultSetConcurrency) When you use this method, the returned Statement object can be used to query for a ResultSet (using the executeQuery() method) that can be updateable and can be scrollable.
    Statement createStatement(int resultSetType, int resultSetConcurrency, int resultSetHoldability) This will create a Statement 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 Statement object you have to decide what kind of connection you want. That is done by the parameters of the createStatement() 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 createStatement() 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 createStatement() 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 Statement object with one of the createStatement() methods using your SQL SELECT statement.
  • The last ting to do is to retrieve the ResultSet with the method:
    Method in Statement interface Description
    ResultSet executeQuery() Executing SQL statement in this Statement object and returns a ResultSet object that is generated based on the SQL statement.
    Example using Statement and ResultSet with a DriverManager connection:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class Main {
      public static void main(String[] args) {
        Connection conn = null;
        Statement 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 passord 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  Statement 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.
          stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                  ResultSet.CONCUR_UPDATABLE);
          // Create a  ResultSet with a query that returns
          // all the columns  from the dB table Carpark
          ResultSet resultSet = stmt.executeQuery("select * from Carpark ");
          // ...
          // ... 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();
          }
        }
      }
    }
  • You should use the close() method in the Statement object when you do not need it anymore.
  • You should also use the close() method in the Connection object when you do not need it anymore.

© 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.