Java Advance DB 3

profilewilden
ch28.zip
Home>Computer Science homework help>Java Advance DB 3

ch28/fig28_23/DisplayAuthors.class

                public 
                synchronized 
                class DisplayAuthors {
    
                static 
                final String 
                DATABASE_URL = jdbc:mysql://localhost/books;
    
                public void DisplayAuthors();
    
                public 
                static void 
                main(String[]);
}

ch28/fig28_23/DisplayAuthors.java

ch28/fig28_23/DisplayAuthors.java

// Fig. 28.23: DisplayAuthors.java
// Displaying the contents of the authors table.
import  java . sql . Connection ;
import  java . sql . Statement ;
import  java . sql . DriverManager ;
import  java . sql . ResultSet ;
import  java . sql . ResultSetMetaData ;
import  java . sql . SQLException ;

public   class   DisplayAuthors  
{
    // database URL                              
    static   final   String  DATABASE_URL  =   "jdbc:mysql://localhost/books" ;
   
    // launch the application
    public   static   void  main (   String  args []   )
    {
       Connection  connection  =   null ;   // manages connection
       Statement  statement  =   null ;   // query statement
       ResultSet  resultSet  =   null ;   // manages results
    
       // connect to database books and query database
       try  
       {
          // establish connection to database                              
         connection  =   DriverManager . getConnection (  
            DATABASE_URL ,   "deitel" ,   "deitel"   );

          // create Statement for querying database
         statement  =  connection . createStatement ();
         
          // query database                                        
         resultSet  =  statement . executeQuery (             
             "SELECT authorID, firstName, lastName FROM authors"   );
         
          // process query results
          ResultSetMetaData  metaData  =  resultSet . getMetaData ();
          int  numberOfColumns  =  metaData . getColumnCount ();      
          System . out . println (   "Authors Table of Books Database:\n"   );
         
          for   (   int  i  =   1 ;  i  <=  numberOfColumns ;  i ++   )
             System . out . printf (   "%-8s\t" ,  metaData . getColumnName (  i  )   );
          System . out . println ();
         
          while   (  resultSet . next ()   )  
          {
             for   (   int  i  =   1 ;  i  <=  numberOfColumns ;  i ++   )
                System . out . printf (   "%-8s\t" ,  resultSet . getObject (  i  )   );
             System . out . println ();
          }   // end while
       }    // end try
       catch   (   SQLException  sqlException  )                                 
       {                                                                   
         sqlException . printStackTrace ();
       }   // end catch                                                     
       finally   // ensure resultSet, statement and connection are closed
       {                                                              
          try                                                         
          {                                                           
            resultSet . close ();                                       
            statement . close ();                                       
            connection . close ();                                      
          }   // end try                                               
          catch   (   Exception  exception  )                               
          {                                                           
            exception . printStackTrace ();                             
          }   // end catch                                             
       }   // end finally                                              
    }   // end main
}   // end class DisplayAuthors



/**************************************************************************
 * (C) Copyright 1992-2012 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

 

ch28/fig28_23/JavaSE7Version/DisplayAuthors.class

                public 
                synchronized 
                class DisplayAuthors {
    
                static 
                final String 
                DATABASE_URL = jdbc:mysql://localhost/books;
    
                public void DisplayAuthors();
    
                public 
                static void 
                main(String[]);
}

ch28/fig28_23/JavaSE7Version/DisplayAuthors.java

ch28/fig28_23/JavaSE7Version/DisplayAuthors.java

// Fig. 28.23: DisplayAuthors.java
// Displaying the contents of the authors table.
import  java . sql . Connection ;
import  java . sql . Statement ;
import  java . sql . DriverManager ;
import  java . sql . ResultSet ;
import  java . sql . ResultSetMetaData ;
import  java . sql . SQLException ;

public   class   DisplayAuthors  
{
    // database URL                              
    static   final   String  DATABASE_URL  =   "jdbc:mysql://localhost/books" ;
   
    // launch the application
    public   static   void  main (   String  args []   )
    {
       Connection  connection  =   null ;   // manages connection
       Statement  statement  =   null ;   // query statement
       ResultSet  resultSet  =   null ;   // manages results

       // connect to database books and query database
       try   (   // establish connection to database  
            connection  =   DriverManager . getConnection (  
               DATABASE_URL ,   "deitel" ,   "deitel"   );  
             // create Statement for querying database
            statement  =  connection . createStatement ();  
             // query database
            resultSet  =  statement . executeQuery (  
                "SELECT authorID, firstName, lastName FROM authors"   )   )
       {
          // process query results
          ResultSetMetaData  metaData  =  resultSet . getMetaData ();
          int  numberOfColumns  =  metaData . getColumnCount ();      
          System . out . println (   "Authors Table of Books Database:\n"   );
         
          for   (   int  i  =   1 ;  i  <=  numberOfColumns ;  i ++   )
             System . out . printf (   "%-8s\t" ,  metaData . getColumnName (  i  )   );
          System . out . println ();
         
          while   (  resultSet . next ()   )  
          {
             for   (   int  i  =   1 ;  i  <=  numberOfColumns ;  i ++   )
                System . out . printf (   "%-8s\t" ,  resultSet . getObject (  i  )   );
             System . out . println ();
          }   // end while
       }    // end try
       catch   (   SQLException  sqlException  )                                 
       {                                                                   
         sqlException . printStackTrace ();
       }   // end catch                                                     
    }   // end main
}   // end class DisplayAuthors



/**************************************************************************
 * (C) Copyright 1992-2012 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

 

ch28/fig28_25_28/DisplayQueryResults$1.class

                synchronized 
                class DisplayQueryResults$1 
                implements java.awt.event.ActionListener {
    void DisplayQueryResults$1(DisplayQueryResults);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_25_28/DisplayQueryResults$2.class

                synchronized 
                class DisplayQueryResults$2 
                implements java.awt.event.ActionListener {
    void DisplayQueryResults$2(DisplayQueryResults, javax.swing.JTextField, javax.swing.table.TableRowSorter);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_25_28/DisplayQueryResults$3.class

                synchronized 
                class DisplayQueryResults$3 
                extends java.awt.event.WindowAdapter {
    void DisplayQueryResults$3(DisplayQueryResults);
    
                public void 
                windowClosed(java.awt.event.WindowEvent);
}

ch28/fig28_25_28/DisplayQueryResults.class

                public 
                synchronized 
                class DisplayQueryResults 
                extends javax.swing.JFrame {
    
                static 
                final String 
                DATABASE_URL = jdbc:mysql://localhost/books;
    
                static 
                final String 
                USERNAME = deitel;
    
                static 
                final String 
                PASSWORD = deitel;
    
                static 
                final String 
                DEFAULT_QUERY = SELECT * FROM authors;
    
                private ResultSetTableModel 
                tableModel;
    
                private javax.swing.JTextArea 
                queryArea;
    
                public void DisplayQueryResults();
    
                public 
                static void 
                main(String[]);
}

ch28/fig28_25_28/DisplayQueryResults.java

ch28/fig28_25_28/DisplayQueryResults.java

// Fig. 28.28: DisplayQueryResults.java
// Display the contents of the Authors table in the books database.
import  java . awt . BorderLayout ;
import  java . awt . event . ActionListener ;
import  java . awt . event . ActionEvent ;
import  java . awt . event . WindowAdapter ;
import  java . awt . event . WindowEvent ;
import  java . sql . SQLException ;
import  java . util . regex . PatternSyntaxException ;
import  javax . swing . JFrame ;
import  javax . swing . JTextArea ;
import  javax . swing . JScrollPane ;
import  javax . swing . ScrollPaneConstants ;
import  javax . swing . JTable ;
import  javax . swing . JOptionPane ;
import  javax . swing . JButton ;
import  javax . swing . Box ;
import  javax . swing . JLabel ;
import  javax . swing . JTextField ;
import  javax . swing . RowFilter ;
import  javax . swing . table . TableRowSorter ;
import  javax . swing . table . TableModel ;

public   class   DisplayQueryResults   extends   JFrame  
{
    // database URL, username and password
    static   final   String  DATABASE_URL  =   "jdbc:mysql://localhost/books" ;
    static   final   String  USERNAME  =   "deitel" ;
    static   final   String  PASSWORD  =   "deitel" ;
   
    // default query retrieves all data from authors table
    static   final   String  DEFAULT_QUERY  =   "SELECT * FROM authors" ;
   
    private   ResultSetTableModel  tableModel ;
    private   JTextArea  queryArea ;
   
    // create ResultSetTableModel and GUI
    public   DisplayQueryResults ()  
    {    
       super (   "Displaying Query Results"   );
        
       // create ResultSetTableModel and display database table
       try  
       {
          // create TableModel for results of query SELECT * FROM authors
         tableModel  =   new   ResultSetTableModel (  DATABASE_URL ,
            USERNAME ,  PASSWORD ,  DEFAULT_QUERY  );

          // set up JTextArea in which user types queries
         queryArea  =   new   JTextArea (  DEFAULT_QUERY ,   3 ,   100   );
         queryArea . setWrapStyleWord (   true   );
         queryArea . setLineWrap (   true   );
         
          JScrollPane  scrollPane  =   new   JScrollPane (  queryArea ,
             ScrollPaneConstants . VERTICAL_SCROLLBAR_AS_NEEDED ,  
             ScrollPaneConstants . HORIZONTAL_SCROLLBAR_NEVER  );
         
          // set up JButton for submitting queries
          JButton  submitButton  =   new   JButton (   "Submit Query"   );

          // create Box to manage placement of queryArea and 
          // submitButton in GUI
          Box  boxNorth  =   Box . createHorizontalBox ();
         boxNorth . add (  scrollPane  );
         boxNorth . add (  submitButton  );

          // create JTable based on the tableModel
          JTable  resultTable  =   new   JTable (  tableModel  );
         
          JLabel  filterLabel  =   new   JLabel (   "Filter:"   );
          final   JTextField  filterText  =   new   JTextField ();
          JButton  filterButton  =   new   JButton (   "Apply Filter"   );
          Box  boxSouth  =   Box . createHorizontalBox ();
         
         boxSouth . add (  filterLabel  );
         boxSouth . add (  filterText  );
         boxSouth . add (  filterButton  );
         
          // place GUI components on content pane
         add (  boxNorth ,   BorderLayout . NORTH  );
         add (   new   JScrollPane (  resultTable  ),   BorderLayout . CENTER  );
         add (  boxSouth ,   BorderLayout . SOUTH  );

          // create event listener for submitButton
         submitButton . addActionListener (  
         
             new   ActionListener ()  
             {
                // pass query to table model
                public   void  actionPerformed (   ActionEvent  event  )
                {
                   // perform a new query
                   try  
                   {
                     tableModel . setQuery (  queryArea . getText ()   );
                   }   // end try
                   catch   (   SQLException  sqlException  )  
                   {
                      JOptionPane . showMessageDialog (   null ,  
                        sqlException . getMessage (),   "Database error" ,  
                         JOptionPane . ERROR_MESSAGE  );
                     
                      // try to recover from invalid user query 
                      // by executing default query
                      try  
                      {
                        tableModel . setQuery (  DEFAULT_QUERY  );
                        queryArea . setText (  DEFAULT_QUERY  );
                      }   // end try
                      catch   (   SQLException  sqlException2  )  
                      {
                         JOptionPane . showMessageDialog (   null ,  
                           sqlException2 . getMessage (),   "Database error" ,  
                            JOptionPane . ERROR_MESSAGE  );
         
                         // ensure database connection is closed
                        tableModel . disconnectFromDatabase ();
         
                         System . exit (   1   );   // terminate application
                      }   // end inner catch                   
                   }   // end outer catch
                }   // end actionPerformed
             }    // end ActionListener inner class          
          );   // end call to addActionListener
         
          final   TableRowSorter <   TableModel   >  sorter  =  
             new   TableRowSorter <   TableModel   > (  tableModel  );
         resultTable . setRowSorter (  sorter  );
         setSize (   500 ,   250   );   // set window size
         setVisible (   true   );   // display window  
         
          // create listener for filterButton
         filterButton . addActionListener (             
             new   ActionListener ()  
             {
                // pass filter text to listener
                public   void  actionPerformed (   ActionEvent  e  )  
                {
                   String  text  =  filterText . getText ();

                   if   (  text . length ()   ==   0   )
                     sorter . setRowFilter (   null   );
                   else
                   {
                      try
                      {
                        sorter . setRowFilter (  
                            RowFilter . regexFilter (  text  )   );
                      }   // end try
                      catch   (   PatternSyntaxException  pse  )  
                      {
                         JOptionPane . showMessageDialog (   null ,
                            "Bad regex pattern" ,   "Bad regex pattern" ,
                            JOptionPane . ERROR_MESSAGE  );
                      }   // end catch
                   }   // end else
                }   // end method actionPerfomed
             }   // end annonymous inner class
          );   // end call to addActionLister
       }   // end try
       catch   (   SQLException  sqlException  )  
       {
          JOptionPane . showMessageDialog (   null ,  sqlException . getMessage (),  
             "Database error" ,   JOptionPane . ERROR_MESSAGE  );
               
          // ensure database connection is closed
         tableModel . disconnectFromDatabase ();
         
          System . exit (   1   );   // terminate application
       }   // end catch
      
       // dispose of window when user quits application (this overrides
       // the default of HIDE_ON_CLOSE)
      setDefaultCloseOperation (  DISPOSE_ON_CLOSE  );
      
       // ensure database connection is closed when user quits application
      addWindowListener (
      
          new   WindowAdapter ()  
          {
             // disconnect from database and exit when window has closed
             public   void  windowClosed (   WindowEvent  event  )
             {
               tableModel . disconnectFromDatabase ();
                System . exit (   0   );
             }   // end method windowClosed
          }   // end WindowAdapter inner class
       );   // end call to addWindowListener
    }   // end DisplayQueryResults constructor
   
    // execute application
    public   static   void  main (   String  args []   )  
    {
       new   DisplayQueryResults ();      
    }   // end main
}   // end class DisplayQueryResults



/**************************************************************************
 * (C) Copyright 1992-2012  by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

ch28/fig28_25_28/ResultSetTableModel.class

                public 
                synchronized 
                class ResultSetTableModel 
                extends javax.swing.table.AbstractTableModel {
    
                private java.sql.Connection 
                connection;
    
                private java.sql.Statement 
                statement;
    
                private java.sql.ResultSet 
                resultSet;
    
                private java.sql.ResultSetMetaData 
                metaData;
    
                private int 
                numberOfRows;
    
                private boolean 
                connectedToDatabase;
    
                public void ResultSetTableModel(String, String, String, String) 
                throws java.sql.SQLException;
    
                public Class 
                getColumnClass(int) 
                throws IllegalStateException;
    
                public int 
                getColumnCount() 
                throws IllegalStateException;
    
                public String 
                getColumnName(int) 
                throws IllegalStateException;
    
                public int 
                getRowCount() 
                throws IllegalStateException;
    
                public Object 
                getValueAt(int, int) 
                throws IllegalStateException;
    
                public void 
                setQuery(String) 
                throws java.sql.SQLException, IllegalStateException;
    
                public void 
                disconnectFromDatabase();
}

ch28/fig28_25_28/ResultSetTableModel.java

ch28/fig28_25_28/ResultSetTableModel.java

// Fig. 28.25: ResultSetTableModel.java
// A TableModel that supplies ResultSet data to a JTable.
import  java . sql . Connection ;
import  java . sql . Statement ;
import  java . sql . DriverManager ;
import  java . sql . ResultSet ;
import  java . sql . ResultSetMetaData ;
import  java . sql . SQLException ;
import  javax . swing . table . AbstractTableModel ;

// ResultSet rows and columns are counted from 1 and JTable 
// rows and columns are counted from 0. When processing 
// ResultSet rows or columns for use in a JTable, it is 
// necessary to add 1 to the row or column number to manipulate
// the appropriate ResultSet column (i.e., JTable column 0 is 
// ResultSet column 1 and JTable row 0 is ResultSet row 1).
public   class   ResultSetTableModel   extends   AbstractTableModel  
{
    private   Connection  connection ;
    private   Statement  statement ;
    private   ResultSet  resultSet ;
    private   ResultSetMetaData  metaData ;
    private   int  numberOfRows ;

    // keep track of database connection status
    private   boolean  connectedToDatabase  =   false ;
   
    // constructor initializes resultSet and obtains its meta data object;
    // determines number of rows
    public   ResultSetTableModel (   String  url ,   String  username ,
       String  password ,   String  query  )   throws   SQLException
    {          
       // connect to database
      connection  =   DriverManager . getConnection (  url ,  username ,  password  );

       // create Statement to query database
      statement  =  connection . createStatement (  
          ResultSet . TYPE_SCROLL_INSENSITIVE ,
          ResultSet . CONCUR_READ_ONLY  );

       // update database connection status
      connectedToDatabase  =   true ;

       // set query and execute it
      setQuery (  query  );
    }   // end constructor ResultSetTableModel

    // get class that represents column type
    public   Class  getColumnClass (   int  column  )   throws   IllegalStateException
    {
       // ensure database connection is available
       if   (   ! connectedToDatabase  )  
          throw   new   IllegalStateException (   "Not Connected to Database"   );

       // determine Java class of column
       try  
       {
          String  className  =  metaData . getColumnClassName (  column  +   1   );
         
          // return Class object that represents className
          return   Class . forName (  className  );
       }   // end try
       catch   (   Exception  exception  )  
       {
         exception . printStackTrace ();
       }   // end catch
      
       return   Object . class ;   // if problems occur above, assume type Object
    }   // end method getColumnClass

    // get number of columns in ResultSet
    public   int  getColumnCount ()   throws   IllegalStateException
    {    
       // ensure database connection is available
       if   (   ! connectedToDatabase  )  
          throw   new   IllegalStateException (   "Not Connected to Database"   );

       // determine number of columns
       try  
       {
          return  metaData . getColumnCount ();  
       }   // end try
       catch   (   SQLException  sqlException  )  
       {
         sqlException . printStackTrace ();
       }   // end catch
      
       return   0 ;   // if problems occur above, return 0 for number of columns
    }   // end method getColumnCount

    // get name of a particular column in ResultSet
    public   String  getColumnName (   int  column  )   throws   IllegalStateException
    {     
       // ensure database connection is available
       if   (   ! connectedToDatabase  )  
          throw   new   IllegalStateException (   "Not Connected to Database"   );

       // determine column name
       try  
       {
          return  metaData . getColumnName (  column  +   1   );   
       }   // end try
       catch   (   SQLException  sqlException  )  
       {
         sqlException . printStackTrace ();
       }   // end catch
      
       return   "" ;   // if problems, return empty string for column name
    }   // end method getColumnName

    // return number of rows in ResultSet
    public   int  getRowCount ()   throws   IllegalStateException
    {       
       // ensure database connection is available
       if   (   ! connectedToDatabase  )  
          throw   new   IllegalStateException (   "Not Connected to Database"   );
 
       return  numberOfRows ;
    }   // end method getRowCount

    // obtain value in particular row and column
    public   Object  getValueAt (   int  row ,   int  column  )  
       throws   IllegalStateException
    {
       // ensure database connection is available
       if   (   ! connectedToDatabase  )  
          throw   new   IllegalStateException (   "Not Connected to Database"   );

       // obtain a value at specified ResultSet row and column
       try  
       {
         resultSet . absolute (  row  +   1   );
          return  resultSet . getObject (  column  +   1   );
       }   // end try
       catch   (   SQLException  sqlException  )  
       {
         sqlException . printStackTrace ();
       }   // end catch
      
       return   "" ;   // if problems, return empty string object
    }   // end method getValueAt
   
    // set new database query string
    public   void  setQuery (   String  query  )  
       throws   SQLException ,   IllegalStateException  
    {
       // ensure database connection is available
       if   (   ! connectedToDatabase  )  
          throw   new   IllegalStateException (   "Not Connected to Database"   );

       // specify query and execute it
      resultSet  =  statement . executeQuery (  query  );

       // obtain meta data for ResultSet
      metaData  =  resultSet . getMetaData ();

       // determine number of rows in ResultSet
      resultSet . last ();                     // move to last row
      numberOfRows  =  resultSet . getRow ();    // get row number      
      
       // notify JTable that model has changed
      fireTableStructureChanged ();
    }   // end method setQuery

    // close Statement and Connection               
    public   void  disconnectFromDatabase ()             
    {               
       if   (  connectedToDatabase  )                   
       {
          // close Statement and Connection            
          try                                           
          {                                             
            resultSet . close ();                         
            statement . close ();                         
            connection . close ();                        
          }   // end try                                 
          catch   (   SQLException  sqlException  )           
          {                                             
            sqlException . printStackTrace ();            
          }   // end catch                               
          finally    // update database connection status
          {                                             
            connectedToDatabase  =   false ;               
          }   // end finally                             
       }   // end if
    }   // end method disconnectFromDatabase          
}    // end class ResultSetTableModel




/**************************************************************************
 * (C) Copyright 1992-2012  by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

ch28/fig28_29/JdbcRowSetTest.class

                public 
                synchronized 
                class JdbcRowSetTest {
    
                static 
                final String 
                DATABASE_URL = jdbc:mysql://localhost/books;
    
                static 
                final String 
                USERNAME = deitel;
    
                static 
                final String 
                PASSWORD = deitel;
    
                public void JdbcRowSetTest();
    
                public 
                static void 
                main(String[]);
}

ch28/fig28_29/JdbcRowSetTest.java

ch28/fig28_29/JdbcRowSetTest.java

// Fig. 28.29: JdbcRowSetTest.java
// Displaying the contents of the authors table using JdbcRowSet.
import  java . sql . ResultSetMetaData ;
import  java . sql . SQLException ;
import  javax . sql . rowset . JdbcRowSet ;   
import  com . sun . rowset . JdbcRowSetImpl ;   // Sun's JdbcRowSet implementation

public   class   JdbcRowSetTest  
{
    // JDBC driver name and database URL                              
    static   final   String  DATABASE_URL  =   "jdbc:mysql://localhost/books" ;
    static   final   String  USERNAME  =   "deitel" ;
    static   final   String  PASSWORD  =   "deitel" ;
   
    // constructor connects to database, queries database, processes 
    // results and displays results in window
    public   JdbcRowSetTest ()  
    {
       // connect to database books and query database
       try  
       {
          // specify properties of JdbcRowSet                       
          JdbcRowSet  rowSet  =   new   JdbcRowSetImpl ();                  
         rowSet . setUrl (  DATABASE_URL  );   // set database URL        
         rowSet . setUsername (  USERNAME  );   // set username           
         rowSet . setPassword (  PASSWORD  );   // set password           
         rowSet . setCommand (   "SELECT * FROM authors"   );   // set query
         rowSet . execute ();   // execute query                        

          // process query results
          ResultSetMetaData  metaData  =  rowSet . getMetaData ();
          int  numberOfColumns  =  metaData . getColumnCount ();
          System . out . println (   "Authors Table of Books Database:\n"   );

          // display rowset header
          for   (   int  i  =   1 ;  i  <=  numberOfColumns ;  i ++   )
             System . out . printf (   "%-8s\t" ,  metaData . getColumnName (  i  )   );
          System . out . println ();
         
          // display each row
          while   (  rowSet . next ()   )  
          {
             for   (   int  i  =   1 ;  i  <=  numberOfColumns ;  i ++   )
                System . out . printf (   "%-8s\t" ,  rowSet . getObject (  i  )   );
             System . out . println ();
          }   // end while

          // close the underlying ResultSet, Statement and Connection
         rowSet . close ();
       }   // end try
       catch   (   SQLException  sqlException  )  
       {
         sqlException . printStackTrace ();
          System . exit (   1   );
       }   // end catch
    }   // end DisplayAuthors constructor
   
    // launch the application
    public   static   void  main (   String  args []   )
    {
       JdbcRowSetTest  application  =   new   JdbcRowSetTest ();       
    }   // end main
}   // end class JdbcRowSetTest


/**************************************************************************
 * (C) Copyright 1992-2012  by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

ch28/fig28_30_32/address.sql

DROP TABLE Addresses; CREATE TABLE Addresses ( AddressID INT NOT NULL GENERATED ALWAYS AS IDENTITY, FirstName VARCHAR (15) NOT NULL, LastName VARCHAR (30) NOT NULL, Email VARCHAR (30) NOT NULL, PhoneNumber VARCHAR (15) NOT NULL ); INSERT INTO Addresses (FirstName,LastName,Email,PhoneNumber) VALUES ('Mike','Green','[email protected]','555-5555'), ('Mary','Brown','[email protected]','555-1234');

ch28/fig28_30_32/AddressBook/db.lck

ch28/fig28_30_32/AddressBook/log/log.ctrl

ch28/fig28_30_32/AddressBook/log/log1.dat

ch28/fig28_30_32/AddressBook/log/logmirror.ctrl

ch28/fig28_30_32/AddressBook/seg0/c10.dat

ch28/fig28_30_32/AddressBook/seg0/c101.dat

ch28/fig28_30_32/AddressBook/seg0/c111.dat

ch28/fig28_30_32/AddressBook/seg0/c121.dat

ch28/fig28_30_32/AddressBook/seg0/c130.dat

ch28/fig28_30_32/AddressBook/seg0/c141.dat

ch28/fig28_30_32/AddressBook/seg0/c150.dat

ch28/fig28_30_32/AddressBook/seg0/c161.dat

ch28/fig28_30_32/AddressBook/seg0/c171.dat

ch28/fig28_30_32/AddressBook/seg0/c180.dat

ch28/fig28_30_32/AddressBook/seg0/c191.dat

ch28/fig28_30_32/AddressBook/seg0/c1a1.dat

ch28/fig28_30_32/AddressBook/seg0/c1b1.dat

ch28/fig28_30_32/AddressBook/seg0/c1c0.dat

ch28/fig28_30_32/AddressBook/seg0/c1d1.dat

ch28/fig28_30_32/AddressBook/seg0/c1e0.dat

ch28/fig28_30_32/AddressBook/seg0/c1f1.dat

ch28/fig28_30_32/AddressBook/seg0/c20.dat

ch28/fig28_30_32/AddressBook/seg0/c200.dat

ch28/fig28_30_32/AddressBook/seg0/c211.dat

ch28/fig28_30_32/AddressBook/seg0/c221.dat

ch28/fig28_30_32/AddressBook/seg0/c230.dat

ch28/fig28_30_32/AddressBook/seg0/c241.dat

ch28/fig28_30_32/AddressBook/seg0/c251.dat

ch28/fig28_30_32/AddressBook/seg0/c260.dat

ch28/fig28_30_32/AddressBook/seg0/c271.dat

ch28/fig28_30_32/AddressBook/seg0/c281.dat

ch28/fig28_30_32/AddressBook/seg0/c290.dat

ch28/fig28_30_32/AddressBook/seg0/c2a1.dat

ch28/fig28_30_32/AddressBook/seg0/c2b1.dat

ch28/fig28_30_32/AddressBook/seg0/c2c1.dat

ch28/fig28_30_32/AddressBook/seg0/c2d0.dat

ch28/fig28_30_32/AddressBook/seg0/c2e1.dat

ch28/fig28_30_32/AddressBook/seg0/c2f0.dat

ch28/fig28_30_32/AddressBook/seg0/c300.dat

ch28/fig28_30_32/AddressBook/seg0/c31.dat

ch28/fig28_30_32/AddressBook/seg0/c311.dat

ch28/fig28_30_32/AddressBook/seg0/c321.dat

ch28/fig28_30_32/AddressBook/seg0/c331.dat

ch28/fig28_30_32/AddressBook/seg0/c340.dat

ch28/fig28_30_32/AddressBook/seg0/c351.dat

ch28/fig28_30_32/AddressBook/seg0/c361.dat

ch28/fig28_30_32/AddressBook/seg0/c371.dat

ch28/fig28_30_32/AddressBook/seg0/c380.dat

ch28/fig28_30_32/AddressBook/seg0/c391.dat

ch28/fig28_30_32/AddressBook/seg0/c3a1.dat

ch28/fig28_30_32/AddressBook/seg0/c3b1.dat

ch28/fig28_30_32/AddressBook/seg0/c3c0.dat

ch28/fig28_30_32/AddressBook/seg0/c3d1.dat

ch28/fig28_30_32/AddressBook/seg0/c3e1.dat

ch28/fig28_30_32/AddressBook/seg0/c3f1.dat

ch28/fig28_30_32/AddressBook/seg0/c400.dat

ch28/fig28_30_32/AddressBook/seg0/c41.dat

ch28/fig28_30_32/AddressBook/seg0/c51.dat

ch28/fig28_30_32/AddressBook/seg0/c60.dat

ch28/fig28_30_32/AddressBook/seg0/c71.dat

ch28/fig28_30_32/AddressBook/seg0/c81.dat

ch28/fig28_30_32/AddressBook/seg0/c90.dat

ch28/fig28_30_32/AddressBook/seg0/ca1.dat

ch28/fig28_30_32/AddressBook/seg0/cb1.dat

ch28/fig28_30_32/AddressBook/seg0/cc0.dat

ch28/fig28_30_32/AddressBook/seg0/cd1.dat

ch28/fig28_30_32/AddressBook/seg0/ce1.dat

ch28/fig28_30_32/AddressBook/seg0/cf0.dat

ch28/fig28_30_32/AddressBook/service.properties

#C:\books\2011\jhtp9\examples\ch28\fig28_30_32\AddressBook # ******************************************************************** # *** Please do NOT edit this file. *** # *** CHANGING THE CONTENT OF THIS FILE MAY CAUSE DATA CORRUPTION. *** # ******************************************************************** #Wed Dec 29 13:19:29 EST 2010 SysschemasIndex2Identifier=225 SyscolumnsIdentifier=144 SysconglomeratesIndex1Identifier=49 SysconglomeratesIdentifier=32 SyscolumnsIndex2Identifier=177 SysschemasIndex1Identifier=209 SysconglomeratesIndex3Identifier=81 SystablesIndex2Identifier=129 SyscolumnsIndex1Identifier=161 derby.serviceProtocol=org.apache.derby.database.Database SysschemasIdentifier=192 derby.storage.propertiesId=16 SysconglomeratesIndex2Identifier=65 derby.serviceLocale=en_US SystablesIdentifier=96 SystablesIndex1Identifier=113

ch28/fig28_30_32/AddressBookDisplay$1.class

                synchronized 
                class AddressBookDisplay$1 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$1(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32/AddressBookDisplay$2.class

                synchronized 
                class AddressBookDisplay$2 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$2(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32/AddressBookDisplay$3.class

                synchronized 
                class AddressBookDisplay$3 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$3(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32/AddressBookDisplay$4.class

                synchronized 
                class AddressBookDisplay$4 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$4(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32/AddressBookDisplay$5.class

                synchronized 
                class AddressBookDisplay$5 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$5(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32/AddressBookDisplay$6.class

                synchronized 
                class AddressBookDisplay$6 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$6(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32/AddressBookDisplay$7.class

                synchronized 
                class AddressBookDisplay$7 
                extends java.awt.event.WindowAdapter {
    void AddressBookDisplay$7(AddressBookDisplay);
    
                public void 
                windowClosing(java.awt.event.WindowEvent);
}

ch28/fig28_30_32/AddressBookDisplay.class

                public 
                synchronized 
                class AddressBookDisplay 
                extends javax.swing.JFrame {
    
                private Person 
                currentEntry;
    
                private PersonQueries 
                personQueries;
    
                private java.util.List 
                results;
    
                private int 
                numberOfEntries;
    
                private int 
                currentEntryIndex;
    
                private javax.swing.JButton 
                browseButton;
    
                private javax.swing.JLabel 
                emailLabel;
    
                private javax.swing.JTextField 
                emailTextField;
    
                private javax.swing.JLabel 
                firstNameLabel;
    
                private javax.swing.JTextField 
                firstNameTextField;
    
                private javax.swing.JLabel 
                idLabel;
    
                private javax.swing.JTextField 
                idTextField;
    
                private javax.swing.JTextField 
                indexTextField;
    
                private javax.swing.JLabel 
                lastNameLabel;
    
                private javax.swing.JTextField 
                lastNameTextField;
    
                private javax.swing.JTextField 
                maxTextField;
    
                private javax.swing.JButton 
                nextButton;
    
                private javax.swing.JLabel 
                ofLabel;
    
                private javax.swing.JLabel 
                phoneLabel;
    
                private javax.swing.JTextField 
                phoneTextField;
    
                private javax.swing.JButton 
                previousButton;
    
                private javax.swing.JButton 
                queryButton;
    
                private javax.swing.JLabel 
                queryLabel;
    
                private javax.swing.JPanel 
                queryPanel;
    
                private javax.swing.JPanel 
                navigatePanel;
    
                private javax.swing.JPanel 
                displayPanel;
    
                private javax.swing.JTextField 
                queryTextField;
    
                private javax.swing.JButton 
                insertButton;
    
                public void AddressBookDisplay();
    
                private void 
                previousButtonActionPerformed(java.awt.event.ActionEvent);
    
                private void 
                nextButtonActionPerformed(java.awt.event.ActionEvent);
    
                private void 
                queryButtonActionPerformed(java.awt.event.ActionEvent);
    
                private void 
                indexTextFieldActionPerformed(java.awt.event.ActionEvent);
    
                private void 
                browseButtonActionPerformed(java.awt.event.ActionEvent);
    
                private void 
                insertButtonActionPerformed(java.awt.event.ActionEvent);
    
                public 
                static void 
                main(String[]);
}

ch28/fig28_30_32/AddressBookDisplay.java

ch28/fig28_30_32/AddressBookDisplay.java

// Fig. 28.32: AddressBookDisplay.java
// A simple address book
import  java . awt . event . ActionEvent ;
import  java . awt . event . ActionListener ;
import  java . awt . event . WindowAdapter ;
import  java . awt . event . WindowEvent ;
import  java . awt . FlowLayout ;
import  java . awt . GridLayout ;
import  java . util . List ;  
import  javax . swing . JButton ;
import  javax . swing . Box ;
import  javax . swing . JFrame ;
import  javax . swing . JLabel ;
import  javax . swing . JPanel ;
import  javax . swing . JTextField ;
import  javax . swing . WindowConstants ;
import  javax . swing . BoxLayout ;
import  javax . swing . BorderFactory ;
import  javax . swing . JOptionPane ;

public   class   AddressBookDisplay   extends   JFrame
{
    private   Person  currentEntry ;
    private   PersonQueries  personQueries ;
    private   List <   Person   >  results ;    
    private   int  numberOfEntries  =   0 ;
    private   int  currentEntryIndex ;

    private   JButton  browseButton ;
    private   JLabel  emailLabel ;
    private   JTextField  emailTextField ;
    private   JLabel  firstNameLabel ;
    private   JTextField  firstNameTextField ;
    private   JLabel  idLabel ;
    private   JTextField  idTextField ;
    private   JTextField  indexTextField ;
    private   JLabel  lastNameLabel ;
    private   JTextField  lastNameTextField ;
    private   JTextField  maxTextField ;
    private   JButton  nextButton ;
    private   JLabel  ofLabel ;
    private   JLabel  phoneLabel ;
    private   JTextField  phoneTextField ;
    private   JButton  previousButton ;
    private   JButton  queryButton ;
    private   JLabel  queryLabel ;
    private   JPanel  queryPanel ;
    private   JPanel  navigatePanel ;
    private   JPanel  displayPanel ;
    private   JTextField  queryTextField ;
    private   JButton  insertButton ;
   
    // no-argument constructor
    public   AddressBookDisplay ()
    {
       super (   "Address Book"   );  
      
       // establish database connection and set up PreparedStatements
      personQueries  =   new   PersonQueries ();  
      
       // create GUI
      navigatePanel  =   new   JPanel ();
      previousButton  =   new   JButton ();
      indexTextField  =   new   JTextField (   2   );
      ofLabel  =   new   JLabel ();
      maxTextField  =   new   JTextField (   2   );
      nextButton  =   new   JButton ();
      displayPanel  =   new   JPanel ();
      idLabel  =   new   JLabel ();
      idTextField  =   new   JTextField (   10   );
      firstNameLabel  =   new   JLabel ();
      firstNameTextField  =   new   JTextField (   10   );
      lastNameLabel  =   new   JLabel ();
      lastNameTextField  =   new   JTextField (   10   );
      emailLabel  =   new   JLabel ();
      emailTextField  =   new   JTextField (   10   );
      phoneLabel  =   new   JLabel ();
      phoneTextField  =   new   JTextField (   10   );
      queryPanel  =   new   JPanel ();
      queryLabel  =   new   JLabel ();
      queryTextField  =   new   JTextField (   10   );
      queryButton  =   new   JButton ();
      browseButton  =   new   JButton ();
      insertButton  =   new   JButton ();

      setLayout (   new   FlowLayout (   FlowLayout . CENTER ,   10 ,   10   )   );
      setSize (   400 ,   355   );
      setResizable (   false   );

      navigatePanel . setLayout (
          new   BoxLayout (  navigatePanel ,   BoxLayout . X_AXIS  )   );

      previousButton . setText (   "Previous"   );
      previousButton . setEnabled (   false   );
      previousButton . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               previousButtonActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

      navigatePanel . add (  previousButton  );
      navigatePanel . add (   Box . createHorizontalStrut (   10   )   );

      indexTextField . setHorizontalAlignment (
          JTextField . CENTER  );
      indexTextField . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               indexTextFieldActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

      navigatePanel . add (  indexTextField  );
      navigatePanel . add (   Box . createHorizontalStrut (   10   )   );

      ofLabel . setText (   "of"   );
      navigatePanel . add (  ofLabel  );
      navigatePanel . add (   Box . createHorizontalStrut (   10   )   );

      maxTextField . setHorizontalAlignment (
          JTextField . CENTER  );
      maxTextField . setEditable (   false   );
      navigatePanel . add (  maxTextField  );
      navigatePanel . add (   Box . createHorizontalStrut (   10   )   );

      nextButton . setText (   "Next"   );
      nextButton . setEnabled (   false   );
      nextButton . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               nextButtonActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

      navigatePanel . add (  nextButton  );
      add (  navigatePanel  );

      displayPanel . setLayout (   new   GridLayout (   5 ,   2 ,   4 ,   4   )   );

      idLabel . setText (   "Address ID:"   );
      displayPanel . add (  idLabel  );

      idTextField . setEditable (   false   );
      displayPanel . add (  idTextField  );

      firstNameLabel . setText (   "First Name:"   );
      displayPanel . add (  firstNameLabel  );
      displayPanel . add (  firstNameTextField  );

      lastNameLabel . setText (   "Last Name:"   );
      displayPanel . add (  lastNameLabel  );
      displayPanel . add (  lastNameTextField  );

      emailLabel . setText (   "Email:"   );
      displayPanel . add (  emailLabel  );
      displayPanel . add (  emailTextField  );

      phoneLabel . setText (   "Phone Number:"   );
      displayPanel . add (  phoneLabel  );
      displayPanel . add (  phoneTextField  );
      add (  displayPanel  );

      queryPanel . setLayout (  
          new   BoxLayout (  queryPanel ,   BoxLayout . X_AXIS )   );

      queryPanel . setBorder (   BorderFactory . createTitledBorder (
          "Find an entry by last name"   )   );
      queryLabel . setText (   "Last Name:"   );
      queryPanel . add (   Box . createHorizontalStrut (   5   )   );
      queryPanel . add (  queryLabel  );
      queryPanel . add (   Box . createHorizontalStrut (   10   )   );
      queryPanel . add (  queryTextField  );
      queryPanel . add (   Box . createHorizontalStrut (   10   )   );

      queryButton . setText (   "Find"   );
      queryButton . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               queryButtonActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

      queryPanel . add (  queryButton  );
      queryPanel . add (   Box . createHorizontalStrut (   5   )   );
      add (  queryPanel  );

      browseButton . setText (   "Browse All Entries"   );
      browseButton . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               browseButtonActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

      add (  browseButton  );

      insertButton . setText (   "Insert New Entry"   );
      insertButton . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               insertButtonActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

       add (  insertButton  );

      addWindowListener (  
          new   WindowAdapter ()  
          {   
             public   void  windowClosing (   WindowEvent  evt  )
             {
               personQueries . close ();   // close database connection
                System . exit (   0   );
             }   // end method windowClosing
          }   // end anonymous inner class
       );   // end call to addWindowListener
    
      setVisible (   true   );
    }   // end no-argument constructor

    // handles call when previousButton is clicked
    private   void  previousButtonActionPerformed (   ActionEvent  evt  )
    {
       -- currentEntryIndex ;
      
       if   (  currentEntryIndex  <   0   )
         currentEntryIndex  =  numberOfEntries  -   1 ;
      
      indexTextField . setText (   ""   +   (  currentEntryIndex  +   1   )   );
      indexTextFieldActionPerformed (  evt  );   
    }   // end method previousButtonActionPerformed

    // handles call when nextButton is clicked
    private   void  nextButtonActionPerformed (   ActionEvent  evt  )  
    {
       ++ currentEntryIndex ;
      
       if   (  currentEntryIndex  >=  numberOfEntries  )
         currentEntryIndex  =   0 ;
      
      indexTextField . setText (   ""   +   (  currentEntryIndex  +   1   )   );
      indexTextFieldActionPerformed (  evt  );
    }   // end method nextButtonActionPerformed

    // handles call when queryButton is clicked
    private   void  queryButtonActionPerformed (   ActionEvent  evt  )
    {
      results  =  
         personQueries . getPeopleByLastName (  queryTextField . getText ()   );
      numberOfEntries  =  results . size ();
      
       if   (  numberOfEntries  !=   0   )
       {
         currentEntryIndex  =   0 ;
         currentEntry  =  results . get (  currentEntryIndex  );
         idTextField . setText ( ""   +  currentEntry . getAddressID ()   );
         firstNameTextField . setText (  currentEntry . getFirstName ()   );
         lastNameTextField . setText (  currentEntry . getLastName ()   );
         emailTextField . setText (  currentEntry . getEmail ()   );
         phoneTextField . setText (  currentEntry . getPhoneNumber ()   );
         maxTextField . setText (   ""   +  numberOfEntries  );
         indexTextField . setText (   ""   +   (  currentEntryIndex  +   1   )   );
         nextButton . setEnabled (   true   );
         previousButton . setEnabled (   true   );
       }   // end if
       else
         browseButtonActionPerformed (  evt  );
    }   // end method queryButtonActionPerformed

    // handles call when a new value is entered in indexTextField
    private   void  indexTextFieldActionPerformed (   ActionEvent  evt  )
    {
      currentEntryIndex  =  
          (   Integer . parseInt (  indexTextField . getText ()   )   -   1   );
      
       if   (  numberOfEntries  !=   0   &&  currentEntryIndex  <  numberOfEntries  )
       {
         currentEntry  =  results . get (  currentEntryIndex  );
         idTextField . setText ( ""   +  currentEntry . getAddressID ()   );
         firstNameTextField . setText (  currentEntry . getFirstName ()   );
         lastNameTextField . setText (  currentEntry . getLastName ()   );
         emailTextField . setText (  currentEntry . getEmail ()   );
         phoneTextField . setText (  currentEntry . getPhoneNumber ()   );
         maxTextField . setText (   ""   +  numberOfEntries  );
         indexTextField . setText (   ""   +   (  currentEntryIndex  +   1   )   );
       }   // end if
     }   // end method indexTextFieldActionPerformed

    // handles call when browseButton is clicked
    private   void  browseButtonActionPerformed (   ActionEvent  evt  )
    {
       try
       {
         results  =  personQueries . getAllPeople ();
         numberOfEntries  =  results . size ();
      
          if   (  numberOfEntries  !=   0   )
          {
            currentEntryIndex  =   0 ;
            currentEntry  =  results . get (  currentEntryIndex  );
            idTextField . setText ( ""   +  currentEntry . getAddressID ()   );
            firstNameTextField . setText (  currentEntry . getFirstName ()   );
            lastNameTextField . setText (  currentEntry . getLastName ()   );
            emailTextField . setText (  currentEntry . getEmail ()   );
            phoneTextField . setText (  currentEntry . getPhoneNumber ()   );
            maxTextField . setText (   ""   +  numberOfEntries  );
            indexTextField . setText (   ""   +   (  currentEntryIndex  +   1   )   );
            nextButton . setEnabled (   true   );
            previousButton . setEnabled (   true   );
          }   // end if
       }   // end try
       catch   (   Exception  e  )
       {
         e . printStackTrace ();
       }   // end catch
    }   // end method browseButtonActionPerformed

    // handles call when insertButton is clicked
    private   void  insertButtonActionPerformed (   ActionEvent  evt  )  
    {
       int  result  =  personQueries . addPerson (  firstNameTextField . getText (),
         lastNameTextField . getText (),  emailTextField . getText (),
         phoneTextField . getText ()   );
      
       if   (  result  ==   1   )
          JOptionPane . showMessageDialog (   this ,   "Person added!" ,
             "Person added" ,   JOptionPane . PLAIN_MESSAGE  );
       else
          JOptionPane . showMessageDialog (   this ,   "Person not added!" ,
             "Error" ,   JOptionPane . PLAIN_MESSAGE  );
          
      browseButtonActionPerformed (  evt  );
    }   // end method insertButtonActionPerformed
   
    // main method
    public   static   void  main (   String  args []   )
    {
       new   AddressBookDisplay ();
    }   // end method main
}   // end class AddressBookDisplay


/**************************************************************************
 * (C) Copyright 1992-2012 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

 

ch28/fig28_30_32/derby.log

---------------------------------------------------------------- 2010-12-29 18:53:36.962 GMT: Booting Derby version The Apache Software Foundation - Apache Derby - 10.5.3.0 - (802917): instance a816c00e-012d-337a-6ac5-0000035e1720 on database directory C:\books\2011\jhtp9\examples\ch28\fig28_30_32\AddressBook Database Class Loader started - derby.database.classpath=''

ch28/fig28_30_32/Person.class

                public 
                synchronized 
                class Person {
    
                private int 
                addressID;
    
                private String 
                firstName;
    
                private String 
                lastName;
    
                private String 
                email;
    
                private String 
                phoneNumber;
    
                public void Person();
    
                public void Person(int, String, String, String, String);
    
                public void 
                setAddressID(int);
    
                public int 
                getAddressID();
    
                public void 
                setFirstName(String);
    
                public String 
                getFirstName();
    
                public void 
                setLastName(String);
    
                public String 
                getLastName();
    
                public void 
                setEmail(String);
    
                public String 
                getEmail();
    
                public void 
                setPhoneNumber(String);
    
                public String 
                getPhoneNumber();
}

ch28/fig28_30_32/Person.java

ch28/fig28_30_32/Person.java

// Fig. 28.30: Person.java
// Person class that represents an entry in an address book.
public   class   Person
{
    private   int  addressID ;
    private   String  firstName ;
    private   String  lastName ;
    private   String  email ;
    private   String  phoneNumber ;

    // no-argument constructor
    public   Person ()
    {
    }   // end no-argument Person constructor

    // constructor
    public   Person (   int  id ,   String  first ,   String  last ,  
       String  emailAddress ,   String  phone  )
    {
      setAddressID (  id  );
      setFirstName (  first  );
      setLastName (  last  );
      setEmail (  emailAddress  );
      setPhoneNumber (  phone  );
    }   // end five-argument Person constructor 

    // sets the addressID
    public   void  setAddressID (   int  id  )
    {
      addressID  =  id ;
    }   // end method setAddressID

    // returns the addressID 
    public   int  getAddressID ()
    {
       return  addressID ;
    }   // end method getAddressID
   
    // sets the firstName
    public   void  setFirstName (   String  first  )
    {
      firstName  =  first ;
    }   // end method setFirstName

    // returns the first name 
    public   String  getFirstName ()
    {
       return  firstName ;
    }   // end method getFirstName
   
    // sets the lastName
    public   void  setLastName (   String  last  )
    {
      lastName  =  last ;
    }   // end method setLastName

    // returns the last name 
    public   String  getLastName ()
    {
       return  lastName ;
    }   // end method getLastName
   
    // sets the email address
    public   void  setEmail (   String  emailAddress  )
    {
      email  =  emailAddress ;
    }   // end method setEmail

    // returns the email address
    public   String  getEmail ()
    {
       return  email ;
    }   // end method getEmail
   
    // sets the phone number
    public   void  setPhoneNumber (   String  phone  )
    {
      phoneNumber  =  phone ;
    }   // end method setPhoneNumber

    // returns the phone number
    public   String  getPhoneNumber ()
    {
       return  phoneNumber ;
    }   // end method getPhoneNumber
}   // end class Person


/**************************************************************************
 * (C) Copyright 1992-2012 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

 

ch28/fig28_30_32/PersonQueries.class

                public 
                synchronized 
                class PersonQueries {
    
                private 
                static 
                final String 
                URL = jdbc:derby:AddressBook;
    
                private 
                static 
                final String 
                USERNAME = deitel;
    
                private 
                static 
                final String 
                PASSWORD = deitel;
    
                private java.sql.Connection 
                connection;
    
                private java.sql.PreparedStatement 
                selectAllPeople;
    
                private java.sql.PreparedStatement 
                selectPeopleByLastName;
    
                private java.sql.PreparedStatement 
                insertNewPerson;
    
                public void PersonQueries();
    
                public java.util.List 
                getAllPeople();
    
                public java.util.List 
                getPeopleByLastName(String);
    
                public int 
                addPerson(String, String, String, String);
    
                public void 
                close();
}

ch28/fig28_30_32/PersonQueries.java

ch28/fig28_30_32/PersonQueries.java

// Fig. 28.31: PersonQueries.java
// PreparedStatements used by the Address Book application
import  java . sql . Connection ;
import  java . sql . DriverManager ;
import  java . sql . PreparedStatement ;
import  java . sql . ResultSet ;
import  java . sql . SQLException ;
import  java . util . List ;
import  java . util . ArrayList ;

public   class   PersonQueries  
{
    private   static   final   String  URL  =   "jdbc:derby:AddressBook" ;
    private   static   final   String  USERNAME  =   "deitel" ;
    private   static   final   String  PASSWORD  =   "deitel" ;

    private   Connection  connection  =   null ;   // manages connection
    private   PreparedStatement  selectAllPeople  =   null ;  
    private   PreparedStatement  selectPeopleByLastName  =   null ;  
    private   PreparedStatement  insertNewPerson  =   null ;  
    
    // constructor
    public   PersonQueries ()
    {
       try  
       {
         connection  =  
             DriverManager . getConnection (  URL ,  USERNAME ,  PASSWORD  );

          // create query that selects all entries in the AddressBook
         selectAllPeople  =  
            connection . prepareStatement (   "SELECT * FROM Addresses"   );
         
          // create query that selects entries with a specific last name
         selectPeopleByLastName  =  connection . prepareStatement (  
             "SELECT * FROM Addresses WHERE LastName = ?"   );
         
          // create insert that adds a new entry into the database
         insertNewPerson  =  connection . prepareStatement (  
             "INSERT INTO Addresses "   +  
             "( FirstName, LastName, Email, PhoneNumber ) "   +  
             "VALUES ( ?, ?, ?, ? )"   );
       }   // end try
       catch   (   SQLException  sqlException  )
       {
         sqlException . printStackTrace ();
          System . exit (   1   );
       }   // end catch
    }   // end PersonQueries constructor
   
    // select all of the addresses in the database
    public   List <   Person   >  getAllPeople ()
    {
       List <   Person   >  results  =   null ;
       ResultSet  resultSet  =   null ;
      
       try  
       {
          // executeQuery returns ResultSet containing matching entries
         resultSet  =  selectAllPeople . executeQuery ();  
         results  =   new   ArrayList <   Person   > ();
         
          while   (  resultSet . next ()   )
          {
            results . add (   new   Person (
               resultSet . getInt (   "addressID"   ),
               resultSet . getString (   "firstName"   ),
               resultSet . getString (   "lastName"   ),
               resultSet . getString (   "email"   ),
               resultSet . getString (   "phoneNumber"   )   )   );
          }   // end while
       }   // end try
       catch   (   SQLException  sqlException  )
       {
         sqlException . printStackTrace ();          
       }   // end catch
       finally
       {
          try  
          {
            resultSet . close ();
          }   // end try
          catch   (   SQLException  sqlException  )
          {
            sqlException . printStackTrace ();          
            close ();
          }   // end catch
       }   // end finally
      
       return  results ;
    }   // end method getAllPeople
   
    // select person by last name   
    public   List <   Person   >  getPeopleByLastName (   String  name  )
    {
       List <   Person   >  results  =   null ;
       ResultSet  resultSet  =   null ;

       try  
       {
         selectPeopleByLastName . setString (   1 ,  name  );   // specify last name

          // executeQuery returns ResultSet containing matching entries
         resultSet  =  selectPeopleByLastName . executeQuery ();  

         results  =   new   ArrayList <   Person   > ();

          while   (  resultSet . next ()   )
          {
            results . add (   new   Person (  resultSet . getInt (   "addressID"   ),
               resultSet . getString (   "firstName"   ),
               resultSet . getString (   "lastName"   ),
               resultSet . getString (   "email"   ),
               resultSet . getString (   "phoneNumber"   )   )   );
          }   // end while
       }   // end try
       catch   (   SQLException  sqlException  )
       {
         sqlException . printStackTrace ();
       }   // end catch
       finally
       {
          try  
          {
            resultSet . close ();
          }   // end try
          catch   (   SQLException  sqlException  )
          {
            sqlException . printStackTrace ();          
            close ();
          }   // end catch
       }   // end finally
      
       return  results ;
    }   // end method getPeopleByName
   
    // add an entry
    public   int  addPerson (  
       String  fname ,   String  lname ,   String  email ,   String  num  )
    {
       int  result  =   0 ;
      
       // set parameters, then execute insertNewPerson
       try  
       {
         insertNewPerson . setString (   1 ,  fname  );
         insertNewPerson . setString (   2 ,  lname  );
         insertNewPerson . setString (   3 ,  email  );
         insertNewPerson . setString (   4 ,  num  );

          // insert the new entry; returns # of rows updated
         result  =  insertNewPerson . executeUpdate ();  
       }   // end try
       catch   (   SQLException  sqlException  )
       {
         sqlException . printStackTrace ();
         close ();
       }   // end catch
      
       return  result ;
    }   // end method addPerson
   
    // close the database connection
    public   void  close ()
    {
       try  
       {
         connection . close ();
       }   // end try
       catch   (   SQLException  sqlException  )
       {
         sqlException . printStackTrace ();
       }   // end catch
    }   // end method close
}   // end class PersonQueries


/**************************************************************************
 * (C) Copyright 1992-2012 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

 

ch28/fig28_30_32-WithoutDBPredefined/address.sql

DROP TABLE Addresses; CREATE TABLE Addresses ( AddressID INT NOT NULL GENERATED ALWAYS AS IDENTITY, FirstName VARCHAR (15) NOT NULL, LastName VARCHAR (30) NOT NULL, Email VARCHAR (30) NOT NULL, PhoneNumber VARCHAR (15) NOT NULL ); INSERT INTO Addresses (FirstName,LastName,Email,PhoneNumber) VALUES ('Mike','Green','[email protected]','555-5555'), ('Mary','Brown','[email protected]','555-1234');

ch28/fig28_30_32-WithoutDBPredefined/AddressBookDisplay$1.class

                synchronized 
                class AddressBookDisplay$1 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$1(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32-WithoutDBPredefined/AddressBookDisplay$2.class

                synchronized 
                class AddressBookDisplay$2 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$2(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32-WithoutDBPredefined/AddressBookDisplay$3.class

                synchronized 
                class AddressBookDisplay$3 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$3(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32-WithoutDBPredefined/AddressBookDisplay$4.class

                synchronized 
                class AddressBookDisplay$4 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$4(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32-WithoutDBPredefined/AddressBookDisplay$5.class

                synchronized 
                class AddressBookDisplay$5 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$5(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32-WithoutDBPredefined/AddressBookDisplay$6.class

                synchronized 
                class AddressBookDisplay$6 
                implements java.awt.event.ActionListener {
    void AddressBookDisplay$6(AddressBookDisplay);
    
                public void 
                actionPerformed(java.awt.event.ActionEvent);
}

ch28/fig28_30_32-WithoutDBPredefined/AddressBookDisplay$7.class

                synchronized 
                class AddressBookDisplay$7 
                extends java.awt.event.WindowAdapter {
    void AddressBookDisplay$7(AddressBookDisplay);
    
                public void 
                windowClosing(java.awt.event.WindowEvent);
}

ch28/fig28_30_32-WithoutDBPredefined/AddressBookDisplay.class

                public 
                synchronized 
                class AddressBookDisplay 
                extends javax.swing.JFrame {
    
                private Person 
                currentEntry;
    
                private PersonQueries 
                personQueries;
    
                private java.util.List 
                results;
    
                private int 
                numberOfEntries;
    
                private int 
                currentEntryIndex;
    
                private javax.swing.JButton 
                browseButton;
    
                private javax.swing.JLabel 
                emailLabel;
    
                private javax.swing.JTextField 
                emailTextField;
    
                private javax.swing.JLabel 
                firstNameLabel;
    
                private javax.swing.JTextField 
                firstNameTextField;
    
                private javax.swing.JLabel 
                idLabel;
    
                private javax.swing.JTextField 
                idTextField;
    
                private javax.swing.JTextField 
                indexTextField;
    
                private javax.swing.JLabel 
                lastNameLabel;
    
                private javax.swing.JTextField 
                lastNameTextField;
    
                private javax.swing.JTextField 
                maxTextField;
    
                private javax.swing.JButton 
                nextButton;
    
                private javax.swing.JLabel 
                ofLabel;
    
                private javax.swing.JLabel 
                phoneLabel;
    
                private javax.swing.JTextField 
                phoneTextField;
    
                private javax.swing.JButton 
                previousButton;
    
                private javax.swing.JButton 
                queryButton;
    
                private javax.swing.JLabel 
                queryLabel;
    
                private javax.swing.JPanel 
                queryPanel;
    
                private javax.swing.JPanel 
                navigatePanel;
    
                private javax.swing.JPanel 
                displayPanel;
    
                private javax.swing.JTextField 
                queryTextField;
    
                private javax.swing.JButton 
                insertButton;
    
                public void AddressBookDisplay();
    
                private void 
                previousButtonActionPerformed(java.awt.event.ActionEvent);
    
                private void 
                nextButtonActionPerformed(java.awt.event.ActionEvent);
    
                private void 
                queryButtonActionPerformed(java.awt.event.ActionEvent);
    
                private void 
                indexTextFieldActionPerformed(java.awt.event.ActionEvent);
    
                private void 
                browseButtonActionPerformed(java.awt.event.ActionEvent);
    
                private void 
                insertButtonActionPerformed(java.awt.event.ActionEvent);
    
                public 
                static void 
                main(String[]);
}

ch28/fig28_30_32-WithoutDBPredefined/AddressBookDisplay.java

ch28/fig28_30_32-WithoutDBPredefined/AddressBookDisplay.java

// Fig. 28.32: AddressBookDisplay.java
// A simple address book
import  java . awt . event . ActionEvent ;
import  java . awt . event . ActionListener ;
import  java . awt . event . WindowAdapter ;
import  java . awt . event . WindowEvent ;
import  java . awt . FlowLayout ;
import  java . awt . GridLayout ;
import  java . util . List ;  
import  javax . swing . JButton ;
import  javax . swing . Box ;
import  javax . swing . JFrame ;
import  javax . swing . JLabel ;
import  javax . swing . JPanel ;
import  javax . swing . JTextField ;
import  javax . swing . WindowConstants ;
import  javax . swing . BoxLayout ;
import  javax . swing . BorderFactory ;
import  javax . swing . JOptionPane ;

public   class   AddressBookDisplay   extends   JFrame
{
    private   Person  currentEntry ;
    private   PersonQueries  personQueries ;
    private   List <   Person   >  results ;    
    private   int  numberOfEntries  =   0 ;
    private   int  currentEntryIndex ;

    private   JButton  browseButton ;
    private   JLabel  emailLabel ;
    private   JTextField  emailTextField ;
    private   JLabel  firstNameLabel ;
    private   JTextField  firstNameTextField ;
    private   JLabel  idLabel ;
    private   JTextField  idTextField ;
    private   JTextField  indexTextField ;
    private   JLabel  lastNameLabel ;
    private   JTextField  lastNameTextField ;
    private   JTextField  maxTextField ;
    private   JButton  nextButton ;
    private   JLabel  ofLabel ;
    private   JLabel  phoneLabel ;
    private   JTextField  phoneTextField ;
    private   JButton  previousButton ;
    private   JButton  queryButton ;
    private   JLabel  queryLabel ;
    private   JPanel  queryPanel ;
    private   JPanel  navigatePanel ;
    private   JPanel  displayPanel ;
    private   JTextField  queryTextField ;
    private   JButton  insertButton ;
   
    // no-argument constructor
    public   AddressBookDisplay ()
    {
       super (   "Address Book"   );  
      
       // establish database connection and set up PreparedStatements
      personQueries  =   new   PersonQueries ();  
      
       // create GUI
      navigatePanel  =   new   JPanel ();
      previousButton  =   new   JButton ();
      indexTextField  =   new   JTextField (   2   );
      ofLabel  =   new   JLabel ();
      maxTextField  =   new   JTextField (   2   );
      nextButton  =   new   JButton ();
      displayPanel  =   new   JPanel ();
      idLabel  =   new   JLabel ();
      idTextField  =   new   JTextField (   10   );
      firstNameLabel  =   new   JLabel ();
      firstNameTextField  =   new   JTextField (   10   );
      lastNameLabel  =   new   JLabel ();
      lastNameTextField  =   new   JTextField (   10   );
      emailLabel  =   new   JLabel ();
      emailTextField  =   new   JTextField (   10   );
      phoneLabel  =   new   JLabel ();
      phoneTextField  =   new   JTextField (   10   );
      queryPanel  =   new   JPanel ();
      queryLabel  =   new   JLabel ();
      queryTextField  =   new   JTextField (   10   );
      queryButton  =   new   JButton ();
      browseButton  =   new   JButton ();
      insertButton  =   new   JButton ();

      setLayout (   new   FlowLayout (   FlowLayout . CENTER ,   10 ,   10   )   );
      setSize (   400 ,   355   );
      setResizable (   false   );

      navigatePanel . setLayout (
          new   BoxLayout (  navigatePanel ,   BoxLayout . X_AXIS  )   );

      previousButton . setText (   "Previous"   );
      previousButton . setEnabled (   false   );
      previousButton . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               previousButtonActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

      navigatePanel . add (  previousButton  );
      navigatePanel . add (   Box . createHorizontalStrut (   10   )   );

      indexTextField . setHorizontalAlignment (
          JTextField . CENTER  );
      indexTextField . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               indexTextFieldActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

      navigatePanel . add (  indexTextField  );
      navigatePanel . add (   Box . createHorizontalStrut (   10   )   );

      ofLabel . setText (   "of"   );
      navigatePanel . add (  ofLabel  );
      navigatePanel . add (   Box . createHorizontalStrut (   10   )   );

      maxTextField . setHorizontalAlignment (
          JTextField . CENTER  );
      maxTextField . setEditable (   false   );
      navigatePanel . add (  maxTextField  );
      navigatePanel . add (   Box . createHorizontalStrut (   10   )   );

      nextButton . setText (   "Next"   );
      nextButton . setEnabled (   false   );
      nextButton . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               nextButtonActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

      navigatePanel . add (  nextButton  );
      add (  navigatePanel  );

      displayPanel . setLayout (   new   GridLayout (   5 ,   2 ,   4 ,   4   )   );

      idLabel . setText (   "Address ID:"   );
      displayPanel . add (  idLabel  );

      idTextField . setEditable (   false   );
      displayPanel . add (  idTextField  );

      firstNameLabel . setText (   "First Name:"   );
      displayPanel . add (  firstNameLabel  );
      displayPanel . add (  firstNameTextField  );

      lastNameLabel . setText (   "Last Name:"   );
      displayPanel . add (  lastNameLabel  );
      displayPanel . add (  lastNameTextField  );

      emailLabel . setText (   "Email:"   );
      displayPanel . add (  emailLabel  );
      displayPanel . add (  emailTextField  );

      phoneLabel . setText (   "Phone Number:"   );
      displayPanel . add (  phoneLabel  );
      displayPanel . add (  phoneTextField  );
      add (  displayPanel  );

      queryPanel . setLayout (  
          new   BoxLayout (  queryPanel ,   BoxLayout . X_AXIS )   );

      queryPanel . setBorder (   BorderFactory . createTitledBorder (
          "Find an entry by last name"   )   );
      queryLabel . setText (   "Last Name:"   );
      queryPanel . add (   Box . createHorizontalStrut (   5   )   );
      queryPanel . add (  queryLabel  );
      queryPanel . add (   Box . createHorizontalStrut (   10   )   );
      queryPanel . add (  queryTextField  );
      queryPanel . add (   Box . createHorizontalStrut (   10   )   );

      queryButton . setText (   "Find"   );
      queryButton . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               queryButtonActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

      queryPanel . add (  queryButton  );
      queryPanel . add (   Box . createHorizontalStrut (   5   )   );
      add (  queryPanel  );

      browseButton . setText (   "Browse All Entries"   );
      browseButton . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               browseButtonActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

      add (  browseButton  );

      insertButton . setText (   "Insert New Entry"   );
      insertButton . addActionListener (
          new   ActionListener ()
          {
             public   void  actionPerformed (   ActionEvent  evt  )
             {
               insertButtonActionPerformed (  evt  );
             }   // end method actionPerformed
          }   // end anonymous inner class
       );   // end call to addActionListener

       add (  insertButton  );

      addWindowListener (  
          new   WindowAdapter ()  
          {   
             public   void  windowClosing (   WindowEvent  evt  )
             {
               personQueries . close ();   // close database connection
                System . exit (   0   );
             }   // end method windowClosing
          }   // end anonymous inner class
       );   // end call to addWindowListener
    
      setVisible (   true   );
    }   // end no-argument constructor

    // handles call when previousButton is clicked
    private   void  previousButtonActionPerformed (   ActionEvent  evt  )
    {
      currentEntryIndex -- ;
      
       if   (  currentEntryIndex  <   0   )
         currentEntryIndex  =  numberOfEntries  -   1 ;
      
      indexTextField . setText (   ""   +   (  currentEntryIndex  +   1   )   );
      indexTextFieldActionPerformed (  evt  );   
    }   // end method previousButtonActionPerformed

    // handles call when nextButton is clicked
    private   void  nextButtonActionPerformed (   ActionEvent  evt  )  
    {
      currentEntryIndex ++ ;
      
       if   (  currentEntryIndex  >=  numberOfEntries  )
         currentEntryIndex  =   0 ;
      
      indexTextField . setText (   ""   +   (  currentEntryIndex  +   1   )   );
      indexTextFieldActionPerformed (  evt  );
    }   // end method nextButtonActionPerformed

    // handles call when queryButton is clicked
    private   void  queryButtonActionPerformed (   ActionEvent  evt  )
    {
      results  =  
         personQueries . getPeopleByLastName (  queryTextField . getText ()   );
      numberOfEntries  =  results . size ();
      
       if   (  numberOfEntries  !=   0   )
       {
         currentEntryIndex  =   0 ;
         currentEntry  =  results . get (  currentEntryIndex  );
         idTextField . setText ( ""   +  currentEntry . getAddressID ()   );
         firstNameTextField . setText (  currentEntry . getFirstName ()   );
         lastNameTextField . setText (  currentEntry . getLastName ()   );
         emailTextField . setText (  currentEntry . getEmail ()   );
         phoneTextField . setText (  currentEntry . getPhoneNumber ()   );
         maxTextField . setText (   ""   +  numberOfEntries  );
         indexTextField . setText (   ""   +   (  currentEntryIndex  +   1   )   );
         nextButton . setEnabled (   true   );
         previousButton . setEnabled (   true   );
       }   // end if
       else
         browseButtonActionPerformed (  evt  );
    }   // end method queryButtonActionPerformed

    // handles call when a new value is entered in indexTextField
    private   void  indexTextFieldActionPerformed (   ActionEvent  evt  )
    {
      currentEntryIndex  =  
          (   Integer . parseInt (  indexTextField . getText ()   )   -   1   );
      
       if   (  numberOfEntries  !=   0   &&  currentEntryIndex  <  numberOfEntries  )
       {
         currentEntry  =  results . get (  currentEntryIndex  );
         idTextField . setText ( ""   +  currentEntry . getAddressID ()   );
         firstNameTextField . setText (  currentEntry . getFirstName ()   );
         lastNameTextField . setText (  currentEntry . getLastName ()   );
         emailTextField . setText (  currentEntry . getEmail ()   );
         phoneTextField . setText (  currentEntry . getPhoneNumber ()   );
         maxTextField . setText (   ""   +  numberOfEntries  );
         indexTextField . setText (   ""   +   (  currentEntryIndex  +   1   )   );
       }   // end if
     }   // end method indexTextFieldActionPerformed

    // handles call when browseButton is clicked
    private   void  browseButtonActionPerformed (   ActionEvent  evt  )
    {
       try
       {
         results  =  personQueries . getAllPeople ();
         numberOfEntries  =  results . size ();
      
          if   (  numberOfEntries  !=   0   )
          {
            currentEntryIndex  =   0 ;
            currentEntry  =  results . get (  currentEntryIndex  );
            idTextField . setText ( ""   +  currentEntry . getAddressID ()   );
            firstNameTextField . setText (  currentEntry . getFirstName ()   );
            lastNameTextField . setText (  currentEntry . getLastName ()   );
            emailTextField . setText (  currentEntry . getEmail ()   );
            phoneTextField . setText (  currentEntry . getPhoneNumber ()   );
            maxTextField . setText (   ""   +  numberOfEntries  );
            indexTextField . setText (   ""   +   (  currentEntryIndex  +   1   )   );
            nextButton . setEnabled (   true   );
            previousButton . setEnabled (   true   );
          }   // end if
       }   // end try
       catch   (   Exception  e  )
       {
         e . printStackTrace ();
       }   // end catch
    }   // end method browseButtonActionPerformed

    // handles call when insertButton is clicked
    private   void  insertButtonActionPerformed (   ActionEvent  evt  )  
    {
       int  result  =  personQueries . addPerson (  firstNameTextField . getText (),
         lastNameTextField . getText (),  emailTextField . getText (),
         phoneTextField . getText ()   );
      
       if   (  result  ==   1   )
          JOptionPane . showMessageDialog (   this ,   "Person added!" ,
             "Person added" ,   JOptionPane . PLAIN_MESSAGE  );
       else
          JOptionPane . showMessageDialog (   this ,   "Person not added!" ,
             "Error" ,   JOptionPane . PLAIN_MESSAGE  );
          
      browseButtonActionPerformed (  evt  );
    }   // end method insertButtonActionPerformed
   
    // main method
    public   static   void  main (   String  args []   )
    {
       new   AddressBookDisplay ();
    }   // end method main
}   // end class AddressBookDisplay


/**************************************************************************
 * (C) Copyright 1992-2012 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

 

ch28/fig28_30_32-WithoutDBPredefined/Person.class

                public 
                synchronized 
                class Person {
    
                private int 
                addressID;
    
                private String 
                firstName;
    
                private String 
                lastName;
    
                private String 
                email;
    
                private String 
                phoneNumber;
    
                public void Person();
    
                public void Person(int, String, String, String, String);
    
                public void 
                setAddressID(int);
    
                public int 
                getAddressID();
    
                public void 
                setFirstName(String);
    
                public String 
                getFirstName();
    
                public void 
                setLastName(String);
    
                public String 
                getLastName();
    
                public void 
                setEmail(String);
    
                public String 
                getEmail();
    
                public void 
                setPhoneNumber(String);
    
                public String 
                getPhoneNumber();
}

ch28/fig28_30_32-WithoutDBPredefined/Person.java

ch28/fig28_30_32-WithoutDBPredefined/Person.java

// Fig. 28.30: Person.java
// Person class that represents an entry in an address book.
public   class   Person
{
    private   int  addressID ;
    private   String  firstName ;
    private   String  lastName ;
    private   String  email ;
    private   String  phoneNumber ;

    // no-argument constructor
    public   Person ()
    {
    }   // end no-argument Person constructor

    // constructor
    public   Person (   int  id ,   String  first ,   String  last ,  
       String  emailAddress ,   String  phone  )
    {
      setAddressID (  id  );
      setFirstName (  first  );
      setLastName (  last  );
      setEmail (  emailAddress  );
      setPhoneNumber (  phone  );
    }   // end five-argument Person constructor 

    // sets the addressID
    public   void  setAddressID (   int  id  )
    {
      addressID  =  id ;
    }   // end method setAddressID

    // returns the addressID 
    public   int  getAddressID ()
    {
       return  addressID ;
    }   // end method getAddressID
   
    // sets the firstName
    public   void  setFirstName (   String  first  )
    {
      firstName  =  first ;
    }   // end method setFirstName

    // returns the first name 
    public   String  getFirstName ()
    {
       return  firstName ;
    }   // end method getFirstName
   
    // sets the lastName
    public   void  setLastName (   String  last  )
    {
      lastName  =  last ;
    }   // end method setLastName

    // returns the last name 
    public   String  getLastName ()
    {
       return  lastName ;
    }   // end method getLastName
   
    // sets the email address
    public   void  setEmail (   String  emailAddress  )
    {
      email  =  emailAddress ;
    }   // end method setEmail

    // returns the email address
    public   String  getEmail ()
    {
       return  email ;
    }   // end method getEmail
   
    // sets the phone number
    public   void  setPhoneNumber (   String  phone  )
    {
      phoneNumber  =  phone ;
    }   // end method setPhoneNumber

    // returns the phone number
    public   String  getPhoneNumber ()
    {
       return  phoneNumber ;
    }   // end method getPhoneNumber
}   // end class Person


/**************************************************************************
 * (C) Copyright 1992-2012 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

 

ch28/fig28_30_32-WithoutDBPredefined/PersonQueries.class

                public 
                synchronized 
                class PersonQueries {
    
                private 
                static 
                final String 
                URL = jdbc:derby:AddressBook;
    
                private 
                static 
                final String 
                USERNAME = javafp;
    
                private 
                static 
                final String 
                PASSWORD = javafp;
    
                private java.sql.Connection 
                connection;
    
                private java.sql.PreparedStatement 
                selectAllPeople;
    
                private java.sql.PreparedStatement 
                selectPeopleByLastName;
    
                private java.sql.PreparedStatement 
                insertNewPerson;
    
                public void PersonQueries();
    
                public java.util.List 
                getAllPeople();
    
                public java.util.List 
                getPeopleByLastName(String);
    
                public int 
                addPerson(String, String, String, String);
    
                public void 
                close();
}

ch28/fig28_30_32-WithoutDBPredefined/PersonQueries.java

ch28/fig28_30_32-WithoutDBPredefined/PersonQueries.java

// Fig. 28.31: PersonQueries.java
// PreparedStatements used by the Address Book application
import  java . sql . Connection ;
import  java . sql . DriverManager ;
import  java . sql . PreparedStatement ;
import  java . sql . ResultSet ;
import  java . sql . SQLException ;
import  java . util . List ;
import  java . util . ArrayList ;

public   class   PersonQueries  
{
    private   static   final   String  URL  =   "jdbc:derby:AddressBook" ;
    private   static   final   String  USERNAME  =   "deitel" ;
    private   static   final   String  PASSWORD  =   "deitel" ;

    private   Connection  connection  =   null ;   // manages connection
    private   PreparedStatement  selectAllPeople  =   null ;  
    private   PreparedStatement  selectPeopleByLastName  =   null ;  
    private   PreparedStatement  insertNewPerson  =   null ;  
    
    // constructor
    public   PersonQueries ()
    {
       try  
       {
         connection  =  
             DriverManager . getConnection (  URL ,  USERNAME ,  PASSWORD  );

          // create query that selects all entries in the AddressBook
         selectAllPeople  =  
            connection . prepareStatement (   "SELECT * FROM Addresses"   );
         
          // create query that selects entries with a specific last name
         selectPeopleByLastName  =  connection . prepareStatement (  
             "SELECT * FROM Addresses WHERE LastName = ?"   );
         
          // create insert that adds a new entry into the database
         insertNewPerson  =  connection . prepareStatement (  
             "INSERT INTO Addresses "   +  
             "( FirstName, LastName, Email, PhoneNumber ) "   +  
             "VALUES ( ?, ?, ?, ? )"   );
       }   // end try
       catch   (   SQLException  sqlException  )
       {
         sqlException . printStackTrace ();
          System . exit (   1   );
       }   // end catch
    }   // end PersonQueries constructor
   
    // select all of the addresses in the database
    public   List <   Person   >  getAllPeople ()
    {
       List <   Person   >  results  =   null ;
       ResultSet  resultSet  =   null ;
      
       try  
       {
          // executeQuery returns ResultSet containing matching entries
         resultSet  =  selectAllPeople . executeQuery ();  
         results  =   new   ArrayList <   Person   > ();
         
          while   (  resultSet . next ()   )
          {
            results . add (   new   Person (
               resultSet . getInt (   "addressID"   ),
               resultSet . getString (   "firstName"   ),
               resultSet . getString (   "lastName"   ),
               resultSet . getString (   "email"   ),
               resultSet . getString (   "phoneNumber"   )   )   );
          }   // end while
       }   // end try
       catch   (   SQLException  sqlException  )
       {
         sqlException . printStackTrace ();          
       }   // end catch
       finally
       {
          try  
          {
            resultSet . close ();
          }   // end try
          catch   (   SQLException  sqlException  )
          {
            sqlException . printStackTrace ();          
            close ();
          }   // end catch
       }   // end finally
      
       return  results ;
    }   // end method getAllPeople
   
    // select person by last name   
    public   List <   Person   >  getPeopleByLastName (   String  name  )
    {
       List <   Person   >  results  =   null ;
       ResultSet  resultSet  =   null ;

       try  
       {
         selectPeopleByLastName . setString (   1 ,  name  );   // specify last name

          // executeQuery returns ResultSet containing matching entries
         resultSet  =  selectPeopleByLastName . executeQuery ();  

         results  =   new   ArrayList <   Person   > ();

          while   (  resultSet . next ()   )
          {
            results . add (   new   Person (  resultSet . getInt (   "addressID"   ),
               resultSet . getString (   "firstName"   ),
               resultSet . getString (   "lastName"   ),
               resultSet . getString (   "email"   ),
               resultSet . getString (   "phoneNumber"   )   )   );
          }   // end while
       }   // end try
       catch   (   SQLException  sqlException  )
       {
         sqlException . printStackTrace ();
       }   // end catch
       finally
       {
          try  
          {
            resultSet . close ();
          }   // end try
          catch   (   SQLException  sqlException  )
          {
            sqlException . printStackTrace ();          
            close ();
          }   // end catch
       }   // end finally
      
       return  results ;
    }   // end method getPeopleByName
   
    // add an entry
    public   int  addPerson (  
       String  fname ,   String  lname ,   String  email ,   String  num  )
    {
       int  result  =   0 ;
      
       // set parameters, then execute insertNewPerson
       try  
       {
         insertNewPerson . setString (   1 ,  fname  );
         insertNewPerson . setString (   2 ,  lname  );
         insertNewPerson . setString (   3 ,  email  );
         insertNewPerson . setString (   4 ,  num  );

          // insert the new entry; returns # of rows updated
         result  =  insertNewPerson . executeUpdate ();  
       }   // end try
       catch   (   SQLException  sqlException  )
       {
         sqlException . printStackTrace ();
         close ();
       }   // end catch
      
       return  result ;
    }   // end method addPerson
   
    // close the database connection
    public   void  close ()
    {
       try  
       {
         connection . close ();
       }   // end try
       catch   (   SQLException  sqlException  )
       {
         sqlException . printStackTrace ();
       }   // end catch
    }   // end method close
}   // end class PersonQueries


/**************************************************************************
 * (C) Copyright 1992-2012 by Deitel & Associates, Inc. and               *
 * Pearson Education, Inc. 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.                     *
 *************************************************************************/

 

ch28/SQLScipts_forExercises/java db/address.sql

DROP TABLE Addresses; CREATE TABLE Addresses ( AddressID INT NOT NULL GENERATED ALWAYS AS IDENTITY, FirstName VARCHAR (15) NOT NULL, LastName VARCHAR (30) NOT NULL, Email VARCHAR (30) NOT NULL, PhoneNumber VARCHAR (15) NOT NULL ); INSERT INTO Addresses (FirstName,LastName,Email,PhoneNumber) VALUES ('Mike','Green','[email protected]','555-5555'), ('Mary','Brown','[email protected]','555-1234');

ch28/SQLScipts_forExercises/java db/books.sql

DROP TABLE "authorISBN"; DROP TABLE "titles"; DROP TABLE "authors"; CREATE TABLE "authors" ( "authorID" INT NOT NULL GENERATED ALWAYS AS IDENTITY, "firstName" varchar (20) NOT NULL, "lastName" varchar (30) NOT NULL, PRIMARY KEY ("authorID") ); CREATE TABLE "titles" ( "isbn" varchar (20) NOT NULL, "title" varchar (100) NOT NULL, "editionNumber" INT NOT NULL, "copyright" varchar (4) NOT NULL, PRIMARY KEY ("isbn") ); CREATE TABLE "authorISBN" ( "authorID" INT NOT NULL, "isbn" varchar (20) NOT NULL, FOREIGN KEY ("authorID") REFERENCES "authors" ("authorID"), FOREIGN KEY ("isbn") REFERENCES "titles" ("isbn") ); INSERT INTO "authors" ("firstName", "lastName") VALUES ('Paul','Deitel'), ('Harvey','Deitel'), ('Abbey','Deitel'), ('Michael','Morgano'), ('Eric','Kern'); INSERT INTO "titles" ("isbn","title","editionNumber", "copyright") VALUES ('0132152134','Visual Basic 2010 How to Program',5,'2011'), ('0132151421','Visual C# 2010 How to Program',4,'2011'), ('0132575663','Java How to Program',9,'2012'), ('0132662361','C++ How to Program',8,'2012'), ('0132404168','C How to Program',6,'2010'), ('013705842X','iPhone for Programmers: An App-Driven Approach',1,'2010'), ('0132121360','Android for Programmers: An App-Driven Approach',1,'2012'); INSERT INTO "authorISBN" ("authorID","isbn") VALUES (1,'0132152134'), (2,'0132152134'), (1,'0132151421'), (2,'0132151421'), (1,'0132575663'), (2,'0132575663'), (1,'0132662361'), (2,'0132662361'), (1,'0132404168'), (2,'0132404168'), (1,'013705842X'), (2,'013705842X'), (3,'013705842X'), (4,'013705842X'), (5,'013705842X'), (1,'0132121360'), (2,'0132121360'), (3,'0132121360'), (4,'0132121360');

