Display Hibernate SQL To Console (Spring)

Question

I'm working with spring 3, hibernate 4. I'm trying to follow this tutorial http://www.mkyong.com/hibernate/hibernate-display-generated-sql-to-console-show_sql-format_sql-and-use_sql_comments/, but my hibernate configuration is different:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:jee="http://www.springframework.org/schema/jee"
  xmlns:mvc="http://www.springframework.org/schema/mvc" xmlns:beans="http://www.springframework.org/schema/beans"
  xmlns:context="http://www.springframework.org/schema/context" xmlns:tx="http://www.springframework.org/schema/tx"
  xsi:schemaLocation="http://www.springframework.org/schema/mvc http://www.springframework.org/schema/mvc/spring-mvc-3.2.xsd
    http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
    http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-3.2.xsd
    http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd
    http://www.springframework.org/schema/jee http://www.springframework.org/schema/jee/spring-jee.xsd">


  <!-- JDBC Data Source. It is assumed you have MySQL running on localhost port 3306 with 
       username root and blank password. Change below if it's not the case -->
 <bean id="myDataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
    <property name="url" value="jdbc:mysql://localhost:3306/project"/>
    <property name="username" value="root"/>
    <property name="password" value="1234"/>
    <property name="validationQuery" value="SELECT 1"/>
     <property name="show_sql" value="true" />
    <property name="format_sql" value="true" />
    <property name="use_sql_comments" value="true" />
  </bean>


</beans>

And the properties show_sql, format_sql and use_sql_comments are not working this way. I get this exception:

Caused by: org.springframework.beans.NotWritablePropertyException: Invalid property 'show_sql' of bean class [org.apache.commons.dbcp.BasicDataSource]: Bean property 'show_sql' is not writable or has an invalid setter method. Does the parameter type of the setter match the return type of the getter?

Is there anyway to achieve the tutorial with the definition of the bean??

Those are Hibernate properties. You can either set them in hibernate.cfg.xml (if you have one), persistence.xml (if using JPA), or give them directly to a Spring factory bean that builds a Hibernate SessionFactory, like LocalSessionFactoryBean. — superEb, Aug 27 '13 at 02:33
How can I do the last thing you said? I dont have hibernate.cfg.xml nor persistence.xml. Can you post an example, please?. Im still learning.. — kiduxa, Aug 27 '13 at 03:07
possible duplicate of [Print query string in hibernate with parameter values](http://stackoverflow.com/questions/1710476/print-query-string-in-hibernate-with-parameter-values) — stevedbrown, Aug 27 '13 at 03:52
This is not a duplicate question because I'm following a tutorial that is not working for me, but after try @ashish chaurasia's solution, the link that you gave me has sense, because is using gerrytan's solution. — kiduxa, Aug 27 '13 at 19:25

score 22 · Accepted Answer · answered Aug 27 '13 at 05:56

show_sql not a property of org.apache.commons.dbcp.BasicDataSource . You have to define it in session factory configuration. like this

 <bean id="sessionFactory" class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
    <property name="dataSource" ref="dataSource" />
    <property name="packagesToScan" value="data" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">org.hibernate.dialect.H2Dialect</prop>
            <prop key="hibernate.current_session_context_class">thread</prop>
            <prop key="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</prop>
            <prop key="hibernate.show_sql">true</prop>
            <prop key="hibernate.hbm2ddl.auto">update</prop>
        </props>
    </property>
</bean>

This is the answer for my question, but @gerrytan's solution is complementary. — kiduxa, Aug 27 '13 at 19:26

gerrytan · Answer 2 · 2013-08-27T04:00:30.200

13

The simplest approach probably is to set following logger to DEBUG:

org.hibernate.SQL

If you use log4j, find / create a log4j.properties file on your classpath root and add

log4j.logger.org.hibernate.SQL=DEBUG

See here for more info about log4j properties: http://logging.apache.org/log4j/1.2/manual.html

edited Aug 27 '13 at 04:00

answered Aug 27 '13 at 03:49

gerrytan

40,313
9
84
99

I'll give an upvote because it was helpful, but @ashish chaurasia's solution is the answer for my question. Thanks anyway ;) – kiduxa Aug 27 '13 at 19:27
This seems to be a better answer and the accepted answer doesn't work for me. – Narayana Nagireddi Apr 11 '14 at 18:47
best and simple answer – VJS Feb 17 '15 at 09:35

score 0 · Answer 3 · answered Oct 23 '19 at 18:28

0

As I'm using JEE 8 and JBoss EAP, I managed to got the SQL after adding this line:

-Dorg.jboss.as.logging.per-deployment=false

on the end of "VM arguments" (Server tab -> JBoss Properties -> Open lauch configuration).

answered Oct 23 '19 at 18:28

Renato Camargos

81
1
2

Display Hibernate SQL To Console (Spring)

3 Answers3

Linked