How to write Hibernate query for collections Set

Question

I have following Entity

@Entity
public class Employee{

    private Integer id;
    private String name;
    private String address;
    private Date created;
    @OneToMany(mappedBy = "employee")
    private Set<Language> languages = new HashSet<AlbumUser>()
}

and Language entity is

  @Entity
    public class Language{

    private String name;
    @ManyToOne
    @JoinColumn(name = "employee_id") 
    private Employee employee;
}

my language table looks like following

enter image description here

I want to select all employee whose name starts with A and who knows java and C and to do so I am trying following

DetachedCriteria criteria = DetachedCriteria.forClass(Employee.class,"employee");
 criteria.add( Restrictions.ilike("name", "A%") );
 criteria.add(Restrictions.in("languages",languageSet));
 return getHibernateTemplate().findByCriteria(criteria)

where languageSet is

Set<Language> languageSet = new HashSet();
languageSet.add(new Language("Java"));
languageSet.add(new Language("C"));

I can see my attempt is completely wrong...I am new to hibernate can some one please help me with it..

Caused by: java.sql.SQLException: No value specified for parameter 2
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
    at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2176)
    at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2100

score 0 · Answer 1 · answered Sep 27 '12 at 01:23

0

Try this:

criteria.add(Restrictions.in("languages.name",languageNames));

where languageNames is collection of language names. If you have an error that "languages" cannot be solved, add new alias.

answered Sep 27 '12 at 01:23

Alex

11,451
6
37
52

score 0 · Answer 2 · answered Oct 09 '11 at 12:48

0

This should be achievable using Hibernate ASSOCIATIONs with CRITERIA. While you look close to correct (while am not sure why you are using a DetachedCriteria, rather than a simple Criteria), you should refer this - http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querycriteria.html#querycriteria-associations - to figure out more on how should build such queries.

answered Oct 09 '11 at 12:48

Saket

45,521
12
59
79

I already went through the link provided ... but since I am new to hibernate I am not able to follow the documentation ..so I am trying to learn by doing... Can you please let me know the query which i can use here – Anupam Gupta Oct 09 '11 at 12:51
can you plz add the trace to the question? – Saket Oct 09 '11 at 13:01

How to write Hibernate query for collections Set

2 Answers2