JDBC & the Database

package lms.dao;

import lms.exception.DataAccessException;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Database - the ONE place that knows how to reach the database (Week 11).
 *
 * Default is SQLite: a single file "lms.db" next to the program. No server
 * to install. The URL can be overridden with  -Dlms.db.url=...  which is how
 * the tests use a throw-away in-memory database.
 *
 * Tables:
 *   items   (id PK, type, title, detail)
 *   members (id PK, name, type)
 *   loans   (item_id FK, member_id FK, borrowed_at, returned_at NULL until returned)
 */
public final class Database {

    public static final String DEFAULT_URL = "jdbc:sqlite:lms.db";

    private Database() { }

    public static String url() {
        return System.getProperty("lms.db.url", DEFAULT_URL);
    }

    /** Opens a NEW connection. Callers close it with try-with-resources. */
    public static Connection getConnection() throws SQLException {
        Connection con = DriverManager.getConnection(url());
        if (url().startsWith("jdbc:sqlite:")) {
            // SQLite ignores foreign keys unless you switch them on per connection.
            try (Statement st = con.createStatement()) {
                st.execute("PRAGMA foreign_keys = ON");
            }
        }
        return con;
    }

    /** Creates the tables if they do not exist yet. Safe to call every start-up. */
    public static void init() {
        String[] ddl = {
            "CREATE TABLE IF NOT EXISTS items ("
                + " id VARCHAR(20) PRIMARY KEY,"
                + " type VARCHAR(10) NOT NULL,"
                + " title VARCHAR(200) NOT NULL,"
                + " detail VARCHAR(200) NOT NULL)",
            "CREATE TABLE IF NOT EXISTS members ("
                + " id VARCHAR(20) PRIMARY KEY,"
                + " name VARCHAR(100) NOT NULL,"
                + " type VARCHAR(10) NOT NULL)",
            "CREATE TABLE IF NOT EXISTS loans ("
                + " item_id VARCHAR(20) NOT NULL,"
                + " member_id VARCHAR(20) NOT NULL,"
                + " borrowed_at VARCHAR(30) NOT NULL,"
                + " returned_at VARCHAR(30),"
                + " PRIMARY KEY (item_id, borrowed_at),"
                + " FOREIGN KEY (item_id) REFERENCES items(id),"
                + " FOREIGN KEY (member_id) REFERENCES members(id))"
        };
        try (Connection con = getConnection(); Statement st = con.createStatement()) {
            for (String sql : ddl) st.execute(sql);
        } catch (SQLException e) {
            throw new DataAccessException("Could not create the database tables", e);
        }
    }
}

package lms.exception;

/**
 * UNCHECKED wrapper for java.sql.SQLException (Week 11).
 *
 * SQLException is a low-level, checked, database-specific exception. If it
 * leaked up to the GUI, every layer would need to know about SQL. Instead
 * the DAO catches it and rethrows this one, with a friendly message.
 */
public class DataAccessException extends RuntimeException {
    public DataAccessException(String message, Throwable cause) {
        super(message, cause);
    }
}

package lms.dao;

import lms.exception.DataAccessException;
import lms.model.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * ItemDAO - Data Access Object for items (Week 11).
 *
 * A DAO hides ALL the SQL for one kind of thing. Nothing outside lms.dao
 * writes SQL. It maps between rows and OBJECTS: the "type" column decides
 * whether a row becomes a Book, a DVD or a Magazine.
 */
public class ItemDAO {

    public void insert(LibraryItem item) {
        String sql = "INSERT INTO items (id, type, title, detail) VALUES (?, ?, ?, ?)";
        try (Connection con = Database.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, item.getId());
            ps.setString(2, item.getClass().getSimpleName());
            ps.setString(3, item.getTitle());
            ps.setString(4, detailOf(item));
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException("Could not save item " + item.getId(), e);
        }
    }

    public List<LibraryItem> findAll() {
        String sql = "SELECT id, type, title, detail FROM items ORDER BY id";
        List<LibraryItem> result = new ArrayList<>();
        try (Connection con = Database.getConnection();
             PreparedStatement ps = con.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                result.add(toItem(rs.getString("id"), rs.getString("type"),
                        rs.getString("title"), rs.getString("detail")));
            }
        } catch (SQLException e) {
            throw new DataAccessException("Could not read items", e);
        }
        return result;
    }

    /** Row -> object. This is the "factory" step. */
    private LibraryItem toItem(String id, String type, String title, String detail) {
        return switch (type) {
            case "Book" -> new Book(id, title, detail);
            case "DVD" -> new DVD(id, title, Integer.parseInt(detail));
            case "Magazine" -> new Magazine(id, title, Integer.parseInt(detail));
            default -> throw new DataAccessException("Unknown item type in database: " + type, null);
        };
    }

    /** Object -> the single "detail" column. */
    private String detailOf(LibraryItem item) {
        if (item instanceof Book b) return b.getAuthor();
        if (item instanceof DVD d) return String.valueOf(d.getDurationMinutes());
        if (item instanceof Magazine m) return String.valueOf(m.getIssueNumber());
        return "";
    }
}

package lms.dao;

import lms.exception.DataAccessException;
import lms.model.Member;
import lms.model.Staff;
import lms.model.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/** MemberDAO - all SQL for the members table (Week 11). */
public class MemberDAO {

    public void insert(Member member) {
        String sql = "INSERT INTO members (id, name, type) VALUES (?, ?, ?)";
        try (Connection con = Database.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, member.getMemberId());
            ps.setString(2, member.getName());
            ps.setString(3, member instanceof Staff ? "STAFF" : "STUDENT");
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException("Could not save member " + member.getMemberId(), e);
        }
    }

    public List<Member> findAll() {
        String sql = "SELECT id, name, type FROM members ORDER BY id";
        List<Member> result = new ArrayList<>();
        try (Connection con = Database.getConnection();
             PreparedStatement ps = con.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                result.add("STAFF".equals(rs.getString("type")) ? new Staff(id, name) : new Student(id, name));
            }
        } catch (SQLException e) {
            throw new DataAccessException("Could not read members", e);
        }
        return result;
    }
}

package lms.dao;

import lms.exception.DataAccessException;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

/**
 * LoanDAO - all SQL for the loans table (Week 11).
 *
 * A loan row with returned_at = NULL means "still on loan".
 * There is no separate "is borrowed" column to keep in sync - the loans
 * table IS the truth.
 */
public class LoanDAO {

    /** A small immutable data holder (a Java record). */
    public record OpenLoan(String itemId, String memberId) { }

    public void recordLoan(String itemId, String memberId) {
        String sql = "INSERT INTO loans (item_id, member_id, borrowed_at) VALUES (?, ?, ?)";
        try (Connection con = Database.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, itemId);
            ps.setString(2, memberId);
            ps.setString(3, LocalDateTime.now().withNano(0).toString());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException("Could not record the loan of " + itemId, e);
        }
    }

    public void recordReturn(String itemId) {
        String sql = "UPDATE loans SET returned_at = ? WHERE item_id = ? AND returned_at IS NULL";
        try (Connection con = Database.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, LocalDateTime.now().withNano(0).toString());
            ps.setString(2, itemId);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new DataAccessException("Could not record the return of " + itemId, e);
        }
    }

    /** Used at start-up to restore "who has what". */
    public List<OpenLoan> findOpenLoans() {
        String sql = "SELECT item_id, member_id FROM loans WHERE returned_at IS NULL";
        List<OpenLoan> result = new ArrayList<>();
        try (Connection con = Database.getConnection();
             PreparedStatement ps = con.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) result.add(new OpenLoan(rs.getString(1), rs.getString(2)));
        } catch (SQLException e) {
            throw new DataAccessException("Could not read open loans", e);
        }
        return result;
    }

    /** A JOIN across three tables: rows of {borrowed, item title, member name, returned}. */
    public List<String[]> history() {
        String sql = "SELECT l.borrowed_at, i.title, m.name, l.returned_at "
                   + "FROM loans l "
                   + "JOIN items i ON i.id = l.item_id "
                   + "JOIN members m ON m.id = l.member_id "
                   + "ORDER BY l.borrowed_at DESC";
        List<String[]> rows = new ArrayList<>();
        try (Connection con = Database.getConnection();
             PreparedStatement ps = con.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                String returned = rs.getString(4);
                rows.add(new String[]{rs.getString(1).replace('T', ' '), rs.getString(2), rs.getString(3),
                        returned == null ? "(on loan)" : returned.replace('T', ' ')});
            }
        } catch (SQLException e) {
            throw new DataAccessException("Could not read loan history", e);
        }
        return rows;
    }
}

