JDBC APPLICATION FOR EMPLOYEE DATA SOURCE
AIM:
To write a program to manipulate the employee database.
PROCEDURE:
1.CODE WINDOW:
Step 1: Start IBM Websphere Application by selecting Start à All Programs à IBM
webSphere Studio à Application Developer 5.0.
Step 2: A dialog box appear in that enter the directory for that session and click OK.
Step 3: A window appear in that select Fileà Newà Project.
Step 4: In the new project dialog box select Java from the left pane and Java project from
the right Pane and then click next button.
Step 5: Enter a project name and click finish .
Step 6: Click on the new class Icon.
Step 7:In the new dialog box and give a name to the class as student and select the modifier
as public and super class as import java.sql.*.
Step 8: Select the type of stub as public static void main(String args[]) and click ok
Step 9:A skeleton of the java program is appear in the window .Type the program in that window.
2.JDBC CONNECTIVITY:
Step 1:Start .
Step 2: Declare the necessary variables.
Step 3: Register the driver using the following statement.
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Step 4:Create a connection using the following statement.
Connection con=DriverManager.getConnection("jdbc:odbc:dsnname",”userid”,”password”);
Step 5:STATIC SQL
The code for execute the static query is given below
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery( SQL);
Step 6:DYNAMIC SQL
The code for execute dynamic sql query is given below
PreparedStatement ps=con.prepareStatement(SQL);
count=ps.executeUpdate() ;
Step 7 : Display the choices as follows
1.Insert
2.Delete
3.Update
4.Search
5.Other queries
Step 8: Get the choice from the user.
Step 9: Using switch case perform their corresponding operations, by using the following queries.
i.For Insertion the code is given below
PreparedStatement ps=con.prepareStatement("Insert into emp values(" + id +",' " + name + "',"+ age + ",' " + desig +"'," + hra +","+ da +"," + bp +","+ it +","+sal+")");
count=ps.executeUpdate() ;
ii .For Deletion the code is given below
PreparedStatement ps=con.prepareStatement("delete from emp where id= "+ id +"");
count=ps.executeUpdate() ;
iii.For Updation the coding is given below
PreparedStatement ps=con.prepareStatement("update emp set id=" + id +",name=' " + name + "',age="+ age + ",designation=' " + desig +"',hra=" + hra +",da="+ da +",basic_salary=" + bp +",it="+ it +",salary="+sal+" where id = "+ id1 +"");
count=ps.executeUpdate() ;
iv.For searching the coding is given below
ResultSet rs=stmt.executeQuery( "Select * from emp where id ="+id+"");
v.For other queries the coding is given below
boolean b =stmt.execute(""+ str1+"");
3.EXECUTION WINDOW:
Step 1:Select projectà Rebuild all.
Step 2:Select Runà Run asà java Application.
Step 3:Enter the input in the console.
Step 4:Output is displayed in the console window.
PROGRAM:
import java.io.*;
import java.sql.*;
/**
* @author students
*
* To change this generated comment edit the template variable "typecomment":
* Window>Preferences>Java>Templates.
* To enable and disable the creation of type comments go to
* Window>Preferences>Java>Code Generation.
*/
public class employee
{
public static void main(String[] args)throws Exception
{
int ch,ch1,id,age,count,no1,ch2,id1;
float hra,da,bp,sal,it;
String name,add,str,phone,gender,str1,c,desig;
do
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:employee");
Statement stmt=con.createStatement();
System.out.println("1.Insert");
System.out.println("2.Delete");
System.out.println("3.Edit");
System.out.println("4.Search");
System.out.println("5.Execute other query");
System.out.println("Enter the choice ");
BufferedReader d1=new BufferedReader(new InputStreamReader(System.in));
str=d1.readLine();
ch=Integer.parseInt(str);
switch(ch)
{
case 1:
{
System.out.println("Enter the employee id ");
str=d1.readLine();
id=Integer.parseInt(str);
System.out.println("Enter the Employee Name ");
name=d1.readLine();
System.out.println("Enter the Age ");
str=d1.readLine();
age=Integer.parseInt(str);
System.out.println("Enter the designation ");
desig=d1.readLine();
System.out.println("Enter the HRA ");
str=d1.readLine();
hra=Float.parseFloat(str);
System.out.println("Enter the DA ");
str=d1.readLine();
da=Float.parseFloat(str);
System.out.println("Enter the Basic pay ");
str=d1.readLine();
bp=Float.parseFloat(str);
System.out.println("Enter the Incom tax");
str=d1.readLine();
it=Float.parseFloat(str);
System.out.println("Enter the Salary ");
str=d1.readLine();
sal=Float.parseFloat(str);
PreparedStatement ps=con.prepareStatement("Insert into emp values(" + id +",' " + name + "',"+ age + ",' " + desig +"'," + hra +","+ da +"," + bp +","+ it +","+sal+")");
count=ps.executeUpdate() ;
System.out.println(count +" record inserted successfully");
ps.close();
break;
}
case 2:
{
System.out.println("Enter the employee id to delete");
str=d1.readLine();
id=Integer.parseInt(str);
PreparedStatement ps=con.prepareStatement("delete from emp where id= "+ id +"");
count=ps.executeUpdate() ;
System.out.println(count +" record deleted successfully");
break;
}
case 3:
{
System.out.println("Enter the employee ID to update");
str=d1.readLine();
id1=Integer.parseInt(str);
System.out.println("Enter the employee id ");
str=d1.readLine();
id=Integer.parseInt(str);
System.out.println("Enter the Employee Name ");
name=d1.readLine();
System.out.println("Enter the Age ");
str=d1.readLine();
age=Integer.parseInt(str);
System.out.println("Enter the designation ");
desig=d1.readLine();
System.out.println("Enter the HRA ");
str=d1.readLine();
hra=Float.parseFloat(str);
System.out.println("Enter the DA ");
str=d1.readLine();
da=Float.parseFloat(str);
System.out.println("Enter the Basic pay ");
str=d1.readLine();
bp=Float.parseFloat(str);
System.out.println("Enter the Incom tax");
str=d1.readLine();
it=Float.parseFloat(str);
System.out.println("Enter the Salary ");
str=d1.readLine();
sal=Float.parseFloat(str);
System.out.println("update emp set id=" + id +",name=' " + name + "',age="+ age + ",designation=' " + desig +"',hra=" + hra +",da="+ da +",basic_salary=" + bp +",it="+ it +",salary="+sal+" where id = "+ id1 +"");
PreparedStatement ps=con.prepareStatement("update emp set id=" + id +",name=' " + name + "',age="+ age + ",designation=' " + desig +"',hra=" + hra +",da="+ da +",basic_salary=" + bp +",it="+ it +",salary="+sal+" where id = "+ id1 +"");
count=ps.executeUpdate() ;
System.out.println(count +" record updated successfully");
break;
}
case 4:
{
System.out.println("1.Search by employee ID ");
System.out.println("2.Search by name");
System.out.println("Enter your choice");
str=d1.readLine();
int ch5=Integer.parseInt(str);
if(ch5==1)
{
System.out.println("enter the employee ID to search");
str=d1.readLine();
id=Integer.parseInt(str);
ResultSet rs=stmt.executeQuery( "Select * from emp where id ="+id+"");
while(rs.next())
{
System.out.println("Employee ID :"+rs.getInt(1));
System.out.println("Name :"+rs.getString(2));
System.out.println("Age :"+rs.getInt(3));
System.out.println("Designation :"+rs.getString(4));
System.out.println("HRA :"+rs.getFloat(5));
System.out.println("DA :"+rs.getFloat(6));
System.out.println("Basic Salary :"+rs.getFloat(7));
System.out.println("IT :"+rs.getFloat(8));
System.out.println("Salary :"+rs.getFloat(9));
}
}
else
{
System.out.println("enter the name to search");
str1=d1.readLine();
ResultSet rs1=stmt.executeQuery( "Select * from emp where name ='"+ str1 +"'");
while(rs1.next())
{
System.out.println("Employee ID :"+rs1.getInt(1));
System.out.println("Name :"+rs1.getString(2));
System.out.println("Age :"+rs1.getInt(3));
System.out.println("Designation :"+rs1.getString(4));
System.out.println("HRA :"+rs1.getFloat(5));
System.out.println("DA :"+rs1.getFloat(6));
System.out.println("Basic Salary :"+rs1.getFloat(7));
System.out.println("IT :"+rs1.getFloat(8));
System.out.println("Salary :"+rs1.getFloat(9));
}
}
break;
}
case 5:
{
System.out.println("Enter the query");
str1=d1.readLine();
try
{
boolean b =stmt.execute(""+ str1+"");
if(b==true)
System.out.println("The query is executed successfully");
}
catch(SQLException e)
{
System.out.println("The query is not executed successfully");
}
break;
}
}
stmt.close();
con.close();
System.out.println("Do you want to continue");
c=d1.readLine();
ch2=Integer.parseInt(c);
}while(ch2==1);
}
}
0 comments:
Post a Comment