Accessing Database Using Java and MySQL: Part 5

Gopi Chand
Jun 04, 2015
4.4k
0
0
Article

Before reading further, read the previous parts of this articles.

In previous parts, we discussed the three modules separately in which only one person can access the database among admin, employee and customer.

admin user

Now here, all the three modules will work together as one module that provides the facility to choose your status and then proceed further. Unlike modules discussed separately, here only one Java file, Conn.java, will work for the entire module.

The database schema table for admin, employee and customer are as in the following:

Table contentd of admin:

Table content of admin

Table contents of employee:

Table content of employee

Table contents of customer:

Table content of customer

Example:

Now let's see the code example that includes conn.java and NewMain.java.

Conn.java

package mybankdb;
import java.sql.*;
public class conn {
public Connection c() throws Exception {
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bankdb", "root", "toor");
return con;
}
}

NewMain.java

import java.sql.*;
import java.io.*;
import mybankdb.conn;
class Customer{
boolean Login(String acc, String pass){
try{
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
ResultSet rst = stm.executeQuery("select * from customer where accno ='"+acc+"' and password = '"+pass+"'");
if(rst.next())
{
return true;
}
else
{
return false;
}
}
catch(Exception e){
System.out.println("Customer class login method"+e);
return false;
}
}
void display_Customer(String acc){
try{
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
ResultSet rst = stm.executeQuery("select * from customer where accno ='"+acc+"'");
if(rst.next())
{
System.out.println("Account no # :"+acc);
System.out.println("Password :"+rst.getString("password"));
System.out.println("Name :"+rst.getString("name"));
System.out.println("Contact :"+rst.getString("contact"));
System.out.println("Current Balance :"+rst.getDouble("balance"));
System.out.println("Address :"+rst.getString("address"));
}
}
catch(Exception e){
System.out.println("Customer class display method"+e);
}
}
void change_pass_cust(String acc, String pass){
try{
BufferedReader in=new BufferedReader(new InputStreamReader(System.in));
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
System.out.println("Enter new password :");
String p1=in.readLine();
System.out.println("Confirm password :");
String p2=in.readLine();
if(p1.equals(p2))
{
stm.executeUpdate("update customer set password='"+p1+"' where accno='"+acc+"' and password='"+pass+"'");
System.out.println("password updated successfully...");
}
else
{
System.out.println("password does not match");
}
}
catch(Exception e){
System.out.println("Customer class change pass method"+e);
}
}
void withdraw(String acc,String pass){
BufferedReader in=new BufferedReader(new InputStreamReader(System.in));
try{
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
ResultSet rst=stm.executeQuery("select * from customer where accno ='"+acc+"' and password ='"+pass+"'");
double amt1=0,amt2=0;
System.out.print("Enter the amount :");
amt2=Double.parseDouble(in.readLine());
if(rst.next())
{
amt1=rst.getDouble("Balance");
}
if(amt1-amt2<1000)
{
System.out.println("You cannot withdraw ");
}
else
{
stm.executeUpdate("update customer set balance='"+(amt1-amt2)+"' where accno ='"+acc+"' and password ='"+pass+"'");
System.out.println("money withdrawn");
System.out.println("Current balance:"+(amt1-amt2));
}
}
catch(Exception e){
System.out.println("customer class withdraw method "+e);
}
}
}
class Employee {
void Employee_dtl(String id){
try{
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
ResultSet rst = stm.executeQuery("select * from employee where EmpID ='"+id+"'");
if(rst.next())
{
System.out.println("EmpID :"+id);
System.out.println("Password :"+rst.getString("password"));
System.out.println("Name :"+rst.getString("name"));
System.out.println("Contact :"+rst.getString("contact"));
System.out.println("Current Balance :"+rst.getDouble("balance"));
System.out.println("Address :"+rst.getString("address"));
}
}
catch(Exception e){
System.out.println("Employee class display method"+e);
}
}
void change_pass_emp(String id, String pass){
try{
BufferedReader in=new BufferedReader(new InputStreamReader(System.in));
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
System.out.println("Enter new password :");
String p1=in.readLine();
System.out.println("Confirm password :");
String p2=in.readLine();
if(p1.equals(p2))
{
stm.executeUpdate("update employee set password='"+p1+"' where EmpID='"+id+"' and password='"+pass+"'");
System.out.println("password updated successfully...");
}
else
{
System.out.println("password does not match");
}
}
catch(Exception e){
System.out.println("Employee class change pass method"+e);
}
}
void Cust_Details(String acc){
try
{
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
ResultSet rst = stm.executeQuery("select * from customer where accno='"+acc+"'");
Customer obj1=new Customer();
obj1.display_Customer(acc);
}
catch(Exception e)
{
System.out.println("Employee see customer details method"+e);
}
}}
class Admin{
BufferedReader in = new BufferedReader (new InputStreamReader (System.in));
void Dtl_of_Customer(String accnt)
{
try{
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
ResultSet rst = stm.executeQuery("select * from customer where accno='"+accnt+"'");
Customer obj1=new Customer();
obj1.display_Customer(accnt);
}
catch(Exception e)
{
System.out.println("Admin see customer details method"+e);
}
}
void Dtl_of_emp(String id)
{
try
{
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
ResultSet rst = stm.executeQuery("select * from employee where EmpID='"+id+"'");
Employee obj2=new Employee();
obj2.Employee_dtl(id);
}
catch(Exception e)
{
System.out.println("Admin see employee detail method"+e);
}
}
void Password_change_cust(String acc)
{
try
{
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
System.out.println("Enter customer's new password :");
String p1=in.readLine();
System.out.println("Confirm password :");
String p2=in.readLine();
if(p1.equals(p2))
{
stm.executeUpdate("update customer set password='"+p1+"' where accno='"+acc+"'");
System.out.println("password updated successfully...");
}
else
{
System.out.println("password does not match");
}
}
catch(Exception e)
{
System.out.println("Change cust pass method"+e);
}
}
void Password_change_emp(String id)
{
try
{
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
System.out.println("Enter employee's new password :");
String p1=in.readLine();
System.out.println("Confirm password :");
String p2=in.readLine();
if(p1.equals(p2))
{
stm.executeUpdate("update employee set password='"+p1+"' where EmpID='"+id+"'");
System.out.println("password updated successfully...");
}
else
{
System.out.println("password does not match");
}
}
catch(Exception e)
{
System.out.println("Change cust emp method"+e);
}
}
void Change_my_pass(String y,String p)
{
try
{
conn ob = new conn();
Connection con = ob.c();
Statement stm = con.createStatement();
System.out.println("Enter new password :");
String p1=in.readLine();
System.out.println("Confirm password :");
String p2=in.readLine();
if(p1.equals(p2))
{
stm.executeUpdate("update employee set password='"+p1+"' where EmpID='"+y+"' and password='"+p+"'");
System.out.println("password updated successfully...");
}
else
{
System.out.println("password does not match");
}
}
catch(Exception e){
System.out.println("Change my pass method"+e);
}
}
}
public class NewMain {
public static void main(String[] args) throws Exception{
try
{
BufferedReader in=new BufferedReader(new InputStreamReader(System.in));
System.out.println("1-Administrator");
System.out.println("2-Employee");
System.out.println("3-Customer");
System.out.println("Enter your status");
int choice0=Integer.parseInt(in.readLine());
String acc;
switch(choice0)
{
case 1:
System.out.println("Enter the Username of Admin");
String y = in.readLine();
System.out.println("Enter the password");
String p = in.readLine();
conn obm = new conn();
Connection con = obm.c();
Statement stm = con.createStatement();
ResultSet rst = stm.executeQuery("select * from admin where username='"+y+"' and password = '"+p+"'");
Admin oba = new Admin();
if(rst.next())
{
System.out.println("1-Display details of any customer");
System.out.println("2-Display details of any employee");
System.out.println("3-Change password of any customer");
System.out.println("4-Change password of any employee");
System.out.println("5-Change my password");
System.out.println("enter your choice: ");
int choice11=Integer.parseInt(in.readLine());
switch(choice11)
{
case 1:System.out.println("Enter A/C of any customer");
String acct =in.readLine();
oba.Dtl_of_Customer(acct);
break;
case 2:System.out.println("Enter Id of any employee");
String id =in.readLine();
oba.Dtl_of_emp(id);
break;
case 3:System.out.println("Enter any customer's A/C #:" );
String accn=in.readLine();
oba.Password_change_cust(accn);
break;
case 4:System.out.println("Enter any employee's Id: ");
String empid=in.readLine();
oba.Password_change_emp(empid);
break;
case 5:oba.Change_my_pass(y, p);
break;
default:System.out.println("Wrong choice");
}
}
else{
System.out.println("Please Enter The Correct Match");
}
break;
case 2:
System.out.println("Enter the Employee Id");
String id =in.readLine();
System.out.println("Enter the password");
String pass = in.readLine();
conn ob = new conn();
Connection conx = ob.c();
Statement stmx = conx.createStatement();
ResultSet rstx = stmx.executeQuery("Select * from employee where EmpID='"+id+"' and password='"+pass+"'");
Employee objE =new Employee();
if(rstx.next())
{
System.out.println("1-Display my details");
System.out.println("2-Change password");
System.out.println("3-Display customer details");
System.out.print("Enter your choice: ");
int choice1=Integer.parseInt(in.readLine());
switch(choice1)
{
case 1:
System.out.println("Enter the id Again");
String idll =in.readLine();
objE.Employee_dtl(idll);
break;
case 2:objE.change_pass_emp(id, pass);
break;
case 3:
System.out.println(" Enter Account number of customer");
String acct = in .readLine();
objE.Cust_Details(acct);
break;
default:System.out.println("wrong choice");
}
}
else{
System.out.println("Incorrect Match occur ");
}
break;
case 3:
Customer objC=new Customer();
System.out.println("Enter A/C # :");
acc=in.readLine();
System.out.println("Enter password :");
pass=in.readLine();
boolean bb=objC.Login(acc,pass);
if(bb)
{
System.out.println("1-Display my details");
System.out.println("2-Change password");
System.out.println("3-Withdraw money");
System.out.print("Enter your choice: ");
int choice2=Integer.parseInt(in.readLine());
switch(choice2)
{
case 1:objC.display_Customer(acc);
break;
case 2:objC.change_pass_cust(acc, pass);
break;
case 3:objC.withdraw(acc, pass);
break;
default:System.out.println("Wrong choice");
}
}
else
{
System.out.println("Invalid A/C or password");
}
break;
default:System.out.println("wrong status");
}
}
catch(Exception e)
{
System.out.println("main method:"+e);
}
}
}