This program is implemented by Sahil from ABESIT (Ghaziabad)
Note: Before using this code please change database name and your machine password in url (which is given inside getConnection() method)
import java.sql.*;
import java.util.Scanner;
public class InsertandfetchDB {
public static void main(String[] args) throws Exception{
Class.forName("com.mysql.jdbc.Driver");
Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/cetpa","root","root");
Statement st=conn.createStatement();
boolean loop2=true;
while(loop2)
{
System.out.println("MENU");
System.out.println("Press 1 to add employee details ");
System.out.println("Press 2 to find employee ");
System.out.println("Press 3 to exit ");
Scanner sc=new Scanner(System.in);
Scanner sc1=new Scanner(System.in);
int choice =sc.nextInt();
switch(choice){
case 1:
System.out.println("enter emp id :");
int i=sc.nextInt();
System.out.println("enter emp name:");
String name=sc1.nextLine();
System.out.println("enter emp designation:");
String des=sc1.nextLine();
System.out.println("enter emp salary:");
float sal=sc.nextFloat();
System.out.println("enter emp city:");
String city=sc1.nextLine();
String sql="insert into employee values('"+i+"','"+name+"','"+des+"','"+sal+"','"+city+"')";
int r=st.executeUpdate(sql);
System.out.println(r+" records inserted");
break;
case 2:
boolean loop=true;
while(loop)
{
System.out.println("SUB MENU");
System.out.println("Press 1 to display all records ");
System.out.println("Press 2 to find by employee ID ");
System.out.println("Press 3 to find by Name ");
System.out.println("Press 4 to find by Designation");
System.out.println("Press 5 to find by Salary ");
System.out.println("Press 6 to find by City ");
System.out.println("Press 7 to find by Designation and City");
System.out.println("Press 8 to find by Designation and City and Salary");
System.out.println("Press 9 to exit ");
int select=sc.nextInt();
switch(select)
{
case 1:
sql="select * from employee";
ResultSet rs=st.executeQuery(sql);
while(rs.next())
{
i=rs.getInt("empid");
name=rs.getString("empname");
des=rs.getString("empdesignation");
sal=rs.getFloat("empsalary");
city=rs.getString("empcity");
System.out.println("ID :"+i);
System.out.println("Name :"+name);
System.out.println("Designation :"+des);
System.out.println("Salary :"+sal);
System.out.println("City :"+city);
System.out.println("----------------------------------------------------------------------");
}
break;
case 2:
System.out.println("Enter emp ID :");
int id=sc.nextInt();
sql="select * from employee";
rs=st.executeQuery(sql);
while(rs.next())
{
i=rs.getInt("empid");
name=rs.getString("empname");
des=rs.getString("empdesignation");
sal=rs.getFloat("empsalary");
city=rs.getString("empcity");
if(i==id)
{
System.out.println("ID :"+i);
System.out.println("Name :"+name);
System.out.println("Designation :"+des);
System.out.println("Salary :"+sal);
System.out.println("City :"+city);
System.out.println("----------------------------------------------------------------------");
}
}
break;
case 3 :
System.out.println("Enter emp Name :");
String n=sc1.nextLine();
sql="select * from employee";
rs=st.executeQuery(sql);
while(rs.next())
{
i=rs.getInt("empid");
name=rs.getString("empname");
des=rs.getString("empdesignation");
sal=rs.getFloat("empsalary");
city=rs.getString("empcity");
if(name.equalsIgnoreCase(n))
{
System.out.println("ID :"+i);
System.out.println("Name :"+name);
System.out.println("Designation :"+des);
System.out.println("Salary :"+sal);
System.out.println("City :"+city);
System.out.println("----------------------------------------------------------------------");
}
}
break;
case 4:
System.out.println("Enter emp Designation :");
String d=sc1.nextLine();
sql="select * from employee";
rs=st.executeQuery(sql);
while(rs.next())
{
i=rs.getInt("empid");
name=rs.getString("empname");
des=rs.getString("empdesignation");
sal=rs.getFloat("empsalary");
city=rs.getString("empcity");
if(des.equalsIgnoreCase(d))
{
System.out.println("ID :"+i);
System.out.println("Name :"+name);
System.out.println("Designation :"+des);
System.out.println("Salary :"+sal);
System.out.println("City :"+city);
System.out.println("----------------------------------------------------------------------");
}
}
break;
case 5:
System.out.println("Enter the range of salary :");
System.out.println("Starting from :");
float s=sc.nextFloat();
System.out.println("To :");
float e=sc.nextFloat();
sql="select * from employee";
rs=st.executeQuery(sql);
while(rs.next())
{
i=rs.getInt("empid");
name=rs.getString("empname");
des=rs.getString("empdesignation");
sal=rs.getFloat("empsalary");
city=rs.getString("empcity");
if(sal>=s && sal<=e)
{
System.out.println("ID :"+i);
System.out.println("Name :"+name);
System.out.println("Designation :"+des);
System.out.println("Salary :"+sal);
System.out.println("City :"+city);
System.out.println("----------------------------------------------------------------------");
}
}
break;
case 6:
System.out.println("Enter emp City :");
String c=sc1.nextLine();
sql="select * from employee";
rs=st.executeQuery(sql);
while(rs.next())
{
i=rs.getInt("empid");
name=rs.getString("empname");
des=rs.getString("empdesignation");
sal=rs.getFloat("empsalary");
city=rs.getString("empcity");
if(city.equalsIgnoreCase(c))
{
System.out.println("ID :"+i);
System.out.println("Name :"+name);
System.out.println("Designation :"+des);
System.out.println("Salary :"+sal);
System.out.println("City :"+city);
System.out.println("----------------------------------------------------------------------");
}
}
break;
case 7:
System.out.println("Enter emp Designation :");
d=sc1.nextLine();
System.out.println("Enter emp City :");
c=sc1.nextLine();
sql="select * from employee";
rs=st.executeQuery(sql);
while(rs.next())
{
i=rs.getInt("empid");
name=rs.getString("empname");
des=rs.getString("empdesignation");
sal=rs.getFloat("empsalary");
city=rs.getString("empcity");
if(des.equalsIgnoreCase(d) && city.equalsIgnoreCase(c))
{
System.out.println("ID :"+i);
System.out.println("Name :"+name);
System.out.println("Designation :"+des);
System.out.println("Salary :"+sal);
System.out.println("City :"+city);
System.out.println("----------------------------------------------------------------------");
}
}
break;
case 8:
System.out.println("Enter emp Designation :");
d=sc1.nextLine();
System.out.println("Enter emp City :");
c=sc1.nextLine();
System.out.println("Enter the range of salary :");
System.out.println("Starting from :");
s=sc.nextFloat();
System.out.println("To :");
e=sc.nextFloat();
sql="select * from employee";
rs=st.executeQuery(sql);
while(rs.next())
{
i=rs.getInt("empid");
name=rs.getString("empname");
des=rs.getString("empdesignation");
sal=rs.getFloat("empsalary");
city=rs.getString("empcity");
if(des.equalsIgnoreCase(d) && city.equalsIgnoreCase(c) &&(sal>=s && sal<=e))
{
System.out.println("ID :"+i);
System.out.println("Name :"+name);
System.out.println("Designation :"+des);
System.out.println("Salary :"+sal);
System.out.println("City :"+city);
System.out.println("----------------------------------------------------------------------");
}
}
break;
case 9:
loop=false;
break;
default :
System.out.println("INVALID OPTION");
}
}
case 3:
loop2=false;
break;
default :
System.out.println("INVALID OPTION");
}
}
}
}
No comments:
Post a Comment