Hibernate Show SQL Example

Santosh BalgarDecember 19th, 2019Last Updated: December 17th, 2019

0 136 1 minute read

This is another example of the hibernate series. Here, we feature a comprehensive article about the Hibernate Show SQL. Hibernate is a feature-rich Object Relational Mapping tool. It offers very fine-tuned debugging capabilities by enabling logs at various levels.

In this article, I am going to show how SQL queries can be printed in a hibernate application.

1. Hibernate Configuration

In this section, we will see how to configure hibernate for your application and how to enable the debugging of SQL in your application.

If you are using xml for configuring hibernate, use the below configuration,

 

    
        org.h2.Driver
        jdbc:h2:mem:test;DB_CLOSE_DELAY=-1
        org.hibernate.dialect.H2Dialect
        true

The configuration show_sql enables hibernate to show debugging SQL.

For Java-based configurations, refer the below code block,

Hibernate Util

public class HibernateUtil {
    private static SessionFactory sessionFactory;
    public static SessionFactory getSessionFactory() {
        if (sessionFactory == null) {
            try {
                Configuration configuration = new Configuration();
                // Hibernate settings similar to hibernate.cfg.xml's properties
                Properties settings = new Properties();
                settings.put(Environment.DRIVER, "org.h2.Driver");
                settings.put(Environment.URL, "jdbc:h2:mem:test;DB_CLOSE_DELAY=-1");
                settings.put(Environment.USER, "sa");
                settings.put(Environment.PASS, "");
                settings.put(Environment.DIALECT, "org.hibernate.dialect.H2Dialect");
                settings.put(Environment.SHOW_SQL, "true");
                settings.put(Environment.CURRENT_SESSION_CONTEXT_CLASS, "thread");
                settings.put(Environment.HBM2DDL_AUTO, "create-drop");
                configuration.setProperties(settings);
                configuration.addAnnotatedClass(Employee.class);
                ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder()
                        .applySettings(configuration.getProperties()).build();
                sessionFactory = configuration.buildSessionFactory(serviceRegistry);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return sessionFactory;
    }
}

By setting configuration Environment.SHOW_SQL to will print the debugging SQL.

Sample SQL output from hibernate looks as in the below image,

Hibernate Show SQL - SQL Output — Show SQL Output

This way it is easy to see which query is getting executed by hibernate. It comes very handy in debugging problematic queries and also fine-tuning the application for better performance.

As you see it is very easy to enable SQL logging in hibernate.