Question

This project will create a GUI to access data held in a MySQL database. You will design a GUI using Swing components that will allow you to add a record, delete a record, update a record and display all current records int he database. The design is your choice. Extra credit will be given for the ability to search for a specific record by a key value. You will need to provide details of the database domain, table and record structure

Answer 1

import java.awt.*;
import java.sql.*;
import javax.swing.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

public class MySQLSwing implements ActionListener {

   JTextField txtFName,txtLName,txtAddress,txtSalary;
   JButton btnAdd,btnUpdate,btnDelete,btnPrev,btnNext;
   JLabel lblFName,lblLname,lblAddress,lblSalary,lblF,lblL,lblA,lblS,lblFVal,lblLVal,lblAVal,lblSVal;
   ResultSet rs;

   private void displayUI()
   {
       JFrame frame = new JFrame("GUI to access data");
JPanel panel = new JPanel(new GridLayout(4,2));

       lblFName = new JLabel(" First Name : ");
       txtFName = new JTextField(15);

       lblLname = new JLabel(" Last Name : ");
       txtLName = new JTextField();

       lblAddress = new JLabel(" Address : ");
       txtAddress = new JTextField();

       lblSalary = new JLabel(" Salary : ");
       txtSalary = new JTextField();

       panel.add(lblFName);
       panel.add(txtFName);

       panel.add(lblLname);
       panel.add(txtLName);

       panel.add(lblAddress);
       panel.add(txtAddress);

       panel.add(lblSalary);
       panel.add(txtSalary);

       JPanel pnlButton = new JPanel(new GridLayout(1,3));

       btnAdd = new JButton("Add");
       btnAdd.addActionListener(this);

       btnUpdate = new JButton("Update");
       btnUpdate.addActionListener(this);

       btnDelete = new JButton("Delete");
       btnDelete.addActionListener(this);

       pnlButton.add(btnAdd);
       pnlButton.add(btnUpdate);
       pnlButton.add(btnDelete);

       JPanel pnlNavigate = new JPanel(new GridLayout(1,2));
       btnPrev = new JButton(" << ");
       btnPrev.setActionCommand("Prev");
       btnPrev.addActionListener(this);

       btnNext = new JButton(" >> ");
       btnNext.setActionCommand("Next");
       btnNext.addActionListener(this);

       pnlNavigate.add(btnPrev);
       pnlNavigate.add(btnNext);

       JPanel pnlNavAns = new JPanel(new GridLayout(4,2));

       lblF = new JLabel(" First Name : ");
       lblFVal = new JLabel("Val");

       lblL = new JLabel(" Last Name : ");
       lblLVal = new JLabel("Val");

       lblA = new JLabel(" Address : ");
       lblAVal = new JLabel("Val");

       lblS = new JLabel(" Salary : ");
       lblSVal = new JLabel("Val");

       pnlNavAns.add(lblF);
       pnlNavAns.add(lblFVal);

       pnlNavAns.add(lblL);
       pnlNavAns.add(lblLVal);

       pnlNavAns.add(lblA);
       pnlNavAns.add(lblAVal);

       pnlNavAns.add(lblS);
       pnlNavAns.add(lblSVal);

       Container cn = frame.getContentPane();
       cn.setLayout(new BoxLayout(cn,BoxLayout.Y_AXIS));

       frame.add(panel);
       frame.add(pnlButton);
       frame.add(pnlNavAns);
       frame.add(pnlNavigate);

       frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
       frame.pack();
       frame.setVisible(true);
   }
  
   @Override
   public void actionPerformed(ActionEvent evt) {

       String action = evt.getActionCommand();
       if(action.equals("Add"))
       {
           insertOperation();
       }else if(action.equals("Update"))
       {
           updateOperation();
       }else if(action.equals("Delete"))
       {
           deleteOperation();
       }else if(action.equals("Prev"))
       {
           preNavigation();
       }else if(action.equals("Next"))
       {
           nextNavigation();
       }
   }
  
  
   private void insertOperation()
   {
       try
       {
           //Load Jdbc Odbc Driver
           Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
           Connection con = DriverManager.getConnection("jdbc:odbc:ShivaEvening");

           String sql = "INSERT INTO Employee (FName,LName,Address,Salary) " +
                   "Values ('"+txtFName.getText()+"'," +
                           "'"+txtLName.getText()+"'," +
                           "'"+txtAddress.getText()+"'," +
                           "'"+txtSalary.getText()+"')";

           Statement st = con.createStatement();
           st.execute(sql);

           JOptionPane.showMessageDialog(null, "Record Added Succesfully.","Record Added",
JOptionPane.INFORMATION_MESSAGE);
           clearControls();
       }catch(Exception e)
       {
           JOptionPane.showMessageDialog(null, e.getMessage(),"Error",
JOptionPane.ERROR_MESSAGE);
       }
   }
  
  
   private void updateOperation()
   {
       try
       {
           //Load Jdbc Odbc Driver
           Class.forName("com.mysql.jdbc.Driver");
           Connection con=DriverManager.getConnection(
                   "jdbc:mysql://localhost:3306/sonoo","root","root");

           String sql = "Update Employee " +
                   "SET LName = '"+txtLName.getText()+"'," +
                           "Address = '"+txtAddress.getText()+"'," +
                           "Salary = '"+txtSalary.getText()+"'" +
                           "Where FName = '"+txtFName.getText()+"'";

           JOptionPane.showMessageDialog(null, sql,"Record Updated",
JOptionPane.INFORMATION_MESSAGE);
           Statement st = con.createStatement();
           st.execute(sql);

           JOptionPane.showMessageDialog(null, "Record Update Succesfully.",
"Record Updated",JOptionPane.INFORMATION_MESSAGE);
           clearControls();
       }catch(Exception e)
       {
           JOptionPane.showMessageDialog(null, e.getMessage(),"Error",
JOptionPane.ERROR_MESSAGE);
       }
   }
  
  
   private void deleteOperation()
   {
       int ans = JOptionPane.showConfirmDialog(null,
               "Are you sure to delete the Record ?", "Delete Record",
JOptionPane.YES_NO_OPTION);
       if(ans == JOptionPane.YES_OPTION)
       {
           try{
           //Load Jdbc Odbc Driver
               Class.forName("com.mysql.jdbc.Driver");
               Connection con=DriverManager.getConnection(
                       "jdbc:mysql://localhost:3306/sonoo","root","root");

           String sql = "Delete FROM Employee where FName = '"+txtFName.getText()+"'";

           Statement st = con.createStatement();
           st.execute(sql);
           }catch(Exception e)
           {
               JOptionPane.showMessageDialog(null, e.getMessage(),"Error",
JOptionPane.ERROR_MESSAGE);
           }
           JOptionPane.showMessageDialog(null, "Record Deleted","Success",
JOptionPane.INFORMATION_MESSAGE);
       }
       else
       {
           JOptionPane.showMessageDialog(null, "Operation Canceled","Cancel",
JOptionPane.INFORMATION_MESSAGE);
       }
   }
  
  
   private void preNavigation()
   {
       try{
           if(rs == null)
           {
           //Load Jdbc Odbc Driver
               Class.forName("com.mysql.jdbc.Driver");
               Connection con=DriverManager.getConnection(
                       "jdbc:mysql://localhost:3306/sonoo","root","root");

           String sql = "SELECT * FROM Employee";

           Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
           rs = st.executeQuery(sql);
           }
           if(rs.previous())
           {
               populateValue();
           }
           }catch(Exception e)
           {
               JOptionPane.showMessageDialog(null, e.getMessage(),"Error",
JOptionPane.ERROR_MESSAGE);
           }
   }
  
  
   private void nextNavigation()
   {
       try{
           if(rs == null)
           {
           //Load Jdbc Odbc Driver
           Class.forName("com.mysql.jdbc.Driver");
           Connection con=DriverManager.getConnection(
                   "jdbc:mysql://localhost:3306/sonoo","root","root");
          
           String sql = "SELECT * FROM Employee";

           Statement st = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_UPDATABLE);
           rs = st.executeQuery(sql);
           }
           if(rs.next())
           {
               populateValue();
           }
           }catch(Exception e)
           {
               JOptionPane.showMessageDialog(null, e.getMessage(),"Error",
JOptionPane.ERROR_MESSAGE);
           }
   }
  
   private void populateValue() throws Exception
   {
       String fName = rs.getString("FName");
       String lName = rs.getString("LName");
       String add = rs.getString("Address");
       String sal = rs.getString("Salary");

       lblFVal.setText(fName);
       lblLVal.setText(lName);
       lblAVal.setText(add);
       lblSVal.setText(sal);

       txtFName.setText(fName);
       txtLName.setText(lName);
       txtAddress.setText(add);
       txtSalary.setText(sal);
   }
  
   private void clearControls()
   {
       txtFName.setText("");
       txtLName.setText("");
       txtAddress.setText("");
       txtSalary.setText("");
   }
  
  
   public static void main(String[] args) {
       MySQLSwing obj = new MySQLSwing();
       obj.displayUI();
   }
}

D MySQLSwing.java 3 Button btnAdd, btnUpdate,btnDelete,btnPrev, btnNext; 12 13 14 15 private void displayUI() ResultSet rs; JFrame frame = new JFrame("GUI to access data"); JPanel panel = new JPanel(new GridLayout(4,2)); 18 19 20 21 IblFName = new JLabel(" First Name : "); GUl to access data txtFName new JTextField(15); = First Name: Last Name: Address: Salary: 23 lblLnamenew JLabel( Last Name"); txtLNamenew JTextField); 25 26 27 1blAddress new Label("Address: "); txtAddress = new JTextField(); Add Update Val Val Val Val Delete First Name: Last Name: Address: Sala lblsalarynew 3Label(" Salary "); 29 30 31 txtSalarynew JTextField); panel.add(lblFName); panel.add (txtFName); 45 34 35 panel.add (lblLname); panel.add(txtLName); 37 panel.add(lblAddress); panel.add(txtAddress); 40 41 42 43 panel.add(1blsalary); panel.add(txtsalary); JPanel pn|Button = new JPanel(new GridLayout (1,3)); btnAddnew JButton ("Add"); 45 46 btnAdd.addActionListener (this); 48