-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 2
  [ 17 posts ]  Go to page 1, 2  Next
  Previous topic | Next topic 
Author Message
lukmanph
 Post subject: pagination and join queries
PostPosted: Wed Oct 22, 2003 1:10 pm 
Newbie

Joined: Wed Oct 22, 2003 12:59 pm
Posts: 14
hi all

I am trying to run pagination in a query that has the following form:


select field1,.. fieldn
from table1,table2,table3
where
table1.condition1=table2.condition1
and table2.condition2=table3.condition3

and i set the maxResults and first Result properties for the query object but i always return me an empty list.
When I see the log file, Hibernate transforms the above query into something like

select * from ( select row_.*, rownum rownum_ from ( select field1,.. fieldn
from table1,table2,table3
where
table1.condition1=table2.condition1
and table2.condition2=table3.condition3)))

is there anything extra that needs to be done?

the workaround we figured out was to create a view based on this query.
but am still curious to know whether pagination will work for join queries as well.

thank you in advance
Top
 Profile  
 
dbenoff
 Post subject:
PostPosted: Wed Oct 22, 2003 4:54 pm 
Beginner
Beginner

Joined: Tue Aug 26, 2003 4:19 pm
Posts: 42
First result and max results work fine with queries involving joins. Post your mappings and the HQL.
Top
 Profile  
 
lukmanph
 Post subject:
PostPosted: Thu Oct 23, 2003 10:17 am 
Newbie

Joined: Wed Oct 22, 2003 12:59 pm
Posts: 14
dbenoff wrote:
First result and max results work fine with queries involving joins. Post your mappings and the HQL.


hi

thanks for your reply and please find attached the info for the query and pagination
please let me know if you need more info.

very much appreciated your help

Patria


here are the mappings

FBB_ACCOUNT

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.sp.fbb.businessobject.account.FbbAccount" table="FBB_ACCOUNT">
<id column="ACCNT_ID" name="id" type="long">
<generator class="sequence">
<param name="sequence">FBB_ACCNT_PK_SEQ</param>
</generator>
</id>
<property column="NAM_ACCOUNT_ID" length="100" name="namAccountId" not-null="true" type="string"/>
<property column="NAM_CUSTODIAN_ACCOUNT" length="30" name="namCustodianAccount" type="string"/>
<property column="GOAL_ID" length="12" name="goalId" type="long"/>
<property column="MEMBER_ID" length="12" name="memberId" type="long"/>
<property column="MEMBER_NAME" length="250" name="memberName" type="string"/>
<property column="ACTIVE" length="1" name="active" type="string"/>
<property column="CUSTODIAL_ACCOUNT_LMD" length="7" name="custodialAccountLmd" type="timestamp"/>
<property column="COMPLETE_ACCOUNT_OPEN_LMD" length="7" name="completeAccountOpenLmd" type="timestamp"/>
<property column="ACCOUNT_CREATION_COMPLETE" length="1" name="accountCreationComplete" type="string"/>
</class>

<query name="com.sp.fbb.businessobject.account.AccountByGoalId">
from FbbAccount as c where c.goalId = :goalID
</query>

</hibernate-mapping>

FBB_GOAL


<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>

<class name="com.sp.fbb.businessobject.goal.Goal" table="FBB_GOAL" dynamic-insert="true" dynamic-update="true">
<id name="goalID" column="GOAL_ID" type="java.lang.Long">
<generator class="sequence">
<param name="sequence">FBB_GOAL_PK_SEQ</param>
</generator>
</id>

<property column="NAM_GOAL_ID" length="12" name="namGoalID" type="java.lang.Long" />
<property column="HHOLD_ID" length="12" name="householdID" type="java.lang.Long" insert="false" />
<property column="USER_ID" length="12" name="producerID" type="java.lang.Long" />
<property column="GOAL_NAME" name="name" length="30" type="string" not-null="true" />
<property column="GOAL_DESCRIPTION" name="description" length="1024" type="string" />
<property column="GOAL_ACTIVE" name="active" length="1" type="string" />
<property column="GOAL_ISDUMMY" name="dummy" length="1" type="string" />
<property column="TAAM_COMPLETED" name="taamCompleted" type="string" length="1" update="true" insert="false"/>
<property column="GOAL_HH_ACTIVE" name="isLinkedToHH" type="string" length="1" />
<property column="GOAL_HH_LMD" length="7" name="goalHHLinklastModifiedDate" type="timestamp" update="true"/>
<property column="LAST_MODIFIED_DATE" name="lastModifiedDate" type="timestamp" update="false" insert="false"/>
<property column="IMPL_ID" length="1024" name="implementationID" type="java.lang.String"/>
<property column="IMPL_COMMENTS" length="1024" name="implementationComments" type="java.lang.String"/>
<property column="GOALCTGRY_ID" length="12" name="goalCategoryID" not-null="true" type="java.lang.Long"/>

