Query Execution

In this section, we write a program that executes queries against the COREJAVA database. For this program to work, you must have populated the COREJAVA database with tables, as described in the preceding section. Figure 4-6 shows the QueryDB application in action.

Figure 4-6. The QueryDB application

You can select the author and the publisher or leave either of them as "Any." Click the Query button; all books matching your selection will be displayed in the text area.

You can also change the data in the database. Select a publisher and type an amount into the text box next to the Change prices button. When you click the button, all prices of that publisher are adjusted by the amount you entered, and the text area contains a message indicating how many records were changed. However, to minimize unintended changes to the database, you can't change all prices at once. The author field is ignored when you change prices. After a price change, you may want to run a query to verify the new prices.

Prepared Statements

In this program, we use one new feature, prepared statements. Consider the query for all books by a particular publisher, independent of the author. The SQL query is

SELECT Books.Price, Books.Title
FROM Books, Publishers
WHERE Books.Publisher_Id = Publishers.Publisher_Id
AND Publishers.Name = the name from the list box

Rather than build a separate query statement every time the user launches such a query, we can prepare a query with a host variable and use it many times, each time filling in a different string for the variable. That technique benefits performance. Whenever the database executes a query, it first computes a strategy of how to efficiently execute the query. By preparing the query and reusing it, you ensure that the planning step is done only once.

Each host variable in a prepared query is indicated with a ?. If there is more than one variable, then you must keep track of the positions of the ? when setting the values. For example, our prepared query becomes

 String publisherQuery =    "SELECT Books.Price, Books.Title" +    " FROM Books, Publishers" +    " WHERE Books.Publisher_Id = Publishers.Publisher_Id AND Publishers.Name = ?"; PreparedStatement publisherQueryStat = conn.prepareStatement(publisherQuery); 

Before executing the prepared statement, you must bind the host variables to actual values with a set method. As with the ResultSet get methods, there are different set methods for the various types. Here, we want to set a string to a publisher name.

 publisherQueryStat.setString(1, publisher); 

The first argument is the position number of the host variable that we want to set. The position 1 denotes the first ?. The second argument is the value that we want to assign to the host variable.

If you reuse a prepared query that you have already executed and the query has more than one host variable, all host variables stay bound as you set them unless you change them with a set method. That means you only need to call a setXxx method on those host variables that change from one query to the next.

Once all variables have been bound to values, you can execute the query

 ResultSet rs = publisherQueryStat.executeQuery(); 

TIP

Even if you don't care about efficiency, you should use prepared statements whenever your query involves variables. If you build a query by hand, you have to worry about special characters (such as quotes). That is more trouble than using a prepared statement.

The price update feature is implemented as an UPDATE statement. Note that we call executeUpdate, not executeQuery, because the UPDATE statement does not return a result set. The return value of executeUpdate is the count of changed rows. We display the count in the text area.

 int r = priceUpdateStmt.executeUpdate(); result.setText(r + " records updated"); 

NOTE

A PreparedStatement object becomes invalid after the associated Connection object is closed. However, many database drivers automatically cache prepared statements. If the same query is prepared twice, the database simply reuses the query strategy. Therefore, don't worry about the overhead of calling prepareStatement.

The following steps briefly describe the example program.

1.	Arrange the components in the frame, using a grid bag layout (see Volume 1, Chapter 9).
2.	Populate the author and publisher text boxes by running two queries that return all author and publisher names in the database.
3.	When the user clicks the Query button, find which of the four query types needs to be executed. If this is the first time this query type is executed, then the prepared statement variable is null, and the prepared statement is constructed. Then, the values are bound to the query and the query is executed. The queries involving authors are more complex. Because a book can have multiple authors, the BooksAuthors table gives the correspondence between authors and books. For example, the book with ISBN number 0-201-96426-0 has two authors with codes DATE and DARW. The BooksAuthors table has the rows 0-201-96426-0, DATE, 1 0-201-96426-0, DARW, 2 to indicate this fact. The third column lists the order of the authors. (We can't just use the position of the records in the table. There is no fixed row ordering in a relational table.) Thus, the query has to join the Books, BooksAuthors, and Authors tables to compare the author name with the one selected by the user. SELECT Books.Price, Books.Title FROM Books, BooksAuthors, Authors, Publishers WHERE Authors.Author_Id = BooksAuthors.Author_Id AND BooksAuthors.ISBN = Books.ISBN AND Books.Publisher_Id = Publishers.Publisher_Id AND Authors.Name = ? AND Publishers.Name = ? TIP Some Java programmers avoid complex SQL statements such as this one. A surprisingly common, but very inefficient, workaround is to write lots of Java code that iterates through multiple result sets. But the database is a lot better at executing query code than a Java program can bethat's the core competency of a database. A rule of thumb: If you can do it in SQL, don't do it in Java.
4.	The results of the query are displayed in the results text box.
5.	When the user clicks the Change prices button, then the update command is constructed and executed. The command is quite complex because the WHERE clause of the UPDATE statement needs the publisher code and we know only the publisher name. This problem is solved with a nested subquery. UPDATE Books SET Price = Price + ? WHERE Books.Publisher_Id = (SELECT Publisher_Id FROM Publishers WHERE Name = ?)
6.	We initialize the connection and statement objects in the constructor. We hang on to them for the life of the program. Just before the program exits, we trap the "window closing" event, and these objects are closed. class QueryDBFrame extends JFrame { public QueryDBFrame() { conn = getConnection(); stat = conn.createStatement(); . . . add(new WindowAdapter() { public void windowClosing(WindowEvent event) { try { stat.close(); conn.close(); } catch (SQLException e) { while (e != null) { e.printStackTrace(); e = e.getNextException(); } } } }); } . . . private Connection conn; private Statement stat; }

Example 4-3 is the complete program code.

Example 4-3. QueryDB.java

[View full width]

   1. import java.net.*;   2. import java.sql.*;   3. import java.awt.*;   4. import java.awt.event.*;   5. import java.io.*;   6. import java.util.*;   7. import javax.swing.*;   8.   9. /**  10.    This program demonstrates several complex database queries.  11. */  12. public class QueryDB  13. {  14.    public static void main(String[] args)  15.    {  16.       JFrame frame = new QueryDBFrame();  17.       frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);  18.       frame.setVisible(true);  19.    }  20. }  21.  22. /**  23.    This frame displays combo boxes for query parameters, a text area for command results,  24.    and buttons to launch a query and an update.  25. */  26. class QueryDBFrame extends JFrame  27. {  28.    public QueryDBFrame()  29.    {  30.       setTitle("QueryDB");  31.       setSize(DEFAULT_WIDTH, DEFAULT_HEIGHT);  32.       setLayout(new GridBagLayout());  33.  34.       authors = new JComboBox();  35.       authors.setEditable(false);  36.       authors.addItem("Any");  37.  38.       publishers = new JComboBox();  39.       publishers.setEditable(false);  40.       publishers.addItem("Any");  41.  42.       result = new JTextArea(4, 50);  43.       result.setEditable(false);  44.  45.       priceChange = new JTextField(8);  46.       priceChange.setText("-5.00");  47.  48.       try  49.       {  50.          conn = getConnection();  51.          Statement stat = conn.createStatement();  52.  53.          String query = "SELECT Name FROM Authors";  54.          ResultSet rs = stat.executeQuery(query);  55.          while (rs.next())  56.             authors.addItem(rs.getString(1));  57.          rs.close();  58.  59.          query = "SELECT Name FROM Publishers";  60.          rs = stat.executeQuery(query);  61.          while (rs.next())  62.             publishers.addItem(rs.getString(1));  63.          rs.close();  64.          stat.close();  65.       }  66.       catch (SQLException e)  67.       {  68.          result.setText("");  69.          while (e != null)  70.          {  71.             result.append("" + e);  72.             e = e.getNextException();  73.          }  74.       }  75.       catch (IOException e)  76.       {  77.          result.setText("" + e);  78.       }  79.  80.       // we use the GBC convenience class of Core Java Volume 1, Chapter 9  81.       add(authors, new GBC(0, 0, 2, 1));  82.  83.       add(publishers, new GBC(2, 0, 2, 1));  84.  85.       JButton queryButton = new JButton("Query");  86.       queryButton.addActionListener(new  87.          ActionListener()  88.          {  89.             public void actionPerformed(ActionEvent event)  90.             {  91.                executeQuery();  92.             }  93.          });  94.       add(queryButton, new GBC(0, 1, 1, 1).setInsets(3));  95.  96.       JButton changeButton = new JButton("Change prices");  97.       changeButton.addActionListener(new  98.          ActionListener()  99.          { 100.             public void actionPerformed(ActionEvent event) 101.             { 102.                changePrices(); 103.             } 104.          }); 105.       add(changeButton, new GBC(2, 1, 1, 1).setInsets(3)); 106. 107.       add(priceChange, new GBC(3, 1, 1, 1).setFill(GBC.HORIZONTAL)); 108. 109.       add(new JScrollPane(result), new GBC(0, 2, 4, 1).setFill(GBC.BOTH).setWeight (100, 100)); 110. 111.       addWindowListener(new 112.          WindowAdapter() 113.          { 114.             public void windowClosing(WindowEvent event) 115.             { 116.                try 117.                { 118.                   if (conn != null) conn.close(); 119.                } 120.                catch (SQLException e) 121.                { 122.                   while (e != null) 123.                   { 124.                      e.printStackTrace(); 125.                      e = e.getNextException(); 126.                   } 127.                } 128.             } 129.         }); 130.    } 131. 132.    /** 133.       Executes the selected query. 134.    */ 135.    private void executeQuery() 136.    { 137.       ResultSet rs = null; 138.       try 139.       { 140.          String author = (String) authors.getSelectedItem(); 141.          String publisher = (String) publishers.getSelectedItem(); 142.          if (!author.equals("Any") && !publisher.equals("Any")) 143.          { 144.             if (authorPublisherQueryStmt == null) 145.                authorPublisherQueryStmt = conn.prepareStatement(authorPublisherQuery); 146.             authorPublisherQueryStmt.setString(1, author); 147.             authorPublisherQueryStmt.setString(2, publisher); 148.             rs = authorPublisherQueryStmt.executeQuery(); 149.          } 150.          else if (!author.equals("Any") && publisher.equals("Any")) 151.          { 152.             if (authorQueryStmt == null) 153.                authorQueryStmt = conn.prepareStatement(authorQuery); 154.             authorQueryStmt.setString(1, author); 155.             rs = authorQueryStmt.executeQuery(); 156.          } 157.          else if (author.equals("Any") && !publisher.equals("Any")) 158.          { 159.             if (publisherQueryStmt == null) 160.                publisherQueryStmt = conn.prepareStatement(publisherQuery); 161.             publisherQueryStmt.setString(1, publisher); 162.             rs = publisherQueryStmt.executeQuery(); 163.          } 164.          else 165.          { 166.             if (allQueryStmt == null) 167.                allQueryStmt = conn.prepareStatement(allQuery); 168.             rs = allQueryStmt.executeQuery(); 169.          } 170. 171.          result.setText(""); 172.          while (rs.next()) 173.          { 174.             result.append(rs.getString(1)); 175.             result.append(", "); 176.             result.append(rs.getString(2)); 177.             result.append("\n"); 178.          } 179.          rs.close(); 180.       } 181.       catch (SQLException e) 182.       { 183.          result.setText(""); 184.          while (e != null) 185.          { 186.             result.append("" + e); 187.             e = e.getNextException(); 188.          } 189.       } 190.    } 191. 192.    /** 193.       Executes an update statement to change prices. 194.    */ 195.    public void changePrices() 196.    { 197.       String publisher = (String) publishers.getSelectedItem(); 198.       if (publisher.equals("Any")) 199.       { 200.          result.setText("I am sorry, but I cannot do that."); 201.          return; 202.       } 203.       try 204.       { 205.          if (priceUpdateStmt == null) 206.             priceUpdateStmt = conn.prepareStatement(priceUpdate); 207.          priceUpdateStmt.setString(1, priceChange.getText()); 208.          priceUpdateStmt.setString(2, publisher); 209.          int r = priceUpdateStmt.executeUpdate(); 210.          result.setText(r + " records updated."); 211.       } 212.       catch (SQLException e) 213.       { 214.          result.setText(""); 215.          while (e != null) 216.          { 217.             result.append("" + e); 218.             e = e.getNextException(); 219.          } 220.       } 221.    } 222. 223.    /** 224.       Gets a connection from the properties specified 225.       in the file database.properties 226.       @return the database connection 227.    */ 228.    public static Connection getConnection() 229.       throws SQLException, IOException 230.    { 231.       Properties props = new Properties(); 232.       FileInputStream in = new FileInputStream("database.properties"); 233.       props.load(in); 234.       in.close(); 235. 236.       String drivers = props.getProperty("jdbc.drivers"); 237.       if (drivers != null) System.setProperty("jdbc.drivers", drivers); 238.       String url = props.getProperty("jdbc.url"); 239.       String username = props.getProperty("jdbc.username"); 240.       String password = props.getProperty("jdbc.password"); 241. 242.       return DriverManager.getConnection(url, username, password); 243.    } 244. 245.    public static final int DEFAULT_WIDTH = 400; 246.    public static final int DEFAULT_HEIGHT = 400; 247. 248.    private JComboBox authors; 249.    private JComboBox publishers; 250.    private JTextField priceChange; 251.    private JTextArea result; 252.    private Connection conn; 253.    private PreparedStatement authorQueryStmt; 254.    private PreparedStatement authorPublisherQueryStmt; 255.    private PreparedStatement publisherQueryStmt; 256.    private PreparedStatement allQueryStmt; 257.    private PreparedStatement priceUpdateStmt; 258. 259.    private static final String authorPublisherQuery = 260.       "SELECT Books.Price, Books.Title FROM Books, BooksAuthors, Authors, Publishers" + 261.       " WHERE Authors.Author_Id = BooksAuthors.Author_Id AND BooksAuthors.ISBN =  Books.ISBN" + 262.       " AND Books.Publisher_Id = Publishers.Publisher_Id AND Authors.Name = ?" + 263.       " AND Publishers.Name = ?"; 264. 265.    private static final String authorQuery = 266.       "SELECT Books.Price, Books.Title FROM Books, BooksAuthors, Authors" + 267.       " WHERE Authors.Author_Id = BooksAuthors.Author_Id AND BooksAuthors.ISBN =  Books.ISBN" + 268.       " AND Authors.Name = ?"; 269. 270.    private static final String publisherQuery = 271.       "SELECT Books.Price, Books.Title FROM Books, Publishers" + 272.       " WHERE Books.Publisher_Id = Publishers.Publisher_Id AND Publishers.Name = ?"; 273. 274.    private static final String allQuery = "SELECT Books.Price, Books.Title FROM Books"; 275. 276.    private static final String priceUpdate = 277.       "UPDATE Books " + "SET Price = Price + ? " + 278.       " WHERE Books.Publisher_Id = (SELECT Publisher_Id FROM Publishers WHERE Name = ?)"; 279. } 

 java.sql.Connection 1.1 

• PreparedStatement prepareStatement(String sql)

  returns a PreparedStatement object containing the precompiled statement. The string sql contains a SQL statement that can contain one or more parameter placeholders denoted by ? characters.

 java.sql.PreparedStatement 1.1 

• void setXxx(int n, Xxx x)

  (Xxx is a type such as int, double, String, Date, etc.) sets the value of the nth parameter to x.

• void clearParameters()

  clears all current parameters in the prepared statement.

• ResultSet executeQuery()

  executes a prepared SQL query and returns a ResultSet object.

• int executeUpdate()

  executes the prepared SQL INSERT, UPDATE, or DELETE statement represented by the PreparedStatement object. Returns the number of rows affected, or 0 for Data Definition Language (DDL) statements such as CREATE TABLE.