Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- package edu.pcc.cis233j.A8;
- import java.sql.*;
- import java.util.ArrayList;
- import java.util.List;
- /**
- * Data access class for the FiredUp database.
- * Currently reads only customer information.
- *
- * @author Cara Tang
- * @version 2016.02.02
- */
- public class FiredUpDB {
- private static final String FIREDUP_DB = "jdbc:sqlite:FiredUp.db";
- //private static final String USERNAME = "275student";
- //private static final String PASSWORD = "275student";
- 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<Customer> readCustomers() {
- ArrayList<Customer> 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<Customer> readCustomerBasics() {
- ArrayList<Customer> 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<Customer> 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();
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement