In the first version of the hotel application that I showed we stored all of the reservations in a text file. Now that we know how to work with a database it is time to update the Hotel application to store all of its data in MySQL.
The first step is to use the MySQL workbench to create the database for our application. The database schema will be named 'hotel', and it will have two tables.
The first table, named 'rooms', stores basic information about the meeting rooms at the hotel.
| Column | Type | Description |
|---|---|---|
| name | VARCHAR(32) | Room name |
| capacity | INT | Room capacity |
The second table, named 'reservations', stores the reservations.
| Column | Type | Description |
|---|---|---|
| id | INT | Id number for the reservation |
| customer | INT | Id number for the customer |
| size | INT | Group size |
| day | DATE | Date of the reservation |
| start | INT | Start time |
| duration | INT | Duration in hours |
| room | VARCHAR(32) | Room name |
Since this is still going to be an FXML application, we follow the usual procedure for making an FXML application. To add database support we need to do two things. The first is adding a dependency to pom.xml:
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.22</version>
</dependency>
The second is adding a line to module-info.java that indicates that we will be using some classes from the java.sql package:
module edu.lawrence.hotelsql {
requires javafx.controls;
requires javafx.fxml;
requires java.sql;
opens edu.lawrence.hotelsql to javafx.fxml;
exports edu.lawrence.hotelsql;
}
The mechanisms I demonstrated in class for interacting with MySQL are sufficient for fairly simple examples. Since we are dealing with a slightly more complex database in this example we will need to replace some of those simple mechanisms with something more powerful and flexible.
Here is an example. Suppose we wanted to fetch all of the Reservations for a particular room and day. Here is some that shows one way to do this.
ObservableList<Reservation> get(LocalDate date,String room) {
ObservableList<Reservation> results;
results = FXCollections.observableArrayList();
String query = "select * from reservations where day =\'"
+ date.toString() + "\' and room = \'" + room;
try {
ResultSet rSet = statement.executeQuery(query);
while(rSet.next()) {
// Read data from rSet, make Reservation
// objects, and add them to results
}
} catch(SQLException ex) {
ex.printStackTrace();
}
return results;
}
This process is somewhat clunky and error prone. A couple of things can go wrong here. One is that we can forget to put the quote marks around items like strings in the SQL code. A second thing is that we can't be entirely sure that the LocalDate toString() method will generate a representation of the date that matches what the MySQL DATE type expects to see.
An alternative way to do all of this is to use the Java PreparedStatement class. This class allows us to construct SQL code that contains placeholders and then use convenient methods to fill in the place holders.
Here is some code that does the same query as above but with a PreparedStatement.
String query="select * from reservations where room=? and day=?"; PreparedStatement ps = connection.prepareStatement(query); // To launch the query... ps.setString(1,room); ps.setDate(2,java.sql.Date.valueOf(date)); ResultSet rSet = ps.executeQuery();
To fill in the placeholders in the query string we use a series of calls to set methods. Each set method takes two parameters, an int that specifies which placeholder we are filling in, and a piece of data that we want to substitute into the placeholder.
This approach is a lot less error prone. The set methods that the PreparedStatement class uses allow only a specific list of data types, and these data types correspond closely to the data types supported by MySQL. For example, to fill in a placeholder for a SQL DATE, we use setDate(), which only accepts the java.sql.Date class as its parameter. This forces us to look in the documentation to find a way to convert our LocalDate object into a Date. Fortunately, the Date class allows for this, and using this conversion guarantees that we will be sending the date information to the database in the correct format. Another benefit of using the PreparedStatement approach is that we don't have to worry about things like quote marks. The PreparedStatement will put those in the SQL code for us automatically anywhere they are needed.
A common design pattern used in database applications is to create a data access object, or DAO, as the gateway to the database. In this application we will have a HotelDAO class to manage all of the database interactions for us. This class will store all of the connection and statement objects needed to communicate with the database. In addition, the DAO will remember what room and date the user has selected in the GUI, so we can save reservations correctly when we create them in the GUI.
The sections below will cover some of the key tasks that the DAO performs.
The HotelDAO needs to maintain a connection to the Hotel database. The obvious place to set up that connection is in the constructor of the DAO class:
public HotelDAO() {
// Load the database driver
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException ex) {
System.out.println("Could not load database driver.");
}
// Open the connection and set up statements
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/hotel?user=student&password=Cmsc250!");
statement = connection.createStatement();
reservationsStmt = connection.prepareStatement(reservationsSQL);
insertStmt = connection.prepareStatement(insertSQL);
deleteStmt = connection.prepareStatement(deleteSQL);
} catch (SQLException ex) {
System.out.println("Could not connect to database.");
ex.printStackTrace();
}
reservations = FXCollections.observableArrayList();
}
The DAO will make use of several PreparedStatement objects, so this constructor is also a natural place to initialize those.
The class also has a matching close() method to close the database connection. The code for the application's exit button will call this:
public void close() {
try {
connection.close();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
The GUI includes components that allow the user to specify the day and room they want to work with. The DAO class can store this information for later use. Whenever the day or the room change in the GUI, the controller for the main window will call the method below. This method remembers the day and room setting, and also refreshes the list of reservations for that combination.
public void setRoomAndDate(String room, LocalDate date) {
this.room = room;
this.date = date;
reservations.clear();
try {
// First look up the capacity of that room
ResultSet resultSet = statement.executeQuery("select capacity from rooms where name=\"" + room + "\"");
if (resultSet.next()) {
capacity = resultSet.getInt(1);
}
// Next search for all reservations for that room and that date.
reservationsStmt.setString(1, room);
reservationsStmt.setDate(2, java.sql.Date.valueOf(date));
resultSet = reservationsStmt.executeQuery();
while (resultSet.next()) {
Reservation r = new Reservation();
r.setIdNumber(resultSet.getInt("id"));
r.setCustomerNumber(resultSet.getInt("customer"));
r.setGroupSize(resultSet.getInt("size"));
int start = resultSet.getInt("start");
r.setStart(start);
int end = start + resultSet.getInt("duration");
r.setEnd(end);
reservations.add(r);
}
} catch (SQLException ex) {
ex.printStackTrace();
}
}
The code here makes use of a prepared statement to do the select that returns all of the reservations for a particular date and room. Here is the SQL code for that prepared statement.
private String reservationsSQL = "select * from reservations where room=? and day=?";
To extract the individual fields from the result set that comes back from this query we take advantage of the fact that Java ResultSet objects allow us to use field names to get fields from a given row.
The addReservation() method inserts a new reservation into the database.
public void addReservation(Reservation newReservation) {
try {
insertStmt.setInt(1, newReservation.getCustomerNumber());
insertStmt.setInt(2, newReservation.getGroupSize());
insertStmt.setDate(3, java.sql.Date.valueOf(date));
insertStmt.setInt(4, newReservation.getStart());
insertStmt.setInt(5, newReservation.getEnd() - newReservation.getStart());
insertStmt.setString(6, room);
insertStmt.execute();
String query = "select LAST_INSERT_ID()";
ResultSet rset = statement.executeQuery(query);
if (rset.next()) {
newReservation.setIdNumber(rset.getInt(1));
}
} catch (SQLException ex) {
ex.printStackTrace();
}
reservations.add(newReservation);
FXCollections.sort(reservations);
}
This code makes use of a prepared statement to handle the insertion. Here is the SQL code for that statement.
private String insertSQL = "insert into reservations(customer,size,day,start,duration,room) values(?,?,?,?,?,?)";
One complication here is that when we insert a new Reservation into the database we are counting on MySQL to assign it an id number automatically. To learn what that id number is we have to run a second query that calls a MySQL function to return the id number of the last database insertion.
The removeReservation() method removes a reservation from the database.
public void removeReservation(Reservation toRemove) {
try {
deleteStmt.setInt(1, toRemove.getIdNumber());
deleteStmt.execute();
} catch (SQLException ex) {
ex.printStackTrace();
}
reservations.remove(toRemove);
}
This code makes use of a prepared statement to do the deletion. Here is the SQL code for that statement.
private String deleteSQL = "delete from reservations where id=?";
Now that we have a DAO class set up to do all of the database interactions we simply need to modify the rest of the application to work with the DAO. The main change we have to make is in the controller class for the main window. We need to add a HotelDAO object as a member variable of this controller class and initialize it properly in the controller's initialize() method.
public void initialize(URL url, ResourceBundle rb) {
dao = new HotelDAO();
LocalDate selectedDate = LocalDate.now();
datePicker.setValue(selectedDate);
ArrayList<String> roomNames = dao.getRoomNames();
String room = roomNames.get(0);
roomChoice.getItems().addAll(roomNames);
roomChoice.setValue(room);
roomChoice.getSelectionModel().selectedItemProperty().addListener((observable, oldValue, newValue)->setRoom(newValue));
dao.setRoomAndDate(room, selectedDate);
reservationsList.setItems(dao.getReservations());
}
Whenever anything happens in the GUI to change either the date or the room we need to notify the DAO of this change and get an updated list of Reservation objects from it. Here is the code linked to the DatePicker: this code will get called when the user selects a new date.
@FXML
private void setDate(ActionEvent event) {
LocalDate selectedDate = datePicker.getValue();
String room = roomChoice.getSelectionModel().getSelectedItem().toString();
dao.setRoomAndDate(room,selectedDate);
reservationsList.setItems(dao.getReservations());
}
Likewise, the code for the dialog controller needs to be modified slightly to work with the new configuration. The controller class will store a reference to the HotelDAO and will talk to the DAO directly to create new Reservations. Here is one of the key methods in the dialog controller updated to use the DAO.
@FXML
private void acceptDialog(ActionEvent event) {
int custNumber = Integer.parseInt(customerNumber.getText());
int start = Integer.parseInt(startTime.getText());
int end = Integer.parseInt(endTime.getText());
int size = Integer.parseInt(groupSize.getText());
Reservation newReservation = new Reservation(custNumber,dao.getDate(),start,end,size,dao.getRoom());
if(dao.allowsReservation(newReservation)) {
dao.addReservation(newReservation);
customerNumber.getScene().getWindow().hide();
} else
errorText.setText("The reservation can not be scheduled.");
}