Accessing Databases with JDBC - Java: How to Program Early Objects - page 1082

Authors Table of Books Database:
AUTHORID FIRSTNAME LASTNAME
1 Paul Deitel
2 Harvey Deitel
3 Abbey Deitel
4 Dan Quirk
5 Michael Morgano
Fig. 24.29 | Displaying the contents of the Authors table using JdbcRowSet . (Part 2 of 2.)
URL. Line 23 invokes JdbcRowSet method setUsername to specify the username. Line 24
invokes JdbcRowSet method setPassword to specify the password. Line 25 invokes Jdbc-
RowSet method setCommand to specify the SQL query that will be used to populate the
RowSet . Line 26 invokes JdbcRowSet method execute to execute the SQL query. Method
execute performs four actions—it establishes a Connection to the database, prepares the
query Statement , executes the query and stores the ResultSet returned by query. The
Connection , Statement and ResultSet are encapsulated in the JdbcRowSet object.
The remaining code is almost identical to Fig. 24.23, except that line 29 (Fig. 24.29)
obtains a ResultSetMetaData object from the JdbcRowSet , line 39 uses the JdbcRowSet 's
next method to get the next row of the result and line 42 uses the JdbcRowSet 's getObject
method to obtain a column's value. When the end of the try block is reached, the try -
with-resources statement invokes JdbcRowSet method close , which closes the RowSet 's
encapsulated ResultSet , Statement and Connection . In a CachedRowSet , invoking close
also releases the resources held by that RowSet . The output of this application is the same
as that of Fig. 24.23.
24.8 PreparedStatement s
A PreparedStatement enables you to create compiled SQL statements that execute more ef-
ficiently than Statement s. PreparedStatement s can also specify parameters, making them
more flexible than Statement s—you can execute the same query repeatedly with different
parameter values. For example, in the books database, you might want to locate all book titles
for an author with a specific last and first name, and you might want to execute that query
for several authors. With a PreparedStatement , that query is defined as follows:
PreparedStatement authorBooks = connection.prepareStatement(
"SELECT LastName, FirstName, Title " +
"FROM Authors INNER JOIN AuthorISBN " +
"ON Authors.AuthorID=AuthorISBN.AuthorID " +
"INNER JOIN Titles " +
"ON AuthorISBN.ISBN=Titles.ISBN " +
"WHERE LastName = ? AND FirstName = ?" );
The two question marks ( ? ) in the the preceding SQL statement's last line are placeholders
for values that will be passed as part of the query to the database. Before executing a Pre-
paredStatement , the program must specify the parameter values by using the Prepared-
Statement interface's set methods.
For the preceding query, both parameters are strings that can be set with Prepared-
Statement method setString as follows: