In
a JDBC application program,
you get the information you need from the database,
and set the information you need to in the database,
via SQL.
The SQL statements are composed in Java strings,
prepared,
and executed.
A cursor is used to walk through the results
(if needed) of an executed SQL query.
E.g.,
String queryText = ""; // The SQL text.
PreparedStatement querySt = null; // The query handle.
ResultSet answers = null; // A cursor.
Design a query:
queryText =
"SELECT COUNT(*) as #custs"
+ " FROM yrb_customer";
Prepare the query:
querySt = conDB.prepareStatement(queryText);
At this point the database system parses the query,
and builds an executable query plan for it.
The query has not been executed yet, though.
A query handle object is returned (querySt here)
which we use to execute the query.
To execute, we can say
answers = querySt.executeQuery();
The executeQuery method returns a cursor object
(answers here),
which is of type ResultSet in JDBC speak.
If there are no tuples being returned
(e.g., this is an update "query"),
we could execute the SQL statement instead with the method
updateQuery().
This returns an int that reports how many rows in the database
were affected.
In the above case,
we expect just a single answer tuple to be found.
answers.next();
int num_of_customers = answers.getInt("#custs");
System.out.print("There are ");
System.out.print(num_of_customers);
System.out.println(" number of customers.");
Of course, as good programmers,
we should check whether there is an answer tuple!
If there are no customers,
the query would have resulted in the empty table.
if (answers.next()) {
int num_of_customers = answers.getInt("#custs");
System.out.print("There are ");
System.out.print(num_of_customers);
System.out.println(" number of customers.");
} else {
System.out.println("There are no customers.");
}
For each of these calls,
we might get an SQLException thrown.
(The COM.ibm.db2.jdbc.DB2Exception
is effectively a superclass of this,
so it is caught when we catch a SQLException in these cases.)
Usually this means that something went wrong
with respect to the SQL statement in question.
Each of the calls above
should be encased in a try...catch block
to catch such exceptions
(or properly thrown along).
When we are done with the cursor,
we should close it.
// Close the cursor.
answers.close();
Likewise,
when we are done with a query handle,
we ought to close it too.
// We're done with the handle.
querySt.close();
|