Counting nonexisting records with foreign key

Question

I have two tables Room and RoomDate. Here's the structure for Room

Room
roomID
checkInDate
checkOutDate
roomType

Here's the structure for RoomDate. They have a one to many relationship.

RoomDateID
checkInDate
checkOutDate
RoomID // foreign key

Now I want to find how many rooms per roomType are available in a specific date range. That is, if a date range with a specified roomID does not yet exist in the RoomDate table, it can be added. This is how I do it.

• Count all records in the Room table per type. RoomTypes are single, double and triple.
• Count all records in the RoomDate that already have the inputed date range per type (E.G. if exist in single, cannot be added in single but can still be added in other room types).
• Subtract the RoomDate results from Room to get all rooms that are still available per type. So if there are no records in RoomDate, all rooms are available.

Sample input:

number of rooms in the database (RoomDate does not have records yet):
single rooms: 5
doubleRooms: 6
triple rooms: 7
User: 2015年11月11日 - 2015年11月21日 - 5 single rooms
Sample output:
five single rooms with the date range of 2015年11月11日 - 2015年11月21日 is added to the RoomDate table.
number of rooms in the database without the range 2015年11月11日 - 2015年11月21日:
single rooms: 0
doubleRooms: 6
triple rooms: 7

Next time the user enters a date in the range of 2015年11月11日 - 2015年11月21日 and again chose any number of single rooms, he cannot insert it in the database because there aren't any single rooms left. But he can still insert it with other room types double and triple as long as they are still available.

I used inner join before, but it does not count null records in RoomDate, so I mad individual queries instead.

Please review my code. I think this is too slow (6 HQL select statements), how can I improve this code and make it more efficient?

public long[] countRooms() {
 Session session = getSessionFactory().getCurrentSession();
 long[] count = new long[3];
 String query = "select count (r) from Room r where r.roomType=";
 count[0] = (long)session.createQuery(query+"0").uniqueResult();
 count[1] = (long)session.createQuery(query+"1").uniqueResult();
 count[2] = (long)session.createQuery(query+"2").uniqueResult();
 return count;
}
public long[] countAvailableRooms(LocalDate checkInDate, LocalDate checkOutDate) {
 Session session = getSessionFactory().getCurrentSession();
 String query = "select count(d) from Room r join r.roomDates d where d.checkInDate=:checkInDate and d.checkOutDate=:checkOutDate and r.roomType=";
 long[] count = countRooms();
 count[0] -= (long)session.createQuery(query+"0").setParameter("checkInDate", checkInDate).setParameter("checkOutDate", checkOutDate).uniqueResult();
 count[1] -= (long)session.createQuery(query+"1").setParameter("checkInDate", checkInDate).setParameter("checkOutDate", checkOutDate).uniqueResult();
 count[2] -= (long)session.createQuery(query+"2").setParameter("checkInDate", checkInDate).setParameter("checkOutDate", checkOutDate).uniqueResult();
 return count;
}

• Phrancis: The join in your second query string looks broken. I tried to replicate in SQL and got Incorrect syntax near the keyword 'where'. Have you actually tested this?
• lightning_missile: @Phrancis I did, and it worked fine. This is the sql query I got from hibernate when roomType=0: Hibernate: select count(roomdates1_.roomDateID) as col_0_0_ from tblroom room0_ inner join tblroomdate roomdates1_ on room0_.roomID=roomdates1_.roomID where roomdates1_.checkInDate=? and roomdates1_.checkOutDate=? and room0_.roomType=0
• Phrancis: Ah ok good, I didn't realize Hibernate transformed queries even if you hard-coded your SQL queries into strings.
• h.j.k.: Could you not use a GROUP BY SQL statement (or the HQL equivalent) so that you make one query instead of three?
• lightning_missile: @Phrancis those hardcoded queries are hql

Answer 1

Database modeling

Is the presence of check-in/check-out dates on both tables intentional?

If RoomDate is more like a reservations table, then having the check-in and check-out dates in it makes sense. How is the pair of date columns in the Room table used then?

Determining occupancy

Getting occupancy by using an = check is quite simplistic, as that does not take into account later check-ins, or earlier check-outs. If you want the check to be more robust, it can be something along the lines of:

-- please check if BETWEEN includes the values or not
d.checkInDate BETWEEN :checkInDate AND :checkOutDate OR
d.checkOutDate BETWEEN :checkInDate AND :checkOutDate OR
d.checkInDate <= :checkInDate AND d.checkOutDate >= :checkOutDate

This counts existing reservations with check-in or check-out dates that either fall within or span over the requested date range.

Efficient SELECT

Could you not use a GROUP BY SQL statement (or the HQL equivalent) so that you make one query instead of three? - myself

I'm not very familiar with Hibernate, (削除) and we don't even know your choice of DBMS, (削除ここまで) so the following is borderline pseudo-query:

SELECT r.roomType, r.total - b.booked AS remaining FROM
 ((SELECT roomType, COUNT(1) AS total FROM Room GROUP BY roomType) AS r JOIN
 (SELECT roomType, COUNT(1) AS booked FROM RoomDate d JOIN Room USING roomID 
 WHERE d.checkInDate BETWEEN :checkInDate AND :checkOutDate OR
 d.checkOutDate BETWEEN :checkInDate AND :checkOutDate OR
 d.checkInDate <= :checkInDate AND d.checkOutDate >= :checkOutDate
 GROUP BY roomType) AS b USING roomType
 )

The idea here is not to fire 6 separate SQL statements, as you have found to be 'unsatisfactory' and 'inefficient'. What you need is one representation from your Room table that shows the total rooms:

$$\begin{array} {|r|r|} \hline roomType & total \\ \hline 0 & 5 \\ \hline 1 & 6 \\ \hline 2 & 7 \\ \hline \end{array}$$

A representation from your RoomDate table that shows the booked, i.e. unavailable rooms (e.g. after the five-single-rooms booking is done):

$$\begin{array} {|r|r|} \hline roomType & booked \\ \hline 0 & 5 \\ \hline 1 & 0 \\ \hline 2 & 0 \\ \hline \end{array}$$

Joining both on the roomType column:

$$\begin{array} {|r|r|r|} \hline roomType & total & booked \\ \hline 0 &5 &5 \\ \hline 1 &6 &0 \\ \hline 2 &7 &0\\ \hline \end{array}$$

And finally subtract the two columns:

$$\begin{array} {|r|r|} \hline roomType & remaining \\ \hline 0 & 0 \\ \hline 1 & 6 \\ \hline 2 & 7 \\ \hline \end{array}$$

You may want to consider if the modeling can be further improved to remove the inner JOIN. This is just something to get you started with.

edit This might be how you can do it in Hibernate/HQL... Again, I'm not familiar with Hibernate, and the casting may be done incorrectly... please test and experiment carefully.

// Assuming you now have a RoomType `enum`
Map<RoomType, Integer> map = new EnumMap<>(RoomType.class);
for (Object[] row : session.createQuery(query).list()) {
 map.put(RoomType.values()[(int)row[0]], row[1]);
}
System.out.println("Room type and free rooms:\n" + map);

Java code review

As you can see, the main solution to your problem lies in doing a more efficient SELECT once. With that out of the way, you may want to consider using a model class to represent the results, instead of a simply long[] array. It is not evident from the array itself how the indices are being mapped... if you decide to use a non-contiguous values to represent new room types, you may encounter issues mapping those.

You can also consider modeling your room types as a Java enum type, for starters.

• lightning_missile: the pair of date columns in the Room table are never used. I thought I needed it if I can just count rows in the Room table, but it's not possible so I will be removing them.
• lightning_missile: I am not familiar with group by, I'm just starting to study it now along with your pseudo query. But how does this single query returns three results? The available rooms in single, double and triple?
• h.j.k.: @morbidCode yup, and instead of uniqueResult() you should be using an appropriate method from Hibernate's API that lets you map the resulting table into a suitable Java object... perhaps a Map? You'll have to read into that I'm afraid...
• h.j.k.: @morbidCode updated my answer... hopefully it gets you closer to the right Hibernate solution.
• lightning_missile: can you give me the pure sql form of your query? I got to understand what group by means, but I'm absolutely stuck in hql's group by right now. Thanks