Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I'm aiming to get this program right as I might show it at a job interview. The code below runs fine but I would like to know if there's anything that can be improved in terms of readability, good practices and/or standards. The program asks a user to take money from a checking account and move it to a savings account. This information then gets updated in a MySQL database I set up locally.

Implementation class

package com.jdbcbank;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;

public class Implementation {

// Database access
static final String DB_NAME = "jdbc_example";
static final String HOSTNAME = "127.0.0.1";
static final int DB_PORT = 3306;
static final String DB_URL = "jdbc:mysql://" + HOSTNAME + ":" + DB_PORT
        + "/" + DB_NAME;

// Database credentials
static final String DB_USR = "root";
static final String DB_PASS = "";

// Connection variables
static Connection conn = null;
static Statement statement = null;
static ResultSet result = null;
static PreparedStatement pStatement = null;

public static void main(String[] args) throws SQLException {

    // Exercise is done with user id 1
    int userId = 1;

    // 1. checking, 2. saving
    int balance[] = new int[2];

    try {

        // Connect to database
        Implementation.connectToDb();

        // Get current balance
        BankAccount bankAccount = new BankAccount();

        balance = bankAccount.getBalance(userId);
        System.out.println("---------------");
        System.out.println("Current balance ");
        System.out.println("---------------");
        System.out.println("Checking Account: " + balance[0] + " USD\n"
                + "Savings Account: " + balance[1] + " USD\n");
        checkIfCheckingBalancePositive(balance[0]); // Throw exception if
                                                    // balance is negative
        System.out
                .print("Enter the amount (in USD) you wish to move your savings account: ");

        // Set amount and do transaction
        Scanner scanner = new Scanner(System.in);

        int amount = checkIfAmountPositive(scanner.nextInt()); // Throw exception if amount is negative
        scanner.close();
        bankAccount.setAmount(amount);
        bankAccount.doTransaction();

        // Get new balance
        balance = bankAccount.getBalance(userId);
        System.out.println();
        System.out.println("---------------");
        System.out.println("New balance ");
        System.out.println("---------------");
        System.out.println("Checking Account: " + balance[0] + " USD\n"
                + "Savings Account: " + balance[1] + " USD\n");
        System.out.println("Thank you.");

    } catch (Exception e) {
        System.out.println("Error: " + e.getMessage());
    } finally {
        // Close all connections
        close(conn, statement, pStatement, result);
    }

}


public static void checkIfCheckingBalancePositive(int balance) {

    if (balance < 0)
        throw new IllegalArgumentException(
                "Your checking balance is negative, you can't withdraw.");
}


public static int checkIfAmountPositive(int amount) {

    if (amount < 0) {
        throw new IllegalArgumentException("Amount must be positive");
    }
    return amount;
}


public static void connectToDb() {

    try {
        // Load driver
        Class.forName("com.mysql.jdbc.Driver");

        // Set connection
        conn = DriverManager.getConnection(DB_URL, Implementation.DB_USR,
                DB_PASS);
        conn.setAutoCommit(false);
        statement = conn.createStatement();

    } catch (ClassNotFoundException e) {
        System.out.println("Error: " + e.getMessage());
    } catch (SQLException e) {
        System.out.println("Error: " + e.getMessage());
    }
}


public static void close(Connection conn, Statement statement,
        PreparedStatement pStatement, ResultSet result) {
    if (conn != null)
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }

    if (statement != null)
        try {
            statement.close();
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    if (pStatement != null)
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    if (result != null)
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
}

BankAccount class

package com.jdbcbank;

import java.sql.*;
import java.math.*;

public class BankAccount {

private static int amount;

public void doTransaction() throws SQLException {

    try {
        // Withdraw from checking and deposit into saving
        withdrawFromChecking(Implementation.statement, new BigDecimal(amount), 1);
        depositIntoSaving(Implementation.statement, new BigDecimal(amount), 1);

        // Execute batch and commit if no issues are found
        Implementation.statement.executeBatch();
        Implementation.conn.commit();

        // Print message
        System.out.println("Successful transaction!");

    } catch (Exception e) {
        System.out.println("Error: " + e.getMessage());
        Implementation.conn.rollback();
    }
}


public int[] getBalance(int id) throws SQLException {

    // Array to be returned
    int balance[] = new int[2];

    // Get results
    String query = "SELECT * from bank_account WHERE id = " + id;
    Implementation.result = Implementation.statement.executeQuery(query);

    while (Implementation.result.next()) {
        // Retrieve by column name
        balance[0] = Implementation.result.getInt("checking_balance");
        balance[1] = Implementation.result.getInt("saving_balance");
    }
    return balance;

}


public static int getAmount() {
    return amount;
}


public void setAmount(int amount) {
    BankAccount.amount = amount;
}


public static void withdrawFromChecking(Statement statement,
        BigDecimal amount, int id) throws SQLException {

    statement
            .addBatch("UPDATE bank_account SET checking_balance = checking_balance - "
                    + amount + " where id = " + id);
}


public static void depositIntoSaving(Statement statement,
        BigDecimal amount, int id) throws SQLException {

    statement
            .addBatch("UPDATE bank_account SET saving_balance = saving_balance + "
                    + amount + " where id = " + id);
    }
}

Output

---------------
Current balance
---------------

Checking Account: 380 USD

Savings Account: 1120 USD

Enter the amount (in USD) you wish to move to your savings account: 20

Successful transaction!

---------------
New balance
---------------

Checking Account: 360 USD Savings Account: 1140 USD

Thank you.
share|improve this question
1  
Guys, I just wanted to say THANKS for all your suggestions. I'll spend some quality time going through each one, hopefully the code will look neat after the changes. –  user1764094 Jun 2 at 18:35
    
Rolled back Rev 3 → 2. See meta.codereview.stackexchange.com/q/1763/9357 –  200_success Jun 3 at 10:20

8 Answers 8

Overall it's not bad, but here are a few things that stuck out for me.

  1. I'd consider renaming getBalance to getBalances, since you're actually returning the balances from multiple bank accounts for that user.

    balance = bankAccount.getBalance(userId);
    
  2. Consider creating and returning an array of AccountBalance type from the getBalance method instead of an array of int. I don't like the fact that your code just knows that balance[0] is the checking account balance and balance[1] is the savings account balance. In my mind, the AccountBalance type would be a data object, with the following fields: Id, Name, Last 4 digits of account number, and Amount. Well, I've reconsidered this suggestion, because it's a prelude to something quite a bit more complicated than what you need. I'd go with the other answer's suggestion of just having a type that has separate fields for Checking Account Amount and Savings Account Amount. That seems to map more closely with your database, anyway.

  3. It's a little unclear what the effect is of the user entering a number. The prompt says "Enter the amount (in USD) you wish to move your savings account: " Are you moving money from the savings account or to the savings account? Also, the name of the methods setAmount and doTransaction are pretty vague as to the purpose. Would something like moveAmountFromCheckingToSavings be more accurate?

  4. In some places you're using the BigDecimal datatype for an amount and in others you're using int.

  5. This is pretty bad: private static int amount; This is not thread-safe, and I don't see the purpose of doing this instead of just passing the value in as a parameter to the doTransaction method. I don't see the purpose of having two separate methods setAmount and doTransaction; they might as well be one method, without the static variable.

  6. This is a nit-pick of mine, but there's no point in initializing your int balance[] variable with new int[2], since you're just going to assign it a totally different value later: balance = bankAccount.getBalance(userId)

  7. The code doesn't verify that the amount entered by the user is not greater than the current checking account balance. Also note that ideally this check would be performed inside of a transaction along with the actual update to both accounts.

share|improve this answer

In addition to all the other good points mentioned:

  • This code massively mixes up presentation logic, business logic, and back end database implementation details. Those are three separate things; they should be kept far apart in the code.

  • This code has "sql injection attack" written all over it. Don't ever use strings to build up SQL queries. Use stored procedures or any other technology that allows passing the data as parameters. (Today you're using numbers, which are generally safe against SQL attacks, but still this is very bad style. Get in the habit of doing it right.)

  • Your implementation of a bank account bears absolutely no resemblance to how real bank accounts are modeled by banks. Banks never use destructive updates. If you have 100 USD in an account and you deposit 50 USD, the bank does not find your balance in a database somewhere, erase the 100 and replace it with 150. Rather, they keep a complete list of every transaction ever and add "deposited 50 USD" to the end of that list. The balance at any point in time can then be computed from that transaction list. (And in fact some banks use special hard drives that do not support destructive updates; once a byte is written, it stays that value forever.)

share|improve this answer
    
I'd say no to stored procedures on most cases: blog.codinghorror.com/who-needs-stored-procedures-anyways –  Pedro Morte Rolo Jun 3 at 15:03
2  
@PedroMorteRolo: The exact technology used isn't important; there are other alternatives to stored procedures. The important thing is that SQL injection attacks happen when people create SQL on the fly through string concatenation, and it's therefore a bad practice. The original poster is considering using this code as part of a submission for a job interview; a candidate who could not tell me why a SQL injection vector is a bad practice would not get my "hire" vote. –  Eric Lippert Jun 3 at 16:05
    
@EricLippert thanks for your comments. I'm aware of the MVC approach of having things separate from each other, I never thought this would be criticized in this very simple program :) I appreciate it though. In my program, would you then have a class to handle the db, one for the presentation layer and one to handle the logic acting as a controller? I kind of want to get this program right. –  user1764094 Jun 3 at 19:47
    
@EricLippert as for your comment on the destructive data I have a question/doubt: so if you want to get the current balance once a transaction has been done, do I necessarily have to go through all previous transactions and add them all together to get that value? can I not have two tables so that one of them shows me the current balance (like in my example with the destructive data) and the other table is used to log all transactions? –  user1764094 Jun 3 at 19:59
    
@EricLippert one last thing, I reposted the program today but I made a mistake as I posted it under another account (still trying to get familiar with how this site works). Here's the URL, any further suggestions on the code will be much appreciated, codereview.stackexchange.com/questions/52367/… –  user1764094 Jun 3 at 20:02

Imagine you are the interviewer - try to understand how they see your code from their own perspective without the knowledge that you have about this code.

Use reasonable names

Implementation in public class Implementation { is not good to begin with. You will need to explain it "this is the implementation for testing if my logic works and for user input and output". It not only shows you that your Implementation class does not satisfy the SRP, it also does not have a good name. Writing a (unit) test could be preferred here.

Same goes for many of your variables like userId or scanner. I need your comments to understand what is in these variables - but with good naming no comments are needed. Why not go with testUserId and transferAmountScanner? Please consider looking through your code and thinking about more appropriate variable names. Some of them are of course fine though.

Don't use hardcoded configuration values

You have lines like static final String DB_USR = "root"; and System.out.println("New balance "); in your code. Imagine the database username changes or you want to add multiple language support or just fix a spelling mistake. You then need to rebuild your software. Better avoid this by using a configuration file where you have all these things in one file so you find faster if you want to change something and you are more flexible.

If it's for an interview you may stay with the hardcoded values, but make sure to let your interviewers know that you did that for the purpose of simplicity and when on a real project you would use a configuration.

Use classes when it's obvious

Although, in your example code, going with just a primitive type for balance is fine, consider creating a Balance class or at least make the interviewers aware they you would do so in a real project because chances are high, that additional functionality would be needed. Same goes for logging class(es). You could even create a transaction class.

Prepared statements

Using prepared statements, even though you seem to have full controll over the query input, shows that you are aware of the security risks and know the appropriate tools and techniques to get around them.

Make use of javas Collection classes

You use simple arrays int balance[] = new int[2];. Don't ever use them unless you have to deal with performance critical issues. Better use ArrayList or other datastructures (or - in this case - create a new class like suggested above)

Stay platform independent

Using a hardcoded line separator like in "Savings Account: " + balance[1] + " USD\n"); may be fine, but you would rather use the one defined in your configuration file. In this case you should also consider using the java line.separator property to get "\r\n", "\r", "\n" or whatever line separator is used on the system your application is running. You don't want your newlines to get eaten when presenting on a computer that is running a different OS than yours.

share|improve this answer

You have too many statics in your code structure. Eliminate all statics that are not effectively global or constant to your application. Specifically, BankAccount.amount and BankAccount.getAmount() must not be static.

Consider using the scoped try-with-resources (since Java 7) to acquire and close your connections, statements, and result sets. In case using Java 7 is not an option: your close method has a copy-paste issue where you check for pStatement but close conn. (The use of finally looks great.)

BankAccount.getBalance() has multiple issues:

  1. It writes to Implementation.result rather than using the result set as a local.
  2. It then loops over these results, but returns only the last row.
  3. You appear to use int[2] as a type; consider making a separate, named type for it.

Here there be nitpicking!

Your error handling is very coarse: print("Error: " + e.getMessage()) does away with stack traces and exception types, both very useful for debugging. Either bubble up the exception, or log the full stack.

checkIfAmountPositive and checkIfCheckingBalancePositive have a similar purpose but a different method signature.

withdrawFromChecking can leave the balance negative. Consider expanding your query to:

UPDATE bank_account
   SET checking_balance = checking_balance - ?
 WHERE id = ?
   AND checking_balance >= ?

int updatedRows = statement.executeUpdate();
if ( updatedRows < 1 ) {
  throw new IllegalArgumentException("Balance too low or no such account.");
}
share|improve this answer

I don't like 2 points:

  1. Your static connection variables:

    // Connection variables
    static Connection conn = null;
    

    If you want to access the connection from everywhere, create a singleton class ConnectionManager.

  2. int balance[] = new int[2];

    You should create a new class with 2 fields checking/saving.

share|improve this answer
  1. If you are using Java 6 or later you will not need:

     Class.forName("com.mysql.jdbc.Driver");
    

    Explanation found here.

  2. It is good to see that you are using prepared statements to protect the database from a SQL attack, but the information is being transmitted in clear text through the network. You should study up on Java Cryptography and add it to the database.

share|improve this answer

Not related to Java, and not a huge problem in a demonstration app, but PLEASE do not use root credentials to authenticate to a database. Ideally you should never need to use the root to connect to a database, except if you're making adjustments to MySQL itself or to the database. Create a separate account with separate credentials and privilege settings to connect. secure this account according to "least access needed" principles. You should also be encrypting that password so you can't just read it if you open the config file.

share|improve this answer

Your formatting around if statements is very erratic and shows a lack of organization in your code (my personal opinion). You have a single statement that is bracketed correctly even though you could disregard the brackets completely and still be relatively safe (although I don't recommend leaving brackets/braces out)

public static int checkIfAmountPositive(int amount) {

    if (amount < 0) {
        throw new IllegalArgumentException("Amount must be positive");
    }
    return amount;
}

But right before that you don't use braces at all

public static void checkIfCheckingBalancePositive(int balance) {

    if (balance < 0)
        throw new IllegalArgumentException(
            "Your checking balance is negative, you can't withdraw.");
}

I didn't read through the entire code but this might be better as a boolean method instead of a void method, or might be better as just another part of the code, it just smells a little bit the way it is written here.

These 2 methods are smelly altogether. They look like the same kind of method that only differs by the property they act upon, but they are not constructed the same way, they have a similar name but aren't constructed the same way, one returns a value if it succeeds and the other doesn't return at all.


Following the if then statement formatting,

In your Close Method you use if statements without braces to enclose try/catch statements, this doesn't look right to me at all

public static void close(Connection conn, Statement statement,
        PreparedStatement pStatement, ResultSet result) {
    if (conn != null)
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }

    if (statement != null)
        try {
            statement.close();
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    if (pStatement != null)
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    if (result != null)
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
}

This is how it should be written (in my opinion)

public static void close(Connection conn, Statement statement,
        PreparedStatement pStatement, ResultSet result) {
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
    if (statement != null) {
        try {
            statement.close();
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
    if (pStatement != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (result != null) {
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

and even this isn't as clean as it should be, I would think that you don't want to try and close the connection twice or close the statement twice, so I would change this to a set of if/then/else statements, like this:

public static void close(Connection conn, Statement statement,
        PreparedStatement pStatement, ResultSet result) {
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    } else if (pStatement != null) {
         try {
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    if (statement != null) {
        try {
            statement.close();
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    } else if (result != null) {
        try {
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

and I only code it as 2 statements because I assume that you want to close the connection and the statement.


In the code that I wrote I am seeing some issues, first thing is that I check for the Connection to be null and if it is then I check for a pStatement and if that isn't null I try to close a connection that I already know is null.

My thought is that you probably don't need to check that Prepared Statement or the ResultSet, you aren't closing them, so why are you checking them here?

One way or the other we move that functionality to another method if it is needed.

so this close method becomes this

public static void close(Connection conn, Statement statement,
        PreparedStatement pStatement, ResultSet result) {
    if (conn != null) {
        try {
            conn.close();
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
    if (statement != null) {
        try {
            statement.close();
        } catch (SQLException e) {
            System.out.println("Error: " + e.getMessage());
        }
    }
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.