Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Behavior of SELECT query using executeUpdate

Tags:

java

oracle

jdbc

I have come across a strange behavior while executing a SELECT query using Statement#executeUpdate() by mistake. While the Javadoc clearly states that executeUpdate() throws SQLException if the given SQL statement produces a ResultSet object. But when I'm executing SELECT * from TABLE_NAME, I don't get any exception. Instead I'm getting an return value which is same as the no. of rows selected, if no. is less than or equal to 10. If the no. is more than 10, the return value is always 10.

Connection conn;
Statement stmt;
try {
    conn = getConnection();
    stmt = conn.createStatement();
    int count = stmt.executeUpdate("SELECT * from TABLE_NAME");
    log.info("row count: " + count);
} catch (SQLException e) {
    log.error(e);
    // handle exception
} finally {
    DbUtils.closeQuietly(stmt);
    DbUtils.closeQuietly(conn);
}

I am using Oracle 10g.

Am I missing something here or is it up to the drivers to define their own behavior?

like image 510
Kalyan Sarkar Avatar asked Dec 19 '12 06:12

Kalyan Sarkar


People also ask

What does executeUpdate () method of query interface returns?

The executeUpdate() method returns the number of rows affected by the SQL statement (an INSERT typically affects one row, but an UPDATE or DELETE statement can affect more). int rowcount = stmt.

Which type of query can be used with executeUpdate?

int executeUpdate(): Executes the SQL statement in this Prepared Statement object, which must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing, such as a DDL statement. Save this answer.

What type of value is returned by the executeUpdate ()?

The JDBC standard states that the executeUpdate method returns a row count or 0. However, if the executeUpdate method is executed against a Db2 for z/OS server, it can return a value of -1.

What does executeUpdate do in Java?

executeUpdate. 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. Note:This method cannot be called on a PreparedStatement or CallableStatement .


2 Answers

This behaviour is definetely contradicts Statement.executeUpdate API. What's interesting, java.sql.Driver.jdbcCompliant API says "A driver may only report true here if it passes the JDBC compliance tests". I tested oracle.jdbc.OracleDriver.jdbcCompliant - it returns true. I also tested com.mysql.jdbc.Driver.jdbcCompliant - it returns false. But in the same situation as you describe it throws

Exception in thread "main" java.sql.SQLException: Can not issue SELECT via executeUpdate().

It seems that JDBC drivers are unpredictable.

like image 145
Evgeniy Dorofeev Avatar answered Sep 28 '22 13:09

Evgeniy Dorofeev


According to the specifications Statement.executeUpdate() method returns the row count for SQL Data Manipulation Language (DML).

UPD: I attempted to make an assumption about the returned result (which is always <=10). It seems, that the oracle statement's implementation returns here a number of a such called premature batch count (according to the decompiled sources OraclePreparedStatement class). This is somehow linked to the update statements. May be this value equals 10 by default.

UPD-2: According to this: Performance Extensions: The premature batch flush count is summed to the return value of the next executeUpdate() or sendBatch() method.

like image 22
Andremoniy Avatar answered Sep 28 '22 13:09

Andremoniy