CODEWITHSUNDEEP
javahibernatejoinannotationshql
DFL
DFL

Reputation: 173

Annotated Hibernate- how to select from a joined list?

So I have 2 tables, a person table with their name and a nick_name table with nicknames that refer to the person. Each person may have 0, 1, or more nick names. 

 PERSON                    NICK_NAME
 ---------                 ---------
 ID                        ID_FK
 NAME                      NICK_NAME
 //other stuff

My Hibernate object is like this: 

 @Entity
 @Table(name="PERSON")
 public class Person{
      @Id
      @Column(name="ID")
      private String id;

      @Column(name="NAME")
      private String name; 

      @ElementCollection
      @CollectionTable(name="NICK_NAME", joinColumns=@JoinColumn(name="ID_FK"))
      @Column(name="NICK_NAME")
      private List<String> nickNames; 

      //other stuff

 }

I want to, given a name, retrieve all people who have that name or nick name. That is, if a person with the name "Robert" has the nick names "Bobby" and "Bob", I want all 3 to be able to retrieve him. 

 public List<Person> retrieveByName(String name){
      Session session = this.sessionFactory.getCurrentSession();
      Criteria criteria = session.createCriteria(Person.class, "person");
      criteria.setResultTransformer(Person.DISTINCT_ROOT_ENTITY);

      Criterion mainName = Restrictions.eq("name", name); 
      Criterion nickName = Restrictions.eq("person.nickNames", name);
      criteria.add(Restrictions.or(mainName, nickName));

      return criteria.list(); 
 }

But I get an SQL error here

 java.sql.SQLException: Missing IN or OUT parameter

What is the right way to do this?

Upvotes: 0

Views: 73

Answers (1)

v.ladynev
v.ladynev

Reputation: 19956

You can't do this for collections

Criterion nickName = Restrictions.eq("person.nickNames", name);

Before use a restriction for a collection you need to join two tables (PERSON and NICK_NAME) by adding an alias to the criteria

Criterion mainName = Restrictions.eq("person.name", name); 
criteria.createAlias("person.nickNames", "personNickName", JoinType.LEFT.ordinal());
Criterion nickName = Restrictions.eq("personNickName.nickName", name);

I am not sure with the last line because of you don't have NickName persistent, may be

Criterion nickName = Restrictions.eq("personNickName", name);

Upvotes: 1

Related Questions