Accessing Database Using Java and MySQL: Part 2

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

• Accessing Database Using Java and MySQL: Part 1

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

 

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

package mydb;
import java.sql.*;

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

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.

 

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

 

 

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

 

 

Now the main Java file is the following.

 

CustomerMain.java

import java.sql.*;
import java.io.*;
import mydb.ConnectDB;

class Customer{
boolean Login(String acc, String pass){
    try{
      ConnectDB ob = new ConnectDB();
      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{
      ConnectDB ob = new ConnectDB();
      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));
      ConnectDB ob = new ConnectDB();
      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{
      ConnectDB ob = new ConnectDB();
      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);
}
}}
public class CustomerMain {
    private static String pass;
    public static void main(String[] args) throws Exception{
        try
        {
          BufferedReader in=new BufferedReader(new InputStreamReader(System.in));
        System.out.println("Customer..??");
        System.out.println("1-Yes or 2-No");
       int choice0=Integer.parseInt(in.readLine());
      String acc;
       switch(choice0)
       {
               case 1:
                  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("Thank you");
       }
    }
catch(Exception e)
{
System.out.println("main method:"+e);
}
 }}

We get the outputs for the customer named himanshu.

 

 

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 the 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.