Now that we have a fingerprint in our database, we need to remove it and allow the user to see it along with all of the account information. Add the following code to the servlet just before the code that builds the reply HTML for a submit request:
statement2 = connection.prepareStatement( "SELECT pic
INTO DUMPFILE 'nail" + inRequest.getParameter("account") + ".jpg' FROM identification.thumbnail WHERE acc_id = " + inRequest.getParameter("account"));
This code will SELECT the pic columns from the identification.thumbnail table and dump the contents to a file (with a name like nail1034033.jpg) on the
database server. By adding the following code to the HTML, we ensure that the user sees the contents of the file from the database rather than the work thumb- nail:
out.println("<td><img src='../images/nail" + inRequest.getParameter("account") + ".jpg'>");
The HTML code uses the <img src> tag to locate the file and display its contents on the client’s browser. However, if your database server isn’t on the same machine as your Web or application server, this won’t work. This is because the database server is typically hidden from the client, which means the client won’t be able to “link” to the file dumped from the database.
The solution is to remove the earlier statement and replace it with this: FileOutputStream fo = new FileOutputStream("./doc/images/nail" + inRequest.getParameter("account") + ".jpg");
BufferedOutputStream bos = new BufferedOutputStream(fo); bos.write(rs.getBytes("thumbnail.pic"));
bos.close();
This code creates a FileOutputStream object and opens a file on the Web server using the nail<account number>.jpg format. This file will be visible to the client browser. Next, our code builds a BufferOutputStream to stream the bytes from the code rs.getBytes(“thumbnail.pic”) to the file. The getBytes() method pulls the fingerprint JPEG file from the database in the same manner that setBytes() replaced the file in the database. If you look back at our original query for the servlet, you can see that we don’t pull any data from the identifi- cation.thumbnail database table, so we have to change the query. The new query is as follows:
statement = connection.prepareStatement( "SELECT *, thumbnail.pic
FROM accounts.acc_acc " + "LEFT JOIN accounts.acc_add on accounts.acc_acc.acc_id = accounts.acc_add.acc_id " + "LEFT JOIN identification.thumbnail on
accounts.acc_acc.acc_id = identification.thumbnail.acc_id " + "WHERE accounts.acc_acc.acc_id = ?
AND accounts.acc_acc.ts = 0");
Our new query pulls data from the acc_acc, acc_add, and thumbnail tables using a join (which we discuss in the next section). Once all of the data is present, the code places a fingerprint file on the Web server and builds an appropriate <img src> tag for viewing the file. We’ve shown the result in Figure 6.5.
Figure 6.5 Full Identification Information for an Account.
Joins
In our original servlet code earlier in this chapter, we included a SELECT query that used a join to find data in both the acc_acc and acc_add tables. The query was:
SELECT * FROM acc_acc
LEFT JOIN acc_add on acc_acc.acc_id = acc_add.acc_id
WHERE acc_acc.acc_id = ? AND acc_acc.ts = 0
To enable our users to view information from a specific account, we need to do several things. First we must make sure that the account exists in acc_acc, the primary account table. We accomplish this with the following query:
SELECT * FROM acc_acc
WHERE acc_acc.acc_id = ? AND acc_acc.ts = 0
If the account exists, we want to pull in any information that might exist in the acc_add table for this account. This can be accomplished using the query
SELECT * FROM acc_add WHERE acc_add.acc_id = ?
If we execute the first query and an account exists, executing the second query is probably safe to do. However, if the first query doesn’t return a result, we don’t want to run the second query. We could use IF conditions to achieve this, but a join will do it for us automatically. The initial join tells the system to return a single result, which will have all of the fields from acc_acc and acc_add, and rows only where the account number is found in the acc_id of the acc_acc table. The result will be either a ResultSet object with no data rows or an object with data from both tables.
In the previous section, we expanded our join to include a third table called thumbnail. This table is unique because it isn’t found in the accounts database but in the identification database.
SELECT *, thumbnail.pic FROM accounts.acc_acc LEFT JOIN accounts.acc_add
on accounts.acc_acc.acc_id = accounts.acc_add.acc_id LEFT JOIN identification.thumbnail
on accounts.acc_acc.acc_id = identification.thumbnail.acc_id WHERE accounts.acc_acc.acc_id = ? AND accounts.acc_acc.ts = 0
In this new join, we take the previous join and add a left join on the identifica- tion.thumbnail database table. We also specify that we want to pull the thumb- nail.pic row along with the rows in acc_acc and acc_add. The join won’t occur unless the account number exists in the acc_acc table. The result of the join provides all the data needed to display the file to the user. These joins demon- strate that Connector/J, Java, and MySQL can pull data in just about any fash- ion for your application.
Updatable ResultSets
All of the applications we have written thus far have handled the issue of updat- ing data within the database using the UPDATE query statement. In many cases, this is the only option. However, suppose we have an application that first exe- cutes a SELECT query that pulls data from the table, displays the information, and then allows the user to make changes. In this case, we can use a feature of the JDBC specification called updatable ResultSets. This feature allows us to change the data within the ResultSet itself and execute a single method to cause the new data to be sent to the database. We can also use these ResultSets to insert new rows as well as delete rows we aren’t interested in.
The code in Listing 6.6 is a combination of code from Chapter 5 (that let us view and modify the account information) and the code for handling a fingerprint. What makes this application unique is the use of updatable ResultSets.
import java.awt.*; import java.awt.event.*; import javax.swing.*; import java.sql.*; import java.util.*; import java.awt.geom.AffineTransform; import java.awt.image.BufferedImage; import java.io.*;
public class IDlook extends JFrame {
private JButton getAccountButton, updateAccountButton, insertAccountButton, nextButton, previousButton, lastButton, firstButton; private JList accountNumberList; private JTextField accountIDText,
nailFileText, thumbIDText; private JTextArea errorText;
private Connection connection; private Statement statement; private ResultSet rs;
private ImageIcon icon = null;
private ImageIcon iconThumbnail = null; JLabel photographLabel;
public IDlook() { try {
Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch (Exception e) {
System.err.println("Unable to find and load driver"); System.exit(1);
} }
private void loadAccounts() { Vector v = new Vector(); try {
rs = statement.executeQuery("SELECT * FROM thumbnail");
while(rs.next()) {
v.addElement(rs.getString("acc_id"));
} } catch(SQLException e) { displaySQLErrors(e); } accountNumberList.setListData(v); }
private void buildGUI() {
Container c = getContentPane(); c.setLayout(new FlowLayout());
accountNumberList = new JList(); loadAccounts();
accountNumberList.setVisibleRowCount(2); JScrollPane accountNumberListScrollPane =
new JScrollPane(accountNumberList);
//Do Get Account Button
getAccountButton = new JButton("Get Account"); getAccountButton.addActionListener (
new ActionListener() {
public void actionPerformed(ActionEvent e) { try { rs.beforeFirst(); while (rs.next()) { if (rs.getString("acc_id"). equals(accountNumberList.getSelectedValue())) break; } if (!rs.isAfterLast()) { accountIDText.setText(rs.getString("acc_id")); thumbIDText.setText(rs.getString("thumb_id"));
icon = new ImageIcon(rs.getBytes("pic")); createThumbnail(); photographLabel.setIcon(iconThumbnail); } } catch(SQLException selectException) { displaySQLErrors(selectException); } } } );
//Do Update Account Button
updateAccountButton = new JButton("Update Account"); updateAccountButton.addActionListener (
new ActionListener() {
public void actionPerformed(ActionEvent e) { try {
byte[] bytes = new byte[50000]; FileInputStream fs = new FileInputStream(nailFileText.getText()); BufferedInputStream bis = new BufferedInputStream(fs); bis.read(bytes); rs.updateBytes("thumbnail.pic", bytes); rs.updateRow(); bis.close(); accountNumberList.removeAll(); loadAccounts(); } catch(SQLException insertException) { displaySQLErrors(insertException); } catch(Exception generalE) { generalE.printStackTrace(); } } } );
//Do insert Account Button
insertAccountButton = new JButton("Insert Account"); insertAccountButton.addActionListener (
new ActionListener() {
public void actionPerformed(ActionEvent e) { try {
File f = new File(nailFileText.getText()); byte[] bytes = new byte[(int)f.length()]; FileInputStream fs = new FileInputStream(f); BufferedInputStream bis = new BufferedInputStream(fs); bis.read(bytes); rs.moveToInsertRow(); rs.updateInt("thumb_id", Integer.parseInt(thumbIDText.getText())); rs.updateInt("acc_id", Integer.parseInt(accountIDText.getText())); rs.updateBytes("pic", bytes); rs.updateObject("sysobject", null);
rs.updateTimestamp("ts", new Timestamp(0)); rs.updateTimestamp("act_ts", new Timestamp(
new java.util.Date().getTime())); rs.insertRow();
bis.close();
accountNumberList.removeAll();
loadAccounts(); } catch(SQLException insertException) { displaySQLErrors(insertException); } catch(Exception generalE) { generalE.printStackTrace(); } } } );
photographLabel = new JLabel();
photographLabel.setHorizontalAlignment(JLabel.CENTER); photographLabel.setVerticalAlignment(JLabel.CENTER); photographLabel.setVerticalTextPosition(JLabel.CENTER); photographLabel.setHorizontalTextPosition(JLabel.CENTER);
JPanel first = new JPanel(new GridLayout(4,1)); first.add(accountNumberListScrollPane);
first.add(getAccountButton); first.add(updateAccountButton); first.add(insertAccountButton);
accountIDText = new JTextField(15); thumbIDText = new JTextField(15); errorText = new JTextArea(5, 15); errorText.setEditable(false);
JPanel second = new JPanel();
second.setLayout(new GridLayout(2,1)); second.add(thumbIDText);
second.add(accountIDText);
JPanel third = new JPanel();
third.add(new JScrollPane(errorText));
nailFileText = new JTextField(25);
c.add(first); c.add(second); c.add(third); c.add(nailFileText); c.add(photographLabel); setSize(500,500); show(); }
public void connectToDB() { try {
connection = DriverManager.getConnection( "jdbc:mysql://localhost/Identification"); statement = connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); } catch(SQLException connectException) { System.out.println(connectException.getMessage()); System.out.println(connectException.getSQLState()); System.out.println(connectException.getErrorCode()); System.exit(1); } }
private void displaySQLErrors(SQLException e) {
errorText.append("SQLException: " + e.getMessage() + "\n"); errorText.append("SQLState: " + e.getSQLState()+"\n"); errorText.append("VendorError: " + e.getErrorCode()+"\n"); }
private void init() { connectToDB(); }
private void createThumbnail() { int maxDim = 350;
try {
Image inImage = icon.getImage();
double scale =
(double)maxDim/(double)inImage.getHeight(null);
If (inImage.getWidth(null) > inImage.getHeight(null)) { scale = (double)maxDim/(double)inImage.getWidth(null); }
int scaledW = (int)(scale*inImage.getWidth(null)); int scaledH = (int)(scale*inImage.getHeight(null));
BufferedImage outImage = new BufferedImage(scaledW, scaledH, BufferedImage.TYPE_INT_RGB);
AffineTransform tx = new AffineTransform();
if (scale < 1.0d) { tx.scale(scale, scale); } Graphics2D g2d = outImage.createGraphics(); g2d.drawImage(inImage, tx, null); g2d.dispose();
iconThumbnail = new ImageIcon(outImage); } catch (Exception e) {
e.printStackTrace(); }
}
public static void main(String[] args) { IDlook id = new IDlook();
id.addWindowListener( new WindowAdapter() {
public void windowClosing(WindowEvent e) { System.exit(0); } } ); id.init(); id.buildGUI(); } }
Listing 6.6 Using updatable ResultSets. (continued)
Figure 6.6 shows an example of the application we want to build. The user is able to select an account number in the combo box and click on the Get Account button. The code pulls information from the thumbnail table and displays the thumb_id, the acc_id, and the fingerprint image. The user is able to change the fingerprint image by placing a file path in the text field above the image and clicking on the Update Account button. To insert a new row into the table, the user simply enters new thumb_id, acc_id, and fingerprint image file values into the appropriate text fields and clicks on the Insert Account button.
The overall makeup of our application is the same as that in the previous chap- ter, where we created a GUI and used JPanels to hold the various controls. The real work is found in the code for the buttons. First, though, let’s look at the code within the loadAccounts() method. The loadAccounts() method pulls all of the data from the thumbnail table using this query:
rs = statement.executeQuery("SELECT * FROM thumbnail");
The result of the executeQuery() method is a ResultSet object. Since we want to use updatable ResultSets, we have to build the Statement object in a little dif- ferent format. Here’s the code:
statement = connection.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE,
Figure 6.6 Our initial account.
By using the ResultSet.CONCUR_UPDATABLE flag, we tell the Statement object to always return a ResultSet object that we can change on the fly and to send those changes back to the database. Therefore, when the executeQuery() method gets executed, the system-wide ResultSet can be updated as needed. Updates to the ResultSet will occur via the update button or the insert button.