Simulate Simple Banking Transaction Process (Transaction Queue)

This tutorial will give you some ideas about simple banking transaction process and learning JDBC.

Download:

  • MySQL
  • MySQL JDBC Driver

Scenario:

In this simplified banking application we can do transfers from one account to another. The account holders want to have statements about the money movements from time to time. The accounts must not have balances below their credit line at any time (if the creditline is -10.0 the balance must never be say -11.0). Transfers are always transferring a positive amount of money.

Prepare for Schema:

create table account(id int primary key, balance real, creditline real) engine=InnoDB;

create table statement(transferid int, stmntacct int,partneracct int, changes real, newbalance real, dateandtime datetime) engine=InnoDB;

create table transferqueue(transferid int primary key, fromid int, toid int, amount real, status ENUM(‘pending’, ‘success’, ‘invalidfromid’, ‘invalidtoid’, ‘negativeamount’, ‘rejected’), requesttime datetime) engine=InnoDB;

Logic:

When user transfer money from account A to account B, it will be stored in the transfer queue. Worker Controller will be ran later. When work controller runs, each worker will access and validate each related transfer account accordingtly. What is order for each worker? Recall that this is queue (First In, First Out). If the transfer account is validated, each transfer account status will be marked as success (initially is pending), otheriwse, it will be marked as rejected. If it is success, each related statement will be generated.

Implementation:

Step One: Create a Connect class to connect and open a connection from your application to MySQL DB.

public class Connect {
private static Connection conn = null;

private final static String DEFAULT_URL = “jdbc:mysql://localhost:3306/transactionQueue”;
private final static String DEFAULT_USERNAME = “root”;
private final static String DEFAULT_PASSWORD = “admin”;

public static Connection getConnection(String url, String username, String password) throws Exception {
if(url == null || url.trim().length() == 0){
url = DEFAULT_URL;
}
if(username == null || username.trim().length() == 0){
username = DEFAULT_USERNAME;
}
if(password == null || password.trim().length() == 0){
password = DEFAULT_PASSWORD;
}

Class.forName (“com.mysql.jdbc.Driver”).newInstance ();
conn = DriverManager.getConnection (url, username, password);
System.out.println (“Database connection established”);
return conn;
}
}

Step Two: Create Java Beans

  • Account.java
  • TransferQueue.java
  • Statement.java
  • TransferStatus.java

Step Three: Create Insert Transfer account into transfer Queue

  • Create command collection (PreparedStatement)
  • Create SQL Service
  • Create main class

CommandCollection.java

Define constant SQL String.  (place any sql constant string in this class) For example:

public final static String INSERT_TRANSFERQUEUE_CMD = “INSERT INTO transferqueue (”
+ “fromid, ”
+ “toid, ”
+ “amount, ”
+ “status, ”
+ “requesttime”
+ “) VALUES(?,?,?,?,?)”;

SQLService.java

Define the implementation of SQL string. (place any sql implementation in this class) For example:

public static void insertTransferQueue(TransferQueue transferQueue, Connection conn) throws Exception{
PreparedStatement pstmt = null;
pstmt = conn.prepareStatement(CommandCollection.INSERT_TRANSFERQUEUE_CMD);
pstmt.setInt(1, transferQueue.getFromId());
pstmt.setInt(2, transferQueue.getToId());
pstmt.setDouble(3, transferQueue.getAmount());
pstmt.setString(4, transferQueue.getStatus().getDisplayName());
pstmt.setTimestamp(5, transferQueue.getRequestTime());
pstmt.executeUpdate();
pstmt.close();
}

Transfer.java

1) User input from command line

2) Validate the input

3) Create TransferQueue

4) Load object and insert related data into DB

Run Transfer.java

Example: java -cp <mysql driver jar> <from account> <to account> <amount> <mysqlURL> <username> <password>

if mysqlUrl, username or password does not provide, it should use default which is defined in your Connection.java

Step Four: Build WorkController and Worker class to access and validate each transfer account.

  • Worker.java
  • WorkerController

Worker.java

