In Focus

Accessing Database Using Java and MySQL: Part 2

This article explains how much a customer is authorized for the transactions and how the processing is done in Java using MySQL.

Before reading further, read the previous part of this article.

Now we are here to explain the procedure involved in the transactions of an authorized customer with a suitable example of it.

Transactions involved for Authorized Customers

Authorized


To explain this module, we will make two separate Java files, CustomerMain.java and ConnectDB.java.

CustomerMain.java consists of all the functionality required for the customer and ConnectDB.java will be used to connect the file with the MySQL database in which all the details are written about the customer.

Here are the few processes that can be done for the customer:

  • Can check his/her details (a detail consists of account number, password, name, address, contact, account balance)
  • Can change his/her password
  • Can withdraw his/her money

Now let's check how all these processes can be done with the following code example.

Example

ConnectDB.java

  1. package mydb;  
  2. import java.sql.*;  
  3.   
  4. public class ConnectDB {  
  5. public Connection c() throws Exception{  
  6.     Class.forName ("com.mysql.jdbc.Driver");  
  7.     Connection con= DriverManager.getConnection("jdbc:mysql://localhost:3306/bankdb""root""toor");  
  8.     return con;  
  9. }}  
In the preceding code, we can observe that the ConnectDB.java file is made in the separate package named “mydb”. The ("jdbc:mysql://localhost:3306/bankdb", "root", "toor") is the path for the database. The “bankdb” is the name of the table made in the database, “root” is the username and “toor” is the password of the MySQL access and “3306” is the port number.

For the connection, there is also one jar file that is necessary named “MySQL-Connector” that should be attached to the library of your project. The jar file is also attached with the codes file.

MySQL

Here are the following tables for customer, employee and administrator.

customer

For now we are working with the customer schema table and the following are the contents of the customer table.

customer schema table

Now the main Java file is the following.

CustomerMain.java
  1. import java.sql.*;  
  2. import java.io.*;  
  3. import mydb.ConnectDB;  
  4.   
  5. class Customer{  
  6. boolean Login(String acc, String pass){  
  7.     try{  
  8.       ConnectDB ob = new ConnectDB();  
  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.     catch(Exception e){  
  21.         System.out.println("Customer class login method"+e);  
  22.       return false;  
  23.     }  
  24. }  
  25.     void display_Customer(String acc){  
  26.     try{  
  27.       ConnectDB ob = new ConnectDB();  
  28.       Connection con = ob.c();  
  29.       Statement stm = con.createStatement();  
  30.       ResultSet rst = stm.executeQuery("select * from customer where accno ='"+acc+"'");  
  31.       if(rst.next())  
  32.       {  
  33.           System.out.println("Account no    #  :"+acc);  
  34.           System.out.println("Password        :"+rst.getString("password"));  
  35.           System.out.println("Name            :"+rst.getString("name"));  
  36.           System.out.println("Contact         :"+rst.getString("contact"));  
  37.           System.out.println("Current Balance :"+rst.getDouble("balance"));  
  38.           System.out.println("Address         :"+rst.getString("address"));  
  39.        }   }  
  40.     catch(Exception e){  
  41.         System.out.println("Customer class display method"+e);  
  42.     }  
  43.     }  
  44.   void change_pass_cust(String acc, String pass){  
  45.       try{  
  46.       BufferedReader in=new BufferedReader(new InputStreamReader(System.in));  
  47.       ConnectDB ob = new ConnectDB();  
  48.       Connection con = ob.c();  
  49.       Statement stm = con.createStatement();  
  50.       System.out.println("Enter new password :");  
  51.       String p1=in.readLine();  
  52.       System.out.println("Confirm password :");  
  53.       String p2=in.readLine();  
  54.       if(p1.equals(p2))  
  55.       {  
  56.        stm.executeUpdate("update customer set password='"+p1+"' where accno='"+acc+"' and password='"+pass+"'");  
  57.        System.out.println("password updated successfully...");  
  58.       }  
  59.       else  
  60.       {  
  61.           System.out.println("password does not match");  
  62.       } }  
  63.       catch(Exception e){  
  64.           System.out.println("Customer class change pass method"+e);  
  65.       }  
  66.   }  
  67. void withdraw(String acc,String pass){  
  68.  BufferedReader in=new BufferedReader(new InputStreamReader(System.in));  
  69.     try{  
  70.       ConnectDB ob = new ConnectDB();  
  71.       Connection con = ob.c();  
  72.       Statement stm = con.createStatement();  
  73.       ResultSet rst=stm.executeQuery("select * from customer where accno ='"+acc+"' and password ='"+pass+"'");  
  74.       double amt1=0,amt2=0;  
  75.       System.out.print("Enter the amount :");  
  76.       amt2=Double.parseDouble(in.readLine());  
  77.       if(rst.next())  
  78.       {  
  79.           amt1=rst.getDouble("Balance");  
  80.       }  
  81.           if(amt1-amt2<1000)  
  82.           {  
  83.             System.out.println("You cannot withdraw ");  
  84.           }    
  85.          else {  
  86.           stm.executeUpdate("update customer set balance='"+(amt1-amt2)+"' where accno ='"+acc+"' and password ='"+pass+"'");  
  87.           System.out.println("money withdrawn");  
  88.           System.out.println("Current balance:"+(amt1-amt2));  
  89.           }}  
  90. catch(Exception e){  
  91.     System.out.println("customer class withdraw method "+e);  
  92. }  
  93. }}  
  94. public class CustomerMain {  
  95.     private static String pass;  
  96.     public static void main(String[] args) throws Exception{  
  97.         try  
  98.         {  
  99.           BufferedReader in=new BufferedReader(new InputStreamReader(System.in));  
  100.         System.out.println("Customer..??");  
  101.         System.out.println("1-Yes or 2-No");  
  102.        int choice0=Integer.parseInt(in.readLine());  
  103.       String acc;  
  104.        switch(choice0)  
  105.        {  
  106.                case 1:  
  107.                   Customer objC=new Customer();  
  108.                   System.out.println("Enter A/C # : ");  
  109.                    acc=in.readLine();  
  110.                   System.out.println("Enter password :");  
  111.                    pass=in.readLine();  
  112.                   boolean bb=objC.Login(acc,pass);  
  113.                   if(bb)  
  114.                   {  
  115.                   System.out.println("1-Display my details");  
  116.                   System.out.println("2-Change password");  
  117.                   System.out.println("3-Withdraw money");  
  118.                   System.out.print("Enter your choice: ");  
  119.                   int choice2=Integer.parseInt(in.readLine());  
  120.                   switch(choice2)  
  121.                   {  
  122.                       case 1:objC.display_Customer(acc);  
  123.                           break;  
  124.                       case 2:objC.change_pass_cust(acc, pass);  
  125.                           break;  
  126.                       case 3:objC.withdraw(acc, pass);  
  127.                           break;  
  128.                       default:System.out.println("Wrong choice");  
  129.                   }    
  130.                  }  
  131.                   else {  
  132.                    System.out.println("Invalid A/C or password");  
  133.                   }  
  134.                    break;  
  135.            default:System.out.println("Thank you");  
  136.        }  
  137.     }  
  138. catch(Exception e)  
  139. {  
  140. System.out.println("main method:"+e);  
  141. }  
  142.  }}  
We get the outputs for the customer named himanshu.

outputs

The following are the outputs associated with the various transactions.

Output 1: display my details



Output 2: Change my password



Output 3: Withdraw money



Output 4: If any of the input parameters are incorrect.



Output 5: If you choose other than given option.



For the discussion on the employee module, click the below link
 
Accessing Database Using Java and MySQL: Part 3


Thank you, keep learning and sharing.