1 2 Previous Next 27 Replies Latest reply on Jun 19, 2008 9:53 AM by Rabbia Qaswar

    EntityManager's Native Query & Paging Results

    Rabbia Qaswar Newbie

      Hello

      I am working with EJB3 on JBoss 4.2.2 GA. In one of the SessionBeans there is a query (sql) which i am running using the createNativeQuery method of the EntityManager.

      The query is supposed to return 20 Ids of an Entity. The query returns all 20 Ids. Now if i want to use paging (see only the last 10 records) through the setFirstResult and setMaxResults method of Query, i do this:

      query = em.createNativeQuery(strQuery.toString());
      query.setFirstResult(10);
      query.setMaxResults(10);
      


      then:

      ArrayList ids = (ArrayList) query.getResultList();
       System.out.println("no of users found: "+ids.size());
      
       if(ids != null && !ids .isEmpty()){
       for (Iterator it = ids.iterator(); it.hasNext();) {
       BigDecimal userId= (BigDecimal)it.next();
       System.out.println("userid: "+userId);
       }
       }
      



      In this case the query finds 10 records as the ids.size method gives 10 but then in the loop it gives this exception i.e.

      03:09:47,451 ERROR [STDERR] java.lang.ClassCastException: [Ljava.lang.Object;
      03:09:47,451 ERROR [STDERR] at com.server.servicefacades.UserSessionBean.searchUser(UserSessionBean.java:802)
      03:09:47,451 ERROR [STDERR] at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)




      I suppose the ids ArrayList contains Object arrays instead of containing the userId somehow but i donot understand why. Help please?

        • 2. Re: EntityManager's Native Query & Paging Results
          Rabbia Qaswar Newbie

          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
            Rabbia Qaswar Newbie

            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 pai Master

              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
                Rabbia Qaswar Newbie

                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
                  Rabbia Qaswar Newbie

                  Anyone? I still cannot solve the issue :(

                  • 7. Re: EntityManager's Native Query & Paging Results
                    jaikiran pai Master

                    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
                      Rabbia Qaswar Newbie

                      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 pai Master

                        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
                          Rabbia Qaswar Newbie

                          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
                            Rabbia Qaswar Newbie

                            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 pai Master

                              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
                                Rabbia Qaswar Newbie

                                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 pai Master

                                  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.


                                  1 2 Previous Next