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.
