Exercises

Included below are short-answer and programming exercises. Answers are provided for those exercises whose exercise number is a hyperlink. Because college faculty use these exercises in their exams, we have provided answers to roughly half of the exercises included here.

18.2 Using the techniques shown in this chapter, define a complete query application for the Books.mdb database. Provide a series of predefined queries with an appropriate name for each query displayed in a JComboBox. Also allow the user to supply their own queries and add them to the JComboBox. Provide the following predefined queries:
  1. Select all authors from the Authors table.
  2. Select all publishers from the Publishers table.
  3. Select a specific author and list all books for that author. Include the title, year and ISBN number. Order the information alphabetically by title.
  4. Select a specific publisher and list all books published by that publisher. Include the title, year and ISBN number. Order the information alphabetically by title.
  5. Provide any other queries you feel are appropriate.

18.3 Modify Exercise 18.2 to define a complete database manipulation application for the Books.mdb database. In addition to the querying capabilities, the user should be able to edit existing data and add new data to the database (obeying referential and entity integrity constraints). Allow the user to edit the database in the following ways:

  1. Add a new author.
  2. Edit the existing information for an author.
  3. Add a new title for an author (remember that the book must have an entry in the AuthorISBN table). Be sure to specify the publisher of the title.
  4. Add a new publisher.
  5. Edit the existing information for a publisher.

For each of the preceding database manipulations, design an appropriate GUI to allow the user to perform the data manipulation.

18.4 Microsoft Access comes with several predefined database wizard templates (music collection, video collection, wine list, book collection, etc.) that are accessible by selecting New from the File menu in Microsoft Access and choosing a database from Database tab. Create a new database using one of the templates of your choice. Perform exercises 18.2 and 18.3 using the new database and its predefined tables. Provide appropriate queries for the database you choose and allow the user to edit and add data to the database.

18.5 Modify the Find capability in Fig. 18.30 to allow the user to scroll through the ResultSet in case there is more than one person with the specified last name in the Address Book. Provide an appropriate GUI.

Selected Answers

Included below are answers to approximately half the of the exercises in the Cyber Classroom. We are not able to include answers to every exercise because college faculty use these exercises in their classroom exams.

18.2

