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

    EntityManager's Native Query & Paging Results

    rabbiaqaswar

      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?

        • 1. Re: EntityManager's Native Query & Paging Results
          jaikiran

          What does your strQuery look like?

          • 2. Re: EntityManager's Native Query & Paging Results
            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

              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

                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

                  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

                    Anyone? I still cannot solve the issue :(

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

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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