Statement Interface in Java

The Statement interface is used to send a fixed SQL string to the database and get the result. You create one from a connection with con.createStatement(), then call one of its three execute methods, depending on the kind of SQL. Statement is the simplest of JDBC's three statement types; PreparedStatement and CallableStatement extend it. Because a Statement takes complete SQL text, it suits SQL that contains no values from outside, such as creating tables. In this lesson, you will learn the three execute methods, what each returns, and how to read a key generated by the database.

Methods

Example: The Three Execute Methods

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
 
public class Stmt {
    public static void main(String[] args) throws Exception {
        try (Connection con = DriverManager.getConnection("jdbc:h2:mem:school");
             Statement st = con.createStatement()) {
 
            // execute: any SQL. Returns true only if there is a ResultSet.
            boolean hasRows = st.execute("CREATE TABLE student ("
                + "id INT AUTO_INCREMENT PRIMARY KEY, "
                + "name VARCHAR(50), marks INT)");
            System.out.println("execute(CREATE) returned " + hasRows);
 
            // executeUpdate: INSERT, UPDATE, DELETE. Returns the row count.
            int inserted = st.executeUpdate(
                "INSERT INTO student (name, marks) VALUES "
                + "('Navika', 91), ('Srihitha', 84), ('Advitha', 78)");
            int updated = st.executeUpdate(
                "UPDATE student SET marks = marks + 2 WHERE marks < 90");
            int deleted = st.executeUpdate(
                "DELETE FROM student WHERE name = 'Nobody'");
            System.out.println(inserted + " inserted, " + updated
                + " updated, " + deleted + " deleted");
 
            // executeQuery: SELECT. Returns a ResultSet.
            try (ResultSet rs = st.executeQuery(
                    "SELECT name, marks FROM student ORDER BY id")) {
                while (rs.next()) {
                    System.out.println(rs.getString("name") + " "
                        + rs.getInt("marks"));
                }
            }
 
            // Getting the key the database generated.
            st.executeUpdate(
                "INSERT INTO student (name, marks) VALUES ('Navika A', 88)",
                Statement.RETURN_GENERATED_KEYS);
            try (ResultSet keys = st.getGeneratedKeys()) {
                keys.next();
                System.out.println("generated id: " + keys.getInt(1));
            }
        }
    }
}

execute(CREATE) returned false
3 inserted, 2 updated, 0 deleted
Navika 91
Srihitha 86
Advitha 80
generated id: 4

Explanation:

1. execute returned false because CREATE TABLE produces no rows.
2. executeUpdate returned the rows affected: 3 inserted; 2 updated (the two students below 90); 0 deleted, because no row matched. A count of 0 is not an error, so check it when a change must happen.
3. executeQuery returned the three rows, showing the updated marks.
4. RETURN_GENERATED_KEYS asks the driver to keep the key that the AUTO_INCREMENT column produced. getGeneratedKeys() returns it as a small result set: the new row got id 4.

One Statement, One Open ResultSet

A Statement can have only one open ResultSet at a time. Running another query on the same statement closes the previous result set. To read two result sets at once, for example in nested loops, use two statements.

When Not to Use Statement

A Statement receives the whole SQL as one string. If part of that string comes from a user, the user's text becomes part of the SQL command, which is the security hole known as SQL injection. For any SQL with values that vary, use PreparedStatement (see "PreparedStatement in Java" and "Statement vs PreparedStatement (SQL Injection)").

Common Mistakes

Using executeQuery for an INSERT, or executeUpdate for a SELECT. The driver throws SQLException.

Ignoring the update count. UPDATE ... WHERE id = 99 succeeds with 0 rows when id 99 doesn't exist.

Joining user input into the SQL string. Use PreparedStatement.

Reusing a statement while still reading its previous result set. The first result set is closed.

Interview Corner

Practice

1. Easy: Use execute to run a SELECT, check that it returns true, and read the rows from getResultSet().
2. Harder: Delete every student below a pass mark with one executeUpdate and print "n students removed", handling the case where n is 0.

Key Takeaways