Troubleshooting Performance Issues - The Data Access - page 280

Let's assume that using a different database is not an option. We know that
the SQL Select statement retrieves a photo for each criminal record. Can that be
changed? Yes, the application can be changed to execute a query that retrieves all
the data except for the photo and then execute another query to retrieve the
photo. In this case, the photo can be retrieved only if the user actually displays
the associated criminal record. Here is the application rewrite:
PreparedStatement getPhotoStmt = con.prepareStatement(
"SELECT photo FROM FBI_most_wanted " +
"WHERE state=? AND fname=? AND lname=? AND crimes=? " +
"AND convictions=? AND laddress=?");
PreparedStatement pstmt = con.prepareStatement(
"SELECT fname, lname, crimes, convictions, laddress " +
"FROM FBI_most_wanted WHERE state=?");
pstmt.setString(1, "NC");
ResultSet rs = pstmt.executeQuery ();
// Display all rows
while (rs.next()) {
String fname = rs.getString(1);
String lname = rs.getString(2);
String crimes = rs.getString(3);
String convictions = rs.getString(4);
String laddress = rs.getString(5);
if (isPersonOfInterest(fname, lname, crimes, convictions,
laddress)) {
getPhotoStmt.setString(1, "NC");
getPhotoStmt.setString(2, fname);
getPhotoStmt.setString(3, lname);
getPhotoStmt.setString(4, crimes);
getPhotoStmt.setString(5, convictions);
getPhotoStmt.setString(6, laddress);
ResultSet rs2 = getPhotoStmt.executeQuery();