JDBC Tutorial

Jdbc Statement

Processing SQL Statements with JDBC

In general, to process any SQL statement with JDBC, you follow these steps:

  1. Establishing a connection.
  2. Create a statement.
  3. Execute the query.
  4. Process the ResultSet object.
  5. Close the connection.

This page uses the following method, CoffeesTables.viewTable, from the tutorial sample to demonstrate these steps. This method outputs the contents of the table COFFEES. This method will be discussed in more detail later in this tutorial:

public static void viewTable(Connection con, String dbName)
    throws SQLException {

    Statement stmt = null;
    String query = "select COF_NAME, SUP_ID, PRICE, " +
                   "SALES, TOTAL " +
                   "from " + dbName + ".COFFEES";
    try {
        stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        while (rs.next()) {
            String coffeeName = rs.getString("COF_NAME");
            int supplierID = rs.getInt("SUP_ID");
            float price = rs.getFloat("PRICE");
            int sales = rs.getInt("SALES");
            int total = rs.getInt("TOTAL");
            System.out.println(coffeeName + "\t" + supplierID +
                               "\t" + price + "\t" + sales +
                               "\t" + total);
        }
    } catch (SQLException e ) {
        JDBCTutorialUtilities.printSQLException(e);
    } finally {
        if (stmt != null) { stmt.close(); }
    }
}

Establishing Connections

First, establish a connection with the data source you want to use. A data source can be a DBMS, a legacy file system, or some other source of data with a corresponding JDBC driver. This connection is represented by a Connection object. See Establishing a Connection for more information.

Creating Statements

A Statement is an interface that represents a SQL statement. You execute Statement objects, and they generate ResultSet objects, which is a table of data representing a database result set. You need a Connection object to create a Statement object.

For example, CoffeesTables.viewTable creates a Statement object with the following code:

stmt = con.createStatement();

There are three different kinds of statements:

Statement: Used to implement simple SQL statements with no parameters.

Prepared Statement: (Extends Statement.) Used for precompiling SQL statements that might contain input parameters. See Using Prepared Statements for more information.

Callable Statement: (Extends PreparedStatement.) Used to execute stored procedures that may contain both input and output parameters. See Stored Procedures for more information.

Executing Queries

To execute a query, call an execute method from Statement such as the following:

execute:Returns true if the first object that the query returns is a ResultSet object. Use this method if the query could return one or more ResultSet objects. Retrieve the ResultSet objects returned from the query by repeatedly calling Statement.getResultSet.

execute Query: Returns one ResultSet object.

execute Update: Returns an integer representing the number of rows affected by the SQL statement. Use this method if you are using INSERT, DELETE, or UPDATE SQL statements.

For example, CoffeesTables.viewTable executed a Statement object with the following code:

ResultSet rs = stmt.executeQuery(query);

See Retrieving and Modifying Values from Result Sets for more information.

Processing ResultSet Objects

You access the data in a ResultSet object through a cursor. Note that this cursor is not a database cursor. This cursor is a pointer that points to one row of data in the ResultSet object. Initially, the cursor is positioned before the first row. You call various methods defined in the ResultSet object to move the cursor.

For example, CoffeesTables.viewTable repeatedly calls the method ResultSet.next to move the cursor forward by one row. Every time it calls next, the method outputs the data in the row where the cursor is currently positioned:

try {
    stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    while (rs.next()) {
        String coffeeName = rs.getString("COF_NAME");
        int supplierID = rs.getInt("SUP_ID");
        float price = rs.getFloat("PRICE");
        int sales = rs.getInt("SALES");
        int total = rs.getInt("TOTAL");
        System.out.println(coffeeName + "\t" + supplierID +
                           "\t" + price + "\t" + sales +
                           "\t" + total);
    }
}
// ...

See Retrieving and Modifying Values from Result Sets for more information.

Closing Connections

When you are finished using a Statement, call the method Statement.close to immediately release the resources it is using. When you call this method, its ResultSet objects are closed.

For example, the method CoffeesTables.viewTable ensures that the Statement object is closed at the end of the method, regardless of any SQLException objects thrown, by wrapping it in a finally block:

} finally {
    if (stmt != null) { stmt.close(); }
}

JDBC throws an SQLException when it encounters an error during an interaction with a data source. See Handling SQL Exceptions for more information.

In JDBC 4.1, which is available in Java SE release 7 and later, you can use a try-with-resources statement to automatically close Connection, Statement, and ResultSet objects, regardless of whether an SQLException has been thrown. An automatic resource statement consists of a try statement and one or more declared resources.

For example, you can modify CoffeesTables.viewTable so that its Statement object closes automatically, as follows:

public static void viewTable(Connection con) throws SQLException {

    String query = "select COF_NAME, SUP_ID, PRICE, " +
                   "SALES, TOTAL " +
                   "from COFFEES";

    try (Statement stmt = con.createStatement()) {

        ResultSet rs = stmt.executeQuery(query);

        while (rs.next()) {
            String coffeeName = rs.getString("COF_NAME");
            int supplierID = rs.getInt("SUP_ID");
            float price = rs.getFloat("PRICE");
            int sales = rs.getInt("SALES");
            int total = rs.getInt("TOTAL");
            System.out.println(coffeeName + ", " + supplierID +
                               ", " + price + ", " + sales +
                               ", " + total);
        }
    } catch (SQLException e) {
        JDBCTutorialUtilities.printSQLException(e);
    }
}

The following statement is an try-with-resources statement, which declares one resource, stmt, that will be automatically closed when the try block terminates:

try (Statement stmt = con.createStatement()) {
    // ...
}
Modifier and TypeMethod and Description
void addBatch(String sql)Adds the given SQL command to the current list of commmands for this Statement object.
void cancel()Cancels this Statement object if both the DBMS and driver support aborting an SQL statement.
void clearBatch()Empties this Statement object's current list of SQL commands.
void clearWarnings()Clears all the warnings reported on this Statement object.
void close()Releases this Statement object's database and JDBC resources immediately instead of waiting for this to happen when it is automatically closed.
void closeOnCompletion()Specifies that this Statement will be closed when all its dependent result sets are closed.
boolean execute(String sql)Executes the given SQL statement, which may return multiple results.
boolean execute(String sql, int autoGeneratedKeys)Executes the given SQL statement, which may return multiple results, and signals the driver that any auto-generated keys should be made available for retrieval.
boolean execute(String sql, int[] columnIndexes)Executes the given SQL statement, which may return multiple results, and signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval.
boolean execute(String sql, String[] columnNames)Executes the given SQL statement, which may return multiple results, and signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval.
int[] executeBatch()Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts.
ResultSet executeQuery(String sql)Executes the given SQL statement, which returns a single ResultSet object.
int executeUpdate(String sql)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 statement.
int executeUpdate(String sql, int autoGeneratedKeys)Executes the given SQL statement and signals the driver with the given flag about whether the auto-generated keys produced by this Statement object should be made available for retrieval.
int executeUpdate(String sql, int[] columnIndexes)Executes the given SQL statement and signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval.
int executeUpdate(String sql, String[] columnNames)Executes the given SQL statement and signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval.
Connection getConnection()Retrieves the Connection object that produced this Statement object.
int getFetchDirection()Retrieves the direction for fetching rows from database tables that is the default for result sets generated from this Statement object.
int getFetchSize()Retrieves the number of result set rows that is the default fetch size for ResultSet objects generated from this Statement object.
ResultSet getGeneratedKeys()Retrieves any auto-generated keys created as a result of executing this Statement object.
int getMaxFieldSize()Retrieves the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object.
int getMaxRows()Retrieves the maximum number of rows that a ResultSet object produced by this Statement object can contain.
boolean getMoreResults()Moves to this Statement object's next result, returns true if it is a ResultSet object, and implicitly closes any current ResultSet object(s) obtained with the method getResultSet.
boolean getMoreResults(int current)Moves to this Statement object's next result, deals with any current ResultSet object(s) according to the instructions specified by the given flag, and returns true if the next result is a ResultSet object.
int getQueryTimeout()Retrieves the number of seconds the driver will wait for a Statement object to execute.
ResultSet getResultSet()Retrieves the current result as a ResultSet object.
int getResultSetConcurrency()Retrieves the result set concurrency for ResultSet objects generated by this Statement object.
int getResultSetHoldability()Retrieves the result set holdability for ResultSet objects generated by this Statement object.
int getResultSetType()Retrieves the result set type for ResultSet objects generated by this Statement object.
int getUpdateCount()Retrieves the current result as an update count; if the result is a ResultSet object or there are no more results, -1 is returned.
SQLWarning getWarnings()Retrieves the first warning reported by calls on this Statement object.
boolean isClosed()Retrieves whether this Statement object has been closed.
boolean isCloseOnCompletion()Returns a value indicating whether this Statement will be closed when all its dependent result sets are closed.
boolean isPoolable()Returns a value indicating whether the Statement is poolable or not.
void setCursorName(String name)Sets the SQL cursor name to the given String, which will be used by subsequent Statement object execute methods.
void setEscapeProcessing(boolean enable)Sets escape processing on or off.
void setFetchDirection(int direction)Gives the driver a hint as to the direction in which rows will be processed in ResultSet objects created using this Statement object.
void setFetchSize(int rows)Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSet objects genrated by this Statement.
void setMaxFieldSize(int max)Sets the limit for the maximum number of bytes that can be returned for character and binary column values in a ResultSet object produced by this Statement object.
void setMaxRows(int max)Sets the limit for the maximum number of rows that any ResultSet object generated by this Statement object can contain to the given number.
void setPoolable(boolean poolable)Requests that a Statement be pooled or not pooled.
void setQueryTimeout(int seconds)Sets the number of seconds the driver will wait for a Statement object to execute to the given number of seconds.