Re: [eclipselink-users] Best way to support case insensitive alphanumeri

[vaidya nathan <vaidyaatdst@xxxxxxxxx>]

From the documentation , I see that we cant use this for non String columns like say Numeric columns.

For eg, if the column is a numeric , then this will not work ..

_expression_  exp = (ascending ? exp.toLowerCase().ascending() :  exp.toLowerCase().descending()) ;

Is there a way in which we can get meta data information about the column and then have this sort something like

if (column is String)
{
_expression_  exp = (ascending ? exp.toLowerCase().ascending() :  exp.toLowerCase().descending()) ;
}

else
{
_expression_  exp = (ascending ? exp.ascending() :  exp.descending()) ;
}

Or is there any other way in which we can do this. If possible please furnish me some  sample codes too or point me to some samples where we are doing this .

Also Dennis you said that didn't work .  Can you elaborate please? What issues did you have ?

Thx for all your response

Vaidya

On Thu, Jun 6, 2013 at 9:11 AM, Dennis Fuglsang <dennis.fuglsang@xxxxxxxxxx> wrote:

I had a similar requirement and initially tried using the UPPER operator in the ORDER BY clause but that did not work for me.  For example:   "SELECT a.ID, a.name FROM Table a ORDER BY UPPER(a.name)"

What did work for me was specifying an upper-cased form of the column to sort on in the SELECT clause.   For example:   "SELECT a.ID, a.name, UPPER(a.name) AS name_order FROM Table a ORDER BY name_order"

-----Original Message-----
From: Tom Ware
Sent: Thursday, June 06, 2013 9:05 AM
To: eclipselink-users@xxxxxxxxxxx
Subject: Re: [eclipselink-users] Best way to support case insensitive alphanumeric sorting..

EclipseLink provides toUpperCase() and toLowerCase() expressions and JPA provides UPPER and LOWER operators.  For queries I expect you could use those operators in the ORDER BY part of your query to sort.

If you are talking about a mapping, you could use an EclipseLink DescriptorCustomizer to add ordering by an EclipseLink _expression_ to the selection query.  Roughly:

   OneToManyMapping _oneToManyMapping_ =
(OneToOneMapping)descriptor.getMappingForAttributeName("attributeName");
   ExpressionBuilder builder =
oneToManyMapping.getSelectionQuery().getExpressionBuilder();
   _expression_ orderExpression = builder.get("postalCode").toLowerCase().ascending();
   oneToManyMapping.getSelectionQuery().addOrdering(orderExpression);

-Tom
On 05/06/2013 11:54 AM, vaidya nathan wrote:
> Hi,
>
> What is the best way to support case insensitive alphanumeric sorting
> using jpa2 and eclipselink ? An example of what i would like to
> achieve is to sort on something similiar to the canada zips which could be alphanumeric..
>
> We are using Eclipselinks ReadAllQuery and _expression_ language to
> build our queries and the queries that we have so far is like this..
>
> SELECT * FROM
> (SELECT a.*, ROWNUM rnum  FROM
> (SELECT DISTINCT t1.ID AS a1,<colname>
>    FROM <Table>  ORDER BY t0.<colname> ASC) a WHERE ROWNUM <= ?) WHERE rnum > ?
> )a
>
>
> Thx
>
>
>
>
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users
_______________________________________________
eclipselink-users mailing list
eclipselink-users@xxxxxxxxxxx
https://dev.eclipse.org/mailman/listinfo/eclipselink-users