Hibernate IN Clause with multiple columns

Question

Need to know how to construct a Hibernate Query which fetches results matching an IN clause containing multiple column values.

e.g.,

Query query=session.createQuery( "from entity e where (e.abc, e.xyz) in (:list)" );
query.setParameterList( "list", list );

Here list would be a 2D array that could contain basic wrapper objects for primitive types e.g., Integer, String etc.

Is this possible?

Answer 1

Putting down here how I implemented this. Basically we need to make a Hibernate Component (read @Embeddable object) out of the set of columns we need to query on and embed it in the main Entity.

The group of columns can be combined as below:

@Embeddable
public class CompositeColumns{
  private String col1;
  private String col2;

  //Empty constructor is required by Hibernate for instantiation
  public CompositeColumns(){
  }

  public CompositeColumns(String col1, String col2){
            this.col1 = col1;
            this.col2 = col2;
      }

  @Column(name="COL1")
  public String getCol1(){
  }
  ...
  ... 
  //Rest of getters and setters
}

---

Embed the above in your main entity class as below:

@Entity
public class MyEntity{
 @Id
 private Integer id;
 private String col3;
 private String col4
 @Embedded
 private CompositeColumns pairedCol1Col2;
 ...
 ...
 //Getters Setters

}

---

The query would then look as below:

List<CompositeColumns> cols = //get a list of CompositeColumns type

Query query=session.createQuery( "from MyEntity where pairedCol1Col2 in (:list)" );
query.setParameterList( "list", list );

This does the job.

Note: I ran this on an Oracle database

Answer 2

What you are asking here is known as the tuple syntax. It is supported by Hibernate but unfortunately many databases do no support it ...

http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-tuple