The PostgreSQL JDBC Interface | ||
---|---|---|
<<< Previous | 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 the Section called Getting results based on a cursor
here for how to alter this behaviour).
Example 1 illustrates this process.
Example 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)); } 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 = conn.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.
![]() |
Cursor based
|
Example 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 conn.setAutoCommit(false); Statement st = conn.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); rs = st.executeQuery("SELECT * FROM mytable"); while (rs.next()) { System.out.print("many rows were returned."); } rs.close(); // Close the statement. st.close(); |
<<< Previous | Home | Next >>> |
Custom SSLSocketFactory | Using the Statement or PreparedStatement Interface |