Issuing a Query and Processing the Result
5.3. Issuing a Query and Processing the Result
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 5-1 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 = 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();
5.3.1. Using the Statement or PreparedStatement Interface
The following must be considered when using the
Statement or
PreparedStatement interface:
You can use a single
Statementinstance 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 oneResultSetcan exist perStatementorPreparedStatementat a given time.If you need to perform a query while processing a
ResultSet, you can simply create and use anotherStatement.If you are using threads, and several are using the database, you must use a separate
Statementfor each thread. Refer to Section 5.8 if you are thinking of using threads, as it covers some important points.When you are done using the
StatementorPreparedStatementyou should close it.
5.3.2. Using the ResultSet Interface
The 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
ResultSetby callingclose()once you have finished using it.Once you make another query with the
Statementused to create aResultSet, the currently openResultSetinstance is closed automatically.ResultSetis currently read only. You can not update data through theResultSet. 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.