HQL where clause with String collection type

Question

I want to do a search ob the basis of some variables provided by user.

What I am doing to achieve data from tables collection:

Here is my entity:

@Entity
@Table
public class FrontRequest implements Serializable { 
    @Id
    @GeneratedValue(strategy= GenerationType.AUTO)
    private Long tutorfront_id;

    @Column
    private String tutorialType;

    @Column
    private String tutorialLevel;

    @ElementCollection(fetch=FetchType.EAGER)
    @Fetch(value = FetchMode.SELECT)
    private Collection<String> tutorialSubjects= new HashSet<String>();

    @Column
    private String tutorialCity;

    @Column
    private String noOfStudent;

    @Column
    private String classWeek;

    @Column
    private String tutionFee;

    @Column
    private String tutionFeerate;

    @Column
    private String tutorSex;

    @Column
    private String tutorEducation;


    @Column
    private Date postingDate;

    @Column
    private String studentSchool;
}

Now I want to fetch FrontRequest objects as list, here I have a subject as input.

Now I want to get FrontRequest here subject in FrontRequest.TutorialSubjects.

For this I was trying to do like below:

Query query=session.createQuery("from FrontRequest frontreq where :tutorialsubject IN frontreq.tutorialSubjects");

query.setParameter("tutorialsubject", tutorialSubject);

 List<FrontRequest> frontRequest=query.list();

But its giving exception with invalid syntax.

I don't know where I am wrong. May be I am doing it wrong way.

Yes the issue is same like

Issue when trying to use the IN operator in a JPQL query

but, its slightly different from that, i.e. I am using string type collection here in my entity. And when I am trying to use member of here, its not working with that and throwing an exception

org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected end of subtree [from com.tutor.entity.FrontRequest frontreq where frontreq.tutorialCity like '%' and :tutorialsubject MEMBER OF frontreq.tutorialSubjects]

Please help.

Thanks in advance.

Possible duplicate of [Issue when trying to use the IN operator in a JPQL query](http://stackoverflow.com/questions/32631930/issue-when-trying-to-use-the-in-operator-in-a-jpql-query) — Dragan Bozanovic, Mar 15 '16 at 18:14
Here I am using String type Collection instead of user defined type. — Namit Khandelwal, Mar 16 '16 at 01:18

score 0 · Answer 1 · answered Mar 16 '16 at 01:27

Finally I have resolved the problem with "In elements" in where clause.

Query is :

 from FrontRequest frontreq where :tutorialsubject IN elements (frontreq.tutorialSubjects)

query.setParameter("tutorialsubject", tutorialSubject);

 List<FrontRequest> frontRequest=query.list();

Thanks to All, should give +1 to it.

HQL where clause with String collection type

1 Answers1