addressbook.java

SQL Database, simple program for sql learner

       
        //Executes the Sl query on the database.
        try {
            rs = stmt.executeQuery(SQL);
            rs.next();
            SurnameField.setText(rs.getString(2));
            AddressField.setText(rs.getString(3));
            CityField.setText(rs.getString(4));
            StateField.setText(rs.getString(5));
            ZipField.setText(rs.getString(6));
            PhoneField.setText(rs.getString(7));
            OfficePhoneNumberField.setText(rs.getString(8));
            YearsEmployeeField.setText(rs.getString(9));
            MonthlyPayField.setText(rs.getString(10));
            con.close();
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null,""+ex.getMessage(),"Communication Error",JOptionPane.WARNING_MESSAGE);
        }
 }    

//This method will populate the drop downlist with data from the database.
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void GetData() {
       
       String dbuser = " ";
       String dbpasswd = " ";
       String DriverPrefix = "jdbc:odbc:";
       String DataSource = "AddressBook";
       
       String SQL = "SELECT name FROM address";
       
       try {
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       }  catch (Exception e) {
           JOptionPane.showMessageDialog(null,""+e.getMessage(),"JDBC Driver Error",JOptionPane.WARNING_MESSAGE);
       }
       
       Statement stmt = null;
       Connection con = null;
       
       try {
            con = DriverManager.getConnection(DriverPrefix+DataSource,dbuser, dbpasswd);
            stmt = con.createStatement();
       } catch (Exception e) {
           JOptionPane.showMessageDialog(null,""+e.getMessage(),"Connection Error",JOptionPane.WARNING_MESSAGE);
       }
       
       ResultSet rs = null;
       
       try {
           rs = stmt.executeQuery(SQL);
           
           while (rs.next()) {
               NameList.add(rs.getString("name"));
           }
       } catch (Exception e) {
           JOptionPane.showMessageDialog(null,""+e.getMessage(),"Communication Problem",JOptionPane.WARNING_MESSAGE);
       }
    }
    
//This method wil delete the currently selected record.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    public void DelCurRec() {
        
        String dbuser = " ";
        String dbpasswd = " ";
        String DriverPrefix = "jdbc:odbc:";
        String DataSource = "AddressBook";
        
        //MyChoice will hold the value for the currently selected item
        String myChoice = NameList.getSelectedItem();
        
        //This is the SQL string
        String SQL = "DELETE FROM Address WHERE name = '" +myChoice+ "'";
    
        try {
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       }  catch (Exception e) {
           JOptionPane.showMessageDialog(null,""+e.getMessage(),"JDBC Driver Error",JOptionPane.WARNING_MESSAGE);
       }
        
        Statement stmt = null;
        Connection con = null;
        
        //Creates connection to database
        try {
            con = DriverManager.getConnection(DriverPrefix+DataSource,dbuser,dbpasswd);
            stmt = con.createStatement();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null,""+e.getMessage(),"Connection Error",JOptionPane.WARNING_MESSAGE);
        }
        
        //Execute the SQL statment for deleting records
        try {
            stmt.executeUpdate(SQL);
            //This closes the connection to the database          
            con.close();
            //This closes the dialog
            dispose();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null,""+e.getMessage(),"Communication Error",JOptionPane.WARNING_MESSAGE);
        }
    }
}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//This class will create the Search Dialog.
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

class SearchDlg extends Dialog implements ActionListener, ItemListener {
    
        //This declares a TextField that will be put on the dialog
        TextField Name;
        
        //This creates a Search and Cancel button on the dialog
        Button BtnGo, BtnCn;
        
        //This creates checkboxes for different search queries
        Checkbox option1 = new Checkbox("By Name");
        Checkbox option2 = new Checkbox("By Surname");
    
        public SearchDlg (AddressBook parent, String title) {
        
        super(parent,title,false);
        setSize(300, 100);
        setLayout(new GridLayout(3,2,8,5));
        setResizable(false);
        setLocation(300,50);
        
       //This creates a label for the search dialog describing the TextField
        Label Srch = new Label("Search For :");
        
       //This creates a Textfield for the user input 
         Name = new TextField(10);
           
        //Creates button for Search and Cancel on the dialog
         BtnGo = new Button("Search");
         BtnCn = new Button("Cancel");
        
        //Disables the Search button unitl a selection is made from the CheckBoxes
         BtnGo.setEnabled(false);
        
        //Adds event listeners to the Button.
         BtnGo.addActionListener(this);
         BtnCn.addActionListener(this);
        
        //Adds Item Listeners to the checkboxes
         option1.addItemListener(this);
         option2.addItemListener(this);
        
        //This will create spacer labels for the GridLayout
         Label space1, space2, space3;
        
         space1 = new Label(" ");
         space2 = new Label(" ");
         space3 = new Label(" ");
        
        //Add Controls to the dialog.
        add(Srch);
        add(Name);
        add(space1);
        add(option1);
        add(option2);
        add(space2);
        add(space3);
        add(BtnGo);
        add(BtnCn);
    }
    
//This method handles all the events that take place on the dialog
//////////////////////////////////////////////////////////////////////////////////////////////////////////
        
    public void actionPerformed(ActionEvent e) {
        String str = e.getActionCommand();
            if (str.equals("Search")) {
                GoSrch();
            }
            if (str.equals("Cancel")) {
                dispose();
            }
       }
      
//This will handle the events from clicking the ChecBoxes on the dialog
//the Search button will also be enable when a selection is made.
/////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void itemStateChanged(ItemEvent e) {
        if (option1.getState() == true) {
            option2.setEnabled(false); 
            BtnGo.setEnabled(true);
        } else {
            option2.setEnabled(true);
            BtnGo.setEnabled(false);
        }
        if (option2.getState() == true) {
            option1.setEnabled(false);
            BtnGo.setEnabled(true);
        } else {
            option1.setEnabled(true);
        }
    }
    
//This method will search for the selected record in the database    
/////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void GoSrch() {
              
       if (option1.getState() == true) {
           Srch1();
       }
       if (option2.getState() == true) {
           Srch2();
       }
    }


//This method will search the database by the name that is input into the TextField
/////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void Srch1()  {
        
         String dbuser = " ";
         String dbpasswd = " ";
         String DriverPrefix = "jdbc:odbc:";
         String DataSource = "AddressBook";
                  
         String mySearch = Name.getText();
         
         //This is the SQL String for retrieving data by name from the database.
         String SQL = "SELECT name,surname,address,phone FROM Address WHERE name = '" +mySearch+"'";
         
         //This loads the driver for the database
         try {
             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         } catch (Exception e) {
             JOptionPane.showMessageDialog(null,""+e.getMessage(),"Database Driver Error", JOptionPane.WARNING_MESSAGE);
         }
         
         Statement stmt = null;
         Connection con = null;
         
         //Creates connection to the database.
         try {
             con = DriverManager.getConnection(DriverPrefix+DataSource,dbuser, dbpasswd);
             stmt = con.createStatement();
         } catch (Exception e) {
             JOptionPane.showMessageDialog(null, " "+e.getMessage(),"Cannot Connect to Database",JOptionPane.WARNING_MESSAGE);
         }
         
         ResultSet rs = null;
         
         //Executes the SQL query on the database and displays the result in a JFC OptionPane
         try {
             rs = stmt.executeQuery(SQL);
             rs.next(); 
             String Result = rs.getString(1) + " " + rs.getString(2) + "\n" + rs.getString(3) + "\n" + rs.getString(4);
             
             //Makes use of a swing OptionPane to display information of the successful search.
             JOptionPane.showMessageDialog(null,Result,"Record Found", JOptionPane.INFORMATION_MESSAGE);
            
             //Close the connection to the database.
             con.close();
             this.dispose();
         
         } catch (Exception e) {
             //Makes use of the JFC Swing OptionPane to display error message
             JOptionPane.showMessageDialog(null ,"Record not Found","Warning", JOptionPane.INFORMATION_MESSAGE);
         }
    }


//This method will search the database for a input record according to the surname.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void Srch2() {
            
         String dbuser = " ";
         String dbpasswd = " ";
         String DriverPrefix = "jdbc:odbc:";
         String DataSource = "AddressBook";

         String mySearch = Name.getText();
         
         //This is the SQL String for retrieving data by name from the database.
         String SQL = "SELECT name,surname,address,phone FROM Address WHERE surname = '" +mySearch+"'";
         
         //This loads the driver for the database
         try {
             Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         } catch (Exception e) {
             JOptionPane.showMessageDialog(null,""+e.getMessage(),"Database Driver Error", JOptionPane.WARNING_MESSAGE);
         }
         
         Statement stmt = null;
         Connection con = null;
         
         //Creates connection to the database.
         try {
             con = DriverManager.getConnection(DriverPrefix+DataSource,dbuser, dbpasswd);
             stmt = con.createStatement();
         } catch (Exception e) {
             JOptionPane.showMessageDialog(null, " "+e.getMessage(),"Cannot Connect to Database",JOptionPane.WARNING_MESSAGE);
         }
         
         ResultSet rs = null;
         
         //Executes the SQL query on the database and displays the result in a JFC OptionPane
         try {
             rs = stmt.executeQuery(SQL);
             rs.next(); 
             String Result = rs.getString(1) + " " + rs.getString(2) + "\n" + rs.getString(3) + "\n" + rs.getString(4);
             
             //Makes use of a swing OptionPane to display information of the successful search.
             JOptionPane.showMessageDialog(null,Result,"Record Found", JOptionPane.INFORMATION_MESSAGE);
            
             //Close the connection to the database.
             con.close();
             this.dispose();
         
         } catch (Exception e) {
             //Makes use of the JFC Swing OptionPane to display error message
             JOptionPane.showMessageDialog(null ,"Record not Found","Warning", JOptionPane.INFORMATION_MESSAGE);
         }
   }
}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//This class handles the menubar events
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

class myMenuHandler implements ActionListener {
    AddressBook appbook;
    public myMenuHandler(AddressBook appbook) {
        this.appbook = appbook;
    }
    
//This code will display an Dialog Boxes for the different Menu Selections
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////   
 
    public void actionPerformed(ActionEvent ae) {
        String arg = (String)ae.getActionCommand();
      
        //This code executed when Exit is selected on the Menu Bar
         if (arg.equals("Exit")) {
           appbook.dispose();
        }      
        
        //This will start the creation of the Add Dialog
        if (arg.equals("Add...")) {
            AddDlg Adlg = new AddDlg(appbook, "Add New Address");
            Adlg.setVisible(true);
        }
        
        //This will start the creation of the Edit Dialog
        if (arg.equals("Edit...")) {
            EditDlg Edlg = new EditDlg(appbook, "Edit Records");
            Edlg.setVisible(true);
        }
                      
        //This will start the creation of the Delete Dialog
        if (arg.equals("Delete...")) {
            DelRec dlg = new DelRec(appbook, "Delete Records");
            dlg.setVisible(true);
        }        
        
        //This will start the creation of the Search Dialog
        if (arg.equals("Search...")) {
            SearchDlg schDlg = new SearchDlg(appbook, "Search Records");
            schDlg.setVisible(true);
        }
        
        //This will Display the About Dialog
        if (arg.equals("About Address Book...")) {
           AboutDlg d = new AboutDlg(appbook, "About");
           d.setVisible(true);
       }
    }

	
	
}