DBManager

package com.unibet.worktest.processing;
import java.math.BigDecimal;
import java.sql.*;

import com.unibet.worktest.bank.Money;
public class DBManager {
        public static Connection connection;
        public DBManager()
                throws Exception {
            Class.forName("org.h2.Driver");
            connection= DriverManager.
                getConnection("jdbc:h2:mem:test;mode=MySQL", "sa", "");
                //getConnection("jdbc:h2:~/test", "sa", "");
            // Create the table
            String sql = "CREATE TABLE ACCOUNTS " +
                    "(accountref VARCHAR(20) , " +
                    " balance DECIMAL(20, 2))";
            createTable(sql);
            sql = "CREATE TABLE TRANSACTIONS " +
                    "(txref VARCHAR(20), " +
                    " accref VARCHAR(20), " +
                    " type VARCHAR(20), "+
                    " amount DECIMAL(20, 2), "+
                    " date TIMESTAMP)";
            createTable(sql);
        }

        public void createTable(String sql){
            Statement stmt = null;

            try {
                stmt = connection.createStatement();
                stmt.executeUpdate(sql);
                System.out.println("Created table in given database...");
            } catch (SQLException se) {
                //Handle errors for JDBC
                se.printStackTrace();
            } catch (Exception e) {
                //Handle errors for Class.forName
                e.printStackTrace();
            }
        }

        public void insertAccount(String accountRef, BigDecimal balance){
            ResultSet rs;
            Statement stmt = null;
             try {
                 stmt = connection.createStatement();
                 String sql = String.format( "INSERT INTO ACCOUNTS " + "VALUES ('%s' , %s) ", accountRef,balance);

                 stmt.executeUpdate(sql);

             } catch (SQLException se) {
                 //Handle errors for JDBC
                 se.printStackTrace();
             } catch (Exception e) {
                 //Handle errors for Class.forName
                 e.printStackTrace();
             }
        }


        public ResultSet retriveBalance(String accountRef){
            ResultSet rs=null;
            Statement stmt = null;
            try {
                stmt = connection.createStatement();
                 String sql = String.format( "SELECT * FROM ACCOUNTS WHERE accountref= '%s'",accountRef);
                 rs = stmt.executeQuery(sql);

            } catch (SQLException se) {
                se.printStackTrace();
            } catch (Exception e) {
                 //Handle errors for Class.forName
                 e.printStackTrace();
             }
            return rs;
        }

        public void changeBalance(String accountRef, BigDecimal amount){
            Statement stmt = null;
             try {
                 stmt = connection.createStatement();
                 BigDecimal currentBal=new BigDecimal(0.0);
                 ResultSet rs=retriveBalance( accountRef);
                 while (rs.next()) {
                    currentBal= new BigDecimal(rs.getString("balance"));
                 }
                 String sql = String.format( "UPDATE ACCOUNTS SET balance=%s WHERE accountref='%s' ",currentBal.add(amount),accountRef);
                 stmt.executeUpdate(sql);

             } catch (SQLException se) {
                 //Handle errors for JDBC
                 se.printStackTrace();
             } catch (Exception e) {
                 //Handle errors for Class.forName
                 e.printStackTrace();
             }
        }
        public void insertTransaction(String transactionRef, String accountRef, String type, BigDecimal amount){
            Statement stmt = null;
             try {
                 stmt = connection.createStatement();

                 String sql = String.format( "INSERT INTO TRANSACTIONS " + "VALUES ('%s', '%s', '%S', %s , CURRENT_TIMESTAMP())", transactionRef, accountRef, type, amount.toString());
                 stmt.executeUpdate(sql);

             } catch (SQLException se) {
                 //Handle errors for JDBC
                 se.printStackTrace();
             } catch (Exception e) {
                 //Handle errors for Class.forName
                 e.printStackTrace();
             }
        }

        public ResultSet getAccountLegs(String accountRef){
            ResultSet rs=null;
            Statement stmt = null;
            try {
                stmt = connection.createStatement();
                 String sql = String.format( "SELECT * FROM TRANSACTIONS WHERE accref= '%s'",accountRef);
                 rs = stmt.executeQuery(sql);
                return rs;
            } catch (SQLException se) {
                se.printStackTrace();
            } catch (Exception e) {
                 //Handle errors for Class.forName
                 e.printStackTrace();
             }
            return rs;
        }
        public ResultSet getTransactionLegs(String txRef){
            ResultSet rs=null;
            Statement stmt = null;
            try {
                stmt = connection.createStatement();
                 String sql = String.format( "SELECT * FROM TRANSACTIONS WHERE txRef= '%s'",txRef);
                 rs = stmt.executeQuery(sql);
                return rs;
            } catch (SQLException se) {
                se.printStackTrace();
            } catch (Exception e) {
                 //Handle errors for Class.forName
                 e.printStackTrace();
             }
            return rs;
        }

}