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

1. Guests(GuestID,LastName,FirstName,Email,PhoneNumber)
2. Rooms(RoomNumber,Type,Rate)
3. 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));
        }

}

Answer 1

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.