001package edu.pdx.cs.joy.phonebill;
002
003import edu.pdx.cs.joy.jdbc.H2DatabaseHelper;
004
005import java.io.File;
006import java.sql.*;
007
008/**
009 * A Data Access Object (DAO) for persisting PhoneBill instances to a database.
010 *
011 * This is a simple example to demonstrate basic JDBC operations.
012 * Students can expand this to include PhoneCall persistence and more
013 * sophisticated query capabilities.
014 */
015public class PhoneBillDAO {
016
017  private final Connection connection;
018
019  /**
020   * Creates a new PhoneBillDAO with the specified database connection.
021   *
022   * @param connection the database connection to use
023   */
024  public PhoneBillDAO(Connection connection) {
025    this.connection = connection;
026  }
027
028  /**
029   * Creates the customers table in the database.
030   *
031   * @param connection the database connection to use
032   * @throws SQLException if a database error occurs
033   */
034  public static void createTable(Connection connection) throws SQLException {
035    String createTableSQL =
036      "CREATE TABLE customers (" +
037      "  name VARCHAR(255) PRIMARY KEY" +
038      ")";
039
040    try (Statement statement = connection.createStatement()) {
041      statement.execute(createTableSQL);
042    }
043  }
044
045  /**
046   * Saves a PhoneBill to the database.
047   *
048   * @param bill the phone bill to save
049   * @throws SQLException if a database error occurs
050   */
051  public void save(PhoneBill bill) throws SQLException {
052    String insertSQL = "INSERT INTO customers (name) VALUES (?)";
053
054    try (PreparedStatement statement = connection.prepareStatement(insertSQL)) {
055      statement.setString(1, bill.getCustomer());
056      statement.executeUpdate();
057    }
058  }
059
060  /**
061   * Finds a PhoneBill by customer name.
062   *
063   * @param customerName the customer name to search for
064   * @return the PhoneBill for the customer, or null if not found
065   * @throws SQLException if a database error occurs
066   */
067  public PhoneBill findByCustomer(String customerName) throws SQLException {
068    String selectSQL = "SELECT name FROM customers WHERE name = ?";
069
070    try (PreparedStatement statement = connection.prepareStatement(selectSQL)) {
071      statement.setString(1, customerName);
072
073      try (ResultSet resultSet = statement.executeQuery()) {
074        if (resultSet.next()) {
075          String name = resultSet.getString("name");
076          return new PhoneBill(name);
077        }
078      }
079    }
080
081    return null;
082  }
083
084  public static void main(String[] args) throws SQLException {
085    if (args.length < 2) {
086      System.err.println("Usage: java PhoneBillDAO <db-file> <customer-name>");
087      return;
088    }
089
090    String dbFile = args[0];
091    String customerName = args[1];
092    try (Connection connection = H2DatabaseHelper.createFileBasedConnection(new File(dbFile))) {
093      PhoneBillDAO dao = new PhoneBillDAO(connection);
094      PhoneBillDAO.createTable(connection);
095
096      PhoneBill bill = new PhoneBill(customerName);
097      dao.save(bill);
098
099      PhoneBill retrievedBill = dao.findByCustomer(customerName);
100      if (retrievedBill != null) {
101        System.out.println("Retrieved PhoneBill for customer: " + retrievedBill.getCustomer());
102      } else {
103        System.out.println("No PhoneBill found for customer: " + customerName);
104      }
105    }
106  }
107}
108