How to use Oracle query hint in Hibernate

Question

I am trying to use Oracle hint in Hibernate to call force index, but didn't find any proper API in Hibernate 3.6.10.Final.

I somehow tried with projections in Hibernate criteria:

proList.add(Projections.sqlProjection("/*+ INDEX_DESC(CONTACT_USER_FK_I) */", new String[]{}, new Type[]{}));
                proList.add(Projections.property("objectId"));
                criteria.setProjection(proList);
                return criteria.list();

But I am getting the exception below:

    EXCEPTION
Caused by: 8 SQL Error  (could not execute query; SQL [select /*+ INDEX_DESC(CONTACT_USER_FK_I) */, this_.CONTACT_ID as y0_ from R4GDEV01_MBW.CONTACT this_ w
here this_.USER_ID=? and this_.ADDRESS_BOOK_ID in (?) and this_.DELETION_DATE is null order by lower(this_.FIRSTNAME) asc]; nested exception is org.hibernate
.exception.SQLGrammarException: could not execute query)
        at com.fusionone.pml.dao.hibernate.AbstractDao.executeCallback(AbstractDao.java:391)
        at com.fusionone.pml.dao.hibernate.AbstractContactDao.searchContacts(AbstractContactDao.java:1019)
        at com.fusionone.nab.core.service.impl.MergeServiceImpl.getFilteredContactIds(MergeServiceImpl.java:154)
        ... 91 more

I found out that the projection is appending a , after query hint.

Is there any other way to use Oracle query hint in Hibernate 3.6.10 criteria or in HQL?

Thanks

Is there any other way to use Oracle hint instead of hard coding the Index in the code, some thing like can we implement it from hibernate property file. — Santosh, Dec 31 '15 at 11:44

score 4 · Accepted Answer · edited May 23 '17 at 12:34

4

You should try to search first. Here is a simple trick to solve this, just transform the hint into a column:

"/*+ INDEX_DESC(CONTACT_USER_FK_I) */ 1 as MYHINT"

edited May 23 '17 at 12:34

Community

1
1

answered Dec 31 '15 at 10:43

vnov

335
1
10

Thanks a lot for quick response, this solved the problem. – Santosh Dec 31 '15 at 11:06

score 1 · Answer 2 · answered Dec 31 '15 at 10:49

1

Your problem is that there is nothing previous to ,. Due to you want to add a projection on objectIdattribute, you could transform your criteria to somenthing like this:

Projections.sqlProjection("/*+ INDEX_DESC(CONTACT_USER_FK_I) */ objectId", new String[]{}, new Type[]{}))

answered Dec 31 '15 at 10:49

malaguna

4,183
1
17
33

Thanks i got the issue, is there any other way to use Oracle hint instead of hard coding the Index in the code, some thing like can we implement it from hibernate property file – Santosh Dec 31 '15 at 11:10

How to use Oracle query hint in Hibernate

2 Answers2

Linked