APPLICATION – EMPLOYEE INFORMATION SYSTEM

Ex No :11 b
Date:

APPLICATION – EMPLOYEE INFORMATION SYSTEM

import javax.swing.JOptionPane;
import java.sql.*;
public class JDBCProgram {

static String userid="me0817",password="abcd";
static String url="jdbc:odbc:csoracle";
static Statement stmt;
static PreparedStatement pstmt;
static Connection con;

public static void main(String args[]) {
JOptionPane.showMessageDialog(null,"JDBC Programming showing Updation of Table Data");
int choice=-1;
do {
choice=getChoice();
if(choice!=0) {
getSelected(choice);
  }
              }
while(choice!=0);
System.exit(0);
}

public static int getChoice()
{
String choice;
int ch;
choice=JOptionPane.showInputDialog(null,
           "1. Create Employees Table\n"+
"2. Create Products Table\n"+
"3. Insert data into Employees Table\n"+
"4. Insert data into Orders table\n"+
"5. Retrieve data for employees table\n"+
"6. Retrieve data for orders table\n"+
"7. Update employees table\n"+
"8. Update employees table usinga prepared statement\n"+
"9. Update many records of orders table using a prepared statement\n"+
"10. List the name of employees who bought CD's\n"+
           "11. Delete data from Employees Table\n"+
"0. Exit\n\n"+"Enter your choice");

ch=Integer.parseInt(choice);
return ch;
}

public static void getSelected(int choice) {
if(choice==1) {
createEmployees();
}
if(choice==2) {
createOrders();
}
if(choice==3)
{
insertEmployees();
}
if(choice==4)
{
insertOrders();
}
if(choice==5)
{
retrieveEmployees();
}
if(choice==6)
{
retrieveOrders();
}
if(choice==7)
{
updateEmployees();
}
if(choice==8)
{
updateEmployeesPrepared();
}
if(choice==9)
{
updateOrdersPrepared();
}
if(choice==10)
{
dynamicQuery();
}
if(choice==11)
{
deleteEmployees();
}
}

public static Connection getConnection()
{
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
}
catch(java.lang.ClassNotFoundException e) {
System.err.print("ClassNotFoundException:");
System.err.print(e.getMessage());
}
try {
con=DriverManager.getConnection(url,userid,password);
} catch(SQLException ex) {
System.err.println("SQLException:"+ex.getMessage());
}
return con;
}

public static void createEmployees()
{
Connection con=getConnection();
String createString;
String deleteString;
//deleteString="delete from Employees";
createString="create table Employees("+"Employees_ID integer,"+ "Name varchar(30))";
try {
stmt=con.createStatement();
stmt.executeUpdate(createString);
stmt.close();
con.close();
} catch(SQLException ex) {
System.err.println("SQLException:"+ex.getMessage());
}
JOptionPane.showMessageDialog(null,"Employees table Created");
}

/*create table orders(prod_id integer, productname varchar2(20),employee_id integer);*/

public static void createOrders()
{
Connection con=getConnection();
String createString;
createString="create table Orders("+"Prod_ID integer," + "ProductName varchar(20),"+"Employee_ID integer)";
try{
stmt=con.createStatement();
stmt.executeUpdate(createString);
stmt.close();
             con.close();
            }
            catch(SQLException e)
  {   System.err.println("SQLException:"+e.getMessage());
            }
   JOptionPane.showMessageDialog(null,"Orders Table Created");
}

public static void insertEmployees()
{
    Connection con=getConnection();
    String insertString1,insertString2,insertString3,insertString4;
    insertString1 = "insert into Employees values(6323,'Hemanth')";
    insertString2 = "insert into Employees values(5768,'bob')";
    insertString3 = "insert into Employees values(1234,'shawn')";
    insertString4 = "insert into Employees values(5678,'Michaels')";
   try{
  stmt=con.createStatement();
 stmt.executeUpdate(insertString1);
 stmt.executeUpdate(insertString2);
 stmt.executeUpdate(insertString3);
 stmt.executeUpdate(insertString4);
   stmt.close();
             con.close();
      }catch(SQLException ex){
          System.err.println("SQLException:"+ex.getMessage());
        }
   JOptionPane.showMessageDialog(null,"Data inserted into Employees Table");
}

public static void insertOrders()
{
    Connection con=getConnection();
    String insertString1,insertString2,insertString3,insertString4;
    insertString1 = "insert into orders values(543,'Belt',6323)";
    insertString2 = "insert into orders values(432,'Bottle',1234)";
    insertString3 = "insert into orders values(876,'Ring',5678)";

   try{
  stmt=con.createStatement();
 stmt.executeUpdate(insertString1);
 stmt.executeUpdate(insertString2);
 stmt.executeUpdate(insertString3);
   stmt.close();
             con.close();
      }catch(SQLException ex){
        System.err.println("SQLException:"+ex.getMessage());
        JOptionPane.showMessageDialog(null,"Data inserted into Orders Table");

}
public static void retrieveEmployees()
{
    Connection con=getConnection();
    String result=null;
    String selectString;
    selectString="select * from Employees";
    result="Employee_ID\t\tName\n";
 try{
stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(selectString);

while(rs.next()){
int id=rs.getInt(1);
String name=rs.getString(2);
result+=id+"\t\t"+name+"\n";
}
stmt.close();
con.close();
}catch(SQLException ex){
System.err.println("SQLException:"+ex.getMessage());
}
JOptionPane.showMessageDialog(null,result);

}

public static void retrieveOrders(){
Connection con=getConnection();
String result=null;
String selectString;
selectString="select * from Orders";
result="Prod_ID\t\tProductName\t\tEmployee_ID\n";
try
{
stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(selectString);
while(rs.next())
{
int pr_id=rs.getInt(1);
String prodName=rs.getString(2);
int id=rs.getInt(3);
result+=pr_id+"\t\t"+prodName+"\t\t"+id+"\n";
}
stmt.close();
con.close();
}catch(SQLException ex){
System.err.println("SQLException :"+ex.getMessage());
}
JOptionPane.showMessageDialog(null,result);
}

public static void updateEmployees(){
Connection con=getConnection();
String updateString1;
updateString1="update Employees set name='hemanthbalaji' where Employees_ID=6323";
try{
stmt=con.createStatement();
stmt.executeUpdate(updateString1);
       stmt.close();
        con.close();
      }catch(SQLException ex){
          System.err.println("SQLException:"+ex.getMessage());
        }
   JOptionPane.showMessageDialog(null,"Data updated into Employees Table");

}

public static void deleteEmployees(){
Connection con=getConnection();
String updateString1;
updateString1="delete from Employees where Employees_ID=6323";
try{
stmt=con.createStatement();
stmt.executeUpdate(updateString1);
       stmt.close();
        con.close();
      }catch(SQLException ex){
          System.err.println("SQLException:"+ex.getMessage());
        }
   JOptionPane.showMessageDialog(null,"Data deleted from Employees Table");

}

public static void updateEmployeesPrepared()
{
   Connection con = getConnection();
   try{
         pstmt=con.prepareStatement("update Employees set name = ? where Employees_ID = ?");
         pstmt.setString(1,"hemanthbob");
         pstmt.setInt(2,6323);
         pstmt.executeUpdate();
pstmt.close();
         con.close();
      }catch(SQLException ex){
          System.err.println("SQLException:"+ex.getMessage());
        }
   JOptionPane.showMessageDialog(null,"Data updated into Employees Table");

}
   
public static void updateOrdersPrepared()
{
   int[] productIds={543,432,876};
   String[] productNames={"cds","dvds","Espresso"};
   int len=productNames.length;
   Connection con = getConnection();
   try{
pstmt=con.prepareStatement("update orders set productname = ? where Prod_Id = ?");
         for(int i=0;i<len;i++)
         {
            pstmt.setInt(2,productIds[i]);
            pstmt.setString(1,productNames[i]);
            pstmt.executeUpdate();
         }

pstmt.close();
         con.close();
      }catch(SQLException ex){
          System.err.println("SQLException:"+ex.getMessage());
        }
   JOptionPane.showMessageDialog(null,"Data updated into Orders Table");

}
public static void dynamicQuery()
{
Connection con=getConnection();
String result=null;
String selectString;
selectString="select Employees.name from Employees,Orders where productname='cds'"+"and Employees.employee_id=Orders.employee_id";
result="Name\n";
try{
stmt=con.createStatement();
ResultSet rs=stmt.executeQuery(selectString);
while(rs.next()){
String name=rs.getString("Name");
result+=name+"\n";
}
stmt.close();
con.close();
}
catch(SQLException ex)
{
System.err.println("SQLException:" +ex.getMessage());
}
JOptionPane.showMessageDialog(null,result);
}
}