Kindly help me with hibernate template or detached criteria query, to fetch data from a table Clause. It has a set of Department table (Many to Many relationship). Now, i need list of only those clauses which are associated with any of the department list- List deptIdList (This is the input parameter). I could get the desired result using SQL native query but i want to achieve the result using hql or detached criteria query.
public class Clause {
@Id
@GeneratedValue
@Column(name = "CLAUSE_ID")
private Long clauseId;
@Column(name = "REG_REFERENCE")
private String regulatoryReference;
@Column(name = "REG_REQ")
private String regulatoryRequirement;
@Column(name = "REQ_IMP_RATING")
private String reqImpactRating;
@ManyToMany(fetch=FetchType.EAGER)
@JoinTable(name = "EWMS_CLAUSE_DEPT",
joinColumns = { @JoinColumn(name = "CLAUSE_ID", nullable = false, updatable = false) },
inverseJoinColumns = { @JoinColumn(name = "DEPT_ID", nullable = false, updatable = false) }
)
private Set<Department> departmentListForClause;
@Column(name = "CLAUSE_KEY_ACT")
private String keyActivity;
@Column(name = "CLAUSE_TOPIC")
private String clauseTopic;
@Column(name = "CLAUSE_KEY_WORDS")
private String clauseKeywords;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "REG_ID")
private RegulationDetails regulation;
//getter setter methods
}
public class Department {
@Id
@GeneratedValue
@Column(name = "DEPT_ID")
private Long departmentId;
@Column(name = "DEPT_NAME")
private String departmentName;
@Column(name = "TIER1_ID")
private String tierId;
@ManyToMany(/*fetch = FetchType.LAZY,*/ mappedBy = "regulatoryDepartments")
private Set<RegulationDetails> regulationListForDepartment;
@ManyToMany(/*fetch = FetchType.LAZY, */mappedBy = "departmentListForClause")
private Set<Clause> clauseListForDepartment;
//getter setter methods
}