How can I access DatePicker value in JavaFX for SELECT MySQL Query?

Question

I'm building a very simple hotel booking program with JDBC. I have 3 tables

Guests(GuestID,LastName,FirstName,Email,PhoneNumber)
Rooms(RoomNumber,Type,Rate)
Reservation(RoomNumber, CheckInDate, CheckOutDate).

If I choose a date and press "Search" button, the tableview should list all the available rooms. However, mine shows nothing.

Main Window

Empty TableView

The following code is what I did for DataSource class.

public static final String QUERY_VACANCY = "SELECT r.* FROM " + TABLE_ROOMS + " r WHERE NOT EXISTS (SELECT 1 FROM " + TABLE_RESERVATION + " re WHERE " +
        "re." + COLUMN_ROOM + "=" + "r." + COLUMN_ROOM + " AND " + "((?>=re." + COLUMN_CHECKIN + " AND ?<re." + COLUMN_CHECKOUT
        + ") OR " + "(?<re." + COLUMN_CHECKOUT + " AND ?>=re." + COLUMN_CHECKIN + ")))";


private PreparedStatement queryVacancy;
private Connection connection;

public boolean open() {
    try {
        connection = DriverManager.getConnection(CONNECTION_STRING, "mememem", "somethingsomething123");
        queryVacancy = connection.prepareStatement(QUERY_VACANCY);

        return true;
    } catch (SQLException e) {
        System.out.println("CANNOT Connect to the DB: " + e.getMessage());
        return false;
    }
}



 public List<Rooms> showSearchResult(Date date) {
    try {
        queryVacancy.setDate(1, date);
        queryVacancy.setDate(2, date);
        queryVacancy.setDate(3, date);
        queryVacancy.setDate(4, date);
        ResultSet resultSet = queryVacancy.executeQuery();

        List<Rooms> rooms = new ArrayList<>();
        while (resultSet.next()) {
            Rooms room = new Rooms();
            room.setRoomNumber(resultSet.getInt(COLUMN_ROOM));
            room.setType(resultSet.getString(COLUMN_TYPE));
            room.setRate(resultSet.getInt(COLUMN_RATE));

            rooms.add(room);
        }
        return rooms;
    } catch (SQLException e) {
        System.out.println("QUERY FAILED: " + e.getMessage());
        return null;
    }
}

For the controller, I created the following code.

public class Controller {
    @FXML
    private TableView<Rooms> roomsTableView;

    @FXML
    private DatePicker checkindatepicker;

    public LocalDate getCheckindatepicker(){
        return checkindatepicker.getValue();
    }

    public void listSearchedRooms(){
        Task<ObservableList<Rooms>> task = new GetSearchedRooms();
        roomsTableView.itemsProperty().bind(task.valueProperty());

         new Thread(task).start();
    }
}



class GetSearchedRooms extends Task{

private Controller controller;
    @Override
    protected ObservableList<Rooms> call() throws Exception {

        controller = new Controller();

        Date date = Date.valueOf(controller.getCheckindatepicker());

        System.out.println(date.toString());

        return FXCollections.observableArrayList
                (DataSource.getInstance().showSearchResult(date));
    }

}

Here is my FXml Code.

<center>
    <VBox alignment="CENTER" spacing="40">
        <children>

            <HBox alignment="CENTER">
                <Label text="Date: "></Label>
                <DatePicker fx:id="checkindatepicker"></DatePicker>
            </HBox>
            <Button text="Search" onAction="#listSearchedRooms"></Button>
            <Button text="Reserve" onAction="#showReserve"></Button>
            <Button fx:id="checkReservation" text="Check Reservation" onAction="#showReservation"></Button>
        </children>
    </VBox>
</center>

What I understand is that, a String value should replace the "?" parts of PreparedStatement. That's why I did

        String date = (java.sql.Date.valueOf(checkindatepicker.getValue())).toString();

in call() method

What should I do?

EDIT: The annotated field should be in Controller class. Due to so, I added private variable checkindatepicker in Controller class. Also, to retrieve its value, I created a method getCheckindatepicker(). Then, I created an instance of Controller class in GetSearchedRooms class. Using getCheckindatepicker() method, I would get the value of the chosen date and convert it to java.sql.Date. However, it's still not working.

There must be nothing wrong with the PreparedStatement nor showSearchResult() method, because with the following code, the tableview shows the available rooms on August 31st.

class GetSearchedRooms extends Task{
        @Override
        protected ObservableList<Rooms> call() throws Exception {

            String str = "2017-08-31";
            Date date = java.sql.Date.valueOf(str);

            System.out.println(date.toString());

            return FXCollections.observableArrayList
                    (DataSource.getInstance().showSearchResult(date));
        }

}

Doesn't your call() method throw a null pointer exception? I don't see any way checkindatepicker could be initialized (it's not initialized in code, and it's not defined in the controller, so the @FXML is redundant). Can you use an onFailed handler with the task, or wrap the code in call() in a try-catch block to verify? — James_D
– James_D, Commented Aug 24, 2017 at 2:41
@James_D If I understand correctly, you are wondering where I defined checkindatepicker. I linked in FXml. I will add my FXml atm. — ChrisLEE
– ChrisLEE, Commented Aug 24, 2017 at 2:51
But @FXML-annotated fields are only initialized in the controller. Your GetSearchedRooms instance isn't the controller. Have you checked if it is null? — James_D
– James_D, Commented Aug 24, 2017 at 3:09
@James_D This will be the stupidest thing you've ever heard. I thought FXML annotated field can be initialized anywhere in "Controller.java". Which means, GetSearchedRoom class is in "Controller.java". I must be fully wrong. Now, I am just panicking... — ChrisLEE
– ChrisLEE, Commented Aug 24, 2017 at 3:20

James_D · Accepted Answer · 2017-08-25 02:23:26Z

2

You can do

class GetSearchedRooms extends Task{

    private final Date date ;

    GetSearchRooms(Date date) {
        this.date = date ;
    }

    @Override
    protected ObservableList<Rooms> call() throws Exception {




        return FXCollections.observableArrayList
                (DataSource.getInstance().showSearchResult(date));
    }

}

And then back in the controller replace

Task<ObservableList<Rooms>> task = new GetSearchedRooms();

with

Task<ObservableList<Rooms>> task = 
    new GetSearchedRooms(Date.valueOf(checkindatepicker.getValue()));

In both cases the Date class is java.sql.Date.

answered Aug 25, 2017 at 2:23

James_D

211k16 gold badges312 silver badges345 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ChrisLEE Over a year ago

I just want to say I think it would be better to add NullPointerException.

ChrisLEE Over a year ago

public GetSearchedRooms(Date date) throws NullPointerException{ this.date = date;} and try-catch block in listSearchedRooms method

James_D Over a year ago

@ChrisLEE it's never a good idea to catch a null pointer exception. You can always write code to avoid it if a value might be null.

Collectives™ on Stack Overflow

How can I access DatePicker value in JavaFX for SELECT MySQL Query?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related