package lms;

import lms.dao.*;
import lms.model.*;

/**
 * DaoDemo - test your DAOs from the console BEFORE touching the GUI.
 * If a DAO is wrong, you want to find out here, not behind a button.
 */
public class DaoDemo {
    public static void main(String[] args) {
        Database.init();
        ItemDAO items = new ItemDAO();
        MemberDAO members = new MemberDAO();
        LoanDAO loans = new LoanDAO();

        items.insert(new Book("B100", "Head First Java", "Kathy Sierra"));
        items.insert(new DVD("D100", "Databases 101", 90));
        members.insert(new Student("M100", "Amal Fernando"));

        System.out.println("--- items in the database ---");
        items.findAll().forEach(System.out::println);
        System.out.println("--- members in the database ---");
        members.findAll().forEach(System.out::println);

        loans.recordLoan("B100", "M100");
        System.out.println("--- open loans ---");
        loans.findOpenLoans().forEach(System.out::println);

        loans.recordReturn("B100");
        System.out.println("--- open loans after return: " + loans.findOpenLoans().size());

        System.out.println("--- history (JOIN) ---");
        for (String[] row : loans.history()) System.out.println(String.join(" | ", row));

        System.out.println("--- foreign key check: loan for a member that does not exist ---");
        try {
            loans.recordLoan("B100", "M999");
        } catch (lms.exception.DataAccessException e) {
            System.out.println("Rejected: " + e.getMessage());
        }
    }
}

package lms.service;

import lms.dao.ItemDAO;
import lms.dao.LoanDAO;
import lms.dao.MemberDAO;
import lms.exception.ItemNotAvailableException;
import lms.model.*;

import java.util.List;

/**
 * LibraryController - the C in MVC (Week 9); now also talks to the DAOs (Week 11).
 *
 *   Model      = lms.model + Library   (data and rules; knows nothing about windows)
 *   View       = lms.ui.MainFrame      (windows; knows nothing about the rules)
 *   Controller = this class            (receives what the user typed, calls the model)
 *
 * The view never touches Book/DVD/Library directly. It asks the controller.
 * That is what lets us swap the console for Swing - and later swap the
 * in-memory list for a database - without rewriting the other layers.
 */
public class LibraryController {

    private final Library library;
    private final ItemDAO itemDAO;
    private final MemberDAO memberDAO;
    private final LoanDAO loanDAO;

    public LibraryController(Library library, ItemDAO itemDAO, MemberDAO memberDAO, LoanDAO loanDAO) {
        this.library = library;
        this.itemDAO = itemDAO;
        this.memberDAO = memberDAO;
        this.loanDAO = loanDAO;
    }

    /** Start-up: rebuild the in-memory model from the database. */
    public void loadFromDatabase() {
        for (LibraryItem item : itemDAO.findAll()) library.addItem(item);
        for (Member m : memberDAO.findAll()) library.addMember(m);
        for (LoanDAO.OpenLoan loan : loanDAO.findOpenLoans()) {
            try {
                library.borrowItem(loan.itemId(), loan.memberId());   // replays the loan through the model's rules
            } catch (ItemNotAvailableException e) {
                throw new IllegalStateException("Database holds an impossible loan: " + loan, e);
            }
        }
    }

    /** Order matters: validate (construct) -> save to DB -> add to memory. */
    public void addItem(String type, String id, String title, String detail) {
        if (library.hasItem(id)) {
            throw new IllegalArgumentException("An item with id '" + id + "' already exists");
        }
        LibraryItem item = switch (type) {
            case "Book" -> new Book(id, title, detail);
            case "DVD" -> new DVD(id, title, parseWholeNumber(detail, "Duration"));
            case "Magazine" -> new Magazine(id, title, parseWholeNumber(detail, "Issue number"));
            default -> throw new IllegalArgumentException("Unknown item type: " + type);
        };
        itemDAO.insert(item);
        library.addItem(item);
    }

    public void addMember(String type, String id, String name) {
        if (library.getMembers().stream().anyMatch(m -> m.getMemberId().equals(id))) {
            throw new IllegalArgumentException("A member with id '" + id + "' already exists");
        }
        Member member = "Staff".equals(type) ? new Staff(id, name) : new Student(id, name);
        memberDAO.insert(member);
        library.addMember(member);
    }

    public void borrow(String itemId, String memberId) throws ItemNotAvailableException {
        library.borrowItem(itemId, memberId);          // rules first
        try {
            loanDAO.recordLoan(itemId, memberId);      // then persist
        } catch (RuntimeException e) {
            library.returnItem(itemId);                // undo the in-memory change
            throw e;
        }
    }

    public void giveBack(String itemId) throws ItemNotAvailableException {
        library.returnItem(itemId);
        loanDAO.recordReturn(itemId);
    }

    public List<String[]> loanHistory() { return loanDAO.history(); }

    public List<LibraryItem> getItems() { return library.getItems(); }

    /** An empty search box means "show everything". */
    public List<LibraryItem> search(String text) {
        return (text == null || text.isBlank()) ? library.getItems() : library.search(text);
    }

    /** Plain-text report for a dialog. */
    public String loanReport() {
        var counts = library.loansPerMember();
        if (counts.isEmpty()) return "No items are currently on loan.";
        StringBuilder sb = new StringBuilder("Items currently on loan:\n");
        counts.forEach((name, n) -> sb.append("  ").append(name).append(": ").append(n).append('\n'));
        sb.append("\nAvailable: ").append(library.availableItems().size());
        return sb.toString();
    }
    public List<Member> getMembers() { return library.getMembers(); }

    private int parseWholeNumber(String text, String label) {
        try {
            return Integer.parseInt(text.trim());
        } catch (NumberFormatException e) {
            throw new IllegalArgumentException(label + " must be a whole number");
        }
    }
}

package lms;

import lms.dao.*;
import lms.model.*;
import lms.service.Library;
import lms.service.LibraryController;
import lms.ui.MainFrame;

import javax.swing.*;

/** Week 11 start-up: database -> model -> controller -> view. */
public class GuiMain {
    public static void main(String[] args) {
        Database.init();                                  // create tables if needed

        ItemDAO itemDAO = new ItemDAO();
        MemberDAO memberDAO = new MemberDAO();
        LoanDAO loanDAO = new LoanDAO();
        seedIfEmpty(itemDAO, memberDAO);                  // first run only

        Library library = new Library();                  // MODEL
        LibraryController controller =                    // CONTROLLER
                new LibraryController(library, itemDAO, memberDAO, loanDAO);
        controller.loadFromDatabase();

        SwingUtilities.invokeLater(() -> new MainFrame(controller).setVisible(true));   // VIEW
    }

    private static void seedIfEmpty(ItemDAO itemDAO, MemberDAO memberDAO) {
        if (itemDAO.findAll().isEmpty()) {
            itemDAO.insert(new Book("B001", "Clean Code", "Robert C. Martin"));
            itemDAO.insert(new Book("B002", "Refactoring", "Martin Fowler"));
            itemDAO.insert(new DVD("D001", "Introduction to Algorithms", 120));
            itemDAO.insert(new Magazine("G001", "Software Weekly", 42));
        }
        if (memberDAO.findAll().isEmpty()) {
            memberDAO.insert(new Student("M001", "Nadeesha Perera"));
            memberDAO.insert(new Staff("M002", "Mr. Kasun Silva"));
        }
    }
}