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.