addressbook.java

SQL Database, simple program for sql learner

        
        //Creates space labels to fill up the space in the GridLayout
        Label space1,space2,space3,space4,space5,
        space6,space7,space8,space9,space10,space11;
        
        space1 = new Label(" ");
        space2 = new Label(" ");
        space3 = new Label(" ");
        space4 = new Label(" ");
        space5 = new Label(" ");
        space6 = new Label(" ");
        space7 = new Label(" ");
        space8 = new Label(" ");
        space9 = new Label(" ");
        space10 = new Label(" ");
        space11= new Label(" ");
            
        //Add all the controls to the Dialog.
        add(Name);
        add(NameList);
        add(space1);
        add(Surname);
        add(SurnameField);
        add(space2);
        add(Address);
        add(AddressField);
        add(space3);
        add(City);
        add(CityField);
        add(space4);
        add(State);
        add(StateField);
        add(space5);
        add(Zip);
        add(ZipField);
        add(space6);
        add(Phone);
        add(PhoneField);
        add(space7);
        add(OfficePhoneNumber);
        add(OfficePhoneNumberField);
        add(space8);
        add(DepartmentAssg);
        add(DepartmentAssgField);
        add(space9);
        add(YearsEmployee);
        add(YearsEmployeeField);
        add(space10);
        add(MonthlyPay);
        add(MonthlyPayField);
        add(space11);
        add(BtnUpdate);
        add(BtnCn);
        GetData();
    }
   
//Handles all the events happeing on the dialog box
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void actionPerformed(ActionEvent e) {
      String str = e.getActionCommand();
        if (str.equals("Update")){
           DataUpdate();
        }
        if (str.equals("Cancel")) {
            dispose();
        }
    }

//This method handles the Event action that take place on the Choide Drop Down List
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void itemStateChanged(ItemEvent ie) {
        String dbuser = " ";
        String dbpasswd = " ";
        String DriverPrefix = "jdbc:odbc:";
        String DataSource = "AddressBook";
        
        //This will hold the currently selected name in the list in myChoice
        String myChoice = NameList.getSelectedItem();
        
        String SQL = "SELECT name,surname,address,phone 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;
        
        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);
            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));
            DepartmentAssgField.setText(rs.getString(9));
            YearsEmployeeField.setText(rs.getString(10));
            MonthlyPayField.setText(rs.getString(11));
            con.close();
            
    } catch (Exception e) {
        JOptionPane.showMessageDialog(null,""+e.getMessage(),"Communication Error",JOptionPane.WARNING_MESSAGE);
    }
}
   
//This method will populate the pop up list with names 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);
            //This will populate the drop down list with all the names of people
            //entered into the database.
            while (rs.next()) {
               NameList.add(rs.getString("name"));
            }             
        } catch(Exception e) {
            JOptionPane.showMessageDialog(null,""+e.getMessage(),"Communication Error",JOptionPane.WARNING_MESSAGE);
        }
       //This try - catch sequence will close the database after the drop down 
       //list has been populated
        try {
           con.close();
       } catch (Exception e) {
           JOptionPane.showMessageDialog(null,""+e.getMessage(),"Warning",JOptionPane.WARNING_MESSAGE);
       }
    }

//This will update the change data from the TextFields to the database.
/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void DataUpdate() {
       
        String dbuser = " ";
        String dbpasswd = " ";
        String DriverPrefix = "jdbc:odbc:";
        String DataSource = "AddressBook";
        
       //Loads the database driver
        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);
        }
        
        //These String variables will hold the updated data retrieved from the TextFields.
        String upSurname = SurnameField.getText();
        String upAddress = AddressField.getText();
        String upCity = CityField.getText();
        String upState = StateField.getText();
        String upZip = ZipField.getText(); 
        String upPhone = PhoneField.getText();
        String upOfficePhoneNumber = OfficePhoneNumberField.getText();
        String DepartmentAssg = DepartmentAssgField.getText();
        String YearsEmployee = YearsEmployeeField.getText();
        String MonthlyPay = MonthlyPayField.getText();
                
        //This is the SQL String that updates the data from the TextFields to the database
        String SQL = "UPDATE Address SET surname = '"+upSurname+"', address = '"+upAddress+"', phone = '"+upPhone+"' WHERE name = '"+NameList.getSelectedItem()+"'"; 
        //Communicates with database
       try {
            stmt.executeUpdate(SQL);
            con.close();
            dispose();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null,""+e.getMessage(),"Communication Error",JOptionPane.WARNING_MESSAGE);
        } 
     }
   }

////////////////////////////////////////////////////////////////////////////////
//This class will delete data from the database
///////////////////////////////////////////////////////////////////////////////

class DelRec extends Dialog implements ActionListener, ItemListener {
    
    //Declaration of TextFields
    TextField SurnameField, AddressField, PhoneField, CityField, StateField,
	  ZipField, OfficePhoneNumberField, DepartmentAssgField, YearsEmployeeField,
	  MonthlyPayField;
    
    //Declaration of drop down list
    Choice NameList;
    
    public DelRec (AddressBook parent, String title) {
      
        super (parent, title, true);
        setLayout(new GridLayout(13,10,20,10));
        setSize(300, 400);
        setResizable(false);
        
       //Declaration of labels that will describe the TextFields
        Label Name,Surname,Address,Phone,City,State, Zip, 
        OfficePhoneNumber, DepartmentAssg,YearsEmployee, MonthlyPay;
        
        //creates Label
        Name = new Label("Name: ");
        Surname = new Label("Surname: ");
        Address = new Label("Address: ");
        City = new Label("City: ");
        State = new Label("State: ");
        Zip = new Label ("Zip: ");
        Phone = new Label("Home Phone Number: ");
        OfficePhoneNumber = new Label ("Office Phone Number: ");
        DepartmentAssg = new Label ("Department Assigned: ");
        YearsEmployee = new Label ("Years Employeed: ");
        MonthlyPay = new Label ("Monthly Pay: ");
        
        //Declaration of labels that will be used as spacers in the GridLayout
        Label space1,space2,space3,space4,space5,
        space6,space7,space8,space9,space10,space11;
        
        space1 = new Label(" ");
        space2 = new Label(" ");
        space3 = new Label(" ");
        space4 = new Label(" ");
        space5 = new Label(" ");
        space6 = new Label(" ");
        space7 = new Label(" ");
        space8 = new Label(" ");
        space9 = new Label(" ");
        space10 = new Label(" ");
        space11= new Label(" ");
        
        //Creates the button on the dialog
        Button BtnDel = new Button("Delete");
        Button BtnCn = new Button("Cancel");
        
        //Creates the drop down list
        NameList = new Choice();
                
        //Adds listeners to buttons and drop down list
        BtnDel.addActionListener(this);
        BtnCn.addActionListener(this);
        NameList.addItemListener(this);
        
        //Creates the TextFields
        SurnameField = new TextField(10);
        AddressField = new TextField(20);
        PhoneField = new TextField(10);
        CityField = new TextField(10);
        StateField = new TextField(10);
        ZipField = new TextField(10);
        OfficePhoneNumberField = new TextField(10);
        DepartmentAssgField = new TextField(10);
        YearsEmployeeField =  new TextField(10);
        MonthlyPayField = new TextField (10);
        
        //Adds the components to the dialog
        add(Name);
        add(NameList);
        add(space1);
        add(Surname);
        add(SurnameField);
        add(space2);
        add(Address);
        add(AddressField);
        add(space3);
        add(City);
        add(CityField);
        add(space4);
        add(State);
        add(StateField);
        add(space5);
        add(Zip);
        add(ZipField);
        add(space6);
        add(Phone);
        add(PhoneField);
        add(space7);
        add(OfficePhoneNumber);
        add(OfficePhoneNumberField);
        add(space8);
        add(DepartmentAssg);
        add(DepartmentAssgField);
        add(space9);
        add(YearsEmployee);
        add(YearsEmployeeField);
        add(space10);
        add(MonthlyPay);
        add(MonthlyPayField);
        add(space11);
        add(BtnDel);
        add(BtnCn);
        GetData();
    }

//This method handles all the action events from the Delete and Cancel buttons.
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void actionPerformed(ActionEvent e) {
        String str = e.getActionCommand();
        if (str.equals("Delete")) {
            DelCurRec();
        }
        if (str.equals("Cancel")) {
            dispose();
        }
   }    
    
//Updates the TextFields with the rest of the data when selection is made.
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    
    public void itemStateChanged(ItemEvent e) {
        
        String dbuser = " ";
        String dbpasswd = " ";
        String DriverPrefix = "jdbc:odbc:";
        String DataSource = "AddressBook";
        
        //This will hold the currently selected name in the list in myChoice
        String myChoice = NameList.getSelectedItem();
        
        //This is the SQL query for extracting data from the database.
        String SQL = "SELECT name,surname,address,phone FROM Address WHERE name ='" +myChoice+ "'";
        
        //Loads the driver for communicating with database
        try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null,""+ex.getMessage(),"JDBC Driver Error",JOptionPane.WARNING_MESSAGE);
        }
        Statement stmt = null;
        Connection con = null;
        
        //Creates a connection to the database
        try {
            con = DriverManager.getConnection(DriverPrefix+DataSource,dbuser,dbpasswd);
            stmt = con.createStatement();
        } catch (Exception ex) {
            JOptionPane.showMessageDialog(null,""+ex.getMessage(),"Connection Error",JOptionPane.WARNING_MESSAGE);
        }
        
        ResultSet rs = null;