How to embed Oracle hints in Hibernate query
来源:互联网 发布:amd表面格式优化 编辑:程序博客网 时间:2024/04/30 13:16
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 = ?
- How to embed Oracle hints in Hibernate query
- How to embed Lua 5.1 in C++
- How to embed Windows Media Player in HTML document
- How to embed a manifest in an assembly
- How to embed VideoIO in your Flex/Flash application?
- How to embed Excel in a .NET WinForms App
- How to embed Lua in C++ on Visual Studio
- How to embed an icon in a Qt application
- How to execute a query in varchar2 ?
- Oracle SQL Query Tuning Hints
- How to get the query statement of LOV in Oracle Form
- How To Query And Change The Oracle Hidden Parameters In Oracle 10g and 11g (文档 ID 315631.1)
- How To Query Cross-Site Lists In DataFormWebPart
- How to show query plan and runtime statistic in Derby
- how to execute the sql query in scala
- How to call stored procedure in Hibernate
- How to call stored procedure in Hibernate
- How to create Function/Procedure in Oracle?
- JSP的八个内置对象-学习笔记
- xcode6加入pch文件
- Google Java编程风格指南
- Jenkins 邮件内容范例
- java设计模式系列--工厂模式
- How to embed Oracle hints in Hibernate query
- abap ALV多级汇总
- qtp,VBScript操作MySQL数据库时,关于多次访问数据库的问题,数据集如何处理
- iOS开发系列--触摸事件、手势识别、摇晃事件、耳机线控
- 剑指Offer系列---(24)栈的压入、弹出序列
- java 反编译 行号对齐 decompiler如何去掉行号
- html5 canvas 自定义画图裁剪图片
- 目前在国内见过最全的 IntelliJ Idea 快捷键
- unity3d从streamingassets拷贝到persistentassets