How to see the SQL generated by Hibernate

In order to show the SQL which Hibernate generates behind the hoods, you need to enable a property in your configuration file.

If you are developing your applications with JPA, all you need to set the property hibernate.show_sql to true in your persistence.xml file. See this as an example:

<persistence version="2.1"
   xmlns="http://xmlns.jcp.org/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="
        http://xmlns.jcp.org/xml/ns/persistence
        http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
   <persistence-unit name="primary">
      <jta-data-source>java:jboss/datasources/KitchensinkQuickstartDS</jta-data-source>
      <properties>
         <!-- Properties for Hibernate -->
         <property name="hibernate.hbm2ddl.auto" value="create-drop" />
         <property name="hibernate.show_sql" value="true" />
      </properties>
   </persistence-unit>
</persistence>

As a result, in your server logs you will see all SQL Statements that Hibernate is executing under the hoods:

hibernate show sql

In order to see the Binding parameters of your Statements, you can activate the following Logger category:

<logger category="org.hibernate.type.descriptor.sql">
      <level name="TRACE"/>
</logger>

Here is the output of an application which has the above Logger:

2022-09-10 17:29:55,502 INFO  [stdout] (default task-1) Hibernate: select member0_.id as id1_0_, member0_.email as email2_0_, member0_.name as name3_0_, member0_.phone_number as phone_nu4_0_ from MemberJSP member0_ order by member0_.name asc
2022-09-10 17:29:55,510 TRACE [org.hibernate.type.descriptor.sql.BasicExtractor] (default task-1) extracted value ([id1_0_] : [BIGINT]) - [0]
2022-09-10 17:29:55,513 TRACE [org.hibernate.type.descriptor.sql.BasicExtractor] (default task-1) extracted value ([email2_0_] : [VARCHAR]) - [[email protected]]
2022-09-10 17:29:55,514 TRACE [org.hibernate.type.descriptor.sql.BasicExtractor] (default task-1) extracted value ([name3_0_] : [VARCHAR]) - [John Smith]
2022-09-10 17:29:55,514 TRACE [org.hibernate.type.descriptor.sql.BasicExtractor] (default task-1) extracted value ([phone_nu4_0_] : [VARCHAR]) - [2125551212]

Finally, to configure the Binding of SQL Statements at datasource level, check this article: How to trace JDBC statements with JBoss and WildFly

Formatting the SQL

As it is, the output you can see on the Console can be quite messy. You can turn on SQL formatting by adding the following property:

<property name="hibernate.format_sql" value="true" />

As a result, you will see nicely formatted output in your logs:

17:37:03,994 INFO  [stdout]  Hibernate: 
17:37:03,994 INFO  [stdout]      
17:37:03,994 INFO  [stdout]      drop table Member if exists
17:37:03,994 INFO  [stdout]  Hibernate: 
17:37:03,994 INFO  [stdout]      
17:37:03,994 INFO  [stdout]      drop sequence if exists hibernate_sequence
17:37:03,995 INFO  [stdout]  Hibernate: create sequence hibernate_sequence start with 1 increment by 1
17:37:03,995 INFO  [stdout]  Hibernate: 
17:37:03,995 INFO  [stdout]      
17:37:03,995 INFO  [stdout]      create table Member (
17:37:03,995 INFO  [stdout]         id bigint not null,
17:37:03,995 INFO  [stdout]          email varchar(255) not null,
17:37:03,995 INFO  [stdout]          name varchar(25) not null,
17:37:03,995 INFO  [stdout]          phone_number varchar(12) not null,
17:37:03,995 INFO  [stdout]          primary key (id)
17:37:03,995 INFO  [stdout]      )

Using Native Hibernate configuration

On the other hand, if you are developing applications using native Hibernate, you need to place the following properties in your hibernate.cfg.xml file:

<hibernate-configuration>
    <session-factory>
         <!-- connection settings -->
        <property name="show_sql">true</property>
        <property name="format_sql">true</property>
    </session-factory>
</hibernate-configuration>

As for the JPA example, the property show_sql when set to true will print the SQL Statements. The property format_sql when set to true will format the SQL Statements.

Found the article helpful? if so please follow us on Socials