// Exercise 18.2 Solutionimport java.sql.*;import javax.swing.*;import java.awt.*;import java.awt.event.*;import java.util.*;public class DisplayQueryResults extends JFrame {   // java.sql types needed for database processing   private Connection connection;   private Statement statement;   private ResultSet resultSet;   private ResultSetMetaData rsMetaData;   // javax.swing types needed for GUI   private JTable table;   private JComboBox inputQuery;   private JButton submitQuery;   private JTextField input;       public DisplayQueryResults()    {         super( "Select Query. Click Submit to See Results." );      // The URL specifying the Books database to which      // this program connects using JDBC to connect to a      // Microsoft ODBC database.      String url = "jdbc:odbc:Books";        String username = "anonymous";      String password = "guest";      // Load the driver to allow connection to the database      try {         Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" );         connection = DriverManager.getConnection(             url, username, password );      }       catch ( ClassNotFoundException cnfex ) {         System.err.println(             "Failed to load JDBC/ODBC driver." );         cnfex.printStackTrace();         System.exit( 1 );  // terminate program      }      catch ( SQLException sqlex ) {         System.err.println( "Unable to connect" );         sqlex.printStackTrace();         System.exit( 1 );  // terminate program      }      String names[] = { "All authors", "All publishers",                         "All books", "A specific author",                         "A specific publisher" };      // If connected to database, set up GUI            inputQuery = new JComboBox( names );      submitQuery = new JButton( "Submit query" );      submitQuery.addActionListener(         new ActionListener() {            public void actionPerformed( ActionEvent e )             {               getTable();            }         }         );      JPanel topPanel = new JPanel();      input = new JTextField( 20 );      input.addActionListener(         new ActionListener() {            public void actionPerformed( ActionEvent e )            {               try {                  String query = input.getText();                  statement = connection.createStatement();                  resultSet = statement.executeQuery( query );                           displayResultSet( resultSet );               }               catch ( SQLException sqlex ) {                  sqlex.printStackTrace();               }            }         }      );      JPanel centerPanel = new JPanel();      centerPanel.setLayout( new FlowLayout() );      centerPanel.add( new JLabel( "Enter query, author or publisher:" ) );      centerPanel.add( input );      topPanel.setLayout( new BorderLayout() );      topPanel.add( inputQuery, BorderLayout.NORTH );      topPanel.add( centerPanel, BorderLayout.CENTER );      topPanel.add( submitQuery, BorderLayout.SOUTH );       table = new JTable( 4, 4 );      Container c = getContentPane();      c.setLayout( new BorderLayout() );      c.add( topPanel, BorderLayout.NORTH );      c.add( table, BorderLayout.CENTER );            getTable();      setSize( 500, 500 );      show();   }   private void getTable()   {              try {         int selection = inputQuery.getSelectedIndex();         String query = null;         switch ( selection ) {            case 0:               query = "SELECT * FROM Authors";               break;            case 1:               query = "SELECT * FROM Publishers";               break;            case 2:               query = "SELECT * FROM Titles";               break;            case 3:               query = "SELECT Titles.Title, Titles.YearPublished, " +                  "Titles.ISBN FROM Titles " +                  "INNER JOIN (AuthorISBN " +                  "INNER JOIN Authors ON AuthorISBN.AuthorID = "                  + "Authors.AuthorID) ON Titles.ISBN = " +                  "AuthorISBN.ISBN WHERE Authors.LastName = '"                   + input.getText() + "' ORDER BY Titles.Title ASC";            case 4:               query = "SELECT Titles.Title, Titles.YearPublished, " +                  "Titles.ISBN FROM Titles " +                  "INNER JOIN Publishers " +                  "ON Publishers.PublisherID = " +                                        "Titles.PublisherID WHERE Publishers.PublisherName = '"                   + input.getText() + "' ORDER BY Titles.Title ASC";         }                    statement = connection.createStatement();         resultSet = statement.executeQuery( query );                  displayResultSet( resultSet );      }      catch ( SQLException sqlex ) {         sqlex.printStackTrace();      }   }   private void displayResultSet( ResultSet rs )      throws SQLException   {      // position to first record      boolean moreRecords = rs.next();        // If there are no records, display a message      if ( ! moreRecords ) {         JOptionPane.showMessageDialog( this,             "ResultSet contained no records" );         setTitle( "No records to display" );         return;      }      Vector columnHeads = new Vector();      Vector rows = new Vector();      try {         // get column heads         ResultSetMetaData rsmd = rs.getMetaData();               for ( int i = 1; i <= rsmd.getColumnCount(); ++i )             columnHeads.addElement( rsmd.getColumnName( i ) );         // get row data         do {            rows.addElement( getNextRow( rs, rsmd ) );          } while ( rs.next() );         // display table with ResultSet contents         table = new JTable( rows, columnHeads );         JScrollPane scroller = new JScrollPane( table );         Container c = getContentPane();         c.remove( 1 );         c.add( scroller, BorderLayout.CENTER );         c.validate();      }      catch ( SQLException sqlex ) {         sqlex.printStackTrace();      }   }   private Vector getNextRow( ResultSet rs,                               ResultSetMetaData rsmd )       throws SQLException   {      Vector currentRow = new Vector();            for ( int i = 1; i <= rsmd.getColumnCount(); ++i )         switch( rsmd.getColumnType( i ) ) {            case Types.VARCHAR:            case Types.LONGVARCHAR:                  currentRow.addElement( rs.getString( i ) );               break;            case Types.INTEGER:                  currentRow.addElement(                      new Long( rs.getLong( i ) ) );               break;            default:                System.out.println( "Type was: " +                   rsmd.getColumnTypeName( i ) );         }            return currentRow;   }   public void shutDown()   {      try {         connection.close();      }      catch ( SQLException sqlex ) {         System.err.println( "Unable to disconnect" );         sqlex.printStackTrace();      }   }   public static void main( String args[] )    {      final DisplayQueryResults app =          new DisplayQueryResults();      app.addWindowListener(          new WindowAdapter() {            public void windowClosing( WindowEvent e )             {                 app.shutDown();               System.exit( 0 );            }         }      );   }}/************************************************************************** * (C) Copyright 1999 by Deitel & Associates, Inc. and Prentice Hall.     * * All Rights Reserved.                                                   * *                                                                        * * DISCLAIMER: The authors and publisher of this book have used their     * * best efforts in preparing the book. These efforts include the          * * development, research, and testing of the theories and programs        * * to determine their effectiveness. The authors and publisher make       * * no warranty of any kind, expressed or implied, with regard to these    * * programs or to the documentation contained in these books. The authors * * and publisher shall not be liable in any event for incidental or       * * consequential damages in connection with, or arising out of, the       * * furnishing, performance, or use of these programs.                     * *************************************************************************/