ok

 TABLE


BOOK


CREATE TABLE books (

    bookid INT PRIMARY KEY,

    author_name VARCHAR(255),

    book_name VARCHAR(255),

    price DECIMAL(10, 2),

    quantity INT

);


USER

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(255),
    useremail VARCHAR(255),
    userpassword VARCHAR(255),
    usernick_name VARCHAR(255)
);



BOOK_TRANSECTION

CREATE TABLE book_transactions (
    transaction_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    user_id INT,
    transaction_type ENUM('issue', 'return', 'renew'),
    transaction_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expiry_date TIMESTAMP,
    FOREIGN KEY (book_id) REFERENCES books(bookid),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);


ISSUED BOOK

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;

public class BookManager {
    public boolean issueBook(User user, Book book) {
        try (Connection connection = DatabaseConnection.getConnection()) {
            // Decrease book quantity
            decrementBookQuantity(connection, book);

            // Calculate expiry date (current date + 4 days)
            LocalDateTime currentDate = LocalDateTime.now();
            LocalDateTime expiryDate = currentDate.plus(4, ChronoUnit.DAYS);

            // Insert transaction into book_transactions table
            insertTransaction(connection, book.getBookId(), user.getUserId(), "issue", currentDate, expiryDate);

            return true; // Successful book issuance
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // Issue failed
        }
    }

    private void decrementBookQuantity(Connection connection, Book book) throws SQLException {
        String decrementQuery = "UPDATE books SET quantity = quantity - 1 WHERE bookid = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(decrementQuery)) {
            preparedStatement.setInt(1, book.getBookId());
            preparedStatement.executeUpdate();
        }
    }

    private void insertTransaction(Connection connection, int bookId, int userId, String transactionType, LocalDateTime transactionDate, LocalDateTime expiryDate) throws SQLException {
        String insertQuery = "INSERT INTO book_transactions (book_id, user_id, transaction_type, transaction_date, expiry_date) VALUES (?, ?, ?, ?, ?)";
        try (PreparedStatement preparedStatement = connection.prepareStatement(insertQuery)) {
            preparedStatement.setInt(1, bookId);
            preparedStatement.setInt(2, userId);
            preparedStatement.setString(3, transactionType);
            preparedStatement.setObject(4, transactionDate);
            preparedStatement.setObject(5, expiryDate);
            preparedStatement.executeUpdate();
        }
    }
}



RETRUN BOOK 

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class BookManager {
    public boolean returnBook(User user, Book book) {
        try (Connection connection = DatabaseConnection.getConnection()) {
            // Increase book quantity
            incrementBookQuantity(connection, book);

            // Update the return transaction in book_transactions table
            updateReturnTransaction(connection, book.getBookId(), user.getUserId());

            return true; // Successful book return
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // Return failed
        }
    }

    private void incrementBookQuantity(Connection connection, Book book) throws SQLException {
        String incrementQuery = "UPDATE books SET quantity = quantity + 1 WHERE bookid = ?";
        try (PreparedStatement preparedStatement = connection.prepareStatement(incrementQuery)) {
            preparedStatement.setInt(1, book.getBookId());
            preparedStatement.executeUpdate();
        }
    }

    private void updateReturnTransaction(Connection connection, int bookId, int userId) throws SQLException {
        String updateQuery = "UPDATE book_transactions SET transaction_type = 'return' WHERE book_id = ? AND user_id = ? AND transaction_type = 'issue'";
        try (PreparedStatement preparedStatement = connection.prepareStatement(updateQuery)) {
            preparedStatement.setInt(1, bookId);
            preparedStatement.setInt(2, userId);
            preparedStatement.executeUpdate();
        }
    }
}


RENEW BOOK 


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.temporal.ChronoUnit;

public class BookManager {
    private static final int MAX_RENEWALS = 4;

    public boolean renewBook(User user, Book book) {
        try (Connection connection = DatabaseConnection.getConnection()) {
            // Check renewal limit
            if (getRenewalCount(connection, book.getBookId(), user.getUserId()) >= MAX_RENEWALS) {
                return false; // Exceeded renewal limit
            }

            // Update expiry date for renewal
            updateExpiryDateForRenewal(connection, book.getBookId(), user.getUserId());

            return true; // Successful book renewal
        } catch (SQLException e) {
            e.printStackTrace();
            return false; // Renewal failed
        }
    }

    private int getRenewalCount(Connection connection, int bookId, int userId) throws SQLException {
        String countQuery = "SELECT COUNT(*) FROM book_transactions WHERE book_id = ? AND user_id = ? AND transaction_type = 'renew'";
        try (PreparedStatement preparedStatement = connection.prepareStatement(countQuery)) {
            preparedStatement.setInt(1, bookId);
            preparedStatement.setInt(2, userId);
            try (ResultSet resultSet = preparedStatement.executeQuery()) {
                if (resultSet.next()) {
                    return resultSet.getInt(1);
                }
            }
        }
        return 0;
    }

    private void updateExpiryDateForRenewal(Connection connection, int bookId, int userId) throws SQLException {
        LocalDateTime newExpiryDate = LocalDateTime.now().plus(4, ChronoUnit.DAYS);

        String updateQuery = "UPDATE book_transactions SET expiry_date = ? WHERE book_id = ? AND user_id = ? AND transaction_type = 'issue'";
        try (PreparedStatement preparedStatement = connection.prepareStatement(updateQuery)) {
            preparedStatement.setObject(1, newExpiryDate);
            preparedStatement.setInt(2, bookId);
            preparedStatement.setInt(3, userId);
            preparedStatement.executeUpdate();
        }
    }
}



Comments

Post a Comment