Question

Complete a Java program named ARMgr that maintains customer accounts receivable in a database.

The code to initialize the CustomerAccountsDB database table and add a set of customer accounts is provided.

Finish the code in these 3 methods in CustomerAccountDB.java to update or query the database:

-purchase(double amountOfPurchase)

-payment(double amountOfPayment)

-getCustomerName()

Hint: For getCustomerName(), look at the getAccountBalance() method to see an example of querying data from the database. For the purchase() and payment() methods, look at the addCustomerAccount() method to see an example of updating information in the database.

Setting Up the Project:
Download the derby.jar file and save it where Eclipse can use it, such as on your Desktop.

Download the Homework5Files.zip file containing these files:

database.properties	Properties file for the database connection
CustomerAccountDB.java	Class to update and query CustomerAccountdb rows
CustomerAccountsDB.java	Class to initialize the CustomerAccountDB table and operate on all customer accounts
ARMgr.java	Main method with user interface
SimpleDataSource.java	SimpleDataSource class to simplify database connections

To run the ARMgr Java program in Eclipse, create a new project, copy all of the Java files into the src folder of your project, and copy the database.properties file into the project’s folder. Open the Project Properties dialog and select Java Build Paths, then click the Libraries tab. Click the Add JARs button, use the Open dialog to find the file derby.jar, and click the Open button. Click OK to save these changes to the Project’s properties.
When you first run the program, the database is empty! Use the (I)nitialize option to setup the accounts receivable database with the initial set of customer accounts.

Here is a sample run of the program:

I) Initialize database  A)ddCustomerAccount  P)urchase  paY)ment  C)heckCustomerAccount  L)istCustomers  Q)uit

I

Enter 'YES' if you wish to reinitialize the customer account list:

YES

Notice: inserted customer account 1001 Apple County Grocery 24.95

Notice: inserted customer account 1002 Uptown Grill 29.95

Notice: inserted customer account 1003 Skyway Shop 19.99

Notice: inserted customer account 1004 River City Music 2.95

I) Initialize database  A)ddCustomerAccount  P)urchase  paY)ment  C)heckCustomerAccount  L)istCustomers  Q)uit

A

Enter new customer account numer: 1005

Enter new customer name: Eastside Deli

Enter new customer account balance: 205.95

I) Initialize database  A)ddCustomerAccount  P)urchase  paY)ment  C)heckCustomerAccount  L)istCustomers  Q)uit

L

Customer Customer Account: 1001 Apple County Grocery 24.95

Customer Customer Account: 1002 Uptown Grill 29.95

Customer Customer Account: 1003 Skyway Shop 19.99

Customer Customer Account: 1004 River City Music 2.95

Customer Customer Account: 1005 Eastside Deli 205.95

I) Initialize database  A)ddCustomerAccount  P)urchase  paY)ment  C)heckCustomerAccount  L)istCustomers  Q)uit

P

Enter customer account number for purchase: 1004

Customer 1004: Customer Account: 1004 River City Music 2.95

Enter amount of purchase: 210.55

Customer Account 1004 now has balance 213.50.

I) Initialize database  A)ddCustomerAccount  P)urchase  paY)ment  C)heckCustomerAccount  L)istCustomers  Q)uit

Y

Enter customer account number for payment: 1003

Customer 1003: Customer Account: 1003 Skyway Shop 19.99

Enter amount of payment: 19.99

Customer 1003 now has balance 0.00.

I) Initialize database  A)ddCustomerAccount  P)urchase  paY)ment  C)heckCustomerAccount  L)istCustomers  Q)uit

C

Enter customer account number to check: 1001

Customer: 1001 Customer Account: 1001 Apple County Grocery 24.95

I) Initialize database  A)ddCustomerAccount  P)urchase  paY)ment  C)heckCustomerAccount  L)istCustomers  Q)uit

q

When you run the program, if you get the error:

Exception in thread "main" java.io.FileNotFoundException: database.properties

Then make sure you have the database.properties file copied into your project folder, and make sure you have the database.properties filename specified correctly in the Run/Debug Settings.

Java Files for this are below:

ARMgr:

package Homework5;
import java.io.IOException;
import java.sql.SQLException;
import java.util.Scanner;

