I am having difficulty writing a HQL query to select ONLY the caseid, title, and caseStatus fields from my Cases entity. The cases returned have to be distinct based on caseid. I do not want the name and userid fields to be included. I also do not want to use Lazy fetching for caseid, title, and caseStatus fields. Note that the caseStatus field is a one-to-many List. Below are the entities. The getters/setters are omitted to save space.
@Entity
@Table(name = "Cases")
public class Cases {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "caseid", nullable = false)
private Integer caseid;
private Integer userid;
private String name;
private String title;
@OrderBy("caseStatusId DESC")
@OneToMany(mappedBy = "cases", fetch = FetchType.EAGER)
private List<CaseStatus> caseStatus;
}
@Entity
@Table(name = "CaseStatus")
public class CaseStatus {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "caseStatusId", nullable = false)
private Integer caseStatusId;
private String info;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "caseid")
private Cases cases;
}
My goal is to retrieve a distinct List<Cases> or List<Object[]> of the Cases entity containing only caseid, title, and a List<CaseStatus>. The List<CaseStatus> will contain CaseStatus objects with all of its fields populated.
public List<Object[]> getCases(String title) {
TypedQuery<Object[]> q = em.createQuery("select distinct c.caseid, c.title, cs "
+ "FROM Cases c join c.caseStatus cs "
+ "where c.title like :title", Object[].class);
q.setParameter("title", "%" + title + "%");
List<Object[]> results = q.getResultList();
return results;
}
The above method is close, but not correct because rather than returning a List<CaseStatus> in one of the indexes, it is only returning a single CaseStatus entity.
For example, if my DB contains a single Case with a List<CaseStatus> having a size of n for example, the results will be similar to the example below:
Example of results I'm getting now. Not correct:
List<Object[]> index 0:
Contains an Object[] where:
Object[0] = {some caseid}
Object[1] = {some title}
Object[2] = {1st CaseStatus}
List<Object[]> index 1:
Contains an Object[] where:
Object[0] = {same caseid as the one found in index 0 above}
Object[1] = {same title as the one found in index 0 above}
Object[2] = {2nd CaseStatus}
...
List<Object[]> index n-1:
Contains an Object[] where:
Object[0] = {same caseid as all the previous}
Object[1] = {same title as all the previous}
Object[2] = {nth CaseStatus}
Example of results I hope to achieve:
List<Object[]> index 0:
Contains an Object[] where:
Object[0] = {unique caseid}
Object[1] = {some title}
Object[2] = List<CaseStatus> with size of n
Updated the question. Instead of name, title, and List<CaseStatus>, the fields I want to retrieve are caseid, title, and List<CaseStatus>. caseid is the primary key of Cases.
I found various threads Select Collections with HQL - hibernate forum and Select collections with HQL - stackoverflow. It's pretty much the problem I ran into. Looks like no one found a solution in these threads.