Criteria inner join

mamntc02 · **Joined:** Mon Dec 10, 2007 12:56 pm **Posts:** 7

Hi all,

I'm quite desperate using criteria queries. I just want to do something like this:

Code:

select a.name, a.description
from t_elem a, t_val b
where 
   a.elem_id = b.elem_id and
   b.date >= TO_DATE('20071109','YYYYMMDD')

It's a simple join, that I cannot make with criteria. I've tried two possibilites, but doesn't work:
First

Code:

      List listaElements = HibernateUtil.getCurrentSession().createCriteria(ElementoValorVO.class).setProjection(
                  Projections.distinct(Projections.projectionList().add(Projections.property(ConstantesHibernate.ELEMENTO_ID))))
                     .add(Restrictions.ge(ConstantesHibernate.FECHA_ALTA, criteriosTipologia.getFechaActualizacion()))
                     .list();

   Criteria criteria = HibernateUtil.getCurrentSession().createCriteria(PersonaResumenVO.class)
            .setFetchMode("listaElements",FetchMode.JOIN);

This seems to work like a left join.
The second try was like the first, but the join was:

Code:

      Criteria criteria = HibernateUtil.getCurrentSession().createCriteria(ElementoVO.class)
                  .createCriteria("listaElementos", CriteriaSpecification.INNER_JOIN);

Of course, this one crashes and throws an Exception telling me 'could not resolver property: listaElementos', which is just a List and not an element of the class to join.

How can I make that query using criteria, thanks!!

Ups... I forgot to tell that I'm using hibernate 3, and sorry for my english ;)

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

You will get more precise answers if you can post your mapping files.

mamntc02 · **Joined:** Mon Dec 10, 2007 12:56 pm **Posts:** 7

Thanks for answer!!

The related map files are:
- Elemento.hbm.xml:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.seat.cdm.elemento">
   <class name="ElementoVO" schema="CUSTOMERDATAMASTER" table="T_ELEMENTO">
      <id name="elementoId" column="ELEMENTO_ID_PK">
            <generator class="assigned"/>
        </id>
        <property name="descripcion" column="DESCRIPCION_ELEMENTO"/>
    </class>
</hibernate-mapping>

- ElementoValor.hbm.xml:

Code:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC 
   "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
   "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="com.seat.cdm.elementovalor">
   <class name="ElementoValorVO" schema="CUSTOMERDATAMASTER" table="T_ELEMENTO_VALOR">
      <composite-id>
         <key-property name="elementoId" column="ELEMENTO_ID_PK"/>
         <key-property name="valorId" column="VALOR_ID_PK"/> 
      </composite-id>
        <property name="fechaAlta" column="FECHA_ALTA"/>
        <property name="fechaBaja" column="FECHA_BAJA_LOGICA"/>
        <property name="descripcion" column="DESCRIPCION_VALOR"/>
    </class>
</hibernate-mapping>

And the related classes are:
- ElementoVO:

Code:

public class ElementoVO  implements Serializable{
   private String elementoId;
   private String descripcion;
}

- ElementoValorVO:

Code:

public class ElementoValorVO implements Serializable{
   private String elementoId;
   private String valorId;
   private Date fechaAlta;
   private Date fechaBaja;
   private String descripcion;
}

I thought it was easier, because it's a very simple query in SQL, but I'm not able to do it using criterias.

Thanks.

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

Is this the complete mapping? I don't see any relationship between two classes.

If that is the case (both classes are not related) then you can try using DetachedCriteria (for doing a subquery).

mamntc02 · **Joined:** Mon Dec 10, 2007 12:56 pm **Posts:** 7

Yes, there is no relationship between the classes. They're related by constraints in DB, and this is not possible to change because is a requirement. The field related is ELEMENTO_ID_PK.

What's a DetachedCriteria? I've read it's used when session is not available, but I always use a session!!

I've been looking for information about criteria joins, and there's an example that is almost everywhere, but I don't understand how it works:

Code:

session.createCriteria(Accommodation.class)
       .createCriteria("languages", "language")
       .add(Expression.eq("language.code", "FR"))
       .list();

Where is the join in this expression? Besides Accomodation.class, where is the other table in the join? Or even better, How is this expression in SQL?

Thanks again.

sethrohit1977 · **Joined:** Mon Jan 22, 2007 10:32 am **Posts:** 101

You can learn more about DetachedCriteria here

http://www.hibernate.org/hib_docs/v3/re ... hedqueries

As far as example goes, here is what I think is happening

Accommodation class has a set called languages that contains elements of another type lets say Language. This language type has a property called code.

createCriteria("languages", "language")

this line create a join between the accommodation and language table and then the last line puts a restriction over a column of language table.

HTH