Hibernate Community • View topic - Setting values for tuples in an IN operator

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Setting values for tuples in an IN operator

Page 1 of 1

[ 2 posts ]

Previous topic | Next topic

Author

Message

ohad.raz@orbitz.com

Post subject: Setting values for tuples in an IN operator

Posted: Thu Apr 14, 2011 10:53 am

Newbie

Joined: Thu Apr 14, 2011 9:35 am
Posts: 2

Greetings,

I have this question (problem, if you wish) for which I failed to get an answer (OK, solution...).
The thing is that my goal is, bottom line, to get a SQL that has is WHERE clause look something like this:

Code:

WHERE (col1, col2) IN ( (1, 10), (2, 20) )

But how do I write such an HQL (giving that the number of tuples is dynamic)?...

I have consulted about this question with some of my colleagues, and they offered me to write an HQL that looks something like:

Code:

<query name="myQuery">
   from  myObj as mo
   where mo.prop1 in (:values1)
     and mo.prop2 in (:values2)
</query>

But this wouldn't do.
Let's say that in my example above I have 1 and 2 as the values of prop1, and 10 and 20 as the values of prop2.
So using the suggested query above would generate a SQL that includes all the permutations, something which is equivalent to the following:

Code:

WHERE (col1, col2) IN ( (1, 10), (1, 20), (2, 10), (2, 20) )

And that not what I am looking for, as (1, 20) and (2, 10) are invalid combination for my needs...

I have thought of several alternatives, but I am not sure which is valid, if any...

Idea 1
The query in the hbm.xml will look like the following:

Code:

<query name="myQuery">
   from  myObj as mo
   where (mo.prop1, mo.prop2) in ((:val1, :val2))
</query>

But I am not sure how Hibernate treats this (:val1, :val2). Would it treat it as properties of the tuple, or would it treat it as items of the IN operator?
In other words, would it expect :val1 to be, in my example, (1, 10) and :val2 to be (2, 20), or :val1 to be 1 and :val2 to be 10?

If it is the first (:val1 as (1, 10) and :val2 as (2, 20)), then how can I write such an HQL where the number of tuples is dynamic (i.e., having the tuples (1, 10), (2, 20), (3, 30), (4, 40), ...)?

Or, if it is the later (:val1 as 1 and :val2 as 10), then how do I set the values, say, using the HibernateTemplate? The findByNamedQueryAndNamedParam API accepts an array of Object-s as the values. This may allow me to pass it an Object array as its items, e.g.,

Code:

List executeMyQuery(HibernateTemplate tmplate) {
   Object[] val1=       new Integer[] {1, 2};
   Object[] val2=       new Integer[] {10, 20};
   Object[] values=     new Object[] {val1, val2};
   String[] paramNames= {"val1", "val2"};

   return template.findByNamedQueryAndNamedParam("myQuery", paramNames, values);
}

But would it do? Would Hibernate know to bind the values by their index, or would it generate all the permutations? Or would it throw some nasty exception?...

Idea 2
The query would look like the following:

Code:

<query name="myQuery">
   from  myObj as mo
   where (mo.prop1, mo.prop2) in (:val1)
</query>

Would it pass validation?
If so, then the following code would work?

Code:

List executeMyQuery(HibernateTemplate tmplate) {
   Object[] val1=       new Integer[] {1, 10};
   Object[] val2=       new Integer[] {2, 20};
   Object[] values=     new Object[] {val1, val2};
   String[] paramNames= {"val1"};

   return template.findByNamedQueryAndNamedParam("myQuery", paramNames, values);
}

Idea 3
Same HQL as in idea 2:

Code:

<query name="myQuery">
   from  myObj as mo
   where (mo.prop1, mo.prop2) in (:val1)
</query>

But a different approach to set the values by the application:

Code:

List executeMyQuery(HibernateTemplate tmplate) {
   Object[] values=     new Object[] {"(1, 10)", "(2, 20)"};
   String[] paramNames= {"val1"};

   return template.findByNamedQueryAndNamedParam("myQuery", paramNames, values);
}

I do not know which approach will work, if any...
If none, then how should I do it?
And in case there are several approaches that will work, what would be the best?

Thanks in advance (and sorry for the long text...),
Ohad

Top

ohad.raz@orbitz.com

Post subject: Re: Setting values for tuples in an IN operator

Posted: Wed May 04, 2011 11:50 am

Newbie

Joined: Thu Apr 14, 2011 9:35 am
Posts: 2

Alright, found a solution:
The trick would be to couple the two element I need in the IN expression in a component element with a corresponding POJO, i.e., something like the following:

Code:

<component name="myComponentType" class="com.company.whatever.MyComponentType">
   <property name="field1" column="COL_1" type="java.lang.Long" unique-key="myComponentType" not-null="true" />
   <property name="field2" column="COL_2" type="java.lang.Long" unique-key="myComponentType" not-null="true" />
</component>

The POJO is rather simple:

Code:

public class MyComponentType {
   private Long field1;
   private Long field2;

   /**
    * Default constructor (used by Hibernate)
    */
   public MyComponentType() {
      super();
   }

   /**
    * Constructor
    * 
    * @param field1 the first field
    * @param field2 the second field
    */
   public MyComponentType(final Long field1, final Long field2) {
      this.field1 = field1;
      this.field2 = field1;
   }

   /**
    * @return field1
    */
   public Long getField1() {
      return this.field1;
   }

   /**
    * @return field2
    */
   public Long getField2() {
      return this.field2;
   }

   /**
    * Setter for the field1
    * 
    * @param field1 the new field1
    */
   void setField1(final Long field1) {
      this.field1 = field1;
   }

   /**
    * Setter for the field2
    * 
    * @param field2 the new field1
    */
   void setField2(final Long field2) {
      this.field2 = field2;
   }

   @Override
   public boolean equals(final Object obj) {
      ...
   }

   @Override
   public int hashCode() {
      ...
   }
}

And now I can write the query as follows:

Code:

<query name="myQuery">
   from   myEntity as e
   where ...
      and e.myComponentType in (:types)
</query>

The code would therefore look something like this:

Code:

List executeMyQuery(HibernateTemplate tmplate) {
   final String[] paramNames= {"types"};

   MyComponentType val1=   new MyComponentType(1, 10);
   MyComponentType val2=   new MyComponentType(2, 20);
   Object[]        values= new Object[] {val1, val2};

   return template.findByNamedQueryAndNamedParam("myQuery", paramNames, values);
}

And Hibernate would bravely cope with it.

Top

Page 1 of 1

[ 2 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum