Complete a Java program named ARMgr that maintains customer

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: https://drive.google.com/drive/folders/0B41Z5suN4j28Z3gyMTlMNVB5ZmM?usp=sharing

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

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

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

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

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

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

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

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.

If you receive the error:

Exception in thread \"main\" java.sql.SQLException: No suitable driver

Make sure that derby.jar has been added to the Libraries list on the Project Properties’ Java Build Paths page.

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

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 = ?\"))