public class ARMgr
{
   public static void main(String[] args) throws ClassNotFoundException, IOException {
       SimpleDataSource.init("database.properties");
       Scanner in = new Scanner(System.in);
       CustomerAccountsDB myAccounts = new CustomerAccountsDB();
       boolean done = false;
       while (!done)
       {
           try
           {
               System.out.println("I) Initialize database A)ddCustomerAccount P)urchase paY)ment C)heckCustomerAccount L)istCustomers Q)uit");
               String input = in.nextLine().toUpperCase();
               if (input.equals("I"))
               {
                   System.out.println("Enter 'YES' if you wish to reinitialize the customer account list: ");
                   String answer = in.nextLine();
                   if (answer.equalsIgnoreCase("YES"))
                       myAccounts.initialize();
                   else
                       System.out.println("OK, existing data preserved");
               }
               else if (input.equals("A"))
               {
                   int customerNumber = promptForInt(in, "Enter new customer account numer: ");
                   if (myAccounts.find(customerNumber) != null)
                   {
                       System.out.printf("Error: customerAccount %d already exists.\n", customerNumber);
                   }
                   else
                   {
                       String name = promptForWord(in, "Enter new customer name: ");
                       double bal = promptForDouble(in, "Enter new customer account balance: ");
                       CustomerAccountDB a = new CustomerAccountDB(customerNumber);
                       a.addCustomerAccount(name, bal);
                   }
               }
               else if (input.equals("P"))
               {
                   int customerNumber = promptForInt(in, "Enter customer account number for purchase: ");
                   CustomerAccountDB a = myAccounts.find(customerNumber);
                   if (a == null)
                   {
                       System.out.printf("Error: customer account %s does not exist.\n", customerNumber);
                   }
                   else
                   {
                       System.out.printf("Customer %s: %s\n", customerNumber, a.toString());
                       double purchaseAmount = promptForDouble(in, "Enter amount of purchase: ");
                       a.purchase(purchaseAmount);
                       System.out.printf("Account %s now has balance %.2f.\n", customerNumber, a.getAccountBalance());
                   }
               }
               else if (input.equals("Y"))
               {
                   int customerNumber = promptForInt(in, "Enter customer account number for payment: ");
                   CustomerAccountDB a = myAccounts.find(customerNumber);
                   if (a == null)
                   {
                       System.out.printf("Error: customer account %s does not exist.\n", customerNumber);
                   }
                   else
                   {
                       System.out.printf("Customer %s: %s\n", customerNumber, a.toString());
                       double paymentAmount = promptForDouble(in, "Enter amount of payment: ");
                       if (a.getAccountBalance() < paymentAmount)
                       {
                           System.out.printf("Error: Customer %s balance %d is less than payment %.2f\n",
                                   customerNumber, a.getAccountBalance(), paymentAmount);
                       }
                       else
                       {
                           a.payment(paymentAmount);
                           System.out.printf("Customer %s now has balance %.2f.\n", customerNumber, a.getAccountBalance());
                       }
                   }
               }
               else if (input.equals("C"))
               {
                   int customerNumber = promptForInt(in, "Enter customer account number to check: ");
                   CustomerAccountDB a = myAccounts.find(customerNumber);
                   if (a == null)
                   {
                       System.out.printf("Error: customer account %d does not exist.\n", customerNumber);
                   }
                   else
                   {
                       System.out.printf("Customer: %d %s\n", customerNumber, a.toString());
                   }
               }
               else if (input.equals("L"))
               {
                   for (CustomerAccountDB p : myAccounts.getAllCustomerAccounts())
                   {
                       System.out.printf("Customer %s\n", p.toString());
                   }
               }
               else if (input.equals("Q"))
               {
                   done = true;
               }
           }
           catch (SQLException e)
           {
               System.out.printf("Database exception: %s\n", e.getMessage());
               e.printStackTrace();
           }
       }
   }
  
   /**
   * Ask the user for an integer input. Repeat until successful.
   * @param in Scanner for reading input
   * @param prompt String to show to user
   * @return value entered by user
   */
   public static int promptForInt(Scanner in, String prompt)
   {
       int result = 0;
       boolean done = false;
       while (!done)
       {
           System.out.print(prompt);
           String inputStr = in.nextLine().trim();
           try
           {
               result = Integer.parseInt(inputStr);
               done = true;
           }
           catch (NumberFormatException e)
           {
               System.out.printf("Error: '%s' was not recognized as an integer. Please try again.\n", inputStr);
           }
       }
       return result;
   }

  
   /**
   * Ask the user for a double precision number. Repeat until successful.
   * @param in Scanner for reading input
   * @param prompt String to show to user
   * @return value entered by user
   */
   public static double promptForDouble(Scanner in, String prompt)
   {
       double result = 0;
       boolean done = false;
       while (!done)
       {
           System.out.print(prompt);
           String inputStr = in.nextLine().trim();
           try
           {
               result = Double.parseDouble(inputStr);
               done = true;
           }
           catch (NumberFormatException e)
           {
               System.out.printf("Error: '%s' was not recognized as a double. Please try again.\n", inputStr);
           }
       }
       return result;
   }

   /**
   * Ask the user for a single word as a string. Repeat until successful.
   * @param in Scanner for reading input
   * @param prompt String to show to user
   * @return value entered by user
   */
   public static String promptForWord(Scanner in, String prompt)
   {
       System.out.print(prompt);
       return in.nextLine().trim();
   }
}

CustomerAccountDB:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
A customer account has an account number, customer name, and account balance.
This implementation uses a database table to contain its data.
*/
public class CustomerAccountDB
{
   private int accountNumber;
  
   /**
   * Constructs a customer account object for operations on the CustomerAccountsDB table.
   * @param _accountNumber the customer number
   */
   public CustomerAccountDB(int _accountNumber)
   {
       accountNumber = _accountNumber;
   }
  
   /**
   * Add the data for a customer account to the database.
   * @param name - customer name
   * @param balance - account balance
   * @throws SQLException - on any database error
   */
   public void addCustomerAccount(String name, double balance) throws SQLException
   {
       try (Connection conn = SimpleDataSource.getConnection())
       {
           try (PreparedStatement stat = conn.prepareStatement("INSERT INTO CustomerAccountsDB (AccountNumber, CustomerName, AccountBalance) VALUES (?, ?, ?)"))
           {
               stat.setInt(1, accountNumber);
               stat.setString(2, name);
               stat.setDouble(3, balance);
               stat.execute();
           }
       }
   }

   /**
   * Add the amount of a customer's purchase to the account receivable.
   * @param amount - amount purchased by customer
   * @throws SQLException - on any database error
   */
   public void purchase(double amount)
           throws SQLException
   {
       // Update the CustomerAccountsDB table's account balance for this
       // object's account number using the purchase amount.

       // TODO: Add the missing code to update the AccountBalance
       // for this AccountNumber.

   }

   /**
   * Apply a payment made by a customer.
   * @param amount - Amount of payment
   * @throws SQLException - on any database error
   */
   public void payment(double amount)
           throws SQLException
   {
       // Update the CustomerAccountsDB table's account balance for this
       // object's account number using the payment amount.

       // TODO: Add the missing code to update the AccountBalance
       // for this AccountNumber.
   }

   /**
   * Gets the name for this customer's account.
   * @return the customer name
   */
   public String getCustomerName()
           throws SQLException
   {   
       // Query the CustomerAccountsDB table for the name
       // for this object's account number.

       // TODO: Add the missing code to query the CustomerName
       // for this AccountNumber.

   }

   /**
   * Gets the balance of this customer's account
   * @return the account balance
   * @throws SQLException - on any database error
   */
   public double getAccountBalance()
           throws SQLException
   {
       // Query the CustomerAccountsDB table for the balance
       // of this object's AccountNumber.
       try (Connection conn = SimpleDataSource.getConnection())
       {
           try (PreparedStatement stat = conn.prepareStatement("SELECT AccountBalance FROM CustomerAccountsDB WHERE AccountNumber = ?"))
           {
               stat.setInt(1, accountNumber);
               ResultSet result = stat.executeQuery();
               // There should be only one row in the result set. Advance to
               // the first row and get the balance.
               result.next();
               // The balance is in the first column of this first row.
               return result.getDouble(1);
           }
       }
   }
  
   /**
   * Gets the account number for this account
   * @return the account number
   */
   public int getAccountNumber()
   {
       // We keep the account number as the key in the object.
       return accountNumber;
   }

   /**
   * Return a string describing this customer account.
   */
   public String toString()
   {
       String result;
       try
       {
           // Query the CustomerAccountsDB table for the name and balance
           // of this object's account number.
           try (Connection conn = SimpleDataSource.getConnection())
           {
               try (PreparedStatement stat = conn.prepareStatement("SELECT CustomerName, AccountBalance FROM CustomerAccountsDB WHERE AccountNumber = ?"))
               {
                   stat.setInt(1, accountNumber);
                   ResultSet rs = stat.executeQuery();
                   // There should be only one row in the result set. Advance to
                   // the first row and get the name and balance.
                   rs.next();
                   result = String.format("Customer Account: %d %s %.2f",
                           accountNumber, rs.getString(1), rs.getDouble(2));
               }
           }
       }
       catch (SQLException e)
       {
           result = "SQLException while getting customer account info: " + e.getMessage();
       }
       return result;
   }
}

CustomerAccountsDB:

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

