CODEWITHSUNDEEP
javahibernate
Ravi Parekh
Ravi Parekh

Reputation: 5594

hibernate Join with criteria with distinct won't work

when i run i got resulted query where i expect Actual Query

Actual Query(to be Produce, Desire Query) distinct [ID]

select distinct rc.* from ratecodeparam rcp , ratecodes rc where rc.travelfrom <= '2011-04-22' and rc.travelto >= '2011-04-25' and rc.id = rcp.p_id;

OR

select distinct rc.* from ratecodeparam rcp join ratecodes rc on rc.travelfrom <= '2011-04-22' and rc.travelto >= '2011-04-25' and rc.id = rcp.p_id;

I want

distinct rc.*

Code

i got through code but disticnt rc.*

  session.createCriteria(RateCode.class)
            .add(Restrictions.le("travelFrom", from.getTime()))
            .createCriteria("rateCodeParams", "rcp")
    list = crit.list();

RateCode.hbm.xml

<class catalog="hermes" name="com.RateCode"  table="ratecodes">
        <id name="rateCodeId" type="java.lang.Integer">
            <column name="id"/>
            <generator class="native"/>
        </id>
        <property name="code" type="string">
            <column length="32" name="code" unique="true"/>
        </property>
        <set name="rateCodeParams" cascade="all, delete-orphan" order-by="param">
            <key>
                <column name="p_id"  />
            </key>
            <one-to-many class="com.RateCodeParam" />
         </set>
</class>

RateCodeParam.hbm.xml

<class catalog="hermes" name="com.RateCodeParam" table="ratecodeparam">
        <id name="id" type="java.lang.Integer">
            <column name="id"/>
            <generator class="identity"/>
        </id>       
        <many-to-one  class="com.RateCode" name="rateCode" insert="false" fetch="join" update="false" > 
            <column name="p_id" />
        </many-to-one>
</class>

Upvotes: 0

Views: 970

Answers (1)

Codo
Codo

Reputation: 78815

Your query is missing a join because you don't add one to the query. Setting the fetch mode (setFetchMode) just adds more details to an existing join. But in your query it doesn't exist.

The correct query probably looks like this:

Criteria crit = session.createCriteria(RateCode.class)
    .add(Restrictions.le("travelFrom", from.getTime()))
    .createCriteria("rateCodeParams", "rcp");
list = crit.list();

Upvotes: 1

Related Questions