CODEWITHSUNDEEP
javajdbcresultset
Rollerball
Rollerball

Reputation: 13108

Statement.execute(sql) vs executeUpdate(sql) and executeQuery(sql)

I have a question related to this method: st.execute(sql); where st obviously is a Statement object. Directly from this oracle java tutorial:

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.getResutSet.

What is meant by "one or more ResultSet objects"? How is it possible to manage them once got an array of ResultSet? Whereas st.executeQuery(sql) and st.executeUpdate(sql) are very clear. It's not (at least to me) the aim of st.execute(sql) which can also return an int as if it was updated a table.

Thanks in advance

Upvotes: 46

Views: 81917

Answers (4)

JeevanKumar R
JeevanKumar R

Reputation: 1

I understand this is a very old post to respond to, but I recently encountered a situation where my code was throwing the exception java.sql.SQLRecoverableException: ORA-17002. I found the issue while executing a CallableStatement, as the Oracle procedure returns a REF CURSOR, whereas SQL Server returns a simple ResultSet in my case. To fix this, I used execute() for Oracle.

execute() : The method is used when you don’t know in advance whether the SQL statement will return a ResultSet (like a SELECT query) or an update count (like an INSERT, UPDATE, DELETE query, or a DDL statement such as CREATE TABLE).It's commonly used for executing stored procedures or dynamic SQL where the outcome is uncertain or mixed (i.e., it might return both a ResultSet and an update count).

executeUpdate() : The executeQuery() method is specifically used for executing SQL SELECT statements. It always expects a ResultSet to be returned, and it should only be used for queries that select data from the database.

executeQuery() : The executeUpdate() method is used for executing DML statements like INSERT, UPDATE, or DELETE, or DDL statements like CREATE TABLE or DROP TABLE. It does not return a ResultSet; instead, it returns the number of rows affected by the execution of the SQL statement.

Upvotes: -1

Stephen C
Stephen C

Reputation: 718886

What do they mean by "one or more ResultSet objects"?

The javadoc for the execute method says this:

"Executes the given SQL statement, which may return multiple results. In some (uncommon) situations, a single SQL statement may return multiple result sets and/or update counts. Normally you can ignore this unless you are (1) executing a stored procedure that you know may return multiple results or (2) you are dynamically executing an unknown SQL string."

That pretty much explains it. Sometimes a query can deliver more than one ResultSet.

if so how is it possible to manage them once got an array of ResultSet?

I'm not sure what you mean but:

  • you can't get them as an array: you must get them one at a time by calling getResultSet(), and
  • you could put the ResultSet objects into an array (if you knew how many to expect) or a List.

It's not (at least to me) the aim of st.execute(sql) which can also return an int as if it was updated a table.

One of the supported use-cases of execute mentioned in the quoted javadoc is to execute an unknown SQL statement. In this case, you don't know if it is a query, an update (of some kind) ... or something else that potentially delivers multiple result sets. This use-case requires the method to be able to return multiple results in some cases.

Basically, execute is a generalization of executeQuery() and executeUpdate(). If you don't need the generality, use the specialized versions instead.

Upvotes: 19

Kaveh Safavi
Kaveh Safavi

Reputation: 567

boolean execute(): Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.

ResultSet executeQuery(): Executes the SQL query in this PreparedStatement object and returns the ResultSet object generated by the query.

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

The best place to find answers to questions like this is the Javadocs: Here

Upvotes: 33

satish kendre
satish kendre

Reputation: 81

execute() : The method used for all types of SQL statements, and that is, returns a boolean value of TRUE or FALSE. If the method return TRUE, return the ResultSet object and FALSE returns the int value.

executeUpdate() : This method is used for execution of DML statement(INSERT, UPDATE and DELETE) which is return int value, count of the affected rows.

executeQuery() : This method is used to retrieve data from database using SELECT query. This method returns the ResultSet object that returns the data according to the query.

Upvotes: 8

Related Questions