Java – Hibernate顯示 SQL 語法及 Parameter Values

在使用 Hibernate 時因它的語法有二種.一種是 HQL，另一種為 SQL，但如何讓 Hibernate 顯示最後執行 SQL 語法呢？可以在 hibernate.cfg.xml 中設定來達成此功能.

1. show_sql
顯示所有產生的 SQL 語法至 console.

<!--hibernate.cfg.xml -->
<property name="show_sql">true</property>

<!--Output -->
Hibernate: INSERT INTO mkyong.stock_transaction 
(CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
VALUES (?, ?, ?, ?, ?, ?)

2. format_sql
讓產生出的 SQL 語法可讀性更高

<!--hibernate.cfg.xml -->
<property name="format_sql">true</property>

<!--Output -->
Hibernate: 
    INSERT 
    INTO
        mkyong.stock_transaction
        (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
    VALUES
        (?, ?, ?, ?, ?, ?)

3. use_sql_comments
Hibernate會加入 comments 至產生出的 SQL 語法

<!--hibernate.cfg.xml -->
<property name="use_sql_comments">true</property>

<!--Output -->
Hibernate: 
    /* insert com.mkyong.common.StockTransaction
        */ INSERT 
        INTO
            mkyong.stock_transaction
            (CHANGE, CLOSE, DATE, OPEN, STOCK_ID, VOLUME) 
        VALUES
            (?, ?, ?, ?, ?, ?)

Hibernate configurate file
Full sample

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.bytecode.use_reflection_optimizer">false</property>
        <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
        <property name="hibernate.connection.password">password</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mkyong</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>
        <property name="use_sql_comments">true</property>
    </session-factory>
</hibernate-configuration>

可是產生出的 SQL 語法都是 Parameter Value 都為 “?”，最後在使用 log4j 的設定來顯示各自的 Parameter Value 為何？

File : log4j.properties

# Direct log messages to stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{ABSOLUTE} %5p %c{1}:%L - %m%n
 
# Root logger option
log4j.rootLogger=INFO, stdout
 
# Hibernate logging options (INFO only shows startup messages)
log4j.logger.org.hibernate=INFO
 
# Log JDBC bind parameter runtime arguments
log4j.logger.org.hibernate.type=trace

<!--Output -->
Hibernate: 
    /* dynamic native SQL query */ select
        distinct SID 
    from
        Table1 
    where
        Column1 = ?
2012-03-01 13:29:53,288 TRACE org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [VARCHAR] - abcde

故已可查看 Hibernate 所產生的 SQL 語法及各個 Parameter Value 為何.