-
1. Re: EntityManager's Native Query & Paging Results
jaikiran Jun 13, 2008 3:06 AM (in response to rabbiaqaswar)What does your strQuery look like?
-
2. Re: EntityManager's Native Query & Paging Results
rabbiaqaswar Jun 13, 2008 5:32 AM (in response to rabbiaqaswar)It is a SQL join query which returns primary keys of type BigDecimal of a database table.
It workds fine without paging i.e. return 20 ids and when the first ten records are fetched using the setFirstResult & setMaxResults methods. The problem occurs when the first result is set to 10 and max to 20.
Thanks -
3. Re: EntityManager's Native Query & Paging Results
rabbiaqaswar Jun 13, 2008 6:07 AM (in response to rabbiaqaswar)Since the query was returning an Object array, i printed its contents
if(ids != null && !ids .isEmpty()){ for (Iterator it = ids.iterator(); it.hasNext();) { Object[] obj = (Object[])it.next(); System.out.println(""+Arrays.deepToString(obj)); } }
and the result was:13:58:53,124 INFO [STDOUT] [206, 11] 13:58:53,124 INFO [STDOUT] [207, 12] 13:58:53,124 INFO [STDOUT] [208, 13] 13:58:53,124 INFO [STDOUT] [209, 14] 13:58:53,124 INFO [STDOUT] [210, 15] 13:58:53,124 INFO [STDOUT] [211, 16] 13:58:53,124 INFO [STDOUT] [212, 17] 13:58:53,124 INFO [STDOUT] [213, 18] 13:58:53,124 INFO [STDOUT] [214, 19] 13:58:53,124 INFO [STDOUT] [215, 20]
-
4. Re: EntityManager's Native Query & Paging Results
jaikiran Jun 13, 2008 7:01 AM (in response to rabbiaqaswar)Looking at the output, it looks like along with the ids, even the record count (ex: 11, 12, 13 etc....) is being returned. Without looking at the actual query which you are using i can't say what the problem is.
-
5. Re: EntityManager's Native Query & Paging Results
rabbiaqaswar Jun 13, 2008 10:04 AM (in response to rabbiaqaswar)Following is the sql query:
SELECT DISTINCT USR.USERID FROM USER USR, USERWATCH UW WHERE USR.ADDEDBYID =1 AND USR.COUNTRY = CASE WHEN (UW.COUNTRY is null) THEN USR.COUNTRY ELSE UW.COUNTRY END AND USR.ALIVE = 'A'
-
6. Re: EntityManager's Native Query & Paging Results
rabbiaqaswar Jun 15, 2008 4:29 AM (in response to rabbiaqaswar)Anyone? I still cannot solve the issue :(
-
7. Re: EntityManager's Native Query & Paging Results
jaikiran Jun 16, 2008 11:13 AM (in response to rabbiaqaswar)I have a simple native query:
select distinct usr.userid from user usr where usr.name like '%jai%'
which returns me the expected output when used with setFirstResult and setMaxResult. Not sure whether its a problem with your query or something else.
You can try enabling TRACE level logs of Hibernate package by adding the following to your jboss-log4j.xml file under the %JBOSS_HOME%\server\< serverName>\conf folder. Then in your server.log see if you can find something which will help you understand what's going on. For me, i see logs like this:2008-06-16 20:38:57,932 TRACE [http-0.0.0.0-8080-2] [org.hibernate.jdbc.AbstractBatcher] preparing statement 2008-06-16 20:38:58,011 DEBUG [http-0.0.0.0-8080-2] [org.hibernate.jdbc.AbstractBatcher] about to open ResultSet (open ResultSets: 0, globally: 0) 2008-06-16 20:38:58,011 TRACE [http-0.0.0.0-8080-2] [org.hibernate.loader.Loader] processing result set 2008-06-16 20:38:58,011 DEBUG [http-0.0.0.0-8080-2] [org.hibernate.loader.Loader] result set row: 0 2008-06-16 20:38:58,011 DEBUG [http-0.0.0.0-8080-2] [org.hibernate.loader.Loader] result row: 2008-06-16 20:38:58,011 TRACE [http-0.0.0.0-8080-2] [org.hibernate.type.BigIntegerType] returning '7' as column: ID 2008-06-16 20:38:58,011 DEBUG [http-0.0.0.0-8080-2] [org.hibernate.loader.Loader] result set row: 1 2008-06-16 20:38:58,011 DEBUG [http-0.0.0.0-8080-2] [org.hibernate.loader.Loader] result row: 2008-06-16 20:38:58,011 TRACE [http-0.0.0.0-8080-2] [org.hibernate.type.BigIntegerType] returning '8' as column: ID 2008-06-16 20:38:58,011 DEBUG [http-0.0.0.0-8080-2] [org.hibernate.loader.Loader] result set row: 2 2008-06-16 20:38:58,011 DEBUG [http-0.0.0.0-8080-2] [org.hibernate.loader.Loader] result row: 2008-06-16 20:38:58,026 TRACE [http-0.0.0.0-8080-2] [org.hibernate.type.BigIntegerType] returning '9' as column: ID 2008-06-16 20:38:58,026 TRACE [http-0.0.0.0-8080-2] [org.hibernate.loader.Loader] done processing result set (3 rows) 2008-06-16 20:38:58,026 DEBUG [http-0.0.0.0-8080-2] [org.hibernate.jdbc.AbstractBatcher] about to close ResultSet (open ResultSets: 1, globally: 1) 2008-06-16 20:38:58,026 DEBUG [http-0.0.0.0-8080-2] [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1) 2008-06-16 20:38:58,026 TRACE [http-0.0.0.0-8080-2] [org.hibernate.jdbc.AbstractBatcher] closing statement
-
8. Re: EntityManager's Native Query & Paging Results
rabbiaqaswar Jun 17, 2008 2:16 AM (in response to rabbiaqaswar)Do you think it has anything to do with the following property in persistence.xml file:
<property name="hibernate.jdbc.batch_size" value="20"/>
Will try debugging through Trace.
Thanks -
9. Re: EntityManager's Native Query & Paging Results
jaikiran Jun 17, 2008 2:31 AM (in response to rabbiaqaswar)I don't believe the jdbc batch size would affect your query output.
For further logging options, see this http://www.hibernate.org/hib_docs/v3/reference/en/html_single/#configuration-logging
And yes, do look for the actual query being fired by Hibernate in the logs. Something similar to:2008-06-16 20:38:57,886 TRACE [http-0.0.0.0-8080-2] [org.hibernate.loader.custom.sql.SQLCustomQuery] starting processing of sql query [SELECT DISTINCT USR.ID FROM USER USR WHERE USR.name like '%jai%'] 2008-06-16 20:38:57,917 TRACE [http-0.0.0.0-8080-2] [org.hibernate.impl.SessionImpl] SQL query: SELECT DISTINCT USR.ID FROM USER USR WHERE USR.name like '%jai%' 2008-06-16 20:38:57,932 DEBUG [http-0.0.0.0-8080-2] [org.hibernate.SQL] SELECT DISTINCT USR.ID FROM USER USR WHERE USR.name like '%jai%' limit ?, ?
-
10. Re: EntityManager's Native Query & Paging Results
rabbiaqaswar Jun 17, 2008 4:56 AM (in response to rabbiaqaswar)Thanks.
I enabled Trace and the two queries executed internally are actually different!
Here is the query which is executed with the setfirstResult(0) and setMaxResults(20):select * from (SELECT DISTINCT USR.USERID FROM USER USR, USERWATCH UW WHERE USR.ADDEDBYID =1 AND USR.COUNTRY = CASE WHEN (UW.COUNTRY is null) THEN USR.COUNTRY ELSE UW.COUNTRY END AND USR.ALIVE = 'A') where rownum <= ?
and for setFirstResult(10) and setMaxResult(10), the query is:select * from ( select row_.*, rownum rownum_ from (SELECT DISTINCT USR.USERID FROM USER USR, USERWATCH UW WHERE USR.ADDEDBYID =1 AND USR.COUNTRY = CASE WHEN (UW.COUNTRY is null) THEN USR.COUNTRY ELSE UW.COUNTRY END AND USR.ALIVE = 'A') row_ where rownum <= ?) where rownum_ > ?
Any idea, what is happening here? -
11. Re: EntityManager's Native Query & Paging Results
rabbiaqaswar Jun 17, 2008 4:59 AM (in response to rabbiaqaswar)Typo error in the previous post, for the first scenario, its:
setfirstResult(0) and setMaxResults(10) -
12. Re: EntityManager's Native Query & Paging Results
jaikiran Jun 17, 2008 7:55 AM (in response to rabbiaqaswar)Which database server/version do you use? Can you post the datasource file that you have configured?
-
13. Re: EntityManager's Native Query & Paging Results
rabbiaqaswar Jun 17, 2008 8:16 AM (in response to rabbiaqaswar)It is Oracle 9i. Here is the datasource file:
<datasources> <xa-datasource> <jndi-name>UserDS</jndi-name> <track-connection-by-tx/> <isSameRM-override-value>false</isSameRM-override-value> <xa-datasource-class>oracle.jdbc.xa.client.OracleXADataSource</xa-datasource-class> <xa-datasource-property name="URL">jdbc:oracle:thin:@mach:1521:ORAUR</xa-datasource-property> <xa-datasource-property name="User">name</xa-datasource-property> <xa-datasource-property name="Password">password</xa-datasource-property> <!-- Uses the pingDatabase method to check a connection is still valid before handing it out from the pool --> <!--valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name--> <!-- Checks the Oracle error codes and messages for fatal errors --> <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name> <!-- Oracles XA datasource cannot reuse a connection outside a transaction once enlisted in a global transaction and vice-versa --> <no-tx-separate-pools/> <!-- corresponding type-mapping in the standardjbosscmp-jdbc.xml --> <metadata> <type-mapping>Oracle9i</type-mapping> </metadata> </xa-datasource> <mbean code="org.jboss.resource.adapter.jdbc.vendor.OracleXAExceptionFormatter" name="jboss.jca:service=OracleXAExceptionFormatter"> <depends optional-attribute-name="TransactionManagerService">jboss:service=TransactionManager</depends> </mbean> </datasources>
-
14. Re: EntityManager's Native Query & Paging Results
jaikiran Jun 17, 2008 9:06 AM (in response to rabbiaqaswar)I dont have a setup which has Oracle on it. I don't know why the query is getting generated incorrectly.
Can you try adding the following property to your persistence.xml and see if it helps:<persistence> <persistence-unit ...> <jta-data-source>...</jta-data-source> <properties> <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle9Dialect"/> </properties> </persistence-unit> </persistence>
Not sure if this is going to help, but give it a try.