How to embed Oracle hints in Hibernate query

With Oracle hints, you can alter the Oracle execution plans to affect the way how Oracle retrieve the data from database.

In Hibernate, is this possible to embed the Oracle hint into the Hibernate query?

Hibernate setComment()?

Can you embed the Oracle hint into HQL with Hibernate custom comment “setComment()” function? Let’s see an example here

1. Original Hibernate Query

This is a simple select HQL to retrieve a Stock with a stock code.

String hql = "from Stock s where s.stockCode = :stockCode";
List result = session.createQuery(hql)
.setString("stockCode", "7277")
.list();

Output

Hibernate: 
    select
        stock0_.STOCK_ID as STOCK1_0_,
        stock0_.STOCK_CODE as STOCK2_0_,
        stock0_.STOCK_NAME as STOCK3_0_ 
    from mkyong.stock stock0_ 
    where stock0_.STOCK_CODE=?

2. Try Hibernate setComment()

Enable the hibernate.use_sql_comments in Hibernate’s configuration file (hibernate.cfg.xml) in order to output the custom comment to your log file or console.

<!-- hibernate.cfg.xml -->
<?xml version="1.0" encoding="utf-8"?>
...
<hibernate-configuration>
 <session-factory>
    ...
    <property name="show_sql">true</property>
    <property name="format_sql">true</property>
    <property name="use_sql_comments">true</property>
    <mapping class="com.mkyong.common.Stock" />
  </session-factory>
</hibernate-configuration>

Using Hibernate setComment() to insert a custom comment to your query.

String hql = "from Stock s where s.stockCode = :stockCode";
List result = session.createQuery(hql)
.setString("stockCode", "7277")
.setComment("+ INDEX(stock idx_stock_code)")
.list();

Output

Hibernate: 
    /* + INDEX(stock idx_stock_code) */ select
        stock0_.STOCK_ID as STOCK1_0_,
        stock0_.STOCK_CODE as STOCK2_0_,
        stock0_.STOCK_NAME as STOCK3_0_ 
    from mkyong.stock stock0_ 
    where stock0_.STOCK_CODE=?

3. Is this work?

It’s not, there are two problem with Hibernate custom comments.

1. The Oracle hint have to append after the ‘select’, not before.

Hibernate generated query

 /* + INDEX(stock idx_stock_code) */ select 

The correct way should be…

select  /*+ INDEX(stock idx_stock_code) */  

2. Hibernate will add an extra space in between “/* +” automatically.

In Hibernate, there are still no official way to embed the Oracle hints into Hibernate query langueges (HQL).

Working solution

The only solution is using the Hibernate createSQLQuery method to execute the native SQL statement.

String hql = "/*+ INDEX(stock idx_stock_code) */ 
    select * from stock s where s.stock_code = :stockCode";
List result = session.createQuery(hql)
.setString("stockCode", "7277")
.list();

output

Hibernate: 
    /*+ INDEX(stock idx_stock_code) */ select * 
    from stock s where s.stock_code = ?