I have a table called ad_session which logs user sessions. I am using Java to get a list of all successful sessions from that table. I then loop through that list to get the user for each session (which is a foreign key to the ad_user table). I then get the client that belongs to that user, and I add the client to a list. However, one of the users no longer exists, so my code stops running and it gives throws the following exception:

org.hibernate.ObjectNotFoundException: No row with the given identifier exists: [ADUser#76A5C22E6D2446A399AE9AD7C1DED0C7]

This is my original code:

List<Session> sessions = getAllSuccessfulSessionsInTable();
List<Client> clientsForThatDay = new ArrayList<>();

try {
   for (Session session : sessions) {
     //code fails when trying to get the non-existent user:
     User user = session.getCreatedBy();
     Client userClient = user.getClient();
     clientsForThatDay.add(userClient);
   }
} catch (Exception e) {     
     log.error("Error getting client from user: ", e);
}

I assumed that when getting a non-existent record, it would return null, so this is what I tried:

List<Session> sessions = getAllSuccessfulSessionsInTable();
List<Client> clientsForThatDay = new ArrayList<>();

//Create new user object to stand in place of the non-existent user
User deletedUser = new User();
deletedUser.setName("Deleted User");

//Create new client object to stand in place of the non-existent client
Client deletedUserClient = new Client();
deletedUserClient.setName("Unknown Client");

try {
  for (Session session : sessions) {
    //check is User is null, if it is, use the deletedUser object, otherwise, use the existing user
    User user = session.getCreatedBy() == null ? deletedUser : session.getCreatedBy();
    
    Client userClient = user.getName().equals("Deleted User") ? deletedUserClient : user.getClient();
    clientsForThatDay.add(userClient);
   }
} catch (Exception e) {
   log.error("Error getting client from user: ", e);
}

However, it is not returning null, it's just throwing the exception and then stopping. How can I get it to return null here so I can deal with the missing record without my code stopping? Thanks in advance for any suggestions.

CodePudding user response：

It seems that your database is missing a foreign key constraint. This means that the table mapping User has a reference to a row in the table for Client that no longer exist.

This can only happen if a client has been deleted without updating the user table. The solution would be to add a foreign key constraint between the tables.

Keep in mind that if the data in your tables are not correct, when Hibernate loads the entity User, it will also believe there's a client. This means that User#getClient won't be null, and every place in the code where you have a check like user.getClient() == null is going to fail. A try-catch approach won't help you with this (unless you set the association to null in case of error, I guess).

The solutions I can think of:

1. Add the foreign key constraint (imho, the best solution)
2. Don't map the association, map client_id as an attribute and load the client using a second query or find (I would only do this if you cannot update the database)

   class User {
       @Column(name = "client_id")
       Long clientId;
   }
   
   User user = ...
   Client client = session.find(Client.class, user.getClientId());
   

3. You can load the client via session.find(Client.class, user.getClient().getId()) and set the association with the result:

   User user = //...
   Client client = session.find(Client.class, user.getClient().getId());
   user.setClient(client);
   

4. Don't map the association at all in User, and run a native SQL query to load the client:

   User user = ...
   String sql = "select * from Client c join User u on c.id = u.client_id where u.id = :uid";
   Client client = session.createNativeQuery(sql, Client.class)
       .setParameter("uid", user.getId())
       .getSingleResultOrNull();
   

You can pick what works best for you, but keep in mind that mapping an association without the foreign key constraint, will cause all sort of consistency issues.

I've decided to put option 3 only because, sometimes, people have some impossible situations at work, but I wouldn't recommend it.