4 Replies Latest reply: Mar 14, 2013 9:48 AM by Cdelahun-Oracle RSS

    Order by in is not giving exact sorted reults in JPA

    996208
      Hi I have wrote a query in sql server like --> Select * from table order by columnName asc

      here I am getting records starting with 'AA' eg: AA i dont know why it is coming, but after searching some forums i have got a solution like

      select * from tablename order by lower(columnName) Collate SQL_Latin1_General_CP850_BIN asc

      is is giving correct results to me, but i nned to write this syntax in JPA , when apply collate syntax in JPA query it is not supprting

      JPA query:
      this.createQuery(select * from tableaName t order by lower(t.columnname) collate SQL_Latin1_General_CP850_BIN asc).getResultList();

      Can some one give me some suggessitions how this collate work exactly and how to appy this in JPA query.


      Thanks in Advance :)
        • 1. Re: Order by in is not giving exact sorted reults in JPA
          Cdelahun-Oracle
          EclipseLink allows adding an "SQL" operator to pass in native SQL to a JPQL query, as described here:
          http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/JPQL#SQL

          For this, you might use it as:
          "Select entity from EntityAName entity order by SQL('lower(?) Collate SQL_Latin1_General_CP850_BIN asc', entity.columnName)"

          Best Regards,
          Chris
          • 2. Re: Order by in is not giving exact sorted reults in JPA
            996208
            Hello cdelahun,

            Thanks for your help

            I have tried this syntax but I am still getting error,

            my query is :

            String queryString = "from Location loc order by SQL('lower(?) Collate SQL_Latin1_General_CP850_BIN asc', loc.name)";
            List<Location> results = this.createQuery(queryString).setFirstResult(pageNumber * 10).setMaxResults(10).getResultList();
            return results.isEmpty() ? null : results;

            here i need to get the 10 records from the sorted order

            error I am getting is

            ERROR [JDBCExceptionReporter] 'SQL' is not a recognized built-in function name.
            20:54:10,403 INFO [LogitCompositeHelper] Composite Event Fired...
            20:54:10,429 ERROR [viewhandler] Error Rendering View[view/refdata/location.xhtml]

            javax.faces.FacesException: javax.el.ELException: /view/refdata/inc/locationSearch.xhtml @42,105 value="#{logit.ui.locationBean.resultList}": Error reading 'resultList' on type com.logit.ui.location.LocationBean_$$_javassist_seam_30
                 at javax.faces.component.UIData.getValue(UIData.java:612)
                 at org.ajax4jsf.component.UIDataAdaptorBase.getValue(UIDataAdaptorBase.java:1647)
                 ...

            Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
                 at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
                 ....

            I am using jsf as front end,

            can you please give me some suggessitions on this why this error is getting and the syntax i wrote is any wrong?

            added to the above my DB is MS SQL Server, I am looking for JPA Query to sort out the problem.

            Thanks in Advance :)

            Edited by: 993205 on Mar 13, 2013 11:14 PM
            • 3. Re: Order by in is not giving exact sorted reults in JPA
              996208
              added to the above my DB is MS SQL Server, I am looking for JPA Query to sort out the problem.
              • 4. Re: Order by in is not giving exact sorted reults in JPA
                Cdelahun-Oracle
                Yes, you are using Hibernate as your persistence provider and using a solution native to EclipseLink/TopLink - the result of asking in a TopLink/JPA forum.

                I do not know of a JPA compatible way to get custom database SQL other than using a native SQL query, such as em.createNativeQuery("your SQL Select string");

                You might want to post on a forum specific to your JPA provider or switch to using EclipseLink if using a native SQL isn't what you are after.

                Best Regards,
                Chris