ch28/SQLScipts_forExercises/mysql/bankDatabase.sql

DROP DATABASE IF EXISTS BankDatabase; CREATE DATABASE BankDatabase; USE BankDatabase; CREATE TABLE BankDatabase ( AccountNumber int NOT NULL PRIMARY KEY, PIN int NOT NULL, AvailableBalance double NOT NULL, TotalBalance double NOT NULL ) ;

ch28/SQLScipts_forExercises/mysql/books.sql

DROP DATABASE IF EXISTS books; CREATE DATABASE books; USE books; CREATE TABLE Authors ( AuthorID int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL ) ; CREATE TABLE Titles ( ISBN varchar(20) NOT NULL PRIMARY KEY, Title varchar(100) NOT NULL, EditionNumber int NOT NULL, Copyright varchar(4) NOT NULL ) ; CREATE TABLE AuthorISBN ( AuthorID int NOT NULL, ISBN varchar(20) NOT NULL, FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID), FOREIGN KEY (ISBN) References Titles(ISBN) ) ; INSERT INTO Authors (FirstName,LastName) VALUES ('Paul','Deitel') ; INSERT INTO Authors (FirstName,LastName) VALUES ('Harvey','Deitel') ; INSERT INTO Authors (FirstName,LastName) VALUES ('Abbey','Deitel') ; INSERT INTO Authors (FirstName,LastName) VALUES ('Michael','Morgano') ; INSERT INTO Authors (FirstName,LastName) VALUES ('Eric','Kern') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132152134','Visual Basic 2010 How to Program',5,'2011') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132152134') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132152134') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132151421','Visual C# 2010 How to Program',4,'2011') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132151421') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132151421') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132575663','Java How to Program',9,'2012') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132575663') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132575663') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132662361','C++ How to Program',8,'2012') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132662361') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132662361') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132404168','C How to Program',6,'2010') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132404168') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132404168') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('013705842X','iPhone for Programmers: An App-Driven Approach',1,'2010') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'013705842X') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'013705842X') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'013705842X') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'013705842X') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (5,'013705842X') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132121360','Android for Programmers: An App-Driven Approach',1,'2012') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132121360') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132121360') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'0132121360') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'0132121360') ;

