With Oracle hints, you can alter the Oracle execution plans to affect the way how Oracle retrieve the data from database. Go here for more detail about Oracle optimizer hints.

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).

P.S Thanks Pete contribute on this.

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 = ?

More Native SQL queries examples.

Any Java questions or problems? please post at this JavaNullPointer.com forum, see you there ~
[ Read More ] You can find more similar articles at Hibernate Tutorials