| PostgreSQL 7.4.30 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 31. JDBC Interface | Fast Forward | Next |
Any time you want to issue SQL statements to the database, you require a
Statement or PreparedStatement instance. Once you have a
Statement or PreparedStatement, you can use issue a query.
This will return a ResultSet
instance, which contains the entire result (see Section 31.3.1 here
for how to alter this behaviour). Example 31-1 illustrates
this process.
Example 31-1. Processing a Simple Query in JDBC
This example will issue a simple query and print out the
first column of each row using a Statement.
Statement st = db.createStatement();
ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");
while (rs.next()) {
System.out.print("Column 1 returned ");
System.out.println(rs.getString(1));
}
rs.close();
st.close();
This example issues the same query as before but uses a
PreparedStatement and a bind
value in the query.
int foovalue = 500;
PreparedStatement st = db.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?");
st.setInt(1, foovalue);
ResultSet rs = st.executeQuery();
while (rs.next()) {
System.out.print("Column 1 returned ");
System.out.println(rs.getString(1));
}
rs.close();
st.close();
By default the driver collects all the results for the query
at once. This can be inconvenient for large data sets so the
JDBC driver provides a means of basing a ResultSet on a database cursor and only
fetching a small number of rows.
A small number of rows are cached on the client side of the connection and when exhausted the next block of rows is retrieved by repositioning the cursor.
Example 31-2. Setting fetch size to turn cursors on and off.
Changing code to cursor mode is as simple as setting the
fetch size of the Statement to
the appropriate size. Setting the fetch size back to 0 will
cause all rows to be cached (the default behaviour).
Statement st = db.createStatement();
// Turn use of the cursor on.
st.setFetchSize(50);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
System.out.print("a row was returned.");
}
rs.close();
// Turn the cursor off.
st.setFetchSize(0);
ResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next()) {
System.out.print("many rows were returned.");
}
rs.close();
// Close the statement.
st.close();
Statement or
PreparedStatement
InterfaceThe following must be considered when using the Statement or PreparedStatement interface:
You can use a single Statement instance as many times as you
want. You could create one as soon as you open the
connection and use it for the connection's lifetime. But
you have to remember that only one ResultSet can exist per Statement or PreparedStatement at a given time.
If you need to perform a query while processing a
ResultSet, you can simply
create and use another Statement.
If you are using threads, and several are using the
database, you must use a separate Statement for each thread. Refer to
Section 31.9 if you are
thinking of using threads, as it covers some important
points.
When you are done using the Statement or PreparedStatement you should close
it.
ResultSet
InterfaceThe following must be considered when using the ResultSet interface:
Before reading any values, you must call next(). This returns true if there is a
result, but more importantly, it prepares the row for
processing.
Under the JDBC specification, you should access a field only once. It is safest to stick to this rule, although at the current time, the PostgreSQL driver will allow you to access a field as many times as you want.
You must close a ResultSet by calling close() once you have finished using
it.
Once you make another query with the Statement used to create a ResultSet, the currently open
ResultSet instance is closed
automatically.