Hibernate Community • View topic - How to do a 'substr' on a order field with criteria?

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

How to do a 'substr' on a order field with criteria?

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

Yoann56

Post subject: How to do a 'substr' on a order field with criteria?

Posted: Thu Nov 15, 2007 11:39 am

Beginner

Joined: Thu Nov 15, 2007 11:27 am
Posts: 34

Hello,

My problem is quite simple, I want to perform order on a field that is too big to be used for SQL ordering (more than 500 char). So I got some thing like [Error Code: -1013, SQL State: I1013] [-1013] (at 1021): Too many order columns.
One sql solution is to order by substr('myfield',0,20). (I don't know others)

Now I want to do that with criteria. I turn around criteria.addOder( Order....) and criteria.addOder( Property.asc(....)) But I can't find how to add this 'substr'.

Some of my conclusion:
- Going to SQL or HQL is not a solution for me.
- crit.addOrder(Order.asc("substr('nmowner', 0, 20)")); doesn't work

Thank you for your help!

Yoann.

Hibernate version: 3.1.3

Mapping documents:

Code:

<hibernate-mapping>
    <class name="MyPackage.Owner" table="OWNER" schema="MYDB">
        <id name="idowner" type="java.lang.Long">
            <column name="IDOWNER" precision="10" scale="0" />
            <generator class="increment" />
        </id>
        <property name="nmowner" type="java.lang.String">
            <column name="NMOWNER" length="510" not-null="true" />
        </property>
    </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

Criteria crit = session.createCriteria(Owner.class);
crit.add(Restrictions.like("nmowner", "%"));
crit.setFirstResult(1);
crit.setMaxResults(10);
crit.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
crit.addOrder(Order.asc("nmowner"));
List result = crit.list();

Full stack trace of any exception that occurs: [Error Code: -1013, SQL State: I1013] [-1013] (at 1021): Too many order columns

Name and version of the database you are using: MaxDB...

The generated SQL (show_sql=true):
select
this_.IDOWNER as IDOWNER93_0_,
this_.NMOWNER as NMOWNER93_0_
from
MYDB.OWNER this_
where
this_.IDOWNER < 10
order by
this_.NMOWNER asc

Debug level Hibernate log excerpt:

Top

Yoann56

Post subject:

Posted: Fri Nov 16, 2007 10:21 am

Beginner

Joined: Thu Nov 15, 2007 11:27 am
Posts: 34

Hi, i'm still digging my problem, I'm near a solution with Projections

Code:

         pl.add(Projections.alias(
               Projections.sqlProjection(
                  "substr(this_.NMOWNER,1,20) as name",
                  new String[]{"name"},
                  new Type[]{ Hibernate.STRING }), 
               "name" ));

Problem is how to get the Owner bean using that?
Ok ok, I got some idea with meta data:

Code:

         ProjectionList pl = Projections.projectionList();
         ClassMetadata metaOwner = session.getSessionFactory().getClassMetadata(Owner.class);
         
         Iterator itProp = Arrays.asList(metaOwner.getPropertyNames()).iterator();
         while (itProp.hasNext()){
            pl.add(Projections.property((String)(itProp.next())));
         }

That do the job, but it return column and no more the bean... It's more and more dirty.

Any other idea?

Top

Yoann56

Post subject:

Posted: Mon Nov 19, 2007 5:43 am

Beginner

Joined: Thu Nov 15, 2007 11:27 am
Posts: 34

Ok, still looking for a good solution, the less dirty I've at the moment:
1st request with projection of Identifier and substr of the field

Code:

      ProjectionList pl = Projections.projectionList();
      ClassMetadata metaOwner = session.getSessionFactory().getClassMetadata(Owner.class);
      
      // Add the class identifier to the output
      pl.add(Projections.property(metaOwner.getIdentifierPropertyName()));
      String propertyName = getOrderFieldName();
      
      pl.add(Projections.alias(
            Projections.sqlProjection(
               "substr(" + propertyName + ",1,100) as fieldToBeOrdered",
               new String[]{"fieldToBeOrdered"},
               new Type[]{ metaOwner.getPropertyType(propertyName) }), 
            "fieldToBeOrdered" ));
   
      criteria.setProjection(pl);

And a loop on the result to build the list:

Code:

            Iterator it = criteria.list().iterator();
            while (it.hasNext()){
               Object[] obj = (Object[])it.next();
               //get the object corresponding at the identifier
               results.add((Owner)getSession().get(Owner.class, (Serializable)obj[0]));
            }

Still no idea on how to build an "order by substr('toto', 1, 20)" with criteria...

Top

hib123user

Post subject: Re: How to do a 'substr' on a order field with criteria?

Posted: Fri May 08, 2009 3:57 pm

Newbie

Joined: Fri May 08, 2009 3:54 pm
Posts: 1

Any suggestions any one on this problem?

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum