This discussion is archived
4 Replies Latest reply: Mar 14, 2013 7:48 AM by cdelahun RSS

Order by in is not giving exact sorted reults in JPA

996208 Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points