/**
* Accounts Receivable manager for multiple customer accounts.
* Uses a JDBC database interface to manage the data.
*/
public class CustomerAccountsDB
{
   /**
   * Create or re-create the CustomerAccountsDB table in the database with
   * some default data.
   * @param conn - Database connection
   * @throws SQLException - on any database error
   */
   public void initialize() throws SQLException
   {
       try (Connection conn = SimpleDataSource.getConnection())
       {
           try (Statement stat = conn.createStatement())
           {
               try
               {
                   // This will fail if the table doesn't exist. That is OK.
                   stat.execute("DROP TABLE CustomerAccountsDB");
               }
               catch (SQLException e)
               {
                   System.out.println("Notice: Exception during DROP TABLE CustomerAccountsDB: " + e.getMessage() + " (This is expected when the database is empty)");
               }
               // If an execption occurs after this point, allow it to be thrown.
               // CustomerAccountsDB table: AccountNumber, CustomerName, AccountBalance
               stat.execute("CREATE TABLE CustomerAccountsDB (AccountNumber INT, CustomerName VARCHAR(40), AccountBalance DECIMAL(10,2))");
               // Add default list of customer accounts using parallel arrays.
               int accountNumbers[] = {1001, 1002, 1003, 1004};
               String customerNames[] = {"Apple County Grocery", "Uptown Grill", "Skyway Shop", "River City Music"};
               double accountBalances[] = {24.95, 29.95, 19.99, 2.95};
               for (int i = 0; i < accountNumbers.length; i++)
               {
                   CustomerAccountDB a = new CustomerAccountDB(accountNumbers[i]);
                   a.addCustomerAccount(customerNames[i], accountBalances[i]);
                   System.out.printf("Notice: inserted customer account %d %s %.2f\n",
                           accountNumbers[i], customerNames[i], accountBalances[i]);
               }
           }
       }
   }

   /**
   * Obtain an array list of all the CustomerAccount objects in the database.
   * @return arraylist of CustomerAccountDB
   * @throws SQLException - on any database error
   */
   public ArrayList<CustomerAccountDB> getAllCustomerAccounts() throws SQLException
   {
       ArrayList<CustomerAccountDB> customerAccounts = new ArrayList<CustomerAccountDB>();
       try (Connection conn = SimpleDataSource.getConnection())
       {
           try (Statement stat = conn.createStatement())
           {
               // CustomerAccountsDB table: AccountNumber, CustomerName, AccountBalance
               ResultSet result = stat.executeQuery("SELECT AccountNumber FROM CustomerAccountsDB");
               while (result.next())
               {
                   CustomerAccountDB a = new CustomerAccountDB(result.getInt(1));
                   customerAccounts.add(a);
               }
           }
       }
       return customerAccounts;
   }
  
   /**
   * Finds a customer account with a given number or null if not found.
   * @param accountNumber the number to find
   * @return the customer account with the given code
   * @throws SQLException - on any database error
   */
   public CustomerAccountDB find(int accountNumber) throws SQLException
   {
       try (Connection conn = SimpleDataSource.getConnection())
       {
           // Does the customer account exist?
           try (PreparedStatement stat = conn.prepareStatement("SELECT COUNT(*) FROM CustomerAccountsDB WHERE AccountNumber = ?"))
           {
               stat.setInt(1, accountNumber);
               ResultSet result = stat.executeQuery();
               // There must be one row returned.
               result.next();
               if (result.getInt(1) == 0)
               {
                   return null;
               }
               // account exists: return it.
               CustomerAccountDB a = new CustomerAccountDB(accountNumber);
               return a;
           }
       }
   }
  
   /**
   * Gets the sum of the account balances.
   * @return the sum of the balances
   * @throws SQLException - on any database error
   */
   public double getTotalAccountBalance() throws SQLException
   {
       double total = 0;
       ArrayList<CustomerAccountDB> accounts = getAllCustomerAccounts();
       for (CustomerAccountDB a : accounts)
       {
           total = total + a.getAccountBalance();
       }
       return total;
   }

   /**
   * Return a string that describes all the accounts in the accounts receivable.
   */
   public String toString()
   {
       StringBuffer sb = new StringBuffer();
       ArrayList<CustomerAccountDB> accounts;
       try
       {
           accounts = getAllCustomerAccounts();
           for (CustomerAccountDB a : accounts)
           {
               sb.append(a.toString());
           }
       }
       catch (SQLException e)
       {
           sb.append("SQLException occurred: " + e.getMessage());
       }
       return sb.toString();
   }
}

SimpleDataSource:

package Homework5;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Properties;