ch28/SQLScipts_forExercises/mysql/employees.sql

DROP DATABASE IF EXISTS employees; CREATE DATABASE employees; USE employees; DROP TABLE IF EXISTS salariedEmployees; DROP TABLE IF EXISTS commissionEmployees; DROP TABLE IF EXISTS basePlusCommissionEmployees; DROP TABLE IF EXISTS hourlyEmployees; DROP TABLE IF EXISTS employees; CREATE TABLE employees ( socialSecurityNumber varchar (30) NOT NULL, firstName varchar (30) NOT NULL, lastName varchar (30) NOT NULL, birthday date NOT NULL, employeeType varchar (30) NOT NULL, departmentName varchar (30) NOT NULL, PRIMARY KEY (socialSecurityNumber) ) ; CREATE TABLE salariedEmployees ( socialsecurityNumber varchar (30) NOT NULL, weeklySalary real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) ) ; CREATE TABLE commissionEmployees ( socialSecurityNumber varchar (30) NOT NULL, grossSales int NOT NULL, commissionRate real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) ) ; CREATE TABLE basePlusCommissionEmployees ( socialSecurityNumber varchar (30) NOT NULL, grossSales int NOT NULL, commissionRate real NOT NULL, baseSalary real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) ) ; CREATE TABLE hourlyEmployees ( socialSecurityNumber varchar (30) NOT NULL, hours int NOT NULL, wage real NOT NULL, bonus real, INDEX (socialSecurityNumber), FOREIGN KEY (socialSecurityNumber) REFERENCES employees (socialSecurityNumber) ) ; INSERT INTO employees VALUES ('111-11-1111', 'John', 'Smith', '1945-1-2', 'salariedEmployee', 'R&D'); INSERT INTO employees VALUES ('222-22-2222', 'Sue', 'Jones', '1961-2-3', 'commissionEmployee', 'SALES'); INSERT INTO employees VALUES ('333-33-3333', 'Bob', 'Lowis', '1958-10-5', 'basePlusCommissionEmployee', 'SALES'); INSERT INTO employees VALUES ('444-44-4444', 'Karen', 'Price', '1972-5-25', 'hourlyEmployee', 'HR'); INSERT INTO salariedEmployees VALUES ('111-11-1111', 2013.67, 0); INSERT INTO commissionEmployees VALUES ('222-22-2222', 10100, 0.05, 0); INSERT INTO basePlusCommissionEmployees VALUES ('333-33-3333', 5000, 0.04, 300, 0); INSERT INTO hourlyEmployees VALUES ('444-44-4444', 30, 35.5, 0);

ch28/SQLscripts/java db/address.sql

DROP TABLE Addresses; CREATE TABLE Addresses ( AddressID INT NOT NULL GENERATED ALWAYS AS IDENTITY, FirstName VARCHAR (15) NOT NULL, LastName VARCHAR (30) NOT NULL, Email VARCHAR (30) NOT NULL, PhoneNumber VARCHAR (15) NOT NULL ); INSERT INTO Addresses (FirstName,LastName,Email,PhoneNumber) VALUES ('Mike','Green','[email protected]','555-5555'), ('Mary','Brown','[email protected]','555-1234');

ch28/SQLscripts/java db/books.sql

DROP TABLE "authorISBN"; DROP TABLE "titles"; DROP TABLE "authors"; CREATE TABLE "authors" ( "authorID" INT NOT NULL GENERATED ALWAYS AS IDENTITY, "firstName" varchar (20) NOT NULL, "lastName" varchar (30) NOT NULL, PRIMARY KEY ("authorID") ); CREATE TABLE "titles" ( "isbn" varchar (20) NOT NULL, "title" varchar (100) NOT NULL, "editionNumber" INT NOT NULL, "copyright" varchar (4) NOT NULL, PRIMARY KEY ("isbn") ); CREATE TABLE "authorISBN" ( "authorID" INT NOT NULL, "isbn" varchar (20) NOT NULL, FOREIGN KEY ("authorID") REFERENCES "authors" ("authorID"), FOREIGN KEY ("isbn") REFERENCES "titles" ("isbn") ); INSERT INTO "authors" ("firstName", "lastName") VALUES ('Paul','Deitel'), ('Harvey','Deitel'), ('Abbey','Deitel'), ('Michael','Morgano'), ('Eric','Kern'); INSERT INTO "titles" ("isbn","title","editionNumber", "copyright") VALUES ('0132152134','Visual Basic 2010 How to Program',5,'2011'), ('0132151421','Visual C# 2010 How to Program',4,'2011'), ('0132575663','Java How to Program',9,'2012'), ('0132662361','C++ How to Program',8,'2012'), ('0132404168','C How to Program',6,'2010'), ('013705842X','iPhone for Programmers: An App-Driven Approach',1,'2010'), ('0132121360','Android for Programmers: An App-Driven Approach',1,'2012'); INSERT INTO "authorISBN" ("authorID","isbn") VALUES (1,'0132152134'), (2,'0132152134'), (1,'0132151421'), (2,'0132151421'), (1,'0132575663'), (2,'0132575663'), (1,'0132662361'), (2,'0132662361'), (1,'0132404168'), (2,'0132404168'), (1,'013705842X'), (2,'013705842X'), (3,'013705842X'), (4,'013705842X'), (5,'013705842X'), (1,'0132121360'), (2,'0132121360'), (3,'0132121360'), (4,'0132121360');

ch28/SQLscripts/mysql/bankDatabase.sql

DROP DATABASE IF EXISTS BankDatabase; CREATE DATABASE BankDatabase; USE BankDatabase; CREATE TABLE BankDatabase ( AccountNumber int NOT NULL PRIMARY KEY, PIN int NOT NULL, AvailableBalance double NOT NULL, TotalBalance double NOT NULL ) ;

ch28/SQLscripts/mysql/books.sql

DROP DATABASE IF EXISTS books; CREATE DATABASE books; USE books; CREATE TABLE Authors ( AuthorID int NOT NULL AUTO_INCREMENT PRIMARY KEY, FirstName varchar(30) NOT NULL, LastName varchar(30) NOT NULL ) ; CREATE TABLE Titles ( ISBN varchar(20) NOT NULL PRIMARY KEY, Title varchar(100) NOT NULL, EditionNumber int NOT NULL, Copyright varchar(4) NOT NULL ) ; CREATE TABLE AuthorISBN ( AuthorID int NOT NULL, ISBN varchar(20) NOT NULL, FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID), FOREIGN KEY (ISBN) References Titles(ISBN) ) ; INSERT INTO Authors (FirstName,LastName) VALUES ('Paul','Deitel') ; INSERT INTO Authors (FirstName,LastName) VALUES ('Harvey','Deitel') ; INSERT INTO Authors (FirstName,LastName) VALUES ('Abbey','Deitel') ; INSERT INTO Authors (FirstName,LastName) VALUES ('Michael','Morgano') ; INSERT INTO Authors (FirstName,LastName) VALUES ('Eric','Kern') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132152134','Visual Basic 2010 How to Program',5,'2011') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132152134') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132152134') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132151421','Visual C# 2010 How to Program',4,'2011') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132151421') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132151421') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132575663','Java How to Program',9,'2012') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132575663') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132575663') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132662361','C++ How to Program',8,'2012') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132662361') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132662361') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132404168','C How to Program',6,'2010') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132404168') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132404168') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('013705842X','iPhone for Programmers: An App-Driven Approach',1,'2010') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'013705842X') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'013705842X') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'013705842X') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'013705842X') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (5,'013705842X') ; INSERT INTO Titles (ISBN,Title,EditionNumber,Copyright) VALUES ('0132121360','Android for Programmers: An App-Driven Approach',1,'2012') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (1,'0132121360') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (2,'0132121360') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (3,'0132121360') ; INSERT INTO AuthorISBN (AuthorID,ISBN) VALUES (4,'0132121360') ;