OrderBy issue in hibernate

javakumar · **Joined:** Wed Sep 15, 2010 3:15 am **Posts:** 3

I try to generate query using EntityManager.createQuery for my ORM mapping classes. In the resulted query order by include primary key field of the class by default even though I have specified two fileds to order by in @OrderBy ("sequenceOrderNo asc, nextXpath asc "). Please any one advice me how to remove the default primary included in order by clause of the generated query. I am looking the for this solution for more than 4 days. Experts pls help me.

I have used below in my project

DB: Oracled 10g
openjpa 2.0 jar

Thanks
Sathees

rajeev.2012 · **Joined:** Fri Sep 17, 2010 1:13 am **Posts:** 2

Not sure how you are doing javakumar, here is the way that works:-

session.createCriteria(VO.class).
addOrder(Order.asc("columName")).list();

Hope this helps

javakumar · **Joined:** Wed Sep 15, 2010 3:15 am **Posts:** 3

Rajeev,
Thanks for ur kind reply. I am using Entitymanager.createQuery. pls look into my code
Query query = em.createQuery("SELECT expression FROM HIPAA_TST_ST_TRAN_X_ASRTN expression WHERE expression.transactionMasterCid.transName =:tranName ");
query.setParameter("tranName",tranName);

The above code generates below query.
SELECT t0.TRAN_X_ASRTN_TYPE_CID, t3.SQNC_ORDER_NMBR, t3.STATIC_WHERE_CONDITION, t3.TRAN_X_ASRTN_TYPE_CID, t2.SELECT_COLUMNS, t2.SQNC_ORDER_NMBR, t2.STATIC_WHERE_CONDITION, t2.TRAN_X_ASRTN_TYPE_CID FROM HIPAA_TST_ST_TRAN_X_ASRTN t0, HIPAA_TST_ST_TRANSACTION_TYPE t1, HIPAA_TST_ST_XPATH_EXPRESSION t2, HIPAA_TST_ST_XPATH_EXPRESSION t3 WHERE (t1.TRNSCTN_TYPE_NAME = ?) AND t0.TRNSCTN_TYPE_CID = t1.TRNSCTN_TYPE_CID AND t0.TRAN_X_ASRTN_TYPE_CID = t2.TRAN_X_ASRTN_TYPE_CID AND t2.NEXT_XPATH = t3.XPATH_EXPRESSION_SID(+) ORDER BY t0.TRAN_X_ASRTN_TYPE_CID ASC, t2.SQNC_ORDER_NMBR ASC, t2.NEXT_XPATH ASC

Here t0.TRAN_X_ASRTN_TYPE_CID is the primary key. It is included by default in the prepared query. But i dont want that PK in order by list instead, i want to order by t2.SQNC_ORDER_NMBR ASC, t2.NEXT_XPATH ASC only.

Please suggest me further if you can.

cwalker · **Joined:** Thu Sep 09, 2010 7:12 am **Posts:** 1

Can you use ORDER BY in the JPQL?

Code:

SELECT expression FROM HIPAA_TST_ST_TRAN_X_ASRTN expression WHERE expression.transactionMasterCid.transName =:tranName ORDER BY expression.sequenceNo, expression.xpath

If not, maybe you can use some type of containing object of ASRTNs that can manage the order of the list.

Code:

@Entity 
public class TRAN_X_CONTAINER {
@ManyToMany
@OrderBy("sequenceNo, "xpath")
protected List<TRAN_X_ASRTN> asrtns = new ArrayList<TRAN_X_ASRTN>();
}

saltnlight5 · **Posted:** Mon Sep 20, 2010 8:04 am

@javakumar

Hum... OpenJPA? You are in a Hibernate forum!

Try it with Hibernate and if you still have problem, post your unit tests with entity mapping and stacktrace here for advice.