implssv implssv - 1 month ago 7
SQL Question

Spring Hibernate find all records that have value in set

I have entity Doctor

@Entity
public class Doctor extends Account {
//other attributes
@Column
@ElementCollection(targetClass = Speciality.class, fetch = FetchType.EAGER)
private Set<Speciality> speciality = new HashSet<Speciality>(0);
//getters and setters
}


and entity Speciality

@Entity
public class Speciality {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private int id;
String name;
//getters and setters here
}


Now in my application user can choose speciality of doctor that want to visit. For example user choose
endocrinologist and now my application needs to find all doctors that have speciality endocrinologist in Set speciality. How to do this?

Answer

You can easily do it using Hibernate Criteria Queries Builder API:

final List<Doctor> doctors = getSession()
       .createCriteria(Doctor.class, "doc")
       .add(Restrictions.eq("doc.speciality.id", specialityId))
       .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
       .list();