Basically, I've two entities.
- Reservation
- Room
The Reservation entity has a one-to-many relationship with the Room entity. Similarly, the Room entity has a many-to-one relationship with the Reservation entity. The relationship from Reservation to Room always exists. But the relation from Room to Reservation exists only when the reservation status is marked as "checked in" (I'm using enumeration ordinals for this. The ordinal of ReservationStatus.CHECKED_IN is 1.)
The @OneToMany annotation has been sufficient to show the one-to-many relationship. For the many-to-one relationship I'm using @ManyToOne and @Formula annotations. I'm fairly new to Hibernate. So I crafted the formula after testing it on MySQL console.
This is the MySQL query I ran for which I got the result. select * from reservation r left join reservation_rooms s on r.identifier = s.reservation_identifier left join room m on s.rooms_identifier = m.identifier where r.reservation_status = 1 and m.identifier = 4
The m.identifier = 4 is replaced with m.identifier = identifier in the Room class. The above query works fine in the MySQL console. But then it always returns null when I run it in the application.
This is the source for Room class.
@Entity
public class Room {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer identifier;
private Integer propertyIdentifier;
private String number;
@ManyToOne(cascade = CascadeType.ALL)
private RoomType roomType;
@ManyToOne(cascade = CascadeType.ALL)
private Floor floor;
@ManyToOne(cascade = CascadeType.ALL)
private Block block;
// select * from reservation r left join reservation_rooms s on r.identifier = s.reservation_identifier left join room m on s.rooms_identifier = m.identifier where r.reservation_status = 1 and m.identifier = 4
@Formula(value = "select r from reservation r left join reservation_rooms s on r.identifier = s.reservation_identifier left join room m on s.rooms_identifier = m.identifier where m.identifier = identifier and r.reservation_status = 1")
@ManyToOne(cascade = CascadeType.ALL)
private Reservation occupantReservation;
private Integer statusIdentifier;
public Integer getIdentifier() {
return identifier;
}
public void setIdentifier(Integer identifier) {
this.identifier = identifier;
}
public Integer getPropertyIdentifier() {
return propertyIdentifier;
}
public void setPropertyIdentifier(Integer propertyIdentifier) {
this.propertyIdentifier = propertyIdentifier;
}
public String getNumber() {
return number;
}
public void setNumber(String number) {
this.number = number;
}
public Integer getStatusIdentifier() {
return statusIdentifier;
}
public void setStatusIdentifier(Integer statusIdentifier) {
this.statusIdentifier = statusIdentifier;
}
public RoomType getRoomType() {
return roomType;
}
public void setRoomType(RoomType roomType) {
this.roomType = roomType;
}
public Floor getFloor() {
return floor;
}
public void setFloor(Floor floor) {
this.floor = floor;
}
public Block getBlock() {
return block;
}
public void setBlock(Block block) {
this.block = block;
}
@Override
public boolean equals(Object object) {
boolean result = false;
if (object == this) {
result = true;
}
else if ((object instanceof Room)) {
Room other = (Room) object;
if (other.identifier.equals(identifier)) {
result = true;
}
}
return result;
}
@Override
public int hashCode() {
return identifier;
}
public Reservation getOccupantReservation() {
return occupantReservation;
}
public void setOccupantReservation(Reservation occupantReservation) {
this.occupantReservation = occupantReservation;
}
}
This is the source for the Reservation class.
@Entity
public class Reservation {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer identifier;
@ManyToOne(cascade = CascadeType.ALL)
private Property property;
@ManyToOne(cascade = CascadeType.ALL)
private Guest guest;
@Temporal(TemporalType.DATE)
private Date arrival;
@Temporal(TemporalType.DATE)
private Date departure;
private Integer nights;
private Integer adults;
private Integer children;
@OneToMany(cascade = CascadeType.ALL)
private Set<Room> rooms;
@Enumerated(value = EnumType.ORDINAL)
private ReservationSource source;
private ReservationStatus reservationStatus;
public Integer getIdentifier() {
return identifier;
}
public void setIdentifier(Integer identifier) {
this.identifier = identifier;
}
public Guest getGuest() {
return guest;
}
public void setGuest(Guest guest) {
this.guest = guest;
}
public Date getArrival() {
return arrival;
}
public void setArrival(Date arrival) {
this.arrival = arrival;
}
public Date getDeparture() {
return departure;
}
public void setDeparture(Date departure) {
this.departure = departure;
}
public Integer getNights() {
return nights;
}
public void setNights(Integer nights) {
this.nights = nights;
}
public Integer getAdults() {
return adults;
}
public void setAdults(Integer adults) {
this.adults = adults;
}
public Integer getChildren() {
return children;
}
public void setChildren(Integer children) {
this.children = children;
}
public Property getProperty() {
return property;
}
public void setProperty(Property property) {
this.property = property;
}
public ReservationStatus getReservationStatus() {
return reservationStatus;
}
public void setReservationStatus(ReservationStatus reservationStatus) {
this.reservationStatus = reservationStatus;
}
public void setRooms(Set<Room> rooms) {
this.rooms = rooms;
}
public Set<Room> getRooms() {
return rooms;
}
public ReservationSource getSource() {
return source;
}
public void setSource(ReservationSource source) {
this.source = source;
}
}
This is the log from MySQL console.
mysql> describe room;
+---------------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------------+--------------+------+-----+---------+-------+
| identifier | int(11) | NO | PRI | NULL | |
| number | varchar(255) | YES | | NULL | |
| property_identifier | int(11) | YES | | NULL | |
| status_identifier | int(11) | YES | | NULL | |
| block_identifier | int(11) | YES | MUL | NULL | |
| floor_identifier | int(11) | YES | MUL | NULL | |
| occupant_reservation_identifier | int(11) | YES | MUL | NULL | |
| room_type_identifier | int(11) | YES | MUL | NULL | |
+---------------------------------+--------------+------+-----+---------+-------+
8 rows in set (0.03 sec)
mysql> describe reservation_rooms;
+------------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------+------+-----+---------+-------+
| reservation_identifier | int(11) | NO | PRI | NULL | |
| rooms_identifier | int(11) | NO | PRI | NULL | |
+------------------------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> describe reservation;
+---------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------+------+-----+---------+-------+
| identifier | int(11) | NO | PRI | NULL | |
| adults | int(11) | YES | | NULL | |
| arrival | date | YES | | NULL | |
| children | int(11) | YES | | NULL | |
| departure | date | YES | | NULL | |
| nights | int(11) | YES | | NULL | |
| reservation_status | int(11) | YES | | NULL | |
| source | int(11) | YES | | NULL | |
| guest_identifier | int(11) | YES | MUL | NULL | |
| property_identifier | int(11) | YES | MUL | NULL | |
+---------------------+---------+------+-----+---------+-------+
10 rows in set (0.02 sec)
mysql> select * from reservation_rooms;
+------------------------+------------------+
| reservation_identifier | rooms_identifier |
+------------------------+------------------+
| 6 | 4 |
+------------------------+------------------+
1 row in set (0.00 sec)
mysql> select * from reservation;
+------------+--------+------------+----------+------------+--------+--------------------+--------+------------------+---------------------+
| identifier | adults | arrival | children | departure | nights | reservation_status | source | guest_identifier | property_identifier |
+------------+--------+------------+----------+------------+--------+--------------------+--------+------------------+---------------------+
| 6 | 2 | 2018-12-27 | 2 | 2018-12-27 | 2 | 1 | NULL | 5 | NULL |
+------------+--------+------------+----------+------------+--------+--------------------+--------+------------------+---------------------+
1 row in set (0.02 sec)
mysql> select * from rooms;
ERROR 1146 (42S02): Table 'onecube_pms.rooms' doesn't exist
mysql> select * from room;
+------------+--------+---------------------+-------------------+------------------+------------------+---------------------------------+----------------------+
| identifier | number | property_identifier | status_identifier | block_identifier | floor_identifier | occupant_reservation_identifier | room_type_identifier |
+------------+--------+---------------------+-------------------+------------------+------------------+---------------------------------+----------------------+
| 4 | 190 | 1 | 2 | 1 | 2 | NULL | 3 |
+------------+--------+---------------------+-------------------+------------------+------------------+---------------------------------+----------------------+
1 row in set (0.00 sec)
Can you please direct me to what I'm doing wrong? Thank you for your time.