/**
A simple data source for getting database connections.
*/
public class SimpleDataSource
{
private static String url;
private static String username;
private static String password;

/**
Initializes the data source.
@param fileName the name of the property file that
contains the database driver, URL, username, and password
*/
public static void init(String fileName)
throws IOException, ClassNotFoundException
{
Properties props = new Properties();
FileInputStream in = new FileInputStream(fileName);
props.load(in);

String driver = props.getProperty("jdbc.driver");
url = props.getProperty("jdbc.url");
username = props.getProperty("jdbc.username");
if (username == null) username = "";
password = props.getProperty("jdbc.password");
if (password == null) password = "";
if (driver != null)
Class.forName(driver);
}

/**
Gets a connection to the database.
@return the database connection
*/
public static Connection getConnection() throws SQLException
{
return DriverManager.getConnection(url, username, password);
}
}

Answer 1

The hint itself suggests so much things and it becomes easy for you to complete the code for the 3 methods. So, the method code and the explanation is as below: (You need not to worry about writing the code for database connection and all steps as it is written in separate file itself. So, this becomes so easy to write the methods).

getCustomerName() :

public String getCustomerName()
throws SQLException
{   
// Query the CustomerAccountsDB table for the name
// for this object's account number.
// TODO: Add the missing code to query the CustomerName
// for this AccountNumber.
try (Connection conn = SimpleDataSource.getConnection())
{
try (PreparedStatement stat = conn.prepareStatement("SELECT CustomerName FROM CustomerAccountsDB WHERE AccountNumber = ?"))
{
stat.setInt(1, accountNumber);
ResultSet result = stat.executeQuery();
// There should be only one row in the result set. Advance to
// the first row and get the name.
result.next();
// There is only 1 entry in the output. So, the name is in the first column of this first row.
return result.getString("CustomerName"); // or return result.getString(1) also works the same.
}
}
}

If you see the getAccountBalance() method, you will see a prepared statement is used which queries the CustomerAccountsDB table for AccountBalance column. You need the name only, so just by replacing AccountBalance with CustomerName; you will get the desired thing. And after that the string is get from the resultset and returned.

purchase() :

Here, if you go through the sample run of the program, you will see that whenever you are calling purchase method, the purchase amount is added to the original balance. Like in above output it became 213.50 after purchase of amount 210.55 for 1004. So, in purchase() method, we need to get original balance first, and for that we don't require another SQL statement execution, we can get it by the method getAccountBalance() of the same class.

So, after getting original balance, we need to add amount in it and then update the table.

So, the update query to modify balance as per account number is : UPDATE CustomerAccountsDB SET AccountBalance = balance WHERE AccountNumber = accountNumber. To prevent SQL injection kind of problems, we have not directly used the variable values, instead we have used Named Parameters using ?s which makes sure that only the valid input is given in the query. And by executing the query, we get the account balance updated.

public void purchase(double amount)
throws SQLException
{
// Update the CustomerAccountsDB table's account balance for this
// object's account number using the purchase amount.
// TODO: Add the missing code to update the AccountBalance
// for this AccountNumber.

try (Connection conn = SimpleDataSource.getConnection())
{
double balance = getAccountBalance();
  
balance = balance + amount;
  
try (PreparedStatement stat = conn.prepareStatement("UPDATE CustomerAccountsDB SET AccountBalance = ? WHERE AccountNumber = ?"))
{
stat.setDouble(1, balance);
stat.setInt(2, accountNumber);
stat.execute();
}
}
}

payment() :

Here, if you go through the sample run of the program, you will see that whenever you are calling payment method, the payment amount is subtracted from the original balance. Like in above output it became 0.00 after payment of amount 19.99 for 1003. So, in payment() method, we need to get original balance first, and for that we don't require another SQL statement execution, we can get it by the method getAccountBalance() of the same class.

So, after getting original balance, we need to subtract the amount from it and then update the table.

So, the update query and all the other procedures are same as purchase() method, just a difference is there that in purchase(), it was balance = balance + amount and in payment(), it is balance = balance - amount.

public void payment(double amount)
throws SQLException
{
// Update the CustomerAccountsDB table's account balance for this
// object's account number using the payment amount.
// TODO: Add the missing code to update the AccountBalance
// for this AccountNumber.
  
try (Connection conn = SimpleDataSource.getConnection())
{
double balance = getAccountBalance();
  
balance = balance - amount;
  
try (PreparedStatement stat = conn.prepareStatement("UPDATE CustomerAccountsDB SET AccountBalance = ? WHERE AccountNumber = ?"))
{
stat.setDouble(1, balance);
stat.setInt(2, accountNumber);
stat.execute();
}
}
}

So, these are the implementations required. Do comment if there is any query. Thank you. :)