I have the following model stored in a data base. Upon deletion of a room i receive a Referencial Integrity Constraint Violation, which I can't figure out.
An important rule for the model is: a person MUST have access to a room. If they lose all access (e.g. all rooms they have access to are deleted), they are removed from the database.
The ManyToMany Mapping in Question exists between Person and Room and exists in the form of the table "access". Ill be sharing some code for:
Person.java
Room.java
an excerpt from DAO.java (data access object)
DeleteRoom.java (main)
In order to uphold consistency in my data base i have come up with the following logic which i have tried to implement in DAO.deleteRoomWithConsistency(RoomId roomId), which I shared below:
When a room is deleted i run a loop over all persons who have access to that room:
Now the code:
Person.java:
@Entity(name = "Person")
@Table(name = "Person")
public class Person {
public Person(int persNumber, String name, LocalDate dob) {
super();
this.persNumber = persNumber;
this.name = name;
this.dob = dob;
}
@Id
@Column(name = "persNumber")
private int persNumber;
@Column(name = "name")
private String name;
@Column(name = "dob")
private LocalDate dob;
@ManyToOne(cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST,CascadeType.REFRESH}, fetch=FetchType.LAZY)
@JoinColumn(name="orgUnit")
private OrgUnit orgUnit;
public void setOrgUnit(OrgUnit orgUnit) {
this.orgUnit = orgUnit;
}
// List of Rooms a Person has Access to
// Owning Side of Bidirectional Relationship with Room
@ManyToMany(fetch = FetchType.LAZY,
cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST,CascadeType.REFRESH}
)
@JoinTable(name = "access", joinColumns = @JoinColumn(name = "person_id"),
inverseJoinColumns = {@JoinColumn(name = "building", referencedColumnName = "building"),
@JoinColumn(name = "number", referencedColumnName = "number")})
private Set<Room> roomAccess = new HashSet<>();
Room.java:
@Entity(name = "Room")
@Table(name = "Room")
public class Room {
@EmbeddedId
private RoomId roomId;
@Column(name = "area")
private int area;
@Column(name = "usetype")
@Enumerated(EnumType.STRING) //Adds the String Value of the Enum, instead of the ordinal value
private UseType usetype;
//List of Persons with Access to a Room
//Inverse Side of Bidirectional Relationship with Person
@ManyToMany(mappedBy = "roomAccess", cascade = {CascadeType.DETACH, CascadeType.MERGE, CascadeType.PERSIST,CascadeType.REFRESH})
private Set<Person> personsWithAccess = new HashSet<>();
DeleteRoom.java
public class DeleteRoom {
public static void main(String[] args) throws SQLException {
//Connection to the database
DAO dao = new DAO();
dao.connect("./db/hw01", "bitub", "");
//Inputs
Building b1 = DAO.getInstance().readBuildingByName(
"Beispielhaus" //Enter the Building of the Room
);
Integer roomNumber = 102; //Enter Room Number
RoomId toBeDeletedRoomId = new RoomId(b1, roomNumber);
//Room r = DAO.getInstance().readRoom(toBeDeletedRoomId);
//System.out.println(r);
DAO.getInstance().deleteRoomWithConsistency(toBeDeletedRoomId);
dao.closeConnection();
}
}
excerpt from DAO.java
public class DAO {
private static Connection conn;
public void connect(String url, String username, String password) throws SQLException {
String connectionURL = "jdbc:h2:" + url;
try {
conn = DriverManager.getConnection(connectionURL, username, password);
} catch (SQLException e) {
e.printStackTrace();
}
System.out.println("connecting to " +connectionURL);
}
/**
* Close
*
*/
public void closeConnection() throws SQLException {
conn.close();
}
/**
* deletes a room by their primary key with consistency
*/
public void deleteRoomWithConsistency(RoomId roomId) throws SQLException {
Session session = null;
Transaction transaction = null;
try {
// open the session
session = HibernateUtil.getSessionFactory().openSession();
// begin the transaction
transaction = session.beginTransaction();
//retrieving room from the database
Room deletedRoom = (Room) session.find(Room.class, roomId);
//Set of people who have access to the room with roomId
Set<Person> peopleWithAccess = this.readPersonsWithAccessForRoom(roomId);
//keeping consistency
//for all persons with access to the deleted room
for (Person pWithAccess : peopleWithAccess) {
//Set of rooms a pWithAccess has access to
Set<Room> roomsWithAccess = this.readRoomsWithAccessForPerson(pWithAccess);
//if
//this person only has access to one room (the deleted room), the person is deleted
if (roomsWithAccess.size()==1) {
//Set of People who are in the same org unit as pWithAccess
Set<Person> orgUnitMembers = this.readOrgUnitMembers(pWithAccess.getOrgUnit().getName());
//if
//this person is the only person in the org unit, the org unit is deleted, by orphan removal the person is also deleted
if(orgUnitMembers.size()==1) {
//remove org unit -> remove lonely member (has access to only one room and is the only member of his org unit)
session.delete(pWithAccess.getOrgUnit().getName());
//there are other people in the orgunit, he/she might be the head of the unit
}else{
//Boolean if pWithAccess is the head of the unit
Boolean isHeadOfUnit = this.checkIfHeadOfUnit(pWithAccess);
//if
//he/she is head of the unit, a new head of the unit must be appointed
if(isHeadOfUnit==true) {
Iterator<Person> it = orgUnitMembers.iterator();
//appoint the first person, who isn't the old head of unit, the new head of unit
while (it.hasNext()) {
if (it.next() != pWithAccess) {
pWithAccess.getOrgUnit().setHeadOfUnit(it.next());
break;
}
}
}
//remove person (has access to only one room, but is not the only one in his org unit)
session.delete(pWithAccess);
}
//this person has access to multiple rooms, the room is deleted from the rooms this person has access to
} else {
session.createSQLQuery(""
+ "DELETE FROM access "
+ "WHERE person_id = '" + pWithAccess.getPersNumber() +"'"
+ "AND building='" + roomId.getBuildingName()+ "' "
+ "AND number ='" + roomId.getNumber()+ "' ");
//roomsWithAccess.remove(deletedRoom);
}
}
//removing the room
session.delete(deletedRoom);
// commit the transaction
transaction.commit();
} catch (final HibernateException e) {
rollbackTransactionIfPossible(transaction);
e.printStackTrace();
} catch (final Exception e) {
rollbackTransactionIfPossible(transaction);
e.printStackTrace();
} finally {
// close the session
if ((session != null) && session.isOpen()) {
session.close();
}
}
}
}
And finally, when I run my main method to delete a room I get the following output:
Hibernate: select room0_.building as building4_5_0_, room0_.number as number1_5_0_, room0_.area as area2_5_0_, room0_.usetype as usetype3_5_0_ from Room room0_ where room0_.building=? and room0_.number=?
Hibernate: delete from Room where building=? and number=?
...
Referential integrity constraint violation: "FK3SHH9TEIQLTR6V1EAPFWO13WM: PUBLIC.ACCESS FOREIGN KEY(BUILDING, NUMBER) REFERENCES PUBLIC.ROOM(BUILDING, NUMBER) ('Beispielhaus', 102)"; SQL statement:
delete from Room where building=? and number=? [23503-214]
Dez. 29, 2022 10:04:38 NACHM. org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl release
INFO: HHH000010: On release of batch it still contained JDBC statements
...
I hope I could provide enough information for someone to help me out. Any insight would be greatly appreciated.
I have tried tinkering with the mapping, adding or removing the CascadeType.REMOVE. But in my research I learned that CascadeType.REMOVE or ALL shouldn't be used for "...ToMany" associations.
I have also tried debugging, but I didn't learn anything from that. The transaction builds up and when it is commited, the error happens.
Any help with this error is greatly appreciated.
