How do I get more debug messages from Hibernate?

Question

I haven't been able to get any more console output (to help with debugging) from Hibernate despite setting a few properties in the hibernate.cfg.xml file. For example, adding the line <property name="show_sql">true</property> did not, in fact, show SQL statements in the console.

I've also tried playing around with the contents of the log4j.properties file - like setting log4j.logger.org.hibernate=debug - with no luck. What am I missing?

---

Edit: the contents of the hibernate-service.xml file are

<server> 
    <mbean code="org.jboss.hibernate.jmx.Hibernate" 
       name="jboss.har:service=Hibernate_SMS"> 
        <attribute name="DatasourceName">java:/SMS_DS</attribute> 
        <attribute name="Dialect">org.hibernate.dialect.HSQLDialect</attribute> 
        <attribute name="SessionFactoryName">java:/hibernate/SessionFactory</attribute> 
        <attribute name="CacheProviderClass">org.hibernate.cache.HashtableCacheProvider</attribute>
        <attribute name="ShowSqlEnabled">true</attribute>
    </mbean> 
</server>

I'm not 100% sure if this actually has any effect, though. That XML file is in the Eclipse project that handles my database stuff, but doesn't seem to be in the JBoss deploy directory.

---

Edit 2: This is definitely deployed as a HAR. That said, I'm pretty sure that I need hibernate.cfg.xml - I recall having problems when a mapping document was omitted as an entry in that file. I think the HAR is generated using ant - there's a target for it in the build.xml file:

<target name="har" depends="prepare" description="Builds the Hibernate HAR file">
    <mkdir dir="${class.root}" />
    <mkdir dir="${distribution.dir}" />

    <jar destfile="${distribution.dir}/${har.name}">                    
        <!-- include the hbm.xml files  -->
        <fileset dir="${class.root}">
            <include name="**/*.hbm.xml"/>
            <include name="com/[redacted]/sms/data/dto/*.class"/>
            <include name="com/[redacted]/sms/data/dto/base/*.class"/>
        </fileset>

        <!-- include jboss-service.xml -->
        <metainf dir="${hibernate.dir}">
            <include name="hibernate-service.xml"/>
        </metainf>
    </jar>
</target>

but when I do the ant build with incorrectly-formed xml in the hibernate-service.xml file, it doesn't fail. Update Even deleting the file entirely doesn't cause a build to fail. Any ideas here? End Update

It sounds like getting Hibernate to output SQL statements should (if everything's set up correctly) take care of it entirely - does that mean that the settings in log4j.properties won't make a difference here? - because that actually does change the output when running ant.

Edit 3: After running into other weird issues with my data har, I deleted the har file entirely and rebuilt it using the har ant build target. Suddenly everything's working! Thanks to chessplay for his awesome helpfulness. Can't say I know exactly what did it in the end, but I'd rather acknowledge his efforts than write and accept my own answer; My apologies if you're not a "he."

Answer 1

The correct property name is hibernate.show_sql and it definitely works.

Make sure that your hibernate.cfg.xml is the right one and it is getting picked up; same goes for your log4j.properties file. I know those seem like dumb suggestions, but they account for 98% of "missing logging output" problems.

Update: I'll update the answer rather than keep adding comments. You do need to make sure your hibernate-service.xml is picked up by JBoss. The easy way to check is to add a syntax error to it (like omit a closing brace on some element) and see if JBoss blows up during restart :-) It should be packaged into har and deployed as part of your build process, so if you realize that it's not being picked up by JBoss, that's the place to look. I would get rid of conflicting settings in hibernate.cfg.xml (e.g. everything that you're specifying as mbean attributes should not be replicated in hibernate.cfg.xml). For that matter, do you even need hibernate.cfg.xml? If deployed as HAR, your mappings should be automatically scanned / picked up.

Answer 2

I just use the log4j.properties file to log Hibernate statements. For SQL statements, the log4j.logger.org.hibernate.SQL property needs to be set to debug, and for SQL parameters/returned values the log4j.logger.org.hibernate.type property needs to be set to trace.

Here is the log4j.properties file I use:

### 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
log4j.rootLogger=debug, stdout

### Main Hibernate
log4j.logger.org.hibernate=debug

### log just the SQL
log4j.logger.org.hibernate.SQL=debug

### log JDBC bind parameters
log4j.logger.org.hibernate.type=trace

Answer 3

Also make shure you're using the correct log4j-configuration.

When running in jboss (you are doing that, right?), you configure log4j-logging in $JBOSS_HOME/server/<config>/conf/jboss-log4j.xml.

There are two default appenders; FILE writes to log/server.log and CONSOLE to stdout (sometimes redirected log/console.log). Adjust threshold on the appender to DEBUG in the file or by setting the systemproperty jboss.server.log.threshold (depends on which version of jboss you are using).

You'll also need to adjust the logging priority of hibernate by adding a category:

<category name="org.hibernate">
  <priority value="DEBUG"/>
</category>