Java -- Working with Databases (classes used: PreparedStatement, Scanner)

Use at least 2 tables in a multi-table SQLite database. (This is provided Below)

Take an input from the user (use the Scanner class) and use the input to build and run a query (use PreparedStatement) against the database. The input must be validated/sanitized appropriately, preventing SQL injection.

Print output that includes data read from the database. Output should include information from at least 2 tables and be in a user-friendly form.

Provided examples for input to be used:

Ask the user which state they want to see customers from, retrieve only the customers from that state, and print the customer details along with email addresses. (CUSTOMER and EMAIL tables involved)

Main:

import java.util.Scanner; public class Main { public static void main(String[] args) { /**  * Add the Scanner Class to ask the user questions  */  Scanner scanner = new Scanner(System.in); System.out.print("Enter the State you wish to see customers from: "); String stateName = scanner.nextLine(); /**  * Create the PreparedStatements below  */    System.out.println("FiredUp Customers:"); FiredUpDB firedUp = new FiredUpDB(); List customers = firedUp.readCustomers(); for (Customer cust : customers) { System.out.println("ID: " + cust.getId() + ", Name: " + cust.getName() + ", City: " + cust.getCity() + ", State: " + cust.getState() + ", Email Address(es): " + cust.getEmailAddresses()); } } } 

Database file: FiredUp.db

FiredUp Customers: ID: 101, Name: David Jones, City: Reno, State: NV, Email Address(es): [] ID: 102, Name: Bruce Leonard, City: Palo Alto, State: CA, Email Address(es): [] ID: 103, Name: Steve Wicks, City: Salem, State: OR, Email Address(es): [] ID: 104, Name: Jenna Tucker, City: Hartford, State: C, Email Address(es): [] ID: 105, Name: Elaine Anderso, City: Portland, State: OR, Email Address(es): [] ID: 106, Name: Bob Blessing, City: Portland, State: OR, Email Address(es): [] ID: 111, Name: James White, City: Beaverto, State: OR, Email Address(es): [jwhite837@aol.com] ID: 112, Name: Wilbur Sargent, City: Klamath Falls, State: OR, Email Address(es): [sarge@hotmail.com] ID: 113, Name: William King, City: Sacramento, State: CA, Email Address(es): [billking@myisp.com] ID: 114, Name: Elizabeth Smith, City: Boise, State: ID, Email Address(es): [lizzie33@aol.com] ID: 115, Name: Mike Gree, City: Eureka, State: CA, Email Address(es): [bigmike@hotmail.com, greenmichael@weyerhauser.com] ID: 116, Name: Karen Black, City: Billings, State: MT, Email Address(es): [kblack@myisp.com] ID: 117, Name: Kurt Smith, City: Bend, State: OR, Email Address(es): [smitty@aol.com] ID: 118, Name: Kara White, City: Pendleton , State: OR, Email Address(es): [krwhite@hotmail.com] ID: 119, Name: Benjamin Rosewood, City: Tukwila, State: WA, Email Address(es): [rosie@myisp.com] ID: 120, Name: Max Wickland, City: Olympia, State: WA, Email Address(es): [mrwickland@aol.com] ID: 121, Name: Donna Penwhite, City: Idaho Falls, State: ID, Email Address(es): [djones222@aol.com, donnajones@compaq.com] ID: 122, Name: Joyce Waters, City: Norfolk, State: VA, Email Address(es): [waterwoman@hotmail.com] ID: 123, Name: Blaine Brow, City: Vancouver, State: BC, Email Address(es): [brownie@myisp.com, blainebrown@ibm.com] ID: 124, Name: Heather Burnford, City: Richmond , State: BC, Email Address(es): [hkburnford@aol.com] ID: 125, Name: Gary Bostwick, City: Helena, State: MT, Email Address(es): [bostwickgary@hotmail.com] ID: 126, Name: Kyle Dustbi, City: Canto, State: OH, Email Address(es): [ktrash@myisp.com] ID: 127, Name: Becky Williams, City: Phoenix, State: AZ, Email Address(es): [beckster@aol.com] ID: 128, Name: Terry Burns, City: Raliegh, State: NC, Email Address(es): [terryburns@hotmail.com] ID: 129, Name: John Coombs, City: San Francisco, State: CA, Email Address(es): [coombs@myisp.com] ID: 130, Name: Orenco Mountain Shop, City: Boulder, State: CO, Email Address(es): [BradleyJ@orencomtn.com, BickerslyW@orencomtn.com] ID: 131, Name: Mid-Town Hiker Supply, City: New York, State: NY, Email Address(es): [mike@midtownhiker.com, joe@midtownhiker.com] ID: 132, Name: Sierra Outfitters, City: Burbank, State: CA, Email Address(es): [sally@sierraoutfitters.com, betty@sierraoutfitter.com] ID: 133, Name: Jerome Bentis, City: Calgary, State: AB, Email Address(es): [jbent@cowboy.com] ID: 134, Name: Sarah Covingto, City: Beddick, State: NS, Email Address(es): [scovington@aol.com] ID: 135, Name: Wilderness Outfitters, City: White Horse, State: YT, Email Address(es): [kelly@whitehorsewilderness.com, peter@whitehorsewilderness.com, kim@whitehorsewilderness.com] ID: 136, Name: Yogi Berra, City: New York, State: NY, Email Address(es): [] ID: 137, Name: Casey Stengal, City: New York, State: NY, Email Address(es): []

Other Java files used along with Main:

FiredUpDB.java

import java.sql.*; import java.util.ArrayList; import java.util.List;  public class FiredUpDB { private static final String FIREDUP_DB = "jdbc:sqlite:FiredUp.db"; private static final String CUSTOMER_SQL = "SELECT CustomerID, Name, City, StateProvince FROM CUSTOMER"; private static final String EMAIL_SQL = "SELECT EmailAddress FROM EMAIL WHERE FK_CustomerID = ?"; /**  * Read all customers from the FiredUp database and return them as a list of Customer objects  * @return a list of customers from the FiredUp database  */   public List readCustomers() { ArrayList customers = readCustomerBasics(); readEmailAddresses(customers); return customers; } /**  * Read from the FiredUp database customers from the given state  * @param state the state of interest (US state or Canadian province)  * @return a list of customers from the given state  */  // Create method readCustomersFromState /**  * @return a connection to the FiredUp database  * @throws SQLException if unable to connect  */  private Connection getConnection() throws SQLException { return DriverManager.getConnection(FIREDUP_DB); } /**  * Read customers from the database, including their basic properties from the CUSTOMER table,  * but not including customer data from related tables such as email addresses or phone numbers  * @return a list of Customer objects  */  private ArrayList readCustomerBasics() { ArrayList customers = new ArrayList<>(); try ( Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(CUSTOMER_SQL); ResultSet rs = stmt.executeQuery() ) { while (rs.next()) { customers.add(new Customer(rs.getInt("CustomerID"), rs.getString("Name"), rs.getString("City"), rs.getString("StateProvince"))); } } catch (SQLException e) { e.printStackTrace(); } return customers; } /**  * Read email addresses from the database for each customer in the given list,  * adding the email addresses found to the corresponding Customer object  * @param customers list of customers whose email addresses should be read  */  private void readEmailAddresses(ArrayList customers) { try ( Connection conn = getConnection(); PreparedStatement stmt = conn.prepareStatement(EMAIL_SQL) ) { for (Customer cust : customers) { stmt.setInt(1, cust.getId()); ResultSet rs = stmt.executeQuery(); while (rs.next()) { cust.addEmailAddress(rs.getString("EmailAddress")); } } } catch (SQLException e) { e.printStackTrace(); } } } 

Customer.java

import java.util.ArrayList;  public class Customer { private int id; private String name; private String city; private String state; private ArrayList emailAddresses; public Customer(int id, String name, String city, String state) { this.id = id; this.name = name; this.city = city; this.state = state; emailAddresses = new ArrayList<>(); } public int getId() { return id; } public String getName() { return name; } public String getCity() { return city; } public String getState() { return state; } /**  * Add an email address to this customer's email addresses  * @param email an email address belonging to this customer  */  public void addEmailAddress(String email) { emailAddresses.add(email); } /**  * @return the list of email addresses belonging to this customer  */  public ArrayList getEmailAddresses() { return emailAddresses; } } 

Final Note: Output of running Main should ask the user to input a State (two letters as indicated by the database) and output the customer's name and email address.