Java JDBC Statement

A Statement sends a complete, fixed SQL string to the database. You create one from a Connection, hand it SQL, and get back either a ResultSet (for queries) or an update count (for changes). It is the simplest of the three JDBC statement types — and the one you should reach for least, because any variable data in the SQL must be concatenated in by hand, which is how SQL injection bugs are born.

This chapter covers how to create and execute a Statement, the three execute methods and when each applies, how to tune the cursor and read generated keys, and — most importantly — when to stop and use a PreparedStatement instead. If you are new to JDBC, start with the JDBC introduction.

Creating and executing

try (Connection conn = DriverManager.getConnection(url, user, pw);
     Statement st = conn.createStatement()) {
  // a query → ResultSet
  try (ResultSet rs = st.executeQuery("SELECT count(*) FROM product")) {
    rs.next();
    System.out.println(rs.getInt(1));
  }
  // a change → update count
  int rows = st.executeUpdate("UPDATE product SET active = true WHERE price > 0");
  System.out.println(rows + " rows updated");
}

Three execute methods

Use executeQuery and executeUpdate whenever you know in advance which kind of statement you are running — they return the right type directly. Reach for execute only in generic tooling (a SQL console, a migration runner) where the SQL is not known until runtime; after it you call getResultSet() or getUpdateCount() to retrieve the result.

executeUpdate returns 0 for DDL such as CREATE TABLE, and for INSERT/UPDATE/DELETE it returns the number of rows affected — useful for confirming that an update actually matched a row.

Tuning the cursor and generated keys

When you create a statement you can choose how the resulting cursor behaves with createStatement(resultSetType, resultSetConcurrency) — for example TYPE_FORWARD_ONLY, CONCUR_READ_ONLY (the default and fastest). Ask for TYPE_SCROLL_INSENSITIVE only when you need to move backwards through the result, and CONCUR_UPDATABLE only when you intend to edit rows through the cursor; both cost more.

For inserts, pass Statement.RETURN_GENERATED_KEYS and then read the database-assigned primary key with getGeneratedKeys():

try (Statement st = conn.createStatement()) {
  st.executeUpdate(
      "INSERT INTO product(name, price) VALUES ('Widget', 9.99)",
      Statement.RETURN_GENERATED_KEYS);
  try (ResultSet keys = st.getGeneratedKeys()) {
    if (keys.next()) {
      long newId = keys.getLong(1);
      System.out.println("inserted id = " + newId);
    }
  }
}

Without that flag the call succeeds but getGeneratedKeys() returns an empty ResultSet, so you cannot recover the new id.

When NOT to use Statement

The moment any part of the SQL comes from a variable — a user name, an id, a search term — stop and use PreparedStatement instead. Concatenating values into a Statement string is unsafe: a value containing a quote can change the meaning of the command. PreparedStatement also caches its parse plan, so a query you run in a loop is faster as a prepared statement. The next chapter is dedicated to that safe alternative; for stored procedures, see CallableStatement.

Reserve Statement for fixed, value-free SQL: schema setup (CREATE TABLE …), one-off DDL, or a hard-coded SELECT with no variable part.

A worked example: the cursor constants and the injection trap

This program prints the ResultSet/Statement tuning constants you pass when creating a statement, then demonstrates concretely why string-built SQL is dangerous — by showing what a malicious value does to the command text.

What to take from the run:

• The cursor constants are plain ints you pass to createStatement. TYPE_FORWARD_ONLY + CONCUR_READ_ONLY is the default and cheapest; you only ask for a scrollable or updatable cursor when you genuinely need it.
• Statement.RETURN_GENERATED_KEYS is the flag that lets an INSERT give you the new auto-increment id back through getGeneratedKeys() — without it you cannot recover the database-assigned key.
• The first concatenated query is harmless because Acme has no SQL metacharacters. That is exactly why string concatenation seems to work in testing — and then breaks in production on real-world input.
• The second value contains a quote and a semicolon, so the single intended SELECT becomes a SELECT followed by a DROP TABLE. The data escaped its quotes and became executable SQL — the textbook definition of injection.
• The fix is never "escape the quotes yourself." It is to stop building SQL from values at all and let PreparedStatement send the template and the data separately — the subject of the next chapter.

Practice