<set name="accounts" table="FBB_ACCOUNT" inverse="true" lazy="true">
<key column="GOAL_ID" />
<one-to-many class="com.sp.fbb.businessobject.account.FbbAccount" />
</set>

</class>

</hibernate-mapping>



FBB_NAM_ALERT_INSTANCE

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.sp.fbb.businessobject.alert.AnAlertInstance" table="FBB_NAM_ALERT_INSTANCE">
<id column="ALRTINST_ID" name="alertID" type="long">
<generator class="sequence">
<param name="sequence"></param>
</generator>
</id>
<property column="USER_ID" length="12" name="userID" not-null="true" type="java.lang.Long"/>
<property column="ALERT_TEMPLATE_ID" length="12" name="alertTemplateID" type="java.lang.Long"/>
<property column="ALERT_ID" length="12" name="namAlertID" type="java.lang.Long"/>
<property column="MESSAGE" length="1024" name="message" type="string"/>
<property column="CREATED_DATE" length="7" name="createdDate" type="timestamp"/>
<property column="CLIENT_ID" length="12" name="clientID" type="java.lang.Long"/>
<property column="CLIENT_NAME" length="1024" name="clientName" type="string"/>
<property column="ACCOUNT_ID" name="accountID" type="java.lang.Long"/>
<property column="CUSTODIAN_ACCOUNT_NO" length="12" name="custodianAccountNo" type="string"/>
<property column="ACCOUNT_VALUE" length="15" name="accountValue" type="java.lang.Float"/>
<property column="ACCOUNT_VARIANCE" length="5" name="accountVariance" type="java.lang.Float"/>
<property column="VARIANCE_LIMIT" length="5" name="varianceLimit" type="java.lang.Float"/>
<property column="MODEL_NAME" length="250" name="modelName" type="string"/>
<property column="MAP_NAME" length="250" name="mapName" type="string"/>
<property column="BASKET_NAME" length="250" name="basketName" type="string"/>
<property column="TICKER" length="250" name="ticker" type="string"/>
<property column="ALRT_STATUS" length="18" name="alertStatus" type="string"/>
<property column="ALERT_STATUS_CODE_ID" length="1" name="alertStatusCode" type="string"/>
<property column="ALRT_DELETE" length="1" name="alertDelete" type="string"/>

</class>
</hibernate-mapping>


FBB_NAM_ALERT_STATUS_CODE

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.sp.fbb.businessobject.alert.AnAlertStatusCode" table="FBB_NAM_ALERT_STATUS_CODE">
<id column="ALERT_STATUS_CODE_ID" name="alertStatusCodeID" type="string">
<generator class="sequence">
<param name="sequence"></param>
</generator>
</id>
<property column="ALERT_STATUS_CODE_TEXT" length="250" name="alertStatusCodeText" not-null="false" type="string"/>
</class>
</hibernate-mapping>


FBB_NAM_ALERT_TEMPLATE_CODE
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 2.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">
<hibernate-mapping>
<class name="com.sp.fbb.businessobject.alert.AnAlertTemplateCode" table="FBB_NAM_ALERT_TEMPLATE_CODE">
<id column="ALERT_TEMPLATE_ID" name="alertTemplateID" type="long">
<generator class="sequence">
<param name="sequence"></param>
</generator>
</id>
<property column="ALERT_TEMPLATE_TEXT" length="250" name="alertTemplateText" not-null="false" type="string"/>
</class>
</hibernate-mapping>




code for pagination

Code:
String query=
"SELECT a.modelName,c.alertTemplateText,a.clientName,a.clientID,a.custodianAccountNo,a.accountValue,d.name,a.createdDate,a.message,a.alertStatusCode,a.accountID,d.goalID,a.alertID
FROM AnAlertInstance a,AccountAlertTemplate b,AnAlertTemplateCode c,Goal d, FbbAccount e
WHERE
(a.alertTemplateID=b.namAlertTmplId) and (a.alertTemplateID=c.alertTemplateID) and (b.subscribe='Y') and (a.userID =:userID) and (e.goalId=d.goalID) and (a.namAlertID=:alertID) and (a.accountID=b.accntId) and (a.accountID=e.id)";



