hibernate sql queries in log files

hibernate sql queries in log files

Logging Hibernate-generated SQL queries can be very useful for debugging and performance tuning. Hibernate provides several ways to log SQL statements and their parameters:

1.  Hibernate Configuration : You can configure Hibernate to log SQL queries using the Hibernate configuration file (hibernate.cfg.xml) or properties file (hibernate.properties).

2.  Log4j/Logback Configuration : By configuring the appropriate logging framework (e.g., Log4j or Logback), you can capture and manage the logs generated by Hibernate.

3.  Custom Logging : Hibernate also supports custom logging configurations if you need more control over how queries are logged.

sql queries

Configuration in hibernate.cfg.xml

Add the following properties to your Hibernate configuration file to enable SQL logging:

Example
xml
<property name="hibernate.show_sql">true</property>
<property name="hibernate.format_sql">true</property>
<property name="hibernate.use_sql_comments">true</property>


Log4j Configuration
To log Hibernate queries using Log4j, you need to configure Log4j properly. Here's an example of a log4j.properties file:

properties
log4j.rootLogger=INFO, console, file

 Console appender
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.target=System.out
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%d{HH:mm:ss} %-5p %c{1}:%L - %m%n

 File appender
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=hibernate.log
log4j.appender.file.MaxFileSize=1MB
log4j.appender.file.MaxBackupIndex=1
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ISO8601} %-5p [%t] %c{2} - %m%n

 Hibernate logging
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Step 1: Define the Entity
Java Example
Let's create a simple Hibernate application that logs SQL queries.

  Step 1: Define the Entity

java
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class Employee {
    @Id
    private int id;
    private String name;
    private String department;
    private double salary;

    // Getters and setters
}
Step 2: Configure Hibernate and Logging

 hibernate.cfg.xml 

xml
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!-- Database connection settings -->
        <property name="hibernate.connection.driver_class">com.mysql.cj.jdbc.Driver</property>
        <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/mydb</property>
        <property name="hibernate.connection.username">root</property>
        <property name="hibernate.connection.password">password</property>
        
        <!-- JDBC connection pool settings ... using built-in test pool -->
        <property name="hibernate.c3p0.min_size">5</property>
        <property name="hibernate.c3p0.max_size">20</property>
        <property name="hibernate.c3p0.timeout">300</property>
        <property name="hibernate.c3p0.max_statements">50</property>
        <property name="hibernate.c3p0.idle_test_period">3000</property>
        
        <!-- Specify dialect -->
        <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>

        <!-- Echo all executed SQL to stdout -->
        <property name="hibernate.show_sql">true</property>
        <property name="hibernate.format_sql">true</property>
        <property name="hibernate.use_sql_comments">true</property>

        <!-- Update the database schema on startup -->
        <property name="hibernate.hbm2ddl.auto">update</property>

        <!-- Mappings -->
        <mapping class="com.example.Employee"/>
    </session-factory>
</hibernate-configuration>


 log4j.properties 

properties
log4j.rootLogger=INFO, console, file

 Console appender
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.target=System.out
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=%d{HH:mm:ss} %-5p %c{1}:%L - %m%n

 File appender
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=hibernate.log
log4j.appender.file.MaxFileSize=1MB
log4j.appender.file.MaxBackupIndex=1
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=%d{ISO8601} %-5p [%t] %c{2} - %m%n

 Hibernate logging
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Step 3: Main Application

java
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class Main {
    public static void main(String[] args) {
        // Set up the Hibernate session factory
        SessionFactory sessionFactory = new Configuration().configure().buildSessionFactory();
        Session session = null;

        try {
            session = sessionFactory.openSession();
            session.beginTransaction();

            // Create and save an Employee
            Employee employee = new Employee();
            employee.setId(1);
            employee.setName("John Doe");
            employee.setDepartment("Engineering");
            employee.setSalary(75000);
            session.save(employee);

            session.getTransaction().commit();
        } catch (Exception e) {
            if (session != null) {
                session.getTransaction().rollback();
            }
            e.printStackTrace();
        } finally {
            if (session != null) {
                session.close();
            }
            sessionFactory.close();
        }
    }
}

Explanation of the Example

  1. Define the Entity : The Employee entity class is defined with fields for id, name, department, and salary.
  2. Configure Hibernate and Logging : In hibernate.cfg.xml, properties are set to show SQL, format SQL, and use SQL comments. In log4j.properties, Log4j is configured to log Hibernate SQL queries and their parameters to both the console and a file named hibernate.log.
  3. Main Application : The main method sets up the Hibernate session factory, opens a session, and creates an Employee object. The transaction is committed, and the session is closed. All SQL queries executed by Hibernate will be logged as configured.
  4. This setup ensures that Hibernate-generated SQL queries are logged for debugging and performance tuning purposes.

Homepage

Readmore