Handling Complex Statements
When you use the Microsoft SQL Server JDBC Driver, you might have to handle complex statements, including statements that are dynamically generated at runtime. Complex statements often perform a variety of tasks, including updates, inserts, and deletes. These types of statements might also return multiple result sets and output parameters. In these situations, the Java code that runs the statements might not know in advance the types and number of objects and data returned.
To effectively process complex statements, the JDBC driver provides a number of methods to query the objects and data that is returned so your application can correctly process them. The key to processing complex statements is the execute method of the SQLServerStatement class. This method returns a boolean value. When the value is true, the first result returned from the statements is a result set. When the value is false, the first result returned was an update count.
When you know the type of object or data that was returned, you can use either the getResultSet or the getUpdateCount method to process that data. To proceed to the next object or data that is returned from the complex statement, you can call the getMoreResults method.
In the following example, an open connection to the SQL Server 2005 AdventureWorks sample database is passed in to the function, a complex statement is constructed that combines a stored procedure call with a SQL statement, the statements are run, and then a do loop is used to process all the result sets and updated counts that are returned.
public static void executeComplexStatement(Connection con) {
try {
String sqlStringWithUnknownResults = "{call dbo.uspGetEmployeeManagers(50)};SELECT TOP 10 * FROM Person.Contact";
Statement stmt = con.createStatement();
boolean results = stmt.execute(sqlStringWithUnknownResults);
int count = 0;
do {
if (results) {
ResultSet rs = stmt.getResultSet();
System.out.println("Result set data displayed here.");
rs.close();
} else {
count = stmt.getUpdateCount();
if (count >= 0) {
System.out.println("DDL or update data displayed here.");
} else {
System.out.println("No more results to process.");
}
}
results = stmt.getMoreResults();
} while (results || count != -1);
stmt.close();
}
catch (Exception e) {
e.printStackTrace();
}
}