Table of Contents
Statement
or
PreparedStatement
InterfaceResultSet
InterfaceAny 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 the section called Getting results based on a
cursor here for how to alter this behaviour). Example 5.1 Processing a Simple Query in
JDBC illustrates this process.
Example 5.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 = conn.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));
}
.close();
rs.close(); st
This example issues the same query as before but uses a
PreparedStatement
and a bind value in the query.
int foovalue = 500;
PreparedStatement st = conn.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?");
.setInt(1, foovalue);
stResultSet rs = st.executeQuery();
while (rs.next())
{
System.out.print("Column 1 returned ");
System.out.println(rs.getString(1));
}
.close();
rs.close(); st
# Getting results based on a cursor
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.
Cursor based
ResultSets
cannot be used in all situations. There a number of restrictions which will make the driver silently fall back to fetching the wholeResultSet
at once.
Connection
must not be in autocommit mode. The
backend closes cursors at the end of transactions, so in autocommit mode
the backend will have closed the cursor before anything can be fetched
from it.Statement
must be created with a
ResultSet
type of ResultSet.TYPE_FORWARD_ONLY
.
This is the default, so no code will need to be rewritten to take
advantage of this, but it also means that you cannot scroll backwards or
otherwise jump around in the ResultSet
.Example 5.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).
// make sure autocommit is off
.setAutoCommit(false);
connStatement st = conn.createStatement();
// Turn use of the cursor on.
.setFetchSize(50);
stResultSet rs = st.executeQuery("SELECT * FROM mytable");
while (rs.next())
{
System.out.print("a row was returned.");
}
.close();
rs
// Turn the cursor off.
.setFetchSize(0);
st= st.executeQuery("SELECT * FROM mytable");
rs while (rs.next())
{
System.out.print("many rows were returned.");
}
.close();
rs
// Close the statement.
.close(); st