IssueBook.java

package LMS.com;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class IssueBook {

    private Scanner sc = new Scanner(System.in);

    // Insert new Issue record
    void insert(Connection connection) throws SQLException {

        String query = """
                INSERT INTO issue_book
                (issue_id, member_id, book_id, issue_date)
                VALUES (?, ?, ?, ?)
                """;

        try (PreparedStatement prestmt = connection.prepareStatement(query)) {

            System.out.print("Enter Issue ID: ");
            int issueId = sc.nextInt();

            System.out.print("Enter Member ID: ");
            int memberId = sc.nextInt();

            System.out.print("Enter Book ID: ");
            int bookId = sc.nextInt();

            System.out.print("Enter Issue Date (YYYY-MM-DD): ");
            String issueDate = sc.next();

            prestmt.setInt(1, issueId);
            prestmt.setInt(2, memberId);
            prestmt.setInt(3, bookId);
            prestmt.setString(4, issueDate);

            int rows = prestmt.executeUpdate();

            if (rows > 0) {
                System.out.println("✅ Book Issued Successfully!");
            } else {
                System.out.println("❌ Issue Failed!");
            }
        }
    }

    // Delete Issue record
    void delete(Connection connection) throws SQLException {

        String query = "DELETE FROM issue_book WHERE issue_id = ?";

        try (PreparedStatement prestmt = connection.prepareStatement(query)) {

            System.out.print("Enter Issue ID to Delete: ");
            int issueId = sc.nextInt();

            prestmt.setInt(1, issueId);

            int rows = prestmt.executeUpdate();

            if (rows > 0) {
                System.out.println("🗑 Issue Record Deleted Successfully!");
            } else {
                System.out.println("❌ Issue ID Not Found!");
            }
        }
    }

    // Update Issue record
    void update(Connection connection) throws SQLException {

        String query = """
                UPDATE issue_book
                SET member_id = ?, book_id = ?, issue_date = ?
                WHERE issue_id = ?
                """;

        try (PreparedStatement prestmt = connection.prepareStatement(query)) {

            System.out.print("Enter Issue ID to Update: ");
            int issueId = sc.nextInt();

            System.out.print("Enter New Member ID: ");
            int memberId = sc.nextInt();

            System.out.print("Enter New Book ID: ");
            int bookId = sc.nextInt();

            System.out.print("Enter New Issue Date (YYYY-MM-DD): ");
            String issueDate = sc.next();

            prestmt.setInt(1, memberId);
            prestmt.setInt(2, bookId);
            prestmt.setString(3, issueDate);
            prestmt.setInt(4, issueId);

            int rows = prestmt.executeUpdate();

            if (rows > 0) {
                System.out.println("✏ Issue Record Updated Successfully!");
            } else {
                System.out.println("❌ Issue ID Not Found!");
            }
        }
    }

    // Get all Issue records
    void getAll(Connection connection) throws SQLException {

        String query = "SELECT * FROM issue_book";

        try (PreparedStatement prestmt = connection.prepareStatement(query);
             ResultSet rs = prestmt.executeQuery()) {

            System.out.println("\n===== 📚 Issued Books =====");

            boolean found = false;
            while (rs.next()) {
                found = true;
                System.out.println(
                        "Issue ID: " + rs.getInt("issue_id") +
                        " | Member ID: " + rs.getInt("member_id") +
                        " | Book ID: " + rs.getInt("book_id") +
                        " | Issue Date: " + rs.getDate("issue_date")
                );
            }

            if (!found) {
                System.out.println("No issued books found.");
            }
        }
    }
}