Here is the database requirements: (I have already completed the database design
ID: 3764358 • Letter: H
Question
Here is the database requirements: (I have already completed the database design, E-R diagrams, etc)
1. The company is organized into departments. Each department has a name, an unique number, and a particular employee who manages the department. We keep track of the start date when the employee began managing the dept. A dept may have several locations.
2. A dept controls a number of projects, each of which has a name, unique number, and single location.
3. We store each employee’s name, SSN, address, salary, sex, and DOB. An employee is assigned to one dept but may work on several projects, which are not necessary controlled by the same dept. We keep track of the number of hours per week that an employee works for each project. We also keep track of the direct supervisor of each employee.
4. We want to keep track of the dependents of each employee for insurance purpose. We keep each dependent’s first name sex, DOB, and relationship to the employee.
HERE'S WHERE I NEED ASSISTANCE:
Complete an imbedded SQL example in JAVA that inserts new data to a table. (It does not need to be an existing table from the example above.) and then retrieves (selects) the data just entered. Use a GUI that allows the user to input their selection and shows (returns) the user's selection.
Explanation / Answer
import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.util.Vector;
import javax.swing.*;
import java.io.*;
public class INSERTRETRIEVETABLE
{
Statement state1;
RessultSet rs1;
String idTemp;
Connection c;
JFrame f1;
JPanel newPanel;
JButton insertButton;
JButton RetrieveButton;
public INSERTRETRIEVETABLE()
{
f1=new JFrame();
f1.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
f1.setLayout(new BorderLayout());
newPanel=new JPanel();
newPanel.setLayout(new GridLayout());
insertButton=new JButton("Insert");
insertButton.addActionListener(this);
RetrieveButton=new JButton("Retrieve");
RetrieveButton.addActionListener(this);
newPanel.add(insertButton);
newPanel.add(RetrieveButton);
f1.add(newPanel);
f1.setVisible(true);
}
public void ActionPerformed(ActionEvent at)
{
if(at.getSource()==insertButton)
insertDataToTable();
else if (at.getSource()==insertButton)
RetrieveData();
}
public void RetrieveData() {
JFrame frameRe = new JFrame("RETRIEVING DATA");
frameRe.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frameRe.setLayout(new BorderLayout());
JComboBox select;
JLabel setLabel=new JLabel("Select name");
JButton retButton=new JButton("Submit");
frameRe.add(setLabel);
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
c = DriverManager.getConnection("jdbc:oracle:thin:@mcndesktop07:1521:xe", "sandeep", "welcome");
state1 = c.createStatement();
rs1 = state1.executeQuery("select uNAME from empTable");
Vector v1 = new Vector();
while (rs1.next()) {
idTemp = rs1.getString(1);
v1.add(idTemp);
}
select = new JComboBox(v1);
state1.close();
rs1.close();
} catch (Exception e) {
}
frameRe.add(select);
frameRe.add(retButton);
Label newLabel=new Label();
frameRe.add(newLabel);
retButton.addActionListener(new ActionListener(
public void ActionPerformed(ActionEvent at)
{
String getEMPNAME = (String) c1.getSelectedItem();
String eID = "";
String uNAME = "";
String mob = "";
String uAddress = "";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
c = DriverManager.getConnection("jdbc:oracle:thin:@mcndesktop07:1521:xe", "sandeep", "welcome");
PreparedStatement pst = con.prepareStatement("select * from empTable where UNAME='" + getEMPNAME + "'");
ResultSet rs = pst.executeQuery();
int k1= 0;
if (rs.next()) {
eID = rs.getString("eID");
uNAME = rs.getString("uNAME");
mob = rs.getString("mob");
uAddress = rs.getString("uAddress");
String newString="Emp ID:"+eID+" Name:"+uNAME+" MobileNumber: "+mob+" Address:"+Address;
newLabel.setText(newString);
k1++;
}
if (k1< 1) {
JOptionPane.showMessageDialog(null, "NO DATA", "Error", JOptionPane.ERROR_MESSAGE);
}
if (k1 == 1) {
System.out.println(k1 + " RECORD FOUND");
} else {
System.out.println(k1 + " RECORD FOUND");
}
pst.close();
rs1.close();
} catch (Exception x) {
JOptionPane.showMessageDialog(null, x.getMessage(), "ERROR", JOptionPane.ERROR_MESSAGE);
}
}));
frameRe.setVisible(true);
frameRe.setSize(400, 300);
}
public void insertDataToTable()
{
String eID;
String eNAME;
String mobNUM;
String eADD;
JFrame frameIn=new JFrame("Insertion");
frameIn.setDefaultCloseOpertion(JFrame.EXIT_ON_CLOSE);
frameIn.setLayout(new BorderLayput());
JPanel newPanel=new JPanel();
newPanel.setLayout(new FlowLayout());
JTextField unameText=new JTextField(20);
JTextField uIDText=new JTextField(20);
JTextField uMobileNumber=new JTextField(20);
TextField uAddress=new TextField(20);
JLabel nameLabel=new JLabel("Enter name");
JLabel idLabel=new JLabel("enter id");
JLabel moblabel=new JLabel("Enter mobile number");
JLabel addLabel=new JLabel("Enter address");
ID=uIDText.getText();;
eNAME=uNameText.getText();;
mobNUM=uModileNumber.getText();
eADD=uAddress.getText();
JButton insertButton=new JButton("INSERT");
insertButtton.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent at)
{
String insertQ="INSERT INTO empTable VALUES('"+eID"','"+eNAME+"','"+mobNUM+"','"+eADD+"')";
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
c = DriverManager.getConnection("jdbc:oracle:thin:@mcndesktop07:1521:xe", "sandeep", "welcome");
Statement inStatement=c.createStatement();
inStatement.executeUpdate(insertQ);
c.close();
}
catch(Exception x)
{
x.printStackTrace();
}
}));
newPanel.add(idLabel);
newPanel.add(uIDText);
newPanel.add(nameLabel);
newPanel.add(unameText);
newPanel.add(mobLabel);
newPanel.add(uMobileNumber);
newPanel.add(addLabel);
newPanel.add(uAddress);
newPanel.add(insertButton);
frameIn.add(newPanel);
frameIn.setVisible(true);
}
}