transaction = session.beginTransaction();
query =session.createQuery(query);
query.setParameter("userID", userID);
query.setParameter("alertID", alertID

query.setMaxResults(1);      
query.setFirstResult(1);
results = query.list();
transaction.commit();
Top
 Profile  
 
dbenoff
 Post subject:
PostPosted: Thu Oct 23, 2003 1:37 pm 
Beginner
Beginner

Joined: Tue Aug 26, 2003 4:19 pm
Posts: 42
Does this query work without the first record/max record parameters? Maybe someone else will give you a more detailed answer, but looking over your mappings it looks like you've got some issues. For example, alert instance should have a alert template mapped as a many-to-one property, not justa as a "long". If your DB schema is set up already, I'd suggest you regenerate your mappings with Middlegen.
Top
 Profile  
 
lukmanph
 Post subject: thanks for your reply
PostPosted: Thu Oct 23, 2003 2:02 pm 
Newbie

Joined: Wed Oct 22, 2003 12:59 pm
Posts: 14
thanks mate
will look into it, and yes, the query works without setting the pagination parameters
cheers
P
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Thu Oct 23, 2003 2:08 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
What happens when you use:

Code:
query.setMaxResults(100);     
query.setFirstResult(1);


Top
 Profile  
 
lukmanph
 Post subject:
PostPosted: Thu Oct 23, 2003 3:01 pm 
Newbie

Joined: Wed Oct 22, 2003 12:59 pm
Posts: 14
hi Gavin
thanks for your help and it does work, i.e, when max of results is set to greater than 1 it works....
thank you very much... just have a naive question though... why it will not work for maxResults =1?
cheers
Patria
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Thu Oct 23, 2003 4:38 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
What version of Hibernate? Looks suspiciously like a bug in the new dialect-specific limit stuff.
Top
 Profile  
 
lukmanph
 Post subject:
PostPosted: Thu Oct 23, 2003 4:44 pm 
Newbie

Joined: Wed Oct 22, 2003 12:59 pm
Posts: 14
current version in production environment is 2.0.1
planning to move to 2.0.3

btw, wanted to congratulate you for the super professional job you are doing with Hibernate
Project we are working on it is for a very interesting customer and they are happy with what they have seen so far, especially re. hibernate capabilities.
keep up the excellent job you guys are doing
cheers
P
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Thu Oct 23, 2003 4:50 pm 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
thanks.

yeah, this is a bug in 2.0.1, IIRC. Upgrade. It should be painless.
Top
 Profile  
 
lorban
 Post subject:
PostPosted: Tue Oct 28, 2003 8:57 am 
Regular
Regular

Joined: Tue Oct 28, 2003 8:25 am
Posts: 72
Location: Belgium
Gavin,

I just ran into the very same problem. From the understanding I have, this code:

Code:
Query query = session.createQuery(sql);
query.setMaxResults(1);
query.setFirstResult(1);


should launch a query that would have up to one row in its result set and starting with the first row the database found.

When I launch the SQL generated by Hibernate from this query, the result set returned by the database always contains the correct amount of row but the first one is always chopped, ie: the query never returns the first row of the result set, it always starts with the second one.

The problem seems to come from line 108 in net.sf.hibernate.dialect.Oracle9Dialect:

Code:
pagingSelect.append(" ) row_ where rownum <= ?) where rownum_ > ?");


Since Oracle starts counting rownum from 1 and not from 0, the correct code should be:

Code:
pagingSelect.append(" ) row_ where rownum < ?) where rownum_ >= ?");


This has been tested on oracle 8 and 9 and it seems to return the correct result in every cases.

Ludovic
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Tue Oct 28, 2003 9:15 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
And are you using the latest version of Hibernate??
Top
 Profile  
 
lorban
 Post subject:
PostPosted: Wed Oct 29, 2003 5:01 am 
Regular
Regular

Joined: Tue Oct 28, 2003 8:25 am
Posts: 72
Location: Belgium
Version 2.0.3, I haven't tried 2.1.
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Wed Oct 29, 2003 9:50 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
If Oracle counts from 1, and we pass in 0 for the firstRow, then "rownum > ?" is quite correct.

My understanding is that this was broken in 2.0, and even in 2.0.1, but everyone agrees that it is fixed in 2.0.3.

This has been a real pain to get working correctly, but I think it -does- now work!
Top
 Profile  
 
gavin
 Post subject:
PostPosted: Wed Oct 29, 2003 9:52 am 
Hibernate Team
Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia
Oh! I see you problem!

You think that Hibernate counts from 1!

Hibernate ALWAYS counts from 0, on all APIs, and the JavaDoc is explicit on this point. Use setFirstResult(0) if you want the first row.
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 2
  [ 17 posts ]  Go to page 1, 2  Next

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

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.