Inside worker, we should implement a function called boolean dequeue(); In the dequeue function, there are couple things below should be implemented

  1. Get transfer account from TransferQueue
  2. Check is the transferQueue is empty, if it is empty return false, otherwise continue
  3. Check from account, to account, amount etc
  4. if everything is good, then create statement, update transfer queue pending status to successful status and update both from and to accounts.
  5. Otherwise, status will be marked as rejected or fail status

WorkerController.java

Every second, run each worker until the transfer queue is empty

Use import java.util.Timer and import java.util.TimerTask.

public static void main(String[] args) throws Exception{
TimerTask runTask = new WController();
timer = new Timer();
timer.schedule(runTask, 0, 1000);
}

@Override
public void run() {
Worker worker = new Worker();
try {
if(!worker.dequeue(conn)){
timer.cancel();
try{
conn.close();//do not forget to close connection when the queue is empty
}catch(SQLException e) {
e.printStackTrace();
}
}
} catch (Exception e) {
e.printStackTrace();
}
}

Advertisements

7 Comments

  1. Hi Roy, in the worker class how would you check if the fromid and toid are valid? I know how to get all the accountIDs from the Account table and place them in a ResultSet but what method can you use to compare each of them with the fromid or toid in a particular transaction?
    Cheers

    1. Recall that this is only simple banking transcation simulation. For your first question, we only check whether fromId or toId exsiting in the DB. You can use select * from account where accountId = fromId passed to preparedStatement, and return the result, if the result exists, well, validation is succesful otheriwise return null. This is simple validation of account existing.
      for your question 2, I am sorry, i did not get what you mean. What did u mean compare each of them?

      Thank you for your reply.

      1. Hi Roy
        Thanks for your quick reply. Actually both two questions were talking about the same thing. Because I was thinking that if I want to check the validity of the fromid and toid passed in by a transaction, like what you said I need to go back to the account table and retrieve all the account IDs from that table and then check if the fromId or toId matches one of the IDs in the account table. I tried some different ways but failed to do so. Your comment just inspired me on some new ways of doing so. Thanks a lot!
        By the way, do you think you would be able to provide some simple skeleton for this worker method? That’d be awesome!
        谢谢!
        Tony

  2. Tony Zhang :

    Hi Roy
    Thanks for your quick reply. Actually both two questions were talking about the same thing. Because I was thinking that if I want to check the validity of the fromid and toid passed in by a transaction, like what you said I need to go back to the account table and retrieve all the account IDs from that table and then check if the fromId or toId matches one of the IDs in the account table. I tried some different ways but failed to do so. Your comment just inspired me on some new ways of doing so. Thanks a lot!
    By the way, do you think you would be able to provide some simple skeleton for this worker method? That’d be awesome!
    谢谢!
    Tony

    Welcome your any thoughts and comments, hope these can be helpful. Well, for worker class, something like below:

    public class Worker {
    //dequeue: return one transcation in the transaction queue depends on the transaction date. if the queue is empty return false
    public boolean dequeue(){
    if(queue is not empty){
    if(fromId is invalid){
    //update this transfer status to unsuccess
    return true
    }
    if(toId is invalid){
    //update this transfer status to unsuccess
    return true
    }
    if(transfer amount is invalid){
    //update this transfer status to unsuccess
    return true
    }
    //validation is finished
    //update transfer pending to unsuccess
    return true;
    }
    return false;
    }
    }

  3. Hi Roy
    Considering we will normally turn off tha autocommit function in the database, how would you commit a transaction once it’s finished? I was thinking about a commit() method in the workercontroller class, what you think?

    1. Yes, you can seperate commit to your workcontroller class, but it depends on your design. From my view, it is better to put commit function inside Worker class other than work controller class. From OOD perspective, worker object will do both validate and update transaction, worker contoller only to control and manage worker to run. Therefore, commit function will be inside of Worker class.

      Basically, you can load data into JavaBean then pass to the perparedstatmenet to update the DB. But if you need extra work on the commit, like data rollback, cascading update, delete when do the transcation, probably you need third-party framework, like Spring, which will handle the transaction management for you.

      cheers,

  4. hi.. were going to make a JAVA program on Banking transaction. we should use switch and if-else statement . but we don’t know how to do it. can you please help me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s