Counting nonexisting records with foreign key
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 &lt;= :checkInDate AND d.checkOutDate &gt;= :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 &lt;= :checkInDate AND d.checkOutDate &gt;= :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 &amp; total \\
\hline
0 &amp; 5 \\
\hline
1 &amp; 6 \\
\hline
2 &amp; 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 &amp; booked \\
\hline
0 &amp; 5 \\
\hline
1 &amp; 0 \\
\hline
2 &amp; 0 \\
\hline
\end{array}$$

Joining both on the roomType column:

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

And finally subtract the two columns:

$$\begin{array} {|r|r|}
\hline
roomType &amp; remaining \\
\hline
0 &amp; 0 \\
\hline
1 &amp; 6 \\
\hline
2 &amp; 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&lt;RoomType, Integer&gt; map = new EnumMap&lt;&gt;(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.