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

Question

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

Answer 1

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

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

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.

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

Answer 2

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, and
• you could put the ResultSets into an array ...

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 use of execute is to execute an SQL statement if you don't know if it is a query, an update (of some kind) ... or something else that potentially delivers multiple result sets. It is a generalization of executeQuery() and executeUpdate() ...

Answer 3

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.