Introduction to Relational Databases: Using SQLite or MySQL for Persistent Storage

Creating a Single Database Table for Persistent Storage of Information

Assignment Description Here is a good tutorial for getting started with SQLite For this assignment you can use SQLite OR MySQL. There are numerous videos in YouTube that demonstrate how to do this. Some are better than others. When you find one that is helpful, please post a link to it on the discussion board. We have been working with the front-end (GUI), and the middle (creating and manipulating collections of objects), and now we will add on the back end. The persistent storage of data in your applications. This exercise is to get you comfortable with connecting to a DB, adding, deleting, retrieving data. I encourage you to play with this one; do more than the minimum. SQLite is a very small database. It is included by default in Android and iOS. It is surprisingly powerful for such a small footprint. It can be frustrating to see what’s going on – what is in the DB, did the query work correctly? MySQL is often called a community database. It belongs to Oracle, but they allow anyone to use it for free. The recent versions of the MySQL workbench that allows you to see what’s going on in your database are really very nice – starting to look like the Access front end. Create a connection to a relational database using SQLite or MySQL. Create a single database table to hold information. Let’s make a simple class called Person for this exercise. Person firstName (String) lastName(String) age (int) ssn (long) creditCard (long) Note that once you have the DB created, you don’t want to do this again every time you run your test program. The easiest way to deal with this – for this assignment, is to comment out the code that creates the DB creation and the table creation while you experiment with the following. (Aside: I choose ssn and credit card as fields here so that you might think about the persistent storage of sensitive data. There are some pretty strict laws governing the storage of some data. Please don’t use any actual social security numbers or credit card numbers in this exercise.) Demonstrate the insertion of a record into the database Insert several records. Write a method called insertPerson(Person person) that adds a person object to your database. Create another object of type Person, and demonstrate calling your method, passing the object to the method. Demonstrate the retrieval of information from the database. Use SQL Select statements, to retrieve a particular Person from the database. Write a method called selectPerson that returns a Person object. This method retrieves the data for a Person from the database. We also need to pass a parameter to identify what person. You can use ‘name’ if you like, or if you find it easier to use the database generated ID that’s fine too. This method returns the object that represents that person. This will require that you extract the data that is returned from the database, and call the Person constructor. (Later you will understand that that this is the data-exchange between the relational database and the business layer. ) Write a method called findAllPeople that returns an ArrayList of objects containing all the people in the database. Demonstrate that it is working correctly. Write a method called deletePerson that removes a person from the database. The parameters will be first name and last name. Print out on the console the data from the record that is being deleted. Use your findAllPeople method to verify that that person has been removed from the database. Consider what this method should return. Suppose the person is not found, should the method return that information somehow? Learning Outcomes The student will be able to write code that creates and modifies a relational database. Submission Instructions Please submit one or more .java files as required. Please submit screenshot(s) of the program running on your computer.