- Add new books.
- Remove existing books.
- Update book information.
- Search for books by different criteria (title, author, ISBN).
- Register new members.
- Remove existing members.
- Update member information.
- Search for members by different criteria (name, member ID).
- Register a book loan.
- Register the return of a book.
- View a member's loan history.
- Check the availability of a book.
- User Interface: Simple console for user interactions.
- Persistence: Use a MySQL database to store data about books, members, and transactions.
- Exception Handling: Properly handle all possible exceptions that may arise during system operations.
- Documentation: Well-documented code, clear instructions on how to set up and run the system.
-
Data Validation:
- Ensure to validate all input data to avoid errors and security issues.
-
Exception Handling:
- Handle all possible exceptions appropriately to prevent the application from failing unexpectedly.
-
Modularity:
- Keep the code modular and organized into logical packages to facilitate system maintenance and scalability.
-
Documentation:
- Document the code and provide clear instructions on how to set up and run the application.
-
Testing:
- Perform thorough testing of all functionalities to ensure that the system works correctly in all possible scenarios.
| Dependency | Usage |
|---|---|
| JUnit Jupiter API » 5.11.0-M2 | Unit testing. |
| MySQL Connector/J » 9.0.0 | Connection to MySQL database. |
| JSON.simple » 1.1.1 | JSON handling for settings file. |
The library management system’s settings are stored in a JSON file. This file contains essential details for database connections, including the host, port, username, and password. The format is as follows:
{
"database_url": "jdbc:mysql://localhost:3306/",
"database_name": "library",
"username": "root",
"password": "SecretPassword123!@#"
}To run the application, ensure that the file is stored locally at the following path:
"%LOCALAPPDATA%\lms\config.json"For example:
"C:\Users\%USER%\AppData\Local\lms\config.json"Google's 'JSON.simple' library (version 1.1.1) manages the handling of this JSON format.
Additionally, a demo file is available within the project directory at /demo/config.json.
The static method getConfig("key"); from the abstract class Config needs to be used to get a String with the requided configuration value from the JSON file. This method should handle any exceptions that may occur during the file reading or parsing process. For example:
String example = Config.getConfig("database_url");Available keys:
database_url: Database URL.database_name: Database name.username: Database username.password: Database password.
The library database is designed to manage the operations of a public library system. It consists of three main tables: books, members, and loans. This document provides a detailed explanation of each table and its columns.
The library database is created using the following SQL statement:
CREATE DATABASE library;After creating the database, the USE statement is used to switch the context to the library database:
USE library;The books table stores information about the books available in the library.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| isbn | VARCHAR(13) | PRIMARY KEY | The unique ISBN (International Standard Book Number) of the book. |
| title | VARCHAR(255) | NOT NULL | The title of the book. |
| author | VARCHAR(255) | NOT NULL | The author of the book. |
| publisher | VARCHAR(255) | NOT NULL | The publisher of the book. |
| publication_year | INT | NOT NULL | The year of publication of the book. |
| page_count | INT | NOT NULL | The number of pages in the book. |
| stock_quantity | INT | NOT NULL, DEFAULT 0 | The quantity of the book in stock. |
| genre | VARCHAR(100) | The genre or category of the book. | |
| language_db | VARCHAR(50) | The language in which the book is written. | |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | The timestamp indicating when the book record was created. |
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | The timestamp indicating when the book record was last updated. |
The members table stores information about the library members.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| member_id | INT | AUTO_INCREMENT, PRIMARY KEY | The unique identifier for each library member. |
| first_name | VARCHAR(50) | NOT NULL | The first name of the member. |
| last_name | VARCHAR(50) | NOT NULL | The last name of the member. |
| phone_number | VARCHAR(20) | The phone number of the member. | |
| VARCHAR(255) | NOT NULL, UNIQUE | The email address of the member. | |
| address_db | VARCHAR(255) | The address of the member. | |
| city | VARCHAR(100) | The city where the member resides. | |
| state_db | VARCHAR(100) | The state where the member resides. | |
| zip_code | VARCHAR(20) | The zip code of the member's address. | |
| membership_start_date | DATE | NOT NULL | The date when the member's membership starts. |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | The timestamp indicating when the member record was created. |
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | The timestamp indicating when the member record was last updated. |
The loans table stores information about the book loans made by library members.
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| loan_id | INT | AUTO_INCREMENT, PRIMARY KEY | The unique identifier for each loan. |
| member_id | INT | NOT NULL, FOREIGN KEY (members.member_id) | The identifier of the member who borrowed the book. |
| isbn | VARCHAR(13) | NOT NULL, FOREIGN KEY (books.isbn) | The ISBN of the borrowed book. |
| loan_date | DATE | NOT NULL | The date when the book was borrowed. |
| return_due_date | DATE | NOT NULL | The date by which the book should be returned. |
| actual_return_date | DATE | The actual date when the book was returned (can be NULL if not yet returned). | |
| status_db | ENUM | NOT NULL, DEFAULT 'active', ('active', 'returned') | The status of the loan ('active' or 'returned'). |
| created_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | The timestamp indicating when the loan record was created. |
| updated_at | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | The timestamp indicating when the loan record was last updated. |
You can create these tables by running:
-- Create the 'books' table
CREATE TABLE books (
isbn VARCHAR(13) PRIMARY KEY,
title VARCHAR(255) NOT NULL,
author VARCHAR(255) NOT NULL,
publisher VARCHAR(255) NOT NULL,
publication_year INT NOT NULL,
page_count INT NOT NULL,
stock_quantity INT NOT NULL DEFAULT 0,
genre VARCHAR(100),
language_db VARCHAR(50),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Create the 'members' table
CREATE TABLE members (
member_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
phone_number VARCHAR(20),
email VARCHAR(255) NOT NULL UNIQUE,
address_db VARCHAR(255),
city VARCHAR(100),
state_db VARCHAR(100),
zip_code VARCHAR(20),
membership_start_date DATE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
-- Create the 'loans' table
CREATE TABLE loans (
loan_id INT AUTO_INCREMENT PRIMARY KEY,
member_id INT NOT NULL,
isbn VARCHAR(13) NOT NULL,
loan_date DATE NOT NULL,
return_due_date DATE NOT NULL,
actual_return_date DATE,
status_db ENUM('active', 'returned') NOT NULL DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);And you can add some filler examples by running:
-- Insert sample data into the 'books' table
INSERT INTO books (isbn, title, author, publisher, publication_year, page_count, stock_quantity, genre, language_db)
VALUES
('9780593311257', 'The Midnight Library', 'Matt Haig', 'Viking', 2020, 304, 15, 'Magical Realism', 'English'),
('9780525559474', 'The Vanishing Half', 'Brit Bennett', 'Riverhead Books', 2020, 352, 12, 'Historical Fiction', 'English'),
('9780593311295', 'The Invisible Life of Addie LaRue', 'V.E. Schwab', 'Tor Books', 2020, 448, 10, 'Fantasy', 'English'),
('9780593190517', 'The Overstory', 'Richard Powers', 'W. W. Norton & Company', 2018, 512, 8, 'Environmental Fiction', 'English'),
('9780593311233', 'The Nickel Boys', 'Colson Whitehead', 'Doubleday', 2019, 224, 6, 'Historical Fiction', 'English'),
('9780593230251', 'The Anthropocene Reviewed', 'John Green', 'Dutton', 2021, 304, 14, 'Essays', 'English'),
('9780593311219', 'The Midnight Bargain', 'C.L. Polk', 'Erewhon', 2020, 384, 9, 'Fantasy', 'English'),
('9780593087497', 'The Prophets', 'Robert Jones Jr.', 'G.P. Putnam''s Sons', 2021, 400, 11, 'Historical Fiction', 'English');
-- Insert sample data into the 'members' table
INSERT INTO members (first_name, last_name, phone_number, email, address_db, city, state_db, zip_code, membership_start_date)
VALUES
('Liam', 'Nguyen', '5551234567', '[email protected]', '1234 Maple Ave', 'Seattle', 'WA', '98101', '2021-01-15'),
('Emma', 'Garcia', '5559876543', '[email protected]', '5678 Oak St', 'Denver', 'CO', '80202', '2021-03-20'),
('Noah', 'Patel', '5554561234', '[email protected]', '9101 Pine Rd', 'Atlanta', 'GA', '30303', '2021-05-10'),
('Ava', 'Kim', '5557894561', '[email protected]', '1213 Cedar Ln', 'Miami', 'FL', '33101', '2021-07-05'),
('William', 'Chen', '5552345678', '[email protected]', '1415 Birch St', 'Boston', 'MA', '02101', '2021-09-12'),
('Sophia', 'Hernandez', '5556789012', '[email protected]', '1617 Walnut Ave', 'Dallas', 'TX', '75201', '2021-11-08'),
('James', 'Wong', '5553456789', '[email protected]', '1819 Spruce Rd', 'San Francisco', 'CA', '94101', '2022-01-18'),
('Mia', 'Tran', '5557890123', '[email protected]', '2021 Elm Ln', 'Washington', 'DC', '20001', '2022-03-22'),
('Oliver', 'Lee', '5551234568', '[email protected]', '2223 Maple Ave', 'Seattle', 'WA', '98101', '2022-05-15'),
('Charlotte', 'Gonzalez', '5559876544', '[email protected]', '2425 Oak St', 'Denver', 'CO', '80202', '2022-07-20'),
('Elijah', 'Singh', '5554561235', '[email protected]', '2627 Pine Rd', 'Atlanta', 'GA', '30303', '2022-09-10'),
('Amelia', 'Park', '5557894562', '[email protected]', '2829 Cedar Ln', 'Miami', 'FL', '33101', '2022-11-05'),
('Benjamin', 'Liu', '5552345679', '[email protected]', '3031 Birch St', 'Boston', 'MA', '02101', '2023-01-12'),
('Isabella', 'Martinez', '5556789013', '[email protected]', '3233 Walnut Ave', 'Dallas', 'TX', '75201', '2023-03-08'),
('Lucas', 'Choi', '5553456790', '[email protected]', '3435 Spruce Rd', 'San Francisco', 'CA', '94101', '2023-05-18'),
('Harper', 'Vu', '5557890124', '[email protected]', '3637 Elm Ln', 'Washington', 'DC', '20001', '2023-07-22');
-- Insert sample data into the 'loans' table
INSERT INTO loans (member_id, isbn, loan_date, return_due_date)
VALUES
(1, '9780593311257', '2024-01-01', '2024-01-15'),
(2, '9780525559474', '2024-01-05', '2024-01-19'),
(1, '9780593311295', '2024-01-10', '2024-01-24'),
(3, '9780593190517', '2024-01-12', '2024-01-26'),
(4, '9780593311233', '2024-01-15', '2024-01-29'),
(5, '9780593311226', '2024-01-18', '2024-02-01'),
(6, '9780593087503', '2024-01-20', '2024-02-03'),
(7, '9780593311271', '2024-01-22', '2024-02-05'),
(8, '9780525559481', '2024-01-25', '2024-02-08'),
(9, '9780593311257', '2024-01-28', '2024-02-11'),
(10, '9780525559474', '2024-01-30', '2024-02-13');The created_at and updated_at columns in each table are used to track the creation and modification timestamps of records. These columns are automatically populated and updated by the database system.
-
created_atStores the timestamp when a record is inserted into the table. -
updated_atStores the timestamp when a record is updated in the table.
These timestamps can be useful for auditing, tracking changes, and determining the age or freshness of records.
Due to time constrains, some basic JUnit testing was done for the DB Drivers only.
The test class is located on /src/test/java/com/jbt/db/drivers/DriversTest.java.
The version of was: JUnit Jupiter API » 5.11.0-M2