7
\$\begingroup\$

Requirements:

  • Add/Delete member.
  • Add/Delete book.
  • Issue/Return book.

Review Request:

General coding comments, bad practices, style et cetera.

Code:

Main.java:

package library;

import javafx.application.Application;
import javafx.fxml.FXMLLoader;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.stage.Stage;

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

import java.io.IOException;

public class Main extends Application {
    private static Stage stg;
    private static final String URL = "jdbc:sqlite:src/library/library.db";
    static Connection conn = null;

    private boolean checkDatabaseTables() throws SQLException {
        final var query = "SELECT name FROM sqlite_master WHERE type = 'table' AND name NOT LIKE 'sqlite_%' LIMIT 1;";
        var stmt = conn.createStatement();
        var rs = stmt.executeQuery(query);
        return rs.next();
    }

    private void populateDatabase() throws SQLException {
        final var query1 = """
                    CREATE TABLE books (
                      id INTEGER PRIMARY KEY,
                      name TEXT NOT NULL,
                      author TEXT NOT NULL,
                      genre TEXT NOT NULL,
                      total_count INTEGER NOT NULL,
                      total_alloc INTEGER NOT NULL DEFAULT 0
                  );
                """;

        final var query2 = """
                CREATE TABLE members (
                    id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    address TEXT NOT NULL,
                    phone_no TEXT NOT NULL,
                    alloc_book_id INTEGER REFERENCES books(id)
                );
                """;

        final var query3 = """
                CREATE TABLE admin (
                    id INTEGER PRIMARY KEY CHECK (id = 1), -- Ensure only one entry
                    password_hash TEXT NOT NULL
                );
                """;

        var stmt = conn.createStatement();
        stmt.execute(query1);
        stmt.execute(query2);
        stmt.execute(query3);
    }

    @Override
    public void start(Stage primaryStage) throws Exception {
        try {
            conn = DriverManager.getConnection(URL);
        } catch (SQLException e) {
            System.err.println(e.getMessage());
            throw new SQLException("Failed to establish connection with database!");
        }

        final var databaseExists = checkDatabaseTables();

        if (!databaseExists) {
            populateDatabase();
        }

        var startingStage = !databaseExists ? "Signup.fxml" : "Login.fxml";
        stg = primaryStage;
        primaryStage.setResizable(false);
        Parent root = FXMLLoader.load(getClass().getResource(startingStage));
        primaryStage.setTitle("Library Management System");
        primaryStage.setScene(new Scene(root, 600, 400));
        primaryStage.show();
    }

    public void changeScene(String fxml) throws IOException {
        Parent pane = FXMLLoader.load(getClass().getResource(fxml));
        stg.getScene().setRoot(pane);
    }

    public static void main(String[] args) {
        launch(args);
    }
}

Login.java:

package library;

import javafx.fxml.FXML;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.PasswordField;
import javafx.event.ActionEvent;

import java.sql.SQLException;

public class Login {
    public Login() {
    }

    @FXML
    private Button button;

    @FXML
    private Label wrongLogin;

    @FXML
    private PasswordField password;

    public void userLogin(ActionEvent event) throws Exception {
        checkLogin();
    }

    private String getPassword() throws Exception {
        final var query = "SELECT password_hash FROM admin";

        try (var rs = Main.conn.createStatement().executeQuery(query)) {
            return rs.getString("password_hash");
        } catch (Exception e) {
            wrongLogin.setText("Internal error: Failed to login!");
            System.err.println(e.getMessage());
            throw new SQLException("Failed to fetch password!");
        }
    }

    private void checkLogin() throws Exception {
        var m = new Main();
        final var pwd = getPassword();

        if (password.getText().equals(pwd)) {
            m.changeScene("AfterLogin.fxml");
        } else if (password.getText().isEmpty()) {
            wrongLogin.setText("Please enter your password!");
        } else {
            wrongLogin.setText("Incorrect password!");
        }
    }
}

Signup.java:

package library;

import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.Button;
import javafx.scene.control.PasswordField;

import java.sql.SQLException;

public class Signup {
    public Signup() {
    }

    @FXML
    private Button button;

    @FXML
    private PasswordField password;

    public void userSignup(ActionEvent event) throws Exception {
        // TODO: Hash password.
        final var pwd = password.getText();
        final var query = "INSERT INTO admin (password_hash) VALUES (?)";

        try (var pstmt = Main.conn.prepareStatement(query)) {
            pstmt.setString(1, pwd);
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw new Exception("Failed to sign up!");
        }

        var m = new Main();
        m.changeScene("Login.fxml");
    }
}

AfterLogin.java:

package library;

import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.scene.control.Button;

import java.io.IOException;

public class AfterLogin {
    @FXML
    private Button memberManagement;

    @FXML
    private Button bookManagement;

    @FXML
    private Button borrowingAndReturning;

    public void manageMember(ActionEvent event) throws IOException {
        var m = new Main();
        m.changeScene("ManageMember.fxml");
    }

    public void manageBook(ActionEvent event) throws IOException {
        var m = new Main();
        m.changeScene("ManageBook.fxml");
    }

    public void borrowAndReturn(ActionEvent event) throws IOException {
        var m = new Main();
        m.changeScene("BorrowAndReturn.fxml");
    }

    public void userLogout(ActionEvent event) throws IOException {
        var m = new Main();
        m.changeScene("Login.fxml");
    }
}

ManageMember.java:

package library;

import javafx.fxml.FXML;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.TextField;
import javafx.event.ActionEvent;

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

import java.io.IOException;

public class ManageMember {
    @FXML
    private TextField MemberName;

    @FXML
    private TextField HouseAddress;

    @FXML
    private TextField ContactAdd;

    @FXML
    private TextField ContactRemove;

    @FXML
    private Button Home;

    @FXML
    private Button AddMember;

    @FXML
    private Button RemoveMember;

    @FXML
    private Label AddLabel;

    @FXML
    private Label RemoveLabel;

    final Connection conn = Main.conn;

    public void returnHome(ActionEvent event) throws IOException {
        var m = new Main();
        m.changeScene("AfterLogin.fxml");
    }

    private boolean checkMember(String phone_no) throws Exception {
        final var query = "SELECT name FROM members WHERE phone_no = ?";
        var pstmt = conn.prepareStatement(query);
        pstmt.setString(3, phone_no);
        final var rs = pstmt.executeQuery();
        return rs.next();
    }

    public void addMember(ActionEvent event) throws Exception {
        final var name = MemberName.getText();
        final var address = HouseAddress.getText();
        final var phone_no = ContactAdd.getText();

        try {
            if (name.isEmpty() || address.isEmpty() || phone_no.isEmpty()) {
                AddLabel.setText("Missing details!");
            } else {
                if (!checkMember(phone_no)) {
                    final var query = "INSERT INTO members (name, address, phone_no) VALUES (?, ?, ?)";
                    var pstmt = conn.prepareStatement(query);

                    pstmt.setString(1, name);
                    pstmt.setString(2, address);
                    pstmt.setString(3, phone_no);
                    pstmt.executeUpdate();

                    AddLabel.setText("");
                    MemberName.clear();
                    HouseAddress.clear();
                    ContactAdd.clear();
                } else {
                    AddLabel.setText("Member already exists!");
                }
            }
        } catch (SQLException e) {
            AddLabel.setText("Internal error: Failed to add member!");
            System.err.println(e.getMessage());
            throw new SQLException("Failed to add member!");
        }
    }

    public void removeMember(ActionEvent event) throws Exception {
        final var phone_no = ContactRemove.getText();

        try {
            if (phone_no.isEmpty()) {
                RemoveLabel.setText("Missing field!");
            } else {
                var query = "SELECT alloc_book_id FROM members WHERE phone_no = ?";
                var pstmt = conn.prepareStatement(query);
                pstmt.setString(1, phone_no);
                var rs = pstmt.executeQuery();

                if (rs.next()) { // Member exists.
                    if (rs.getObject("alloc_book_id") != null) { // Has issued a book.
                        // Add 1 to total_alloc
                        query = "SELECT total_alloc FROM books WHERE id = ?";
                        var pstmt1 = conn.prepareStatement(query);
                        final var id = rs.getInt("alloc_book_id");
                        pstmt1.setInt(1, id);
                        rs = pstmt1.executeQuery();
                        var total_alloc = rs.getInt("total_alloc") - 1;

                        query = "UPDATE books SET total_alloc = ? WHERE id = ?";
                        pstmt1 = conn.prepareStatement(query);
                        pstmt1.setInt(1, total_alloc);
                        pstmt1.setInt(2, id);
                        pstmt1.executeUpdate();
                    }

                    query = "DELETE FROM members WHERE phone_no = ?";
                    var pstmt2 = conn.prepareStatement(query);
                    pstmt2.setString(1, phone_no);
                    pstmt2.executeUpdate();

                    RemoveLabel.setText("");
                    ContactRemove.clear();
                } else {
                    RemoveLabel.setText("The member does not exist!");
                }
            }
        } catch (SQLException e) {
            RemoveLabel.setText("Internal error: Failed to remove book!");
            System.err.println(e.getMessage());
            throw new SQLException("Failed to remove member!");
        }
    }
}

ManageBook.java:

package library;

import javafx.fxml.FXML;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.TextField;
import javafx.event.ActionEvent;

import java.sql.Connection;
import java.sql.SQLException;
import java.io.IOException;

public class ManageBook {
    @FXML
    private TextField BookName;

    @FXML
    private TextField AuthorName;

    @FXML
    private TextField Genre;

    @FXML
    private Button Home;

    @FXML
    private Button AddBook;

    @FXML
    private Button RemoveBook;

    @FXML
    private Label Error;

    final Connection conn = Main.conn;

    public void returnHome(ActionEvent event) throws IOException {
        var m = new Main();
        m.changeScene("AfterLogin.fxml");
    }

    private void insertBook(String name, String author, String genre, int total_count) throws Exception {
        final var query = "INSERT INTO books (name, author, genre, total_count) VALUES (?, ?, ?, ?)";
        var pstmt = Main.conn.prepareStatement(query);
        pstmt.setString(1, name);
        pstmt.setString(2, author);
        pstmt.setString(3, genre);
        pstmt.setInt(4, total_count);
        pstmt.executeUpdate();
    }

    private void updateBookCount(String name, String author, String genre, int count) throws Exception {
        final var query = "UPDATE books SET total_count = ? WHERE name = ? AND author = ? AND genre = ?";
        var pstmt = conn.prepareStatement(query);
        pstmt.setInt(1, count);
        pstmt.setString(2, name);
        pstmt.setString(3, author);
        pstmt.setString(4, genre);
        pstmt.executeUpdate();
    }

    public void addBook(ActionEvent event) throws Exception {
        final var name = BookName.getText();
        final var author = AuthorName.getText();
        final var genre = Genre.getText();
        int total_count = 1;

        final var query = "SELECT name, author, genre, total_count FROM books WHERE name = ? AND author = ? AND genre = ?";

        if (name.isEmpty() || author.isEmpty() || genre.isEmpty()) {
            Error.setText("Missing details!");
        } else {
            try (var pstmt = conn.prepareStatement(query)) {
                pstmt.setString(1, name);
                pstmt.setString(2, author);
                pstmt.setString(3, genre);

                final var rs = pstmt.executeQuery();

                if (rs.next()) {
                    total_count += rs.getInt("total_count");
                    updateBookCount(name, author, genre, total_count);
                } else {
                    insertBook(name, author, genre, total_count);
                }

                Error.setText("");
                BookName.clear();
                AuthorName.clear();
                Genre.clear();
            } catch (Exception e) {
                Error.setText("Internal error: Failed to add book!");
                System.err.println(e.getMessage());
                throw new SQLException("Failed to add book!");
            }
        }
    }

    private void deleteBook(String name, String author, String genre) throws Exception {
        final var query = "DELETE FROM books WHERE name = ? AND author = ? AND genre = ?";
        var pstmt = conn.prepareStatement(query);
        pstmt.setString(1, name);
        pstmt.setString(2, author);
        pstmt.setString(3, genre);
        pstmt.executeUpdate();
    }

    public void removeBook(ActionEvent event) throws Exception {
        final var name = BookName.getText();
        final var author = AuthorName.getText();
        final var genre = Genre.getText();

        final var query = "SELECT name, author, genre, total_count, total_alloc FROM books WHERE name = ? AND author = ? AND genre = ?";

        if (name.isEmpty() || author.isEmpty() || genre.isEmpty()) {
            Error.setText("Missing details!");
        } else {
            try (var pstmt = conn.prepareStatement(query)) {
                pstmt.setString(1, name);
                pstmt.setString(2, author);
                pstmt.setString(3, genre);

                final var rs = pstmt.executeQuery();

                if (!rs.next()) {
                    Error.setText("Book does not exist!");
                } else if (rs.getInt("total_count") > rs.getInt("total_alloc")) {
                    final var total_count = rs.getInt("total_count") - 1;

                    if (total_count == 0) {
                        deleteBook(name, author, genre);
                    } else {
                        updateBookCount(name, author, genre, total_count);
                    }

                    Error.setText("");
                    BookName.clear();
                    AuthorName.clear();
                    Genre.clear();
                } else {
                    Error.setText("The book is currently issued!");
                }
            } catch (Exception e) {
                Error.setText("Internal error: Failed to remove book!");
                System.err.println(e.getMessage());
                throw new SQLException("Failed to remove book!");
            }
        }
    }
}

BorrowAndReturn.java:

package library;

import javafx.fxml.FXML;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.TextField;
import javafx.event.ActionEvent;

import java.sql.Connection;
import java.sql.SQLException;
import java.io.IOException;

public class BorrowAndReturn {
    @FXML
    private TextField BookName;

    @FXML
    private TextField AuthorName;

    @FXML
    private TextField Genre;

    @FXML
    private TextField ContactNo;

    @FXML
    private Button Home;

    @FXML
    private Button IssueBook;

    @FXML
    private Button ReturnBook;

    @FXML
    private Label Error;

    final Connection conn = Main.conn;

    public void returnHome(ActionEvent event) throws IOException {
        var m = new Main();
        m.changeScene("AfterLogin.fxml");
    }

    public void issueBook(ActionEvent event) throws Exception {
        final var book = BookName.getText();
        final var author = AuthorName.getText();
        final var genre = Genre.getText();
        final var phone_no = ContactNo.getText();

        var query = "SELECT id, total_count, total_alloc FROM books WHERE name = ? AND author = ? AND genre = ?";

        if (book.isEmpty() || author.isEmpty() || genre.isEmpty() || phone_no.isEmpty()) {
            Error.setText("Missing details!");
        } else {
            try (var pstmt = conn.prepareStatement(query)) {
                pstmt.setString(1, book);
                pstmt.setString(2, author);
                pstmt.setString(3, genre);

                var rs = pstmt.executeQuery();

                if (!rs.next()) {
                    Error.setText("The book doesn't exist in the database!");
                } else if (rs.getInt("total_alloc") == rs.getInt("total_count")) {
                    Error.setText("All the books have already been issued!");
                } else {
                    query = "SELECT * FROM members WHERE phone_no = ?";
                    var pstmt2 = conn.prepareStatement(query);
                    pstmt2.setString(1, phone_no);
                    var res = pstmt2.executeQuery();

                    if (res.next() && res.getObject("alloc_book_id") == null) {
                        query = "UPDATE members SET alloc_book_id = ? WHERE phone_no = ?";

                        var pstmt3 = conn.prepareStatement(query);
                        pstmt3.setInt(1, rs.getInt("id"));
                        pstmt3.setString(2, phone_no);
                        pstmt3.executeUpdate();

                        query = "UPDATE books SET total_alloc = ? WHERE name = ? AND author = ? AND genre = ?";

                        var pstmt4 = conn.prepareStatement(query);
                        pstmt4.setInt(1, rs.getInt("total_alloc") + 1);
                        pstmt4.setString(2, book);
                        pstmt4.setString(3, author);
                        pstmt4.setString(4, genre);
                        pstmt4.executeUpdate();

                        Error.setText("");
                        BookName.clear();
                        AuthorName.clear();
                        Genre.clear();
                        ContactNo.clear();
                    } else {
                        Error.setText("The member does not exist or has already issued a book!");
                    }
                }
            } catch (Exception e) {
                Error.setText("Internal error: Failed to issue book!");
                System.err.println(e.getMessage());
                throw new SQLException("Failed to issue book!");
            }
        }
    }

    public void returnBook(ActionEvent event) throws Exception {
        final var book = BookName.getText();
        final var author = AuthorName.getText();
        final var genre = Genre.getText();
        final var phone_no = ContactNo.getText();
        var query = "SELECT * FROM books WHERE name = ? AND author = ? AND genre = ?";

        if (book.isEmpty() || author.isEmpty() || genre.isEmpty() || phone_no.isEmpty()) {
            Error.setText("Missing details!");
        } else {
            try (var pstmt = conn.prepareStatement(query)) {
                pstmt.setString(1, book);
                pstmt.setString(2, author);
                pstmt.setString(3, genre);

                var rs = pstmt.executeQuery();

                if (!rs.next()) {
                    Error.setText("The book doesn't exist in the database!");
                } else {
                    query = "SELECT * FROM members WHERE phone_no = ?";
                    var pstmt2 = conn.prepareStatement(query);
                    pstmt2.setString(1, phone_no);
                    var res = pstmt2.executeQuery();

                    if (res.next() && res.getInt("alloc_book_id") == rs.getInt("id")) {
                        query = "UPDATE members SET alloc_book_id = NULL WHERE phone_no = ?";
                        var pstmt3 = conn.prepareStatement(query);
                        pstmt3.setString(1, phone_no);
                        pstmt3.executeUpdate();

                        query = "UPDATE books SET total_alloc = ? WHERE name = ? AND author = ? AND genre = ?";
                        var pstmt4 = conn.prepareStatement(query);
                        pstmt4.setInt(1, rs.getInt("total_alloc") - 1);
                        pstmt4.setString(2, book);
                        pstmt4.setString(3, author);
                        pstmt4.setString(4, genre);
                        pstmt4.executeUpdate();
                    } else {
                        Error.setText("The member does not exist or has not issued this book!");
                    }

                    Error.setText("");
                    BookName.clear();
                    AuthorName.clear();
                    Genre.clear();
                    ContactNo.clear();
                }
            } catch (Exception e) {
                Error.setText("Internal error: Failed to return book!");
                System.err.println(e.getMessage());
                throw new SQLException("Failed to return book!");
            }
        }
    }
}
\$\endgroup\$

3 Answers 3

Reset to default
7
\$\begingroup\$

Separations of Concerns

You've mixed UI with the business logic. The same components are responsible for interacting with the user and communicating with the database.

By doing so, you're violating the Single responsibility principle which leads to clattered low-cohesion code which is hard to evolve and horrendously hard to test (I'm talking about automated testing) because the data layer is coupled to UI.

I'd suggest introducing abstractions solely responsible for interacting with the database instead of issuing quires from the UI.

Connection Management

There are many places in the code where you're not closing a Statement and you're not closing the Connection opened at the application startup.

You should really take care of this to avoid resource leaks, and it would be even more important if you switch from an embedded SQLite database to an external database on a remote server (then clients would be accumulating idle connections on the server-side, exhausting connection pool and increasing memory-usage).

If you wish to open a connection at the start-up and recycle and recycle it throughout the whole user-session, I would recommend to use connection pooling by creating a DataSource (HikariCP is a commonly used implementation) with the pool size of one.

And if you decide to follow my advice on using HikariCP, be aware that HikariDataSource has method close() (which is not available in javax.sql.DataSource) that should be invoked at application termination. To achieve that, you can override javafx.application.Application.stop() method.

Exception-handling

Avoid catching general exception super types such as Exception or Throwable.

Except for the code in a global exception handler, dealing with exceptions that are not treated in your business logic

catch (Exception e) {
    wrongLogin.setText("Internal error: Failed to login!");
    System.err.println(e.getMessage());
    throw new SQLException("Failed to fetch password!");
}

This is wrong, a caught exception might be a RuntimeException caused by a bug which will be rethrown an SQLException.

There's a couple of more issues:

  • You should be using proper logging tools (such as logging frameworks Logback, or Log4j2) instead of printing on the console;
  • You want to log the whole exception data, not only exception message, because stack trace is very helpful while investigating the cause of exception.
  • This catch-block exemplifies the log and rethrow antipattern. You're logging an exception and then throwing a new exception with a new message, which creates a possibility of logging the same exception twice (by the upstream caller) causing a mess in the logs.

A better approach is to catch only narrow exception types that match use cases of your business logic. And set a global exception handler using Thread.setDefaultUncaughtExceptionHandler() to deal with those exceptions that are not treated anywhere in your code.

Thread
    .currentThread()
    .setUncaughtExceptionHandler(<ClassName>::handleUncaught)

private static void handleUncaught(Thread thread, Throwable throwable) {
        // error-handling logic here; 
        // use Java 21 Pattern Matching for switch expressions 
        // to determine what action to perform based on the exception type
    }
}

And in case if you need to add some data to exception, wrap with a new exception, this way the initial exception because a cause (accessible via getCause()) of the new one, instead of being lost.

Password hashing

Never store passwords in plain text.

and avoid // TO DO comments in the code submitted for review, you kind of know that passwords should be hashed, yet hashing doesn't take place

So, instead you need to use a cryptographic algorithm, such as BCrypt or Argon2 (implementations of both available in the Bouncy Castle library), to generate hashes that will be persisted into the database.

Note, that it would be better to have a dedicated type which encapsulates a cryptographic function and exposes methods for generating a hash-string and verifying a password, rather than putting that code in the UI.

Naming

MemberName, HouseAddress, etc. - according to the Java language naming convention, field names should start with a lowercase letter.

\$\endgroup\$
5
\$\begingroup\$

Alexander Ivanchenko treated the most points. That you were aware of the need of password hashing is clear, so okay.

In general I find the code advanced, assuming some non-java background, and thorough java study. Despite the missed java conventions that are taken really serious in the java community.

Below I think the original code was larger as the phone_no is not the 3rd parameter.

What I deplore is the fine granularity of methods, as I would suggest always using try-with-resources on AutoCloseable objects like PreparedStatement and ResultSet. Then one could split the methods a bit differently. You already did use try-with-resources, so fine.

private boolean checkMember(String phone_no) throws Exception {
    final var query = "SELECT name FROM members WHERE phone_no = ?";
    try (var pstmt = conn.prepareStatement(query)) {
        pstmt.setString(3, phone_no);
        try {final var rs = pstmt.executeQuery();
            return rs.next();
        }
    }
}

Here pstmt and rs closed despite the inner return or a possible SQLException (which in a webapp could cause leaking resources).

Separating view fields from data object is neater. JavaFX has some means with form validation and error messages. But indeed something for later. Your main javafx class or javafx controller class could have the @FXML fields (View), and hold the logic in a separate class (with the Connection).

Instead of System.out and System.err a logger should be used, even if loggers often are more verbose, printing maybe class and method too.

There are several popular loggers. Standard is java.util.logging. One advice especially for libraries is to use the standard logging façade System.getLogger so any application using that library can itself choose the exact logging implementation. When no implementation is indicated it defaults to java.util.logging. Typically:

private final Logger LOGGER = System.getLogger(getClass().getName());
...
    LOGGER.log(Level.WARNING, "...");

Something to consider in future: concurrent access of several people at once, with connections, connection pool. Transactional connection where all done operations are either committed successfully or rolled back.

Your check on SQL NULL for alloc_book_id can be done in JDBC, though a bit ugly as below.

public void removeMember(ActionEvent event) throws Exception {
    final var phone_no = ContactRemove.getText();
    if (phone_no.isBlank()) { // Form validation up front.
        RemoveLabel.setText("Missing field!");
        return;
    }
    var query = """
        SELECT alloc_book_id
        FROM members
        WHERE phone_no = ?
        """;
    try (var pstmt = conn.prepareStatement(query)) {
        pstmt.setString(1, phone_no);
        try (var rs = pstmt.executeQuery()) {
            if (rs.next()) { // Member exists.
                final int id = rs.getInt("alloc_book_id");
                if (!rs.wasNull()) { // Has issued a book.
                    // Remove 1 from total_alloc
                    var query1 = """
                        UPDATE books
                        SET total_alloc = total_alloc - 1
                        WHERE id = ?
                        """;
                    try (var pstmt1 = conn.prepareStatement(query1)) {
                        pstmt1.setInt(1, id);
                        pstmt1.executeUpdate();
                    }
                }
                var query2 = "DELETE FROM members WHERE phone_no = ?";
                try (var pstmt2 = conn.prepareStatement(query2)) {
                    pstmt2.setString(1, phone_no);
                    pstmt2.executeUpdate();
                }

                RemoveLabel.setText("");
                ContactRemove.clear();
            } else {
                RemoveLabel.setText("The member does not exist!");
            }
        }
    } catch (SQLException e) {
        RemoveLabel.setText("Internal error: Failed to remove book!");
        LOGGER.log(Level.ERROR, e.getMessage());
        throw new SQLException("Failed to remove member!", e);
    }
}

What can be seen, is that every small prepared statement should have its own method, and not be numbered as here. That is error prone.

For concurreny sake increasing or decreasing a field in the database is best done - atomically - in the database itself. Otherwise two people at the same time could corrupt the count.

\$\endgroup\$
5
\$\begingroup\$

All of the Buttons go unused:

    @FXML
    private Button memberManagement;

    @FXML
    private Button bookManagement;

    @FXML
    private Button borrowingAndReturning;

There's no need to declare them if you're not using them. Moreover, they can be written more succintly:

    @FXML
    private Button memberManagement, bookManagement, borrowingAndReturning;

Lowercase everything before insertions:

'Harry Potter and the Philosopher's Stone' and 'harry potter and the philosopher's stone' would compare equal this way, and you probably do not want different entries for them.

Validate the contact numbers:

In the database's members table, the phone_no field should be validated. Currently, "csakcbkasd" is just as valid as "+39-199-1341".

\$\endgroup\$

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.