[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-1743) Wrong order of binding parameters in Restrictions.in for composite primary keys

[Jay (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-1743?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_27811 ] 

Jay commented on HHH-1743:
--------------------------

Hi. I've noticed the same problem with inner components.

*** The HBM ***

<class name="EntreeJournal" table="journal_alimentation" ... >
...
    <component name="plageCollecte" class="fr.recouv.contact.core.bo.calendrier.PlageJournaliere" ... >
        <property name="date" column="date_collecte" type="date" />
        <component name="plageHoraire" class="fr.recouv.contact.core.bo.calendrier.PlageHoraire" ... >
            <property name="heureDebut" column="heure_debut" type="time" />
            <property name="heureFin" column="heure_fin" type="time" />
        </component>
    </component>

*** The BOs ***

The pojo PlageJournaliere has a date property and a plageHoraire property, that in turn has two date fields: heureDebut and heureFin.

public class PlageJournaliere {
    ...
    private Date date;
    private PlageHoraire plageHoraire;
    ...
}

public class PlageHoraire {
    ...
    private Date heureDebut;
    private Date heureFin;
    ...
}

*** The DAO ***

Collection<PlageJournaliere> plages = ...;

Criteria criteria = session.createCriteria(EntreeJournal.class).
    add( Restrictions.in("plageCollecte", plages) );

*** THE QUERY ***

    [...]
    where
        (
            this_.date_collecte, this_.heure_debut, this_.heure_fin
        ) in (
            (
                ?, ?, ?
            ), (
                ?, ?, ?
           ), (
                ?, ?, ?
            )
        ) 

Query is good but binding is done in he wrong order.

  $1 = date collecte 1
  $2 = date collecte 2
  $3 = date collecte 3
  $4 = heure debut 1
  $5 = heure fin 1
  $6 = heure debut 2
  $7 = heure fin 2
  $8 = heure debut 3
  $9 = heure fin 3

As a workaround, I build the where statement dynamicaly in doInHibernate() with Restrictions.and(), Restrictions.or() and others.
Best regards.


> Wrong order of binding parameters in Restrictions.in for composite primary keys
> -------------------------------------------------------------------------------
>
>                 Key: HHH-1743
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-1743
>             Project: Hibernate3
>          Issue Type: Bug
>          Components: query-criteria
>    Affects Versions: 3.2.0 cr1
>         Environment: Windows XP, Oracle 10g
>            Reporter: Viatcheslav Sysoltsev (Slavka)
>         Attachments: HibernateTest_restrictions_in_parameter_binding.zip
>
>   Original Estimate: 1 hour
>  Remaining Estimate: 1 hour
>
> There is a table Person with composite primary key:
> PERSON
> primary key fields:
>   FAMILY
>   NUMBER
> For the Criteria Query like
> 		List<PersonId> ids = new Vector<PersonId>();
> 		ids.add(new PersonId("SLAVA1", 10L));
> 		ids.add(new PersonId("SLAVA2", 20L));
> 		ids.add(new PersonId("SLAVA3", 30L));
> 		List<Person> persons = session.createCriteria(Person.class)
> 			.add(Restrictions.in("id", ids))
> 			.list();
>  the query  is generated
> select this_.FAMILY as FAMILY0_0_, this_.NUMBER as NUMBER0_0_ from PERSON this_ where (this_.FAMILY, this_.NUMBER) in ((?, ?), (?, ?), (?, ?))
> The query is right, but the parameters are bound in wrong order, here is the excerpt from the log:
> 16:21:33,221 DEBUG [SQL] select this_.FAMILY as FAMILY0_0_, this_.NUMBER as NUMBER0_0_ from PERSON this_ where (this_.FAMILY, this_.NUMBER) in ((?, ?), (?, ?), (?, ?))
> Hibernate: select this_.FAMILY as FAMILY0_0_, this_.NUMBER as NUMBER0_0_ from PERSON this_ where (this_.FAMILY, this_.NUMBER) in ((?, ?), (?, ?), (?, ?))
> 16:21:33,221 DEBUG [AbstractBatcher] preparing statement
> 16:21:33,330 DEBUG [StringType] binding 'SLAVA1' to parameter: 1
> 16:21:33,330 DEBUG [StringType] binding 'SLAVA2' to parameter: 2
> 16:21:33,330 DEBUG [StringType] binding 'SLAVA3' to parameter: 3
> 16:21:33,330 DEBUG [LongType] binding '10' to parameter: 4
> 16:21:33,330 DEBUG [LongType] binding '20' to parameter: 5
> 16:21:33,330 DEBUG [LongType] binding '30' to parameter: 6
> I've made an example (attached, standalone eclipse project with all libs) which demonstrates the problem, but you need running oracle somewhere to run it. See also bug report HH-708 which is predecessor of this.
> The problem may lay in org.hibernate.criterion.InExpression::getTypedValues.
> Instead of 
> for ( int i=0; i<types.length; i++ ) { 
> for ( int j=0; j<values.length; j++ ) { 
> Object subval = values[j]==null ? 
> null : 
> actype.getPropertyValues( values[j], EntityMode.POJO )[i]; 
> list.add( new TypedValue( types[i], subval, EntityMode.POJO ) ); 
> } 
> } 
> should be 
> for ( int j=0; j<values.length; j++ ) { 
> for ( int i=0; i<types.length; i++ ) { 
> Object subval = values[j]==null ? 
> null : 
> actype.getPropertyValues( values[j], EntityMode.POJO )[i]; 
> list.add( new TypedValue( types[i], subval, EntityMode.POJO ) ); 
> } 
> } 
> After change have worked for me. But I am not sure whether the syntax of what's going from Restriction.in is database-specific.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira