Library Management System - Database Persistence
Migrate from CSV to database storage using JDBC with H2 embedded database
Objectives
By the end of this practical work, you will be able to:
- Understand JDBC architecture and components
- Connect to a database using JDBC
- Create tables using DDL statements
- Implement CRUD operations with PreparedStatements
- Handle database exceptions properly
- Use transactions for data integrity
- Apply the DAO (Data Access Object) pattern
Prerequisites
- Completed Practical Work 8 (Library with Collections)
- Understanding of SQL basics (SELECT, INSERT, UPDATE, DELETE)
- Maven or Gradle for dependency management
Project Setup
Step 0.1: Add H2 Database Dependency
Add the H2 embedded database to your project:
Maven (pom.xml):
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.2.224</version>
</dependency>Gradle (build.gradle):
dependencies {
implementation 'com.h2database:h2:2.2.224'
}
Why H2? H2 is an embedded database - no installation required! Perfect for learning and testing. It stores data in a local file.
Project Structure Update
library-system
src
com.library
model
(existing model classes)
dao
DatabaseManager.java
MediaItemDao.java
MemberDao.java
LoanDao.java
service
MediaCatalog.java
MemberService.java
LoanService.java
app
LibraryApp.java
data
library.mv.db (H2 database file)
Part 1: Database Connection Management
Step 1.1: Create DatabaseManager Class
Centralize database connection management:
package com.library.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class DatabaseManager {
// H2 database URL - file-based storage in 'data' folder
private static final String DB_URL = "jdbc:h2:./data/library"; // (#1:JDBC URL)
private static final String DB_USER = "sa";
private static final String DB_PASSWORD = "";
private static DatabaseManager instance;
private Connection connection;
private DatabaseManager() {
// Private constructor for singleton
}
public static synchronized DatabaseManager getInstance() { // (#2:Singleton pattern)
if (instance == null) {
instance = new DatabaseManager();
}
return instance;
}
// Get a connection to the database
public Connection getConnection() throws SQLException {
if (connection == null || connection.isClosed()) {
connection = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD); // (#3:Get connection)
}
return connection;
}
// Initialize database schema
public void initializeDatabase() throws SQLException {
try (Connection conn = getConnection();
Statement stmt = conn.createStatement()) {
// Create MEMBERS table
stmt.execute("""
CREATE TABLE IF NOT EXISTS members (
id VARCHAR(50) PRIMARY KEY,
name VARCHAR(200) NOT NULL,
email VARCHAR(200) UNIQUE NOT NULL,
phone VARCHAR(50),
join_date DATE DEFAULT CURRENT_DATE
)
"""); // (#4:DDL statement)
// Create MEDIA_ITEMS table
stmt.execute("""
CREATE TABLE IF NOT EXISTS media_items (
id VARCHAR(50) PRIMARY KEY,
media_type VARCHAR(20) NOT NULL,
title VARCHAR(300) NOT NULL,
year INT,
status VARCHAR(30) DEFAULT 'AVAILABLE',
-- Book specific
author VARCHAR(200),
isbn VARCHAR(30),
category VARCHAR(50),
page_count INT,
-- DVD specific
director VARCHAR(200),
duration_minutes INT,
rating VARCHAR(10),
-- Magazine specific
publisher VARCHAR(200),
issue_number INT,
month VARCHAR(20)
)
""");
// Create LOANS table
stmt.execute("""
CREATE TABLE IF NOT EXISTS loans (
id VARCHAR(50) PRIMARY KEY,
item_id VARCHAR(50) NOT NULL,
member_id VARCHAR(50) NOT NULL,
checkout_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
due_date DATE NOT NULL,
return_date TIMESTAMP,
late_fee DECIMAL(10,2) DEFAULT 0,
FOREIGN KEY (item_id) REFERENCES media_items(id),
FOREIGN KEY (member_id) REFERENCES members(id)
)
"""); // (#5:Foreign key constraints)
// Create indexes for faster lookups
stmt.execute("CREATE INDEX IF NOT EXISTS idx_loans_member ON loans(member_id)");
stmt.execute("CREATE INDEX IF NOT EXISTS idx_loans_item ON loans(item_id)");
stmt.execute("CREATE INDEX IF NOT EXISTS idx_media_status ON media_items(status)");
System.out.println("Database initialized successfully");
}
}
// Close connection
public void closeConnection() {
if (connection != null) {
try {
connection.close();
System.out.println("Database connection closed");
} catch (SQLException e) {
System.err.println("Error closing connection: " + e.getMessage());
}
}
}
}
Code Annotations:
- JDBC URL: Protocol:database:path format
- Singleton: One instance manages all connections
- DriverManager: Factory for database connections
- DDL: Data Definition Language for schema
- Foreign keys: Ensure referential integrity
Part 2: Member DAO Implementation
Step 2.1: Create MemberDao Class
Implement CRUD operations for members:
package com.library.dao;
import com.library.model.Member;
import java.sql.*;
import java.time.LocalDate;
import java.util.*;
public class MemberDao {
private final DatabaseManager dbManager;
public MemberDao() {
this.dbManager = DatabaseManager.getInstance();
}
// Create (Insert)
public void insert(Member member) throws SQLException {
String sql = """
INSERT INTO members (id, name, email, phone, join_date)
VALUES (?, ?, ?, ?, ?)
""";
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) { // (#1:PreparedStatement)
pstmt.setString(1, member.getId()); // (#2:Set parameters by index)
pstmt.setString(2, member.getName());
pstmt.setString(3, member.getEmail());
pstmt.setString(4, member.getPhone());
pstmt.setDate(5, Date.valueOf(member.getJoinDate())); // (#3:Convert LocalDate)
pstmt.executeUpdate(); // (#4:Execute INSERT)
}
}
// Read (Select by ID)
public Optional<Member> findById(String id) throws SQLException {
String sql = "SELECT * FROM members WHERE id = ?";
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, id);
try (ResultSet rs = pstmt.executeQuery()) { // (#5:Execute SELECT)
if (rs.next()) {
return Optional.of(mapResultSetToMember(rs));
}
}
}
return Optional.empty();
}
// Read all
public List<Member> findAll() throws SQLException {
String sql = "SELECT * FROM members ORDER BY name";
List<Member> members = new ArrayList<>();
try (Connection conn = dbManager.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) { // (#6:Iterate ResultSet)
members.add(mapResultSetToMember(rs));
}
}
return members;
}
// Update
public void update(Member member) throws SQLException {
String sql = """
UPDATE members
SET name = ?, email = ?, phone = ?
WHERE id = ?
""";
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, member.getName());
pstmt.setString(2, member.getEmail());
pstmt.setString(3, member.getPhone());
pstmt.setString(4, member.getId());
int rowsAffected = pstmt.executeUpdate(); // (#7:Returns row count)
if (rowsAffected == 0) {
throw new SQLException("Member not found: " + member.getId());
}
}
}
// Delete
public boolean delete(String id) throws SQLException {
String sql = "DELETE FROM members WHERE id = ?";
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, id);
return pstmt.executeUpdate() > 0;
}
}
// Find by email
public Optional<Member> findByEmail(String email) throws SQLException {
String sql = "SELECT * FROM members WHERE LOWER(email) = LOWER(?)";
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, email);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return Optional.of(mapResultSetToMember(rs));
}
}
}
return Optional.empty();
}
// Check if email exists
public boolean emailExists(String email) throws SQLException {
String sql = "SELECT COUNT(*) FROM members WHERE LOWER(email) = LOWER(?)";
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, email);
try (ResultSet rs = pstmt.executeQuery()) {
return rs.next() && rs.getInt(1) > 0;
}
}
}
// Count members
public int count() throws SQLException {
String sql = "SELECT COUNT(*) FROM members";
try (Connection conn = dbManager.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
return rs.next() ? rs.getInt(1) : 0;
}
}
// Helper: Map ResultSet row to Member object
private Member mapResultSetToMember(ResultSet rs) throws SQLException { // (#8:Result mapping)
Member member = new Member(
rs.getString("id"),
rs.getString("name"),
rs.getString("email")
);
member.setPhone(rs.getString("phone"));
Date joinDate = rs.getDate("join_date");
if (joinDate != null) {
member.setJoinDate(joinDate.toLocalDate());
}
return member;
}
}
Code Annotations:
- PreparedStatement: Prevents SQL injection, improves performance
- Parameter index: Starts at 1, not 0
- Date conversion: JDBC uses java.sql.Date
- executeUpdate: For INSERT/UPDATE/DELETE
- executeQuery: For SELECT, returns ResultSet
- ResultSet iteration: next() moves cursor forward
- Row count: Returns number of affected rows
- Result mapping: Convert database row to Java object
Part 3: MediaItem DAO Implementation
Step 3.1: Create MediaItemDao Class
package com.library.dao;
import com.library.model.*;
import java.sql.*;
import java.util.*;
public class MediaItemDao {
private final DatabaseManager dbManager;
public MediaItemDao() {
this.dbManager = DatabaseManager.getInstance();
}
// Insert media item
public void insert(MediaItem item) throws SQLException {
String sql = """
INSERT INTO media_items (
id, media_type, title, year, status,
author, isbn, category, page_count,
director, duration_minutes, rating,
publisher, issue_number, month
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
// Common fields
pstmt.setString(1, item.getId());
pstmt.setString(2, item.getMediaType());
pstmt.setString(3, item.getTitle());
pstmt.setInt(4, item.getYear());
pstmt.setString(5, item.getStatus().name());
// Initialize type-specific fields to null
for (int i = 6; i <= 15; i++) {
pstmt.setNull(i, Types.VARCHAR); // (#1:Set null explicitly)
}
// Set type-specific fields
if (item instanceof Book book) {
pstmt.setString(6, book.getAuthor());
pstmt.setString(7, book.getIsbn());
pstmt.setString(8, book.getCategory().name());
pstmt.setInt(9, book.getPageCount());
} else if (item instanceof DVD dvd) {
pstmt.setString(10, dvd.getDirector());
pstmt.setInt(11, dvd.getDurationMinutes());
pstmt.setString(12, dvd.getRating());
} else if (item instanceof Magazine mag) {
pstmt.setString(13, mag.getPublisher());
pstmt.setInt(14, mag.getIssueNumber());
pstmt.setString(15, mag.getMonth());
}
pstmt.executeUpdate();
}
}
// Find by ID
public Optional<MediaItem> findById(String id) throws SQLException {
String sql = "SELECT * FROM media_items WHERE id = ?";
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, id);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
return Optional.of(mapResultSetToMediaItem(rs));
}
}
}
return Optional.empty();
}
// Find all
public List<MediaItem> findAll() throws SQLException {
String sql = "SELECT * FROM media_items ORDER BY title";
List<MediaItem> items = new ArrayList<>();
try (Connection conn = dbManager.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
items.add(mapResultSetToMediaItem(rs));
}
}
return items;
}
// Find by status
public List<MediaItem> findByStatus(MediaStatus status) throws SQLException {
String sql = "SELECT * FROM media_items WHERE status = ?";
List<MediaItem> items = new ArrayList<>();
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, status.name());
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
items.add(mapResultSetToMediaItem(rs));
}
}
}
return items;
}
// Search by title (partial match)
public List<MediaItem> searchByTitle(String searchTerm) throws SQLException {
String sql = "SELECT * FROM media_items WHERE LOWER(title) LIKE LOWER(?)"; // (#2:LIKE search)
List<MediaItem> items = new ArrayList<>();
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, "%" + searchTerm + "%"); // (#3:Wildcards)
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
items.add(mapResultSetToMediaItem(rs));
}
}
}
return items;
}
// Update status
public void updateStatus(String id, MediaStatus status) throws SQLException {
String sql = "UPDATE media_items SET status = ? WHERE id = ?";
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, status.name());
pstmt.setString(2, id);
pstmt.executeUpdate();
}
}
// Delete
public boolean delete(String id) throws SQLException {
String sql = "DELETE FROM media_items WHERE id = ?";
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, id);
return pstmt.executeUpdate() > 0;
}
}
// Count by type
public Map<String, Integer> countByType() throws SQLException {
String sql = "SELECT media_type, COUNT(*) as cnt FROM media_items GROUP BY media_type"; // (#4:Aggregate query)
Map<String, Integer> counts = new HashMap<>();
try (Connection conn = dbManager.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
counts.put(rs.getString("media_type"), rs.getInt("cnt"));
}
}
return counts;
}
// Helper: Map ResultSet to MediaItem
private MediaItem mapResultSetToMediaItem(ResultSet rs) throws SQLException {
String type = rs.getString("media_type");
String id = rs.getString("id");
String title = rs.getString("title");
int year = rs.getInt("year");
MediaStatus status = MediaStatus.valueOf(rs.getString("status"));
MediaItem item;
switch (type) {
case "BOOK" -> {
String author = rs.getString("author");
String isbn = rs.getString("isbn");
BookCategory category = BookCategory.valueOf(rs.getString("category"));
int pages = rs.getInt("page_count");
item = new Book(id, title, author, year, isbn, category, pages);
}
case "DVD" -> {
String director = rs.getString("director");
int duration = rs.getInt("duration_minutes");
String rating = rs.getString("rating");
item = new DVD(id, title, director, year, duration, rating);
}
case "MAGAZINE" -> {
String publisher = rs.getString("publisher");
int issue = rs.getInt("issue_number");
String month = rs.getString("month");
item = new Magazine(id, title, publisher, year, issue, month);
}
default -> throw new SQLException("Unknown media type: " + type);
}
item.setStatus(status);
return item;
}
}
Code Annotations:
- setNull: Explicitly set NULL for unused columns
- LIKE query: Pattern matching in SQL
- Wildcards: % matches any sequence of characters
- GROUP BY: Aggregate data by category
Part 4: Loan DAO with Transactions
Step 4.1: Create LoanDao with Transaction Support
package com.library.dao;
import com.library.model.*;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.*;
public class LoanDao {
private final DatabaseManager dbManager;
private final MediaItemDao mediaItemDao;
public LoanDao() {
this.dbManager = DatabaseManager.getInstance();
this.mediaItemDao = new MediaItemDao();
}
// Create loan with transaction
public void createLoan(Loan loan) throws SQLException {
Connection conn = null;
try {
conn = dbManager.getConnection();
conn.setAutoCommit(false); // (#1:Start transaction)
// 1. Insert loan record
String loanSql = """
INSERT INTO loans (id, item_id, member_id, checkout_date, due_date)
VALUES (?, ?, ?, ?, ?)
""";
try (PreparedStatement pstmt = conn.prepareStatement(loanSql)) {
pstmt.setString(1, loan.getLoanId());
pstmt.setString(2, loan.getItem().getId());
pstmt.setString(3, loan.getMember().getId());
pstmt.setTimestamp(4, Timestamp.valueOf(loan.getCheckoutDateTime()));
pstmt.setDate(5, Date.valueOf(loan.getDueDate()));
pstmt.executeUpdate();
}
// 2. Update media item status
String statusSql = "UPDATE media_items SET status = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(statusSql)) {
pstmt.setString(1, MediaStatus.BORROWED.name());
pstmt.setString(2, loan.getItem().getId());
pstmt.executeUpdate();
}
conn.commit(); // (#2:Commit transaction)
} catch (SQLException e) {
if (conn != null) {
conn.rollback(); // (#3:Rollback on error)
}
throw e;
} finally {
if (conn != null) {
conn.setAutoCommit(true); // (#4:Restore auto-commit)
}
}
}
// Return item with transaction
public void returnLoan(String loanId, double lateFee) throws SQLException {
Connection conn = null;
try {
conn = dbManager.getConnection();
conn.setAutoCommit(false);
// 1. Get the loan to find the item ID
String findSql = "SELECT item_id FROM loans WHERE id = ?";
String itemId = null;
try (PreparedStatement pstmt = conn.prepareStatement(findSql)) {
pstmt.setString(1, loanId);
try (ResultSet rs = pstmt.executeQuery()) {
if (rs.next()) {
itemId = rs.getString("item_id");
} else {
throw new SQLException("Loan not found: " + loanId);
}
}
}
// 2. Update loan record
String loanSql = """
UPDATE loans
SET return_date = ?, late_fee = ?
WHERE id = ?
""";
try (PreparedStatement pstmt = conn.prepareStatement(loanSql)) {
pstmt.setTimestamp(1, Timestamp.valueOf(LocalDateTime.now()));
pstmt.setDouble(2, lateFee);
pstmt.setString(3, loanId);
pstmt.executeUpdate();
}
// 3. Update media item status
String statusSql = "UPDATE media_items SET status = ? WHERE id = ?";
try (PreparedStatement pstmt = conn.prepareStatement(statusSql)) {
pstmt.setString(1, MediaStatus.AVAILABLE.name());
pstmt.setString(2, itemId);
pstmt.executeUpdate();
}
conn.commit();
} catch (SQLException e) {
if (conn != null) {
conn.rollback();
}
throw e;
} finally {
if (conn != null) {
conn.setAutoCommit(true);
}
}
}
// Find active loans (not returned)
public List<LoanRecord> findActiveLoans() throws SQLException { // (#5:Use record for results)
String sql = """
SELECT l.*, m.name as member_name, mi.title as item_title
FROM loans l
JOIN members m ON l.member_id = m.id
JOIN media_items mi ON l.item_id = mi.id
WHERE l.return_date IS NULL
ORDER BY l.due_date
"""; // (#6:JOIN query)
List<LoanRecord> loans = new ArrayList<>();
try (Connection conn = dbManager.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
loans.add(new LoanRecord(
rs.getString("id"),
rs.getString("item_id"),
rs.getString("item_title"),
rs.getString("member_id"),
rs.getString("member_name"),
rs.getTimestamp("checkout_date").toLocalDateTime(),
rs.getDate("due_date").toLocalDate()
));
}
}
return loans;
}
// Find overdue loans
public List<LoanRecord> findOverdueLoans() throws SQLException {
String sql = """
SELECT l.*, m.name as member_name, mi.title as item_title
FROM loans l
JOIN members m ON l.member_id = m.id
JOIN media_items mi ON l.item_id = mi.id
WHERE l.return_date IS NULL AND l.due_date < CURRENT_DATE
ORDER BY l.due_date
""";
List<LoanRecord> loans = new ArrayList<>();
try (Connection conn = dbManager.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
loans.add(new LoanRecord(
rs.getString("id"),
rs.getString("item_id"),
rs.getString("item_title"),
rs.getString("member_id"),
rs.getString("member_name"),
rs.getTimestamp("checkout_date").toLocalDateTime(),
rs.getDate("due_date").toLocalDate()
));
}
}
return loans;
}
// Find loans by member
public List<LoanRecord> findByMember(String memberId) throws SQLException {
String sql = """
SELECT l.*, m.name as member_name, mi.title as item_title
FROM loans l
JOIN members m ON l.member_id = m.id
JOIN media_items mi ON l.item_id = mi.id
WHERE l.member_id = ?
ORDER BY l.checkout_date DESC
""";
List<LoanRecord> loans = new ArrayList<>();
try (Connection conn = dbManager.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, memberId);
try (ResultSet rs = pstmt.executeQuery()) {
while (rs.next()) {
loans.add(new LoanRecord(
rs.getString("id"),
rs.getString("item_id"),
rs.getString("item_title"),
rs.getString("member_id"),
rs.getString("member_name"),
rs.getTimestamp("checkout_date").toLocalDateTime(),
rs.getDate("due_date").toLocalDate()
));
}
}
}
return loans;
}
// Calculate total outstanding fees
public double getTotalOutstandingFees(double dailyRate) throws SQLException {
String sql = """
SELECT SUM(DATEDIFF(DAY, due_date, CURRENT_DATE)) as total_days_overdue
FROM loans
WHERE return_date IS NULL AND due_date < CURRENT_DATE
""";
try (Connection conn = dbManager.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
if (rs.next()) {
int totalDays = rs.getInt("total_days_overdue");
return totalDays * dailyRate;
}
}
return 0.0;
}
// Get next loan ID
public int getNextLoanId() throws SQLException {
String sql = "SELECT MAX(CAST(SUBSTRING(id, 2) AS INT)) FROM loans";
try (Connection conn = dbManager.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
if (rs.next()) {
return rs.getInt(1) + 1;
}
}
return 1;
}
// Record class for loan query results
public record LoanRecord(
String loanId,
String itemId,
String itemTitle,
String memberId,
String memberName,
LocalDateTime checkoutDate,
LocalDate dueDate
) {
public boolean isOverdue() {
return LocalDate.now().isAfter(dueDate);
}
public long getDaysOverdue() {
if (!isOverdue()) return 0;
return java.time.temporal.ChronoUnit.DAYS.between(dueDate, LocalDate.now());
}
}
}
Code Annotations:
- setAutoCommit(false): Begin explicit transaction
- commit(): Make all changes permanent
- rollback(): Undo all changes on error
- Restore auto-commit: Return to normal mode
- Record for results: Immutable data carrier
- JOIN: Combine data from multiple tables
Part 5: Integrated Library Application
Step 5.1: Update LibraryApp to Use DAOs
package com.library.app;
import com.library.model.*;
import com.library.dao.*;
import com.library.exception.*;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.*;
public class LibraryApp {
private final DatabaseManager dbManager;
private final MemberDao memberDao;
private final MediaItemDao mediaItemDao;
private final LoanDao loanDao;
private final Scanner scanner;
private int loanIdCounter;
public LibraryApp() {
this.dbManager = DatabaseManager.getInstance();
this.memberDao = new MemberDao();
this.mediaItemDao = new MediaItemDao();
this.loanDao = new LoanDao();
this.scanner = new Scanner(System.in);
}
public void start() {
System.out.println("=== Library Management System (Database Edition) ===\n");
try {
// Initialize database
dbManager.initializeDatabase();
loanIdCounter = loanDao.getNextLoanId();
boolean running = true;
while (running) {
displayMenu();
String choice = scanner.nextLine().trim();
try {
running = processChoice(choice);
} catch (SQLException e) {
System.out.println("Database error: " + e.getMessage());
} catch (Exception e) {
System.out.println("Error: " + e.getMessage());
}
}
} catch (SQLException e) {
System.out.println("Failed to initialize database: " + e.getMessage());
} finally {
dbManager.closeConnection();
System.out.println("Goodbye!");
}
}
private void displayMenu() {
System.out.println("""
╔══════════════════════════════════════╗
║ LIBRARY MENU (Database) ║
╠══════════════════════════════════════╣
║ 1. Browse Catalog ║
║ 2. Search Items ║
║ 3. Add New Item ║
║ 4. Checkout Item ║
║ 5. Return Item ║
║ 6. View Active Loans ║
║ 7. View Overdue Items ║
║ 8. Register Member ║
║ 9. View Member Loans ║
║ 10. Statistics ║
║ 0. Exit ║
╚══════════════════════════════════════╝
""");
System.out.print("Enter choice: ");
}
private boolean processChoice(String choice) throws SQLException {
switch (choice) {
case "1" -> browseCatalog();
case "2" -> searchItems();
case "3" -> addNewItem();
case "4" -> checkoutItem();
case "5" -> returnItem();
case "6" -> viewActiveLoans();
case "7" -> viewOverdueItems();
case "8" -> registerMember();
case "9" -> viewMemberLoans();
case "10" -> viewStatistics();
case "0" -> { return false; }
default -> System.out.println("Invalid choice");
}
return true;
}
private void browseCatalog() throws SQLException {
List<MediaItem> items = mediaItemDao.findAll();
if (items.isEmpty()) {
System.out.println("Catalog is empty");
return;
}
System.out.println("\n=== Library Catalog ===");
for (MediaItem item : items) {
System.out.printf("[%s] %s - %s [%s]%n",
item.getMediaType(),
item.getId(),
item.getTitle(),
item.getStatus());
}
}
private void searchItems() throws SQLException {
System.out.print("Enter search term: ");
String term = scanner.nextLine();
List<MediaItem> results = mediaItemDao.searchByTitle(term);
if (results.isEmpty()) {
System.out.println("No items found");
} else {
System.out.println("\nSearch Results:");
for (MediaItem item : results) {
System.out.println(" - " + item);
}
}
}
private void addNewItem() throws SQLException {
System.out.println("Type: 1=Book, 2=DVD, 3=Magazine");
String type = scanner.nextLine();
System.out.print("ID: ");
String id = scanner.nextLine();
System.out.print("Title: ");
String title = scanner.nextLine();
System.out.print("Year: ");
int year = Integer.parseInt(scanner.nextLine());
MediaItem item;
switch (type) {
case "1" -> {
System.out.print("Author: ");
String author = scanner.nextLine();
System.out.print("ISBN: ");
String isbn = scanner.nextLine();
System.out.print("Category (FICTION/NON_FICTION/TECHNOLOGY/SCIENCE): ");
BookCategory cat = BookCategory.valueOf(scanner.nextLine().toUpperCase());
item = new Book(id, title, author, year, isbn, cat);
}
case "2" -> {
System.out.print("Director: ");
String director = scanner.nextLine();
System.out.print("Duration (min): ");
int duration = Integer.parseInt(scanner.nextLine());
System.out.print("Rating: ");
String rating = scanner.nextLine();
item = new DVD(id, title, director, year, duration, rating);
}
case "3" -> {
System.out.print("Publisher: ");
String publisher = scanner.nextLine();
System.out.print("Issue #: ");
int issue = Integer.parseInt(scanner.nextLine());
System.out.print("Month: ");
String month = scanner.nextLine();
item = new Magazine(id, title, publisher, year, issue, month);
}
default -> {
System.out.println("Invalid type");
return;
}
}
mediaItemDao.insert(item);
System.out.println("Item added successfully!");
}
private void checkoutItem() throws SQLException {
System.out.print("Item ID: ");
String itemId = scanner.nextLine();
System.out.print("Member ID: ");
String memberId = scanner.nextLine();
Optional<MediaItem> itemOpt = mediaItemDao.findById(itemId);
Optional<Member> memberOpt = memberDao.findById(memberId);
if (itemOpt.isEmpty()) {
System.out.println("Item not found");
return;
}
if (memberOpt.isEmpty()) {
System.out.println("Member not found");
return;
}
MediaItem item = itemOpt.get();
Member member = memberOpt.get();
if (item.getStatus() != MediaStatus.AVAILABLE) {
System.out.println("Item is not available: " + item.getStatus());
return;
}
String loanId = String.format("L%05d", loanIdCounter++);
Loan loan = new Loan(loanId, item, member, item.getMaxLoanDays());
loanDao.createLoan(loan);
System.out.println("Checkout successful!");
System.out.println("Loan ID: " + loanId);
System.out.println("Due date: " + loan.getFormattedDueDate());
}
private void returnItem() throws SQLException {
System.out.print("Loan ID: ");
String loanId = scanner.nextLine();
// Find the loan details to calculate late fee
List<LoanDao.LoanRecord> activeLoans = loanDao.findActiveLoans();
LoanDao.LoanRecord loan = null;
for (LoanDao.LoanRecord l : activeLoans) {
if (l.loanId().equals(loanId)) {
loan = l;
break;
}
}
if (loan == null) {
System.out.println("Active loan not found: " + loanId);
return;
}
double lateFee = 0;
if (loan.isOverdue()) {
lateFee = loan.getDaysOverdue() * 0.50; // $0.50 per day
System.out.printf("Late fee: $%.2f (%d days overdue)%n",
lateFee, loan.getDaysOverdue());
}
loanDao.returnLoan(loanId, lateFee);
System.out.println("Return processed successfully!");
}
private void viewActiveLoans() throws SQLException {
List<LoanDao.LoanRecord> loans = loanDao.findActiveLoans();
if (loans.isEmpty()) {
System.out.println("No active loans");
return;
}
System.out.println("\n=== Active Loans ===");
for (LoanDao.LoanRecord loan : loans) {
String status = loan.isOverdue() ? "[OVERDUE]" : "";
System.out.printf("%s: %s → %s (due: %s) %s%n",
loan.loanId(),
loan.itemTitle(),
loan.memberName(),
loan.dueDate(),
status);
}
}
private void viewOverdueItems() throws SQLException {
List<LoanDao.LoanRecord> overdue = loanDao.findOverdueLoans();
if (overdue.isEmpty()) {
System.out.println("No overdue items - great!");
return;
}
System.out.println("\n=== Overdue Items ===");
for (LoanDao.LoanRecord loan : overdue) {
System.out.printf("%s: %s - %d days overdue%n",
loan.loanId(),
loan.itemTitle(),
loan.getDaysOverdue());
}
}
private void registerMember() throws SQLException {
System.out.print("ID: ");
String id = scanner.nextLine();
System.out.print("Name: ");
String name = scanner.nextLine();
System.out.print("Email: ");
String email = scanner.nextLine();
if (memberDao.emailExists(email)) {
System.out.println("Email already registered!");
return;
}
Member member = new Member(id, name, email);
memberDao.insert(member);
System.out.println("Member registered successfully!");
}
private void viewMemberLoans() throws SQLException {
System.out.print("Member ID: ");
String memberId = scanner.nextLine();
Optional<Member> memberOpt = memberDao.findById(memberId);
if (memberOpt.isEmpty()) {
System.out.println("Member not found");
return;
}
List<LoanDao.LoanRecord> loans = loanDao.findByMember(memberId);
System.out.println("\nLoans for: " + memberOpt.get().getName());
if (loans.isEmpty()) {
System.out.println("No loan history");
} else {
for (LoanDao.LoanRecord loan : loans) {
System.out.printf(" %s: %s (due: %s)%n",
loan.loanId(), loan.itemTitle(), loan.dueDate());
}
}
}
private void viewStatistics() throws SQLException {
System.out.println("\n=== Library Statistics ===");
Map<String, Integer> counts = mediaItemDao.countByType();
int total = counts.values().stream().mapToInt(Integer::intValue).sum();
System.out.printf("Total items: %d%n", total);
for (Map.Entry<String, Integer> entry : counts.entrySet()) {
System.out.printf(" %s: %d%n", entry.getKey(), entry.getValue());
}
System.out.printf("Total members: %d%n", memberDao.count());
System.out.printf("Active loans: %d%n", loanDao.findActiveLoans().size());
System.out.printf("Overdue items: %d%n", loanDao.findOverdueLoans().size());
}
public static void main(String[] args) {
new LibraryApp().start();
}
}Exercises
Exercise 1: Member Loan Limit
Implement a loan limit for members:
- Add
max_loanscolumn to members table - Check current active loans before checkout
- Reject checkout if limit reached
- Default limit: 5 items
Exercise 2: Reservation System
Create a reservation table and DAO:
- Create
reservationstable - Allow members to reserve unavailable items
- Notify when item becomes available
- Auto-cancel reservations after 3 days
Exercise 3: Fee Payment History
Track fee payments:
- Create
paymentstable (id, member_id, amount, date) - Record late fee payments
- Calculate member balance
- Block checkout if balance > $10
Exercise 4: Database Backup
Implement database export/import:
- Export all data to SQL script file
- Import data from SQL script
- Use H2's SCRIPT and RUNSCRIPT commands
- Add backup option to menu
Deliverables
Bonus Challenges
Advanced
Connection Pooling: Implement connection pooling using HikariCP
Advanced
MySQL Migration: Adapt the code to work with MySQL instead of H2
Expert
ORM Introduction: Refactor one DAO to use JPA/Hibernate