First, these methods are not directly related to stored procedures. They are methods on the JDBC API's Statement interface. See the javadoc for Statement. The following recommendations apply any time a statement or anything that extends statement (PreparedStatement, CallableStatement) is used. It makes no difference Whether it is used to execute DML queries like SELECT, UPDATE, DELETE or to execute a stored procedure.
However, stored procedures add some confusion in determining what to use. If someone else wrote the stored procedure we may have to do a bit of research to find out the implementation details in which to consider how to use the following guidelines.
executeBatch - returns an int[] of update counts.
- Use this to send multiple commands to the database as a batch. It returns an array of ints with the update counts when all commands execute successfully. Batching can be useful to increase performance if several commands can be sent to the database for execution at that same time. Batching also requires other code changes not covered in this answer.
executeUpdate - returns an int for the update count
- For queries: Use for queries like INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, ETC. that return an status like rows affected.
- For stored procedures: Use when the stored procedure does not return a result set.
executeQuery - returns a ResultSet.
- For queries: Use when executing SELECT queries that return a result set.
- For stored procedures: Use when the stored procedure returns one result set.
For an example of using executeQuery in QIE see this KB article.
execute - The return value is a boolean. When true the query returned one or more ResultSets. When false an int or nothing is returned.
- For queries: Used for any kind of query or when the more the one ResultSet or update count is returned.
- For stored procedures: Use this method if you do not know what a stored procedure returns or when a stored procedure returns multiple ResultSets or update counts. If the execute method returns true you can then check for returned result sets etc.
NOTE: When a stored procedure returns multiple ResultSets or UpdateCounts it is recommened to '...retrieve all ResultSet(s) and Update Counts first followed by retrieving the OUT type parameters and return values' as per "The Stored Procedure" section of this reference.
This example demonstrates using execute with multiple ResultSets in QIE. It is a conversion of the "Using Muliple Result Sets" example into a QIE script.
NOTE: How the "results" variable stores a boolean that is used to determine if there are ResultSets to be processed. Compare that to the example mentioned above for executeQuery.
//STEP 1: Obtain a connection
var connection = qie.getDbConnection('AdventureWorks2012');
var statement;
try {
//STEP 2: Create a statement
qie.debug("Creating statement...");
statement = connection.prepareCall("{CALL [dbo].[storedProcedureWithTwoResults]}");
// statement = connection.prepareCall("EXEC [dbo].[storedProcedureWithTwoResults] ");
//STEP 3: Execute stored procedure
var results = statement.execute();
//STEP 4: Loop through the available result sets.
var rsCount = 0;
do {
if (results) {
var rs = statement.getResultSet();
rsCount++;
//STEP 5: Show data from the result set.
qie.debug("RESULT SET #" + rsCount);
while (rs.next()) {
qie.debug(rs.getString("LastName") + ", " + rs.getString("FirstName"));
}
}
results = statement.getMoreResults();
} while (results);
//STEP 6: Clean-up environment
statement.close();
connection.close();
} catch(e) {
qie.debug(e);
} finally {
try{
if (statement !== null) {
statement.close();
}
} catch (e) {
qie.debug(e);
}
try{
if (connection !== null) {
connection.close();
}
} catch (e) {
qie.debug(e);
}
}
For more about the details of the JDBC API see the JDBC javadocs and the JDBC tutorial.
Also see the javadocs for the database vendor specific JDBC driver. Here is Microsoft's JDBC driver for SQL Server documentation. This section covers Using Statements with stored procedures.
Also see these topics:
