NPE when using a dynamic SQL statement in JavaFX app

Question

I have a basic library crud app using, everything works as it should but Im not sure why Im getting a NPE on an SQL statement. I have a Users and a Books class and I can crud Users and Books with mysql db connection for both. These tables are connected with a foreign key so borrowing a book works too. I have a scene for borrowing and a scene for returning, both very similar using a table view. Borrow shows all books, Return shows all books borrowed by the current logged in user. My question is: when I hard code the sql statement with the logged in userId (WHERE user_id = 1) it populates the table view fine. But when I try to use a dynamic SQL statement (SELECT b.*, d.user_id FROM books b INNER JOIN borrowed d ON b.id = d.book_id WHERE user_id = " + userId). I am able to get the username and I populate a label with it.

Users

package com.example.libraryapp.users;

public class Users {
    private int id;
    private String fullName;
    private String userName;
    private String email;
    private String password;

    public Users(int id, String fullName, String lastName, String email, String password) {
        this.id = id;
        this.fullName = fullName;
        this.userName = lastName;
        this.email = email;
        this.password = password;
    }

    public Users() {

    }

    public int getId() {
        return id;
    }

    public String getFullName() {
        return fullName;
    }

    public String getUserName() {
        return userName;
    }

    public String getEmail() {
        return email;
    }

    public String getPassword() {
        return password;
    }
}

MainController

package com.example.libraryapp;

import com.example.libraryapp.books.BooksController;
import com.example.libraryapp.borrow.BorrowController;
import com.example.libraryapp.screencontroller.ScreensController;
import com.example.libraryapp.users.Users;
import com.example.libraryapp.users.UsersController;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.fxml.Initializable;
import javafx.scene.Node;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.image.ImageView;
import javafx.stage.Stage;

import java.io.IOException;
import java.net.URL;
import java.util.ResourceBundle;

public class MainController implements Initializable {
    @FXML private Button btnMainReturn;
    @FXML private Button btnMainBooks;

    @FXML private Button btnMainUsers;

    @FXML private Button btnMainBorrow;

    @FXML private ImageView ivBranding;

    @FXML private Label lblLogin;

    @FXML private Label lblMainUsername;

    @FXML private Button btnMainReturnToLogin;

    private Users user;

    public Users mainControllerInitData(Users user) {
        this.user = user;
        try {
            if (user != null) {
                lblMainUsername.setText(user.getUserName());
                return user;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void initialize(URL url, ResourceBundle resourceBundle) {

    }

    @FXML
    protected void handleMainToBooks(ActionEvent event) throws IOException {
        FXMLLoader loader = new FXMLLoader(MainController.class.getResource("bookmain.fxml"));
        Parent root = loader.load();

        BooksController bc = loader.getController();
        bc.booksControllerInitData(mainControllerInitData(this.user));

        Stage stage = (Stage) ((Node)event.getSource()).getScene().getWindow();
        Scene scene = new Scene(root);
        stage.setScene(scene);
        stage.show();
    }

    @FXML
    protected void handleMainToUsers(ActionEvent event) throws IOException {
        FXMLLoader loader = new FXMLLoader(MainController.class.getResource("usermain.fxml"));
        Parent root = loader.load();

        UsersController uc = loader.getController();
        uc.userControllerInitData(mainControllerInitData(this.user));

        Stage stage = (Stage) ((Node)event.getSource()).getScene().getWindow();
        Scene scene = new Scene(root);
        stage.setScene(scene);
        stage.show();

    }

    @FXML
    protected void handleMainToBorrow(ActionEvent event) throws IOException {
        FXMLLoader loader = new FXMLLoader(MainController.class.getResource("borrow.fxml"));
        Parent root = loader.load();

        BorrowController bc = loader.getController();
        bc.borrowControllerInitData(mainControllerInitData(this.user));

        Stage stage = (Stage) ((Node)event.getSource()).getScene().getWindow();
        Scene scene = new Scene(root);
        stage.setScene(scene);
        stage.show();
    }

    @FXML
    protected void handleMainToReturn(ActionEvent event) throws IOException {
        FXMLLoader loader = new FXMLLoader(MainController.class.getResource("return.fxml"));
        Parent root = loader.load();

        ReturnController rc = loader.getController();
        rc.returnControllerInitData(mainControllerInitData(this.user));

        Stage stage = (Stage) ((Node)event.getSource()).getScene().getWindow();
        Scene scene = new Scene(root);
        stage.setScene(scene);
        stage.show();
    }

    @FXML
    protected void handleMainLogoutToLoginPage(ActionEvent event) throws IOException {
        FXMLLoader loader = new FXMLLoader(MainController.class.getResource("login.fxml"));
        Parent root = loader.load();

        Stage stage = (Stage) ((Node)event.getSource()).getScene().getWindow();
        Scene scene = new Scene(root);
        stage.setScene(scene);
        stage.show();
    }
}

ReturnController

package com.example.libraryapp;

import com.example.libraryapp.books.Books;
import com.example.libraryapp.books.DaoBooks;
import com.example.libraryapp.users.Users;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.FXMLLoader;
import javafx.fxml.Initializable;
import javafx.scene.Node;
import javafx.scene.Parent;
import javafx.scene.Scene;
import javafx.scene.control.*;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.stage.Modality;
import javafx.stage.Stage;

import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Optional;
import java.util.ResourceBundle;

import static com.example.libraryapp.dao.DBConnection.executeQuery;

public class ReturnController implements Initializable {

    @FXML private Button btnReturnToMain;
    @FXML private Button btnReturn;
    @FXML private Label lblReturnUsername;
    @FXML private TableView<Books> tvReturn;
    @FXML private TableColumn<Books, Integer> colIdReturn;
    @FXML private TableColumn<Books, String> colTitleReturn;
    @FXML private TableColumn<Books, String> colAuthorReturn;
    @FXML private TableColumn<Books, Integer> colYearReturn;
    @FXML private TableColumn<Books, Integer> colPagesReturn;

    private static Users user;

    @Override
    public void initialize(URL url, ResourceBundle resourceBundle) {
        try {
            showReturnBooks();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public Users returnControllerInitData(Users user) {
        ReturnController.user = user;
        lblReturnUsername.setText(user.getUserName());
        return user;
    }

    public static ObservableList<Books> getBookReturnList() throws SQLException {
        ObservableList<Books> bookReturnList = FXCollections.observableArrayList();
        Connection conn = DaoBooks.getConnection();
        String userId = String.valueOf(user.getId());
        String query = "SELECT b.*, d.user_id FROM books b INNER JOIN borrowed d ON b.id = d.book_id WHERE user_id = " + userId;
        assert conn != null;
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(query);
        Books book;
        while (resultSet.next()) {
            book = new Books(resultSet.getInt("id"), resultSet.getString("title"),
                    resultSet.getString("author"), resultSet.getInt("year"),
                    resultSet.getInt("copies"));
            bookReturnList.add(book);
        }
        return bookReturnList;
    }

    public void showReturnBooks() throws SQLException {
        ObservableList<Books> bookList = getBookReturnList();
        colIdReturn.setCellValueFactory(new PropertyValueFactory<Books, Integer>("id"));
        colTitleReturn.setCellValueFactory(new PropertyValueFactory<Books, String>("title"));
        colAuthorReturn.setCellValueFactory(new PropertyValueFactory<Books, String>("author"));
        colYearReturn.setCellValueFactory(new PropertyValueFactory<Books, Integer>("year"));
        colPagesReturn.setCellValueFactory(new PropertyValueFactory<Books, Integer>("copies"));

        tvReturn.setItems(bookList);
    }

    @FXML
    public void handleSelectBookToReturn() throws SQLException {
        Books book = tvReturn.getSelectionModel().getSelectedItem();

        Alert.AlertType type = Alert.AlertType.CONFIRMATION;
        Alert alert = new Alert(type, "");
        alert.initModality(Modality.APPLICATION_MODAL);
        alert.getDialogPane().setHeaderText("Are you sure you want to return: " + book.getTitle() + "?");
        Optional<ButtonType> result = alert.showAndWait();
        if (result.get() == ButtonType.OK) {
            String query = "DELETE FROM borrowed WHERE book_id = " + book.getId();
            executeQuery(query);
            showReturnBooks();
        } else if (result.get() == ButtonType.CANCEL){
            alert.close();
        }



    }


    @FXML
    protected void handleReturnToMain(ActionEvent event) throws IOException {
        FXMLLoader loader = new FXMLLoader(MainController.class.getResource("main.fxml"));
        Parent root = loader.load();

        MainController mc = loader.getController();
        mc.mainControllerInitData(returnControllerInitData(user));

        Stage stage = (Stage) ((Node)event.getSource()).getScene().getWindow();
        Scene scene = new Scene(root);
        stage.setScene(scene);
        stage.show();
    }
}

Return FXML

<?xml version="1.0" encoding="UTF-8"?>

<?import javafx.geometry.Insets?>
<?import javafx.scene.control.Button?>
<?import javafx.scene.control.Label?>
<?import javafx.scene.control.Tab?>
<?import javafx.scene.control.TabPane?>
<?import javafx.scene.control.TableColumn?>
<?import javafx.scene.control.TableView?>
<?import javafx.scene.layout.AnchorPane?>
<?import javafx.scene.layout.HBox?>
<?import javafx.scene.layout.VBox?>
<?import javafx.scene.text.Font?>

<AnchorPane xmlns="http://javafx.com/javafx"
            xmlns:fx="http://javafx.com/fxml"
            fx:controller="com.example.libraryapp.ReturnController"
            prefHeight="500.0" prefWidth="800.0" style="-fx-background-color: a2eeff;">
    <children>
        <VBox alignment="CENTER" prefHeight="154.0" prefWidth="800.0" spacing="20.0">
            <padding>
                <Insets bottom="20.0" left="20.0" right="20.0" top="20.0" />
            </padding>
            <children>

                <Label fx:id="lblLogin" alignment="CENTER" prefHeight="55.0" prefWidth="774.0" text="Return a book" textFill="#c6395c">
                    <font>
                        <Font name="Arial Bold" size="48.0" />
                    </font>
                </Label>
            </children>
        </VBox>
        <HBox layoutX="17.0" layoutY="110.0" prefHeight="34.0" prefWidth="571.0">
            <children>
                <Label prefHeight="34.0" prefWidth="108.0" text="Username:">
                    <font>
                        <Font size="20.0" />
                    </font>
                </Label>
                <Label fx:id="lblReturnUsername" prefHeight="34.0" prefWidth="463.0">
                    <font>
                        <Font size="20.0" />
                    </font>
                </Label>
            </children></HBox>
        <TabPane layoutX="165.0" layoutY="144.0" prefHeight="238.0" prefWidth="470.0" tabClosingPolicy="UNAVAILABLE">
            <tabs>
                <Tab fx:id="tabBorrow" text="Borrow">
                    <content>
                        <AnchorPane minHeight="0.0" minWidth="0.0" prefHeight="180.0" prefWidth="200.0">
                            <children>
                                <TableView fx:id="tvReturn" prefHeight="280.0" prefWidth="470.0">
                                    <columns>
                                        <TableColumn fx:id="colIdReturn" prefWidth="38.0" text="ID"/>
                                        <TableColumn fx:id="colTitleReturn" prefWidth="151.0" text="Title"/>
                                        <TableColumn fx:id="colAuthorReturn" prefWidth="133.0" text="Author"/>
                                        <TableColumn fx:id="colYearReturn" prefWidth="64.0" text="Year"/>
                                        <TableColumn fx:id="colPagesReturn" prefWidth="83.0" text="Copies"/>
                                    </columns>
                                </TableView>
                            </children>
                        </AnchorPane>
                    </content>
                </Tab>
            </tabs>
        </TabPane>
        <Button fx:id="btnReturn" onAction="#handleSelectBookToReturn" layoutX="34.0" layoutY="170.0" mnemonicParsing="false" prefWidth="97.0" text="Return" />
        <Button fx:id="btnReturnToMain" layoutX="34.0" layoutY="219.0" mnemonicParsing="false" onAction="#handleReturnToMain" prefWidth="97.0" text="Main" />
    </children>
</AnchorPane>

Error

Exception in thread "JavaFX Application Thread" java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
    at javafx.fxml/javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1787)
    at javafx.fxml/javafx.fxml.FXMLLoader$ControllerMethodEventHandler.handle(FXMLLoader.java:1670)
    at javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:86)
    at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:49)
    at javafx.base/javafx.event.Event.fireEvent(Event.java:198)
    at javafx.graphics/javafx.scene.Node.fireEvent(Node.java:8879)
    at javafx.controls/javafx.scene.control.Button.fire(Button.java:200)
    at javafx.controls/com.sun.javafx.scene.control.behavior.ButtonBehavior.mouseReleased(ButtonBehavior.java:206)
    at javafx.controls/com.sun.javafx.scene.control.inputmap.InputMap.handle(InputMap.java:274)
    at javafx.base/com.sun.javafx.event.CompositeEventHandler$NormalEventHandlerRecord.handleBubblingEvent(CompositeEventHandler.java:218)
    at javafx.base/com.sun.javafx.event.CompositeEventHandler.dispatchBubblingEvent(CompositeEventHandler.java:80)
    at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:238)
    at javafx.base/com.sun.javafx.event.EventHandlerManager.dispatchBubblingEvent(EventHandlerManager.java:191)
    at javafx.base/com.sun.javafx.event.CompositeEventDispatcher.dispatchBubblingEvent(CompositeEventDispatcher.java:59)
    at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:58)
    at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at javafx.base/com.sun.javafx.event.BasicEventDispatcher.dispatchEvent(BasicEventDispatcher.java:56)
    at javafx.base/com.sun.javafx.event.EventDispatchChainImpl.dispatchEvent(EventDispatchChainImpl.java:114)
    at javafx.base/com.sun.javafx.event.EventUtil.fireEventImpl(EventUtil.java:74)
    at javafx.base/com.sun.javafx.event.EventUtil.fireEvent(EventUtil.java:54)
    at javafx.base/javafx.event.Event.fireEvent(Event.java:198)
    at javafx.graphics/javafx.scene.Scene$MouseHandler.process(Scene.java:3851)
    at javafx.graphics/javafx.scene.Scene$MouseHandler.access$1200(Scene.java:3579)
    at javafx.graphics/javafx.scene.Scene.processMouseEvent(Scene.java:1849)
    at javafx.graphics/javafx.scene.Scene$ScenePeerListener.mouseEvent(Scene.java:2588)
    at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:397)
    at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler$MouseEventNotification.run(GlassViewEventHandler.java:295)
    at java.base/java.security.AccessController.doPrivileged(Native Method)
    at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.lambda$handleMouseEvent$2(GlassViewEventHandler.java:434)
    at javafx.graphics/com.sun.javafx.tk.quantum.QuantumToolkit.runWithoutRenderLock(QuantumToolkit.java:390)
    at javafx.graphics/com.sun.javafx.tk.quantum.GlassViewEventHandler.handleMouseEvent(GlassViewEventHandler.java:433)
    at javafx.graphics/com.sun.glass.ui.View.handleMouseEvent(View.java:556)
    at javafx.graphics/com.sun.glass.ui.View.notifyMouse(View.java:942)
    at javafx.graphics/com.sun.glass.ui.mac.MacView.notifyMouse(MacView.java:127)
Caused by: java.lang.reflect.InvocationTargetException
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at com.sun.javafx.reflect.Trampoline.invoke(MethodUtil.java:76)
    at jdk.internal.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at javafx.base/com.sun.javafx.reflect.MethodUtil.invoke(MethodUtil.java:273)
    at javafx.fxml/com.sun.javafx.fxml.MethodHelper.invoke(MethodHelper.java:83)
    at javafx.fxml/javafx.fxml.FXMLLoader$MethodHandler.invoke(FXMLLoader.java:1782)
    ... 45 more
Caused by: javafx.fxml.LoadException: 
/Volumes/toshiba%201TB/Web%20Developer%20Course/Apps/LibraryApp/target/classes/com/example/libraryapp/return.fxml

    at javafx.fxml/javafx.fxml.FXMLLoader.constructLoadException(FXMLLoader.java:2625)
    at javafx.fxml/javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2603)
    at javafx.fxml/javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2466)
    at javafx.fxml/javafx.fxml.FXMLLoader.load(FXMLLoader.java:2435)
    at com.example.libraryapp/com.example.libraryapp.MainController.handleMainToReturn(MainController.java:106)
    ... 56 more
Caused by: java.lang.NullPointerException
    at com.example.libraryapp/com.example.libraryapp.ReturnController.getBookReturnList(ReturnController.java:65)
    at com.example.libraryapp/com.example.libraryapp.ReturnController.showReturnBooks(ReturnController.java:81)
    at com.example.libraryapp/com.example.libraryapp.ReturnController.initialize(ReturnController.java:50)
    at javafx.fxml/javafx.fxml.FXMLLoader.loadImpl(FXMLLoader.java:2573)
    ... 59 more

