Menu driven Java JDBC program with PL/SQL

-

Create a Menu driven program in JDBC (USE appropriate statement, preparestatement or callable statement object as an when require)

Requirements:

  • Display all the Records from the both tables by employee_number
  • Insert Record into the Table. (Hint:- create stored procedure insertdata() in PL/SQL to insert all the field in both tables. Call the procedure from java application using callablestatement)
  • Update The existing record by asking the useremployee_number) (HINT:- Take Updable Resultset)
  • Delete Record by asking the user employee_number)) (HINT:- Take Updable Resultset) Program should handle SQLException and SqlWarning.

Code:

package aj.pkg13;

import com.sun.swing.internal.plaf.basic.resources.basic;

import java.io.File;

import java.io.FileInputStream;

import java.io.InputStream;

import java.sql.*;

import java.text.DateFormat;

import java.text.SimpleDateFormat;

import java.util.HashSet;

import java.util.Scanner;



public class AJ13 {

public static void main(String[] args) {

Scanner sc = new Scanner(System.in);

try{

Class.forName("com.mysql.jdbc.Driver");

Connection con=DriverManager.getConnection("jdbc:mysql://localhost:3306/student","root","");



int opt=0;

System.out.println("Employee Record");

System.out.print("1.Display Record, ");

System.out.print("2.Insert a record, ");

System.out.print("3.Update record, " );

System.out.print("4.Delete an record, ");

System.out.println("5.Quit, ");

System.out.println("Enter your choice: ");

opt=Integer.parseInt(sc.nextLine());

switch(opt){

case 1:

{

System.out.println("Enter ID of the employee:");

int tempid=Integer.parseInt(sc.nextLine());

String sql = "select employee_detail.employee_number,employee_detail.employee_name,employee_detail.department_id,employee_detail.department_name,\n" +

"emp_personal_detail.emp_designation,emp_personal_detail.basic_salary,emp_personal_detail.allowance,employee_detail.Salary,\n" +

"emp_personal_detail.hiredate,emp_personal_detail.phone_number,emp_personal_detail.email,emp_personal_detail.emp_address from employee_detail INNER JOIN emp_personal_detail on employee_detail.employee_number=emp_personal_detail.employee_number\n" +

"where employee_detail.employee_number="+tempid+";";

PreparedStatement ps = con.prepareStatement(sql);

ResultSet rs = ps.executeQuery(sql);

if (!rs.next()) {

System.out.println("No data found!");

}else

{

System.out.println("Employee Number: "+rs.getString(1));

System.out.println("Employee Name: "+rs.getString(2));

System.out.println("Department ID: "+rs.getString(3));

System.out.println("Department Name: "+rs.getString(4));

System.out.println("Designation: "+rs.getString(5));

System.out.println("Basic Salary: "+rs.getString(6));

System.out.println("Allowence: "+rs.getString(7));

System.out.println("Total Salary: "+rs.getString(8));

System.out.println("Hire Date: "+rs.getString(9));

System.out.println("Phone Number: "+rs.getString(10));

System.out.println("Email: "+rs.getString(11));

System.out.println("Address: "+rs.getString(12));

}

con.close();

break;

}



case 2:

{

System.out.println("Enter ID Of the new Employee");

int tempid=Integer.parseInt(sc.nextLine());

String sql = "select * from employee_detail where employee_number="+tempid+";";

System.out.println("Enter Name:");

String tname = sc.nextLine();

System.out.println("Department ID(Numeric):");

int tdeptid=Integer.parseInt(sc.nextLine());

System.out.println("Department Name:");

String tdeptname=sc.nextLine();

System.out.println("Designation:");

String tdesignation=sc.nextLine();

System.out.println("Basic salary:");

int tbasicsalary=Integer.parseInt(sc.nextLine());

System.out.println("allowence:");

int tallowance=Integer.parseInt(sc.nextLine());

int tsalary=tbasicsalary+tallowance;

System.out.println("Hire Date(YYYY-MM-DD):");

String thiredate=sc.nextLine();

SimpleDateFormat sdf1 = new SimpleDateFormat("YYYY-MM-DD");

java.util.Date date = sdf1.parse(thiredate);

java.sql.Date sqlStartDate = new java.sql.Date(date.getTime());

System.out.println("Phone Number:");

String tphone=sc.nextLine();

System.out.println("Email:");

String temail=sc.nextLine();

System.out.println("Path of photo:");

String tphoto=sc.nextLine();

String filePath = tphoto;

InputStream inputStream = new FileInputStream(new File(filePath));



byte[] b1 = null;



System.out.println("Address:");

String taddress=sc.nextLine();

System.out.println("Path of Resume:");

String tresume=sc.nextLine();

String filePath1 = tresume;

InputStream inputStream1 = null;//new FileInputStream(new File(filePath1));

CallableStatement cstm = con.prepareCall("{Call ins_data(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");

cstm.setInt(1,tempid);

cstm.setString(2,tname);

cstm.setInt(3,tdeptid);

cstm.setInt(4,tsalary);

cstm.setString(5,tdeptname);

cstm.setString(6,tdesignation);

cstm.setInt(7,tbasicsalary);

cstm.setInt(8,tallowance);

cstm.setDate(9,sqlStartDate);

cstm.setString(10,tphone);

cstm.setString(11,temail);

cstm.setBlob(12, inputStream);

cstm.setBlob(13, inputStream1);

cstm.setString(14,taddress);

int b = cstm.executeUpdate();

System.out.println(b);

if(b==1){

System.out.println("INSERTION SUCCESSFUL");

}

else

{

System.out.println("INSERTION FAILED");

}

con.close();

break;

}



case 3:{

System.out.println("Enter ID Of the Employee");

int tempid=Integer.parseInt(sc.nextLine());

String sql = "select * from employee_detail where employee_number="+tempid+";";

PreparedStatement ps = con.prepareStatement(sql);

ResultSet rs = ps.executeQuery(sql);

if (!rs.next()) {

System.out.println("NO Employee Found");

}

else{



System.out.println("New Basic salary:");

int tbasicsalary=Integer.parseInt(sc.nextLine());

System.out.println("New allowence:");

int tallowence=Integer.parseInt(sc.nextLine());

PreparedStatement stmt=con.prepareStatement("UPDATE emp_personal_detail set basic_salary=?,allowance=? WHERE employee_number=?;");

stmt.setInt(1,tbasicsalary);

stmt.setInt(2,tallowence);

stmt.setInt(3,tempid);

if (stmt.executeUpdate() == 1 ) {

stmt=con.prepareStatement("UPDATE employee_detail SET Salary=? where employee_number=?;");

stmt.setInt(1,(tbasicsalary+tallowence));

stmt.setInt(2,tempid);

if (stmt.executeUpdate() == 1 ) {

System.out.println("Insertion Successful!");

}

else{

System.out.println("Insertion could not be done!");

}

}

else{

System.out.println("Insertion could not be done!");

}

}

con.close();

break;

}



case 4:System.out.println("Enter ID Of the Employee");

int tempid=Integer.parseInt(sc.nextLine());

String sql = "select * from employee_detail where employee_number="+tempid+";";

PreparedStatement ps = con.prepareStatement(sql);

ResultSet rs = ps.executeQuery(sql);

if (!rs.next()) {

System.out.println("NO Employee Found");

}

else

{

PreparedStatement stmt=con.prepareStatement("delete from employee_detail where employee_number=?;");

stmt.setInt(1,tempid);

if (stmt.executeUpdate() == 1 ) {

stmt=con.prepareStatement("delete from emp_personal_detail where employee_number=?;");

stmt.setInt(1,tempid);

if (stmt.executeUpdate() == 1 ) {System.out.println("Deletation Successful");}



}else{System.out.println("Deletation Could not be done");}



}



con.close();

break;

}

}catch(Exception e){ System.out.println(e);}

}

}

Procedure for Database:

DELIMITER //

CREATE PROCEDURE ins_data

(IN eno decimal(10,0),IN ename CHAR(50),IN did decimal(10,0),IN salary decimal(6,0),IN dname char(50),IN edes CHAR(20),IN bs decimal(10,0),IN allowance decimal(10,0),IN hired date,IN pno char(20),IN emlid char(25), IN pic longblob,IN res longblob,IN addr char(100))

BEGIN

INSERT into employee_detail(employee_number,employee_name,department_id,Salary,department_name) values (eno,ename,did,salary,dname)

INSERT into emp_personal_detail (employee_number,emp_designation,basic_salary,allowance,hiredate,phone_number,email,photo,resume,emp_address)values (eno,edes,bs,allowance,hired,pno,emlid,pic,res,addr)

END //

DELIMITER ;

Comments

Post a Comment

Popular posts from this blog

C program to evaluate Prefix Expression using Stack data structure

-
Simple C program to evaluate Prefix Expression using Stack data structure Code: #include<stdio.h> #include<conio.h> #include<string.h> int steck[10]; int top=-1; void push(int value) { steck[top++]=value; } int pop() { return(steck[top--]); } int ope(char c) { if(c=='+'||c=='-'||c=='*'||c=='/') return 1; else return 0; } void main() { char prefix[10]; int len,val,i,opr1,opr2,res; clrscr(); printf("Enter the prefix Expression: "); scanf("%s",prefix); len=strlen(prefix); for(i=len-1;i>=0;i--) { switch(ope(prefix[i])) { case 0: val=prefix[i]; push(val); break; case 1: opr1=pop(); opr2=pop(); switch(prefix[i]) { case '+': res=opr1+opr2; break; case '-': res=opr1-opr2; break; case '*': res=opr1*opr2; break; case '/': res=opr1/opr2; break; } push(res); } } printf("Result is %d",steck[top]); getch(); }
Read more

Java Program to Implement sorting algorithm using TCP on Server application

-
Simple Java program to Implement sorting algorithm using TCP on Server application Input: Give input on client side and client should receive sorted output from server and display sorted list on input(client) side. Note: You can implement any sorting algorithm by changing the sorting algorithm code on server side Code: Server: package aj.pkg7; import java.io.*; import java.net.*; import java.util.*; class Server { public static void main(String args[]) throws Exception { ServerSocket ss=new ServerSocket(1111); Socket s=ss.accept(); DataInputStream din=new DataInputStream(s.getInputStream()); DataOutputStream dout=new DataOutputStream(s.getOutputStream()); int r,i=0; int n=din.readInt(); int a[]=new int[n]; System.out.println("data:"); int count=0; for(i=0;i<n;i++) { a[i]=din.readInt(); } int number = a.length; for (int i1 = 0; i1 < number-1; i1++) for (int j1 = 0; j1 < number-i1-1; j1++) if (a[j1] > a[j1+1]) { int temp = a[j1]; a[j1] = a[j1+1]; a[j1+1] = temp; } ...
Read more

C++ program to perform data transformation Min-max and Z score Normalization

-
Simple C++ program to perform data transformation using min-max normalization, z-score normalization and decimal scaling operation. Code: #include<iostream> #include<math.h> using namespace std; int main() { int n; double sum=0; cout<<"Enter The Number of values:"; cin>>n; int a[n],r1,r2; for(int i=0;i<n;i++) { cin>>a[i]; } cout<<"Enter starting and ending range for normalization:"; cin>>r1>>r2; double max = a[0]; int min = a[0]; for (int i = 0; i <n; i++) { if (a[i] > max) { max = a[i]; } else if (a[i] < min) { min = a[i]; } } float v[n];double temp; for(int i=0;i<n;i++) { temp=double(double(double(a[i]-min)*(r2-r1))/(max-min))+ r1; v[i]=temp; } cout<<"normalization by Min-max"<<endl; cout<<"Values a...
Read more