How to check if any object is related to a row in a table with constrain of foreign key

Question

I am using Hibernate and MySql.

I have a 2 tables:

User: id, name, type
City: id, name, type
type: id, name

Where user.type has foreign key to user_type.id. and as well city.

I would like before deleting a row in user_type table, to check if any row from any table is related to it.

my columns are mapped for example:

@ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "type_id")

How can I do it?

Answer 1

use native SQL with Hibernate together:

  boolean canDeleteType(ind type_id){
         Session s = HibernateUtil.getSessionFactory(); 
         s.beginTransaction();
         Query q = s.createQuery("SELECT User.type_id From User");
         List l = q.list();

    if(l.contains(type_id){
   return false;
}
return false;

}

and do the same for your City table too.

Answer 2

You said

I have around 100 tables like User and City mapped to this value

ok. Hibernate with JPA book says

You may have removed all other references manually

Which implies you should query manually any related Table. But it says if other entity references Type, database constraints prevent any inconsistency and you see a foreign key constraint exception. I Think it is the best way you can check out what you want. Otherwise, you should query manually for any related Table.

try {
    userType = (Type) session.load(Type.class, id);

    session.delete(userType);
/**
  * or JDBCException 
  * e.getCause()
  * e.getErrorCode() - vendor-specific
  */
} catch (HibernateException e) {
    // checkout Exception right here e.getCause();
} 

All exceptions thrown by Hibernate are fatal. This means you have to roll back the database transaction and close the current Session. So you may want To open a new session.