Any time you want to issue SQL queries, an instance of either the Statement or the PreparedStatement class is required. Objects of both of these classes can be obtained from Connection object that represents the database that you wish to query. The Connection object's createStatement method returns a Statement object; the prepareStatement method returns a PreparedStatement.
Executing a query is done by invoking the executeQuery method of your Statement or PreparedStatement object. An executeQuery returns a ResultSet object that contains the entire result of the query. ResultSets are usually iterated over, with an action being performed on each row.
Example 7-1 illustrates the process of using a Statement object and Example 7-2 shows the use of a PreparedStatement object.
Example 7-1. Processing a Simple Query in JDBC
Statement stmt = con.createStatement(); ResultSet rs = st.executeQuery("SELECT * FROM mytable"); while(rs.next()) { System.out.print("Column 1 returned "); System.out.println(rs.getString(1)); } rs.close(); stmt.close(); |
Example 7-2. Processing a Simple Query in JDBC
int foovalue = 500; PreparedStatement pstmt = con.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?"); pstmt.setInt(1, foovalue); ResultSet rs = pstmt.executeQuery(); while (rs.next()) { System.out.println("Column 1: " + rs.getString()); } rs.close(); pstmt.close(); |
The 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. However, 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 or PreparedStatement object.
If you are using threads and several are using the database, you must use a separate Statement for each thread. See the Section called Multi-Threaded / Servlet Environment Issues for more information on using threads.
You should close a Statement or PreparedStatement object when you are done using it. ResultSets that were obtained from the execution of a query are automatically closed if they were not previously explicitly closed.
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 drivers 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 cannot update data through the ResultSet. If you want to update data, you need to do it by issuing a SQL update statement.