For SharpCoder

Databases

In this exercise, you will add database functionality to the application we built in

Homework #3(code.zip that you did for me on the previous assignment).

Preliminaries For this exercise, you will use a SQLite database (https://www.sqlite.org/) rather than the Derby database used in the book. If you do not already have SQLite on your machine, you will need to install it. The database is free and works on all major operating systems. You can download the JDBC driver at https://bitbucket.org/xerial/sqlite-jdbc/downloads. Instructions for the driver are at

https://bitbucket.org/xerial/sqlite-jdbc. Note that you do not use cre-ate=true in

the driver string since SQLite automatically creates the database if it does not exist.

Part 1 Create an instance function in the InventoryManager class called

getDatabaseConnection. This function should • Create a new database if it does not exist. • Create the tables if they do not already exist. You will need to do some research to

determine the best way to do this. There are a few different approaches. Google is

your friend. You will need two tables - one for household items and the other for food

items. It is up to you to choose appropriate data types and column names. • Set the value of a Connection field in the InventoryManager object. Your

other methods should use this connection object.

◦ Specifically, you should have this in your InventoryManager:

private Connection _conn;

Create a second function called populateDatabase that is called only if the

database has not previously been populated with data. Hint: If you had to create the

tables in the step above, you can be sure that the tables need to be populated! This

function should create records in the database for food and household items. Use

the items from the example inventory file.

Verifying Your Data Visual tools are great for looking at your data to insure that your code is working correctly.

Eclipse has a plugin for SQLite that you can install. You might also one of these tools: • https://github.com/sqlitebrowser/sqlitebrowser/releases • http://saxmike.com/MySoftware/MySoftware.asp?Menu=MYSOFTWARE

- 1 -

Part 2 Complete the method load loadInventory() in the InventoryManager (there is a

comment above it saying that it loads data from the database). In your main() function,

replace the call the loadInventory("inventory.txt") with this call so that the

data is loaded from the database instead of the file. If your code is correct, the output

should be exactly the same as it was when you loaded the data from a file. - 2 -