Handling complex statements
When you use the Microsoft JDBC Driver for SQL Server, you might have to handle complex statements, including statements that are dynamically generated at runtime. Complex statements often perform different kinds of tasks, including updates, inserts, and deletes. These types of statements may 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 process complex statements, the JDBC driver provides many methods to query the objects and data that's 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. If the value is false, the first result returned is an update count.
When you know the type of object or data that's returned, you can use either the getResultSet or the getUpdateCount method to process that data. To continue to the next object or data that's returned from the complex statement, you can call the getMoreResults method.
In the following example, an open connection to the AdventureWorks2022 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 (Statement stmt = con.createStatement();) {
String sqlStringWithUnknownResults = "{call dbo.uspGetEmployeeManagers(50)}; SELECT TOP 10 * FROM Person.Contact";
boolean results = stmt.execute(sqlStringWithUnknownResults);
int count = 0;
do {
if (results) {
ResultSet rs = stmt.getResultSet();
System.out.println("Result set data displayed here.");
}
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);
}
// Handle any errors that may have occurred.
catch (SQLException e) {
e.printStackTrace();
}
}