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. Example 8-1 illustrates
this process.
Example 8-1. Processing a Simple Query in JDCB
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 will issue the same query as before using 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();
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 8.8 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.
ResultSet
is currently
read only. You can not update data through the ResultSet
. If you want to update data
you need to do it the old fashioned way by issuing a
SQL update statement.
This is in conformance with the JDBC specification which does not
require drivers to provide this functionality.