Skip to main content
Code Review

Return to Question

added 4 characters in body
Source Link
lightning_missile
lightning_missile
  • 2.7k
  • 2
  • 23
  • 40

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?

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

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?

added 601 characters in body
Source Link
lightning_missile
lightning_missile
  • 2.7k
  • 2
  • 23
  • 40

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

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.

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

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.

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.

Source Link
lightning_missile
lightning_missile
  • 2.7k
  • 2
  • 23
  • 40

Counting nonexisting records with foreign key

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, a date range wiht a specified roomID does not yet exist in the RoomDate table, so in 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.

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 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;
}