Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

JDBC executeQuery() on an INSERT, UPDATE or DELETE throws SQLException

Errorjava.sql.SQLException thrown by Statement.executeQuery() when the SQL is an INSERT, UPDATE or DELETE

Short answer

executeQuery() is only for statements that produce one ResultSet. An INSERT, UPDATE or DELETE produces an update count, so use executeUpdate(), or execute() when you do not know in advance. The reverse also fails: executeUpdate() rejects a statement that returns a ResultSet.

  • Applies to Java 21, JDBC 4.3, MySQL Connector/J 8
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Find the call and read the SQL it passes. If the statement starts with INSERT, UPDATE or DELETE, the method is the wrong one.

    grep -rn "executeQuery(" src/ | head -20
  2. 2Match what you see, then jump to its fix

    1. 1The failing SQL is a data-changing statementexecuteQuery() used for INSERT, UPDATE or DELETEFix
    2. 2SQL comes from a file, a console or user inputThe statement type is not known when the code is writtenFix
    3. 3The failing SQL is a SELECT, or a call that returns a result setexecuteUpdate() used for a statement that returns rowsFix

What does the error look like?

  • java.sql.SQLException thrown by Statement.executeQuery() when the SQL is an INSERT, UPDATE or DELETE
  • java.sql.SQLException thrown by Statement.executeUpdate() when the SQL is a SELECT

Cause 1 of 3

executeQuery() used for INSERT, UPDATE or DELETE

1Check

The JDK documentation says executeQuery cannot be used for statements that do not produce a ResultSet, and throws SQLException when the statement produces anything other than a single ResultSet.

ResultSet rs = stmt.executeQuery("UPDATE users SET active = 1 WHERE id = 5"); // wrong

2Fix

Use executeUpdate(). It returns the row count for DML statements, or 0 for statements that return nothing.

int rows = stmt.executeUpdate("UPDATE users SET active = 1 WHERE id = 5");

3Verify

The call returns the number of changed rows without an exception.

System.out.println(rows);

Confirmed by docs.oracle.com

Did this fix it?

Cause 2 of 3

The statement type is not known when the code is written

1Check

execute() accepts any SQL. It returns true when the first result is a ResultSet and false for an update count, and it does not reject statements that produce a ResultSet.

boolean isResultSet = stmt.execute(sql);

2Fix

Branch on the return value, and use getResultSet() or getUpdateCount() as documented.

boolean isResultSet = stmt.execute(sql);
if (isResultSet) {
    try (ResultSet rs = stmt.getResultSet()) { /* read rows */ }
} else {
    int count = stmt.getUpdateCount(); // -1 means no more results
}

3Verify

Run one SELECT and one UPDATE through the same code path.

// both run without SQLException

Confirmed by docs.oracle.com

Did this fix it?

Cause 3 of 3

executeUpdate() used for a statement that returns rows

1Check

executeUpdate() throws SQLException when the given statement produces a ResultSet object.

int n = stmt.executeUpdate("SELECT * FROM users"); // wrong

2Fix

Use executeQuery() for queries that return rows.

try (ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {
    while (rs.next()) { /* read */ }
}

3Verify

The rows print.

// iterate the ResultSet

Confirmed by docs.oracle.com

Did this fix it?

How do I stop it coming back?

  • Pick the method from the SQL verb: executeQuery for SELECT, executeUpdate for DML, execute when unsure.
  • On a PreparedStatement, call the no-argument executeQuery() or executeUpdate(); passing a SQL string to those methods on a PreparedStatement is an error per the JDK docs.
  • Close statements and result sets with try-with-resources.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.