Hibernate Community • View topic - Order By clause in HQL

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Order By clause in HQL

Page 1 of 1

[ 5 posts ]

Previous topic | Next topic

Author

Message

mpachar

Post subject: Order By clause in HQL

Posted: Fri Feb 27, 2009 6:04 am

Newbie

Joined: Sat Feb 21, 2009 1:30 pm
Posts: 5
Location: Bangalore

How to parametrize the Order By clause in HQL.

For Eg.
There is table Customer with columns CustID, CustName, CustAddress....
Map CustomerEntity to Customer Table.

SQL : Select * from customer T1
Order by T1.custID desc

HQL: Select T1 from CustomerEntity T1
Order by T1.custID desc.

I want the order by clause parametrize i.e
HQL: Select T1 from CustomerEntity T1
Order by T1.:param desc

where param can be either custID or custName

Can anybody help me how to do this in Hibernate.......
In my actual requirement the param is more than 6 and i dont want to create the same query 6 times.....

Top

nordborg

Post subject:

Posted: Fri Feb 27, 2009 6:28 am

Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden

It is not possible. I don't know of any databases that supports using parameters like that. You'll have to dynamically build your HQL. A simple way is to use a template HQL string, with a placeholder, that you can search-and-replace on the fly. For example:

Code:

String hqlTemplate = "select T1 from CustomerEntity T1 order by {ORDERBY} desc";
String orderBy = "T1.custID";
String hql = hqlTemplate.replace("{ORDERBY}", orderBy);

Top

mpachar

Post subject:

Posted: Sat Feb 28, 2009 6:59 am

Newbie

Joined: Sat Feb 21, 2009 1:30 pm
Posts: 5
Location: Bangalore

Thanks for your reply.

But in my actual requirement, i am using the query as a NamedQuery. Since NamedQueries are precompiled and improves performance, we dont want to change that. My actual query is something like this......

Code:

@NamedQuery(name = "PRODUCTS_DETAIL1"
             query = "SELECT T1  FROM ProductsDetail T1 ,"
                + " Customer T2 " 
                   + "WHERE T1.groupType=:grpType " 
                   + "AND T1.customerID = T2.customerID " 
                   + "AND T1.customer_ID =:customerID " 
                   + "ORDER BY T1.productID"
             ),

@NamedQuery(name = "PRODUCTS_DETAIL2"
             query = "SELECT T1  FROM ProductsDetail T1 ,"
                + " Customer T2 " 
                   + "WHERE T1.groupType=:grpType " 
                   + "AND T1.customerID = T2.customerID " 
                   + "AND T1.customer_ID =:customerID " 
                   + "ORDER BY T1.productName"
             ),

@NamedQuery(name = "PRODUCTS_DETAIL3"
             query = "SELECT T1  FROM ProductsDetail T1 ,"
                + " Customer T2 " 
                   + "WHERE T1.groupType=:grpType " 
                   + "AND T1.customerID = T2.customerID " 
                   + "AND T1.customer_ID =:customerID " 
                   + "ORDER BY T1.quantity"
             ),

If you see the above queries, only Order By clause is different, everything is same.

How to change the NamedQuery during runtime????

Top

mpachar

Post subject:

Posted: Sat Feb 28, 2009 7:03 am

Newbie

Joined: Sat Feb 21, 2009 1:30 pm
Posts: 5
Location: Bangalore

Code:

@NamedQuery(name = "PRODUCTS_DETAIL1"
             query = "SELECT T1  FROM ProductsDetail T1 ,"
                + " Customer T2 " 
                   + "WHERE T1.groupType=:grpType " 
                   + "AND T1.customerID = T2.customerID " 
                   + "AND T1.customer_ID =:customerID " 
                   + "ORDER BY T1.productID"
             ),

@NamedQuery(name = "PRODUCTS_DETAIL2"
             query = "SELECT T1  FROM ProductsDetail T1 ,"
                + " Customer T2 " 
                   + "WHERE T1.groupType=:grpType " 
                   + "AND T1.customerID = T2.customerID " 
                   + "AND T1.customer_ID =:customerID " 
                   + "ORDER BY T1.productName"
             ),

@NamedQuery(name = "PRODUCTS_DETAIL3"
             query = "SELECT T1  FROM ProductsDetail T1 ,"
                + " Customer T2 " 
                   + "WHERE T1.groupType=:grpType " 
                   + "AND T1.customerID = T2.customerID " 
                   + "AND T1.customer_ID =:customerID " 
                   + "ORDER BY T1.quantity"
             ),

If you see the above queries, only Order By clause is different, everything is same.

How to change the NamedQuery during runtime????

Top

nordborg

Post subject:

Posted: Sat Feb 28, 2009 10:47 am

Expert

Joined: Wed Mar 03, 2004 6:35 am
Posts: 1240
Location: Lund, Sweden

Quote:

Since NamedQueries are precompiled and improves performance....

As you said, they are precompiled, and you can't change them after that. A query with a different "order by" can be a very different from a performance point of view. Eg. the strategy the database uses for sorting depends on the datatype, presence of indexes, number of matching rows, and so on. So, you either need to build your query dynamically or create more named queries.

Top

Page 1 of 1

[ 5 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