Java Database Connect Write a program that views inserts and

Java Database Connect

Write a program that views, inserts, and updates staff information stored in a database, as shown below. The view button displays a record with a specified ID. The Insert button inserts a new record. The Update button updates the record for the specified ID. The Staff table is created as follows:

Create table Staff(id char(9) not null, lastName varchar(15), firstName varchar(15), mi char(1), address varchar(20), city varchar(20), state char(2), telephone char(10), email varchar(40), primary key(id));

Solution

import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import javax.swing.JOptionPane;
class Assignment6DB extends Frame implements ActionListener
{
   Button b1,b2,b3,b4;
   Panel top,bottom;
   TextField tf1,tf2,tf3,tf4,tf5,tf6,tf7,tf8;
   TextArea ta1;
   Label l1,l2,l3,l4,l5,l6,l7,l8,l9;
   Connection con;
   Statement st;
   PreparedStatement pst;
   ResultSet rs;
   Assignment6DB(){
       setVisible(true);setSize(750,700);
       setTitle(\"Assignment6DB\");

       setFont(new Font(\"Arial\",Font.BOLD,25));
       bottom=new Panel();
       b1=new Button(\"view\");bottom.add(b1);
       b2=new Button(\"insert\");bottom.add(b2);
       b3=new Button(\"delete\");bottom.add(b3);
       b4=new Button(\"clear\");bottom.add(b4);
       add(bottom,\"South\");
       top=new Panel();
       top.setBackground(Color.cyan);
l1=new Label(\"ID\");top.add(l1);
tf1=new TextField(10);top.add(tf1);
l2=new Label(\"LastName\");top.add(l2);
tf2=new TextField(20);top.add(tf2);
l3=new Label(\"FirstName\");top.add(l3);
tf3=new TextField(20);top.add(tf3);
l4=new Label(\"MI\");top.add(l4);
tf4=new TextField(10);top.add(tf4);
l5=new Label(\"Address\");top.add(l5);
ta1=new TextArea(5,10);top.add(ta1);
l6=new Label(\"City\");top.add(l6);
tf5=new TextField(20);top.add(tf5);
l7=new Label(\"State\");top.add(l7);
tf6=new TextField(20);top.add(tf6);
l8=new Label(\"Telephone\");top.add(l8);
tf7=new TextField(10);top.add(tf7);
l9=new Label(\"EmailID\");top.add(l9);
tf8=new TextField(30);top.add(tf8);
add(top,\"Center\");
   validate();
b1.addActionListener(this);
b2.addActionListener(this);
b3.addActionListener(this);
b4.addActionListener(this);
   }
public void actionPerformed(ActionEvent ae)
   {
   try{
       Class.forName(\"oracle.jdbc.OracleDriver\");//type-4 driver for oracle
con=DriverManager.getConnection(\"jdbc:oracle:thin:1521:@xe\",\"system\",\"system\");//URL,USerName,Password
String str=ae.getActionCommand();
if(str.equals(\"clear\"))//clear operation
       {
   tf1.setText(\"\");
   tf2.setText(\"\");
   tf3.setText(\"\");
   tf4.setText(\"\");
   tf5.setText(\"\");
   tf6.setText(\"\");
   tf7.setText(\"\");
   ta1.setText(\"\");
       }
else if(str.equals(\"insert\"))//insert operation
       {
   String id=tf1.getText();
   String lname=tf2.getText();
   String fname=tf3.getText();
   String mi=tf4.getText();
   String address=ta1.getText();
   String city=tf5.getText();
   String state=tf6.getText();
   String phone=tf7.getText();
   String email=tf8.getText();
PreparedStatement pst=con.prepareStatement(\"insert into staff values(?,?,?,?,?,?,?,?,?)\");
pst.setString(1,id);
pst.setString(2,lname);
pst.setString(3,fname);
pst.setString(4,mi);
pst.setString(5,address);
pst.setString(6,city);
pst.setString(7,state);
pst.setString(8,phone);
pst.setString(9,email);
int i=pst.executeUpdate();
if(i>0){
   JOptionPane.showMessageDialog(null,\"Values Inserted\");
tf1.setText(\"\");
   tf2.setText(\"\");
   tf3.setText(\"\");
   tf4.setText(\"\");
   tf5.setText(\"\");
   tf6.setText(\"\");
   tf7.setText(\"\");
   ta1.setText(\"\");
}
else{
           JOptionPane.showMessageDialog(null,\"something went worg,Try again\");
}
}//end of else if

else if(str.equals(\"update\"))//update operation
       {
   String id=tf1.getText();
   String lname=tf2.getText();
   String fname=tf3.getText();
   String mi=tf4.getText();
   String address=ta1.getText();
   String city=tf5.getText();
   String state=tf6.getText();
   String phone=tf7.getText();
   String email=tf8.getText();
PreparedStatement pst=con.prepareStatement(\"update staff set lastName=?,firstName=?,mi=?,address=?,city=?,state=?,telephone=?,email=? where id=?\");
pst.setString(9,id);
pst.setString(1,lname);
pst.setString(2,fname);
pst.setString(3,mi);
pst.setString(4,address);
pst.setString(5,city);
pst.setString(6,state);
pst.setString(7,phone);
pst.setString(8,email);
int i=pst.executeUpdate();
if(i>0){
   JOptionPane.showMessageDialog(null,\"Values are Updated\");
tf1.setText(\"\");
   tf2.setText(\"\");
   tf3.setText(\"\");
   tf4.setText(\"\");
   tf5.setText(\"\");
   tf6.setText(\"\");
   tf7.setText(\"\");
   ta1.setText(\"\");
}
else{
           JOptionPane.showMessageDialog(null,\"something went worg,Try again\");
}
}//end of else if

else if(str.equals(\"view\"))//for view operation
       {
String id=tf1.getText();
Statement st=con.createStatement();
ResultSet rs=st.executeQuery(\"select * from staff where id=\'\"+id+\"\'\");
if(rs.next()){
JOptionPane.showMessageDialog(null,\"Records Found\");
tf1.setText(rs.getString(1));
tf2.setText(rs.getString(2));
tf3.setText(rs.getString(3));
tf4.setText(rs.getString(4));
ta1.setText(rs.getString(5));
tf5.setText(rs.getString(6));
tf6.setText(rs.getString(7));
tf7.setText(rs.getString(8));
tf8.setText(rs.getString(9));
}
else
           {
JOptionPane.showMessageDialog(null,\"Records Not Found,Enter Correct Id\");
           }
       }//end of else if

   }catch(Exception e){
   e.printStackTrace();
   }
   }
   public static void main(String args[])
   {
       new Assignment6DB();
   }
}