So my question is, if I have checked that an object (user) is not null then used user.getUserName it to fill a Label in the ReturnController, but what is happening when using user.getId in an SQL statement returns null. But hard coding the userId works fine?

Does this answer your question? [What is a NullPointerException, and how do I fix it?](https://stackoverflow.com/questions/218384/what-is-a-nullpointerexception-and-how-do-i-fix-it) — f1sh, Jun 03 '22 at 11:44
You [need to show errors](https://technojeeves.com/index.php/aliasjava1/17-errors) — g00se, Jun 03 '22 at 11:52
This does not look like a JavaFX problem, so I would concentrate on the SQL query in isolation first. — mipa, Jun 03 '22 at 12:07
Still no [mre]. PS Please use standard spelling & punctuation. See [How do comment replies work?](https://meta.stackexchange.com/q/43019/266284) to learn to use `@x` to notify one non-sole non-poster commenter `x` re a comment. Posters, sole commenters & followers of posts always get notified. — philipxy, Jun 03 '22 at 13:06
@philipxy I refined my question, Ive tried checking for null in several spots using the error code and intellij help. — Kayd Anderson, Jun 03 '22 at 13:32
The following line of code does not sit right with me logically speaking. `rc.returnControllerInitData(mainControllerInitData(this.user));`. It should probably be `rc.returnControllerInitData(this.user);`. That's my guess. — SedJ601, Jun 03 '22 at 13:52
*"Ive tried checking for null in several spots"*. What does this mean? Why don't you just read the stack trace, identify the line which is throwing the exception, and use that to figure out what is null? — James_D, Jun 03 '22 at 15:05
@James_D I have tried each in the stack trace (MainController.java:106), (ReturnController.java:65), (ReturnController.java:81) and (ReturnController.java:50). The one that confuses me is (MainController.java:106), it says Parent root = loader.load(); is null. My question is, all the other mainToBooks/Users/Borrow works. I can't see why mainToReturn isnt working like the others? — Kayd Anderson, Jun 03 '22 at 15:40
But it tells you the exception is thrown on line 65 of ReturnController. Which line is that? — James_D, Jun 03 '22 at 15:43
@James_D line 65 is: ```String userId = String.valueOf(user.getId());``` as my question asks, I can't find why this is null, but when I hard code in the userId it works fine. And the user.getUserName works for the label. Sorry I must be really missing something obvious. — Kayd Anderson, Jun 03 '22 at 17:36
So `user` is null when you execute `getBookReturnList()`, which is called from `initialize()`, which in turn is invoked when you call `load()` on the `FXMLLoader`, which is done (necessarily) *before* you call `returnControllerInitData(...)`. You need to move anything that depends on the data passed to `returnControllerInitData(...)` to that method. — James_D, Jun 03 '22 at 18:01
Thank you @James_D this was a great learning curve. For beginners like me, instead of using the ```initialize()``` method to call ```getBookReturnList``` and ```showReturnBooks()``` as James said to do it in ```returnControllerInitData()``` worked perfect. Thanks again. — Kayd Anderson, Jun 03 '22 at 21:26

NPE when using a dynamic SQL statement in JavaFX app

0 Answers0