Create a JDBC General Statement (GS)

How to create a Statement object?

  • A java.sql.Connection object has several methods used to create a java.sql.Statement object, which we can use to execute SQL queries against a database.
    Here is the methods in the java.sql.Connection interface to use:
    Methods in Connection interface Description
    Statement createStatement() When you use this method, the returned Statement object can be used to query for a ResultSet (using the executeQuery() method) that is non-updateable and non-scrollable.
    The next two methods are intended to be used when you want an updatable ResultSet
    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)

    The resultSetHoldability constant will indicates that an open ResultSet objects shall remain open or closed when the current transaction is committed.
  • When you have got a java.sql.Statement object, several methods on that object can be used to execute a SQL statement against the database.
    Here are some importent methods from the java.sql.Statement interface to use:
    Methods in Statement interface Description
    int executeUpdate(String) Executes the given SQL statement, which may be an INSERT, UPDATE, or DELETE statement, or an SQL statement that returns nothing, such as an SQL DDL (Data Definition Language) statement.
    ResultSet executeQuery(String) Used for querying database tables and get back a java.sql.ResultSet object.
    int[] executeBatch() Used for sending multiple SQL statements in a single operation.
    boolean execute(String) Execute the given SQL statement, which may return multiple results. The indicates the form of the first result. You must then use the methods getResultSet or getUpdateCount to retrieve the result, and getMoreResults to move to any subsequent result(s).
    Example using a Statement object and a DriverManager connection:
    import java.sql.Connection;
    import java.sql.DriverManager;
    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 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  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();
          // ...
          // ... codes to handle the Statement 
          // ...
        } catch (ClassNotFoundException ex) {
          Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException e) {
          e.printStackTrace();
        } finally {
          try {
            // Close the Statement
            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.