Accessing Database Using Java and MySQL: Part 5

This article ends the series by explaining the three modules in combination discussed in previous parts of the series.

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:
 
customer
 
Table contents 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
  1. package mybankdb;  
  2.   
  3. import java.sql.*;  
  4.   
  5. public class conn {  
  6.    public Connection c() throws Exception {  
  7.         Class.forName("com.mysql.jdbc.Driver");  
  8.         Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/bankdb""root""toor");  
  9.         return con;  
  10.     }  
  11. }  
NewMain.java
  1. import java.sql.*;  
  2. import java.io.*;  
  3. import mybankdb.conn;  
  4.   
  5. class Customer{  
  6. boolean Login(String acc, String pass){  
  7.     try{  
  8.       conn ob = new conn();  
  9.       Connection con = ob.c();  
  10.       Statement stm = con.createStatement();  
  11.       ResultSet rst = stm.executeQuery("select * from customer where accno ='"+acc+"' and password = '"+pass+"'");  
  12.       if(rst.next())  
  13.       {  
  14.           return true;  
  15.       }  
  16.       else  
  17.       {  
  18.           return false;  
  19.       }  
  20.     }  
  21.     catch(Exception e){  
  22.         System.out.println("Customer class login method"+e);  
  23.       return false;  
  24.     }  
  25. }  
  26.   
  27.     void display_Customer(String acc){  
  28.     try{  
  29.       conn ob = new conn();  
  30.       Connection con = ob.c();  
  31.       Statement stm = con.createStatement();  
  32.       ResultSet rst = stm.executeQuery("select * from customer where accno ='"+acc+"'");  
  33.       if(rst.next())  
  34.       {  
  35.           System.out.println("Account no    #  :"+acc);  
  36.           System.out.println("Password        :"+rst.getString("password"));  
  37.           System.out.println("Name            :"+rst.getString("name"));  
  38.           System.out.println("Contact         :"+rst.getString("contact"));  
  39.           System.out.println("Current Balance :"+rst.getDouble("balance"));  
  40.           System.out.println("Address         :"+rst.getString("address"));  
  41.       }  
  42.     }  
  43.     catch(Exception e){  
  44.         System.out.println("Customer class display method"+e);  
  45.     }  
  46.     }  
  47.   
  48.   void change_pass_cust(String acc, String pass){  
  49.       try{  
  50.       BufferedReader in=new BufferedReader(new InputStreamReader(System.in));  
  51.       conn ob = new conn();  
  52.       Connection con = ob.c();  
  53.       Statement stm = con.createStatement();  
  54.       System.out.println("Enter new password :");  
  55.       String p1=in.readLine();  
  56.       System.out.println("Confirm password :");  
  57.       String p2=in.readLine();  
  58.   
  59.       if(p1.equals(p2))  
  60.       {  
  61.        stm.executeUpdate("update customer set password='"+p1+"' where accno='"+acc+"' and password='"+pass+"'");  
  62.        System.out.println("password updated successfully...");  
  63.       }  
  64.       else  
  65.       {  
  66.           System.out.println("password does not match");  
  67.       }  
  68.       }  
  69.       catch(Exception e){  
  70.           System.out.println("Customer class change pass method"+e);  
  71.       }  
  72.   }  
  73.   
  74. void withdraw(String acc,String pass){  
  75.  BufferedReader in=new BufferedReader(new InputStreamReader(System.in));  
  76.     try{    
  77.       conn ob = new conn();  
  78.       Connection con = ob.c();  
  79.       Statement stm = con.createStatement();  
  80.       ResultSet rst=stm.executeQuery("select * from customer where accno ='"+acc+"' and password ='"+pass+"'");  
  81.       double amt1=0,amt2=0;  
  82.       System.out.print("Enter the amount :");  
  83.       amt2=Double.parseDouble(in.readLine());  
  84.       if(rst.next())  
  85.       {  
  86.           amt1=rst.getDouble("Balance");  
  87.       }  
  88.           if(amt1-amt2<1000)  
  89.           {  
  90.             System.out.println("You cannot withdraw ");  
  91.           }  
  92.           else  
  93.           {  
  94.           stm.executeUpdate("update customer set balance='"+(amt1-amt2)+"' where accno ='"+acc+"' and password ='"+pass+"'");  
  95.           System.out.println("money withdrawn");  
  96.           System.out.println("Current balance:"+(amt1-amt2));  
  97.           }  
  98. }  
  99. catch(Exception e){  
  100.     System.out.println("customer class withdraw method "+e);  
  101. }  
  102.  }  
  103.   }  
  104.   
  105. class Employee {    
  106.  void  Employee_dtl(String id){  
  107.     try{  
  108.       conn ob = new conn();  
  109.       Connection con = ob.c();  
  110.       Statement stm = con.createStatement();  
  111.       ResultSet rst = stm.executeQuery("select * from employee where EmpID ='"+id+"'");  
  112.       if(rst.next())  
  113.       {  
  114.           System.out.println("EmpID           :"+id);  
  115.           System.out.println("Password        :"+rst.getString("password"));  
  116.           System.out.println("Name            :"+rst.getString("name"));  
  117.           System.out.println("Contact         :"+rst.getString("contact"));  
  118.           System.out.println("Current Balance :"+rst.getDouble("balance"));  
  119.           System.out.println("Address         :"+rst.getString("address"));  
  120.       }  
  121.     }  
  122.     catch(Exception e){  
  123.         System.out.println("Employee class display method"+e);  
  124.     }  
  125.     }  
  126.   
  127.   void change_pass_emp(String id, String pass){  
  128.       try{  
  129.       BufferedReader in=new BufferedReader(new InputStreamReader(System.in));  
  130.       conn ob = new conn();  
  131.       Connection con = ob.c();  
  132.       Statement stm = con.createStatement();  
  133.       System.out.println("Enter new password :");  
  134.       String p1=in.readLine();  
  135.       System.out.println("Confirm password :");  
  136.       String p2=in.readLine();  
  137.       if(p1.equals(p2))  
  138.       {  
  139.        stm.executeUpdate("update employee set password='"+p1+"' where EmpID='"+id+"' and password='"+pass+"'");  
  140.        System.out.println("password updated successfully...");  
  141.       }  
  142.       else  
  143.       {  
  144.           System.out.println("password does not match");  
  145.       }  
  146.       }  
  147.       catch(Exception e){  
  148.           System.out.println("Employee class change pass method"+e);  
  149.       }  
  150.   }  
  151.   
  152. void Cust_Details(String acc){  
  153.      try  
  154.      {        
  155.       conn ob = new conn();  
  156.       Connection con = ob.c();  
  157.       Statement stm = con.createStatement();  
  158.      ResultSet rst = stm.executeQuery("select * from customer where accno='"+acc+"'");   
  159.          Customer obj1=new Customer();  
  160.       obj1.display_Customer(acc);  
  161.      }  
  162.      catch(Exception e)  
  163.      {  
  164.       System.out.println("Employee see customer details method"+e);  
  165.      }  
  166. }}  
  167.   
  168. class Admin{  
  169.     BufferedReader in = new BufferedReader (new InputStreamReader (System.in));  
  170. void Dtl_of_Customer(String accnt)  
  171. {  
  172.     try{  
  173.       conn ob = new conn();  
  174.       Connection con = ob.c();  
  175.       Statement stm = con.createStatement();  
  176.        ResultSet rst = stm.executeQuery("select * from customer where accno='"+accnt+"'");  
  177.       Customer obj1=new Customer();  
  178.       obj1.display_Customer(accnt);  
  179. }  
  180.     catch(Exception e)  
  181.     {  
  182. System.out.println("Admin see customer details method"+e);  
  183. }  
  184.    }  
  185.   
  186. void Dtl_of_emp(String id)  
  187. {  
  188.     try  
  189.     {  
  190.       conn ob = new conn();  
  191.       Connection con = ob.c();  
  192.       Statement stm = con.createStatement();  
  193.        ResultSet rst = stm.executeQuery("select * from employee where EmpID='"+id+"'");  
  194.        Employee obj2=new Employee();  
  195.        obj2.Employee_dtl(id);  
  196. }  
  197.     catch(Exception e)  
  198.     {  
  199.         System.out.println("Admin see employee detail method"+e);  
  200.     }  
  201. }  
  202.     void Password_change_cust(String acc)  
  203.     {  
  204.         try  
  205.         {  
  206.         conn ob = new conn();  
  207.         Connection con = ob.c();  
  208.         Statement stm = con.createStatement();  
  209.         System.out.println("Enter customer's new password :");  
  210.         String p1=in.readLine();  
  211.         System.out.println("Confirm password :");  
  212.         String p2=in.readLine();  
  213.         if(p1.equals(p2))  
  214.       {  
  215.        stm.executeUpdate("update customer set password='"+p1+"' where accno='"+acc+"'");  
  216.        System.out.println("password updated successfully...");  
  217.       }  
  218.       else  
  219.       {  
  220.           System.out.println("password does not match");  
  221.       }  
  222.         }  
  223.         catch(Exception e)  
  224.         {  
  225.             System.out.println("Change cust pass method"+e);  
  226.         }  
  227. }  
  228.   
  229.     void Password_change_emp(String id)  
  230.     {  
  231.         try  
  232.         {  
  233.         conn ob = new conn();  
  234.         Connection con = ob.c();  
  235.         Statement stm = con.createStatement();  
  236.         System.out.println("Enter employee's new password :");  
  237.         String p1=in.readLine();  
  238.         System.out.println("Confirm password :");  
  239.         String p2=in.readLine();  
  240.         if(p1.equals(p2))  
  241.       {  
  242.        stm.executeUpdate("update employee set password='"+p1+"' where EmpID='"+id+"'");  
  243.        System.out.println("password updated successfully...");  
  244.       }  
  245.       else  
  246.       {  
  247.           System.out.println("password does not match");  
  248.       }  
  249.         }  
  250.         catch(Exception e)  
  251.         {  
  252.        System.out.println("Change cust emp method"+e);  
  253.     }  
  254.     }  
  255.     void Change_my_pass(String y,String p)  
  256.     {  
  257.         try  
  258.         {  
  259.              conn ob = new conn();  
  260.         Connection con = ob.c();  
  261.         Statement stm = con.createStatement();  
  262.         System.out.println("Enter new password :");  
  263.         String p1=in.readLine();  
  264.         System.out.println("Confirm password :");  
  265.         String p2=in.readLine();  
  266.         if(p1.equals(p2))  
  267.       {  
  268.        stm.executeUpdate("update employee set password='"+p1+"' where EmpID='"+y+"' and password='"+p+"'");  
  269.        System.out.println("password updated successfully...");  
  270.       }  
  271.       else  
  272.       {  
  273.           System.out.println("password does not match");  
  274.       }  
  275.         }  
  276.         catch(Exception e){  
  277.             System.out.println("Change my pass method"+e);  
  278.         }  
  279.     }  
  280. }  
  281. public class NewMain {  
  282.   
  283.     public static void main(String[] args) throws Exception{  
  284.         try  
  285.         {  
  286.           BufferedReader in=new BufferedReader(new InputStreamReader(System.in));  
  287.         System.out.println("1-Administrator");  
  288.         System.out.println("2-Employee");  
  289.         System.out.println("3-Customer");  
  290.         System.out.println("Enter your status");  
  291.        int choice0=Integer.parseInt(in.readLine());  
  292.       String acc;  
  293.        switch(choice0)  
  294.        {  
  295.                case 1:  
  296.                    System.out.println("Enter the Username of Admin");  
  297.                    String y = in.readLine();  
  298.                    System.out.println("Enter the password");  
  299.                    String p = in.readLine();  
  300.                     conn obm = new conn();  
  301.                     Connection con = obm.c();  
  302.                     Statement stm = con.createStatement();  
  303.                     ResultSet rst = stm.executeQuery("select * from admin where username='"+y+"' and password = '"+p+"'");  
  304.                     Admin oba = new Admin();  
  305.   
  306.                     if(rst.next())  
  307.                     {  
  308.                     System.out.println("1-Display details of any customer");  
  309.                     System.out.println("2-Display details of any employee");  
  310.                     System.out.println("3-Change password of any customer");  
  311.                     System.out.println("4-Change password of any employee");  
  312.                     System.out.println("5-Change my password");  
  313.                     System.out.println("enter your choice:  ");  
  314.                     int choice11=Integer.parseInt(in.readLine());  
  315.                     switch(choice11)  
  316.                     {  
  317.                         case 1:System.out.println("Enter A/C of any customer");  
  318.                                String acct =in.readLine();  
  319.                                oba.Dtl_of_Customer(acct);  
  320.                             break;  
  321.                         case 2:System.out.println("Enter Id of any employee");  
  322.                         String id =in.readLine();  
  323.                             oba.Dtl_of_emp(id);  
  324.                             break;  
  325.                         case 3:System.out.println("Enter any customer's A/C #:"  );  
  326.                                String accn=in.readLine();  
  327.                             oba.Password_change_cust(accn);  
  328.                             break;  
  329.                         case 4:System.out.println("Enter any employee's Id:  ");  
  330.                               String empid=in.readLine();  
  331.                             oba.Password_change_emp(empid);  
  332.                             break;  
  333.                         case 5:oba.Change_my_pass(y, p);  
  334.                             break;  
  335.                         default:System.out.println("Wrong choice");  
  336.                     }  
  337.                     }  
  338.                     else{  
  339.                     System.out.println("Please Enter The Correct Match");  
  340.                     }  
  341.                    break;  
  342.   
  343.                case 2:                   
  344.                    System.out.println("Enter the Employee Id");  
  345.                    String id =in.readLine();  
  346.                    System.out.println("Enter the password");  
  347.                    String pass = in.readLine();  
  348.                     conn ob = new conn();  
  349.                     Connection conx = ob.c();  
  350.                     Statement stmx = conx.createStatement();  
  351.                     ResultSet rstx = stmx.executeQuery("Select * from employee where  EmpID='"+id+"' and password='"+pass+"'");  
  352.                     Employee objE  =new Employee();  
  353.                     if(rstx.next())  
  354.                     {                
  355.                   System.out.println("1-Display my details");  
  356.                   System.out.println("2-Change password");  
  357.                   System.out.println("3-Display customer details");  
  358.                   System.out.print("Enter your choice: ");  
  359.                    int choice1=Integer.parseInt(in.readLine());  
  360.                   switch(choice1)  
  361.                   {  
  362.                       case 1:  
  363.                           System.out.println("Enter the  id Again");  
  364.                           String idll =in.readLine();                                
  365.                          objE.Employee_dtl(idll);  
  366.                           break;  
  367.                       case 2:objE.change_pass_emp(id, pass);  
  368.                           break;  
  369.                       case 3:  
  370.                        System.out.println(" Enter Account number of customer");  
  371.                        String acct = in .readLine();  
  372.                           objE.Cust_Details(acct);  
  373.                           break;  
  374.                       default:System.out.println("wrong choice");  
  375.                   }  
  376.                  }  
  377.                     else{  
  378.                         System.out.println("Incorrect Match  occur ");  
  379.                     }  
  380.                     break;  
  381.   
  382.                case 3:  
  383.                   Customer objC=new Customer();  
  384.                   System.out.println("Enter A/C #   :");  
  385.                    acc=in.readLine();  
  386.                   System.out.println("Enter password :");  
  387.                    pass=in.readLine();  
  388.                   boolean bb=objC.Login(acc,pass);  
  389.                   if(bb)  
  390.                   {  
  391.                   System.out.println("1-Display my details");  
  392.                   System.out.println("2-Change password");  
  393.                   System.out.println("3-Withdraw money");  
  394.                   System.out.print("Enter your choice: ");  
  395.                   int choice2=Integer.parseInt(in.readLine());  
  396.                   switch(choice2)  
  397.                   {  
  398.                       case 1:objC.display_Customer(acc);  
  399.                           break;  
  400.                       case 2:objC.change_pass_cust(acc, pass);  
  401.                           break;  
  402.                       case 3:objC.withdraw(acc, pass);  
  403.                           break;  
  404.                       default:System.out.println("Wrong choice");  
  405.                   }  
  406.                   }  
  407.                   else  
  408.                   {  
  409.                    System.out.println("Invalid A/C or password");  
  410.                   }  
  411.                    break;  
  412.            default:System.out.println("wrong status");  
  413.        }  
  414.     }  
  415.     catch(Exception e)  
  416.     {  
  417.       System.out.println("main method:"+e);  
  418.     }  
  419.  }
  420. }  
Various outputs associated with admin, employee and customer.
 
Output: Selecting status
 
Admin and employee status.
 
Admin
 
Customer and wrong status
 
wrong status
 
The further outputs will be the same as discussed in previous modules.
 
Thank you, keep learning and sharing.