CODEWITHSUNDEEP
javasqldatabasespringhibernate
Steve
Steve

Reputation: 2816

How can I get a printout of a database query from Hibernate and Spring

I work on a huge Java project includes Spring and hibernate.

I was recently debugging some database query issues and drilled down until I discovered this is actually where a query is executed:

org.springframework.transaction.support.TransactionTemplate.execute(TransactionCallback<T>)

The code for the callback method it takes as the input argument is:

new TransactionCallback<List<?>>() {
    @Override
    public List<?> doInTransaction(TransactionStatus status) {
        String queryString = query.createHQLQuery();
        Query hibQuery = getCurrentSession().createQuery(queryString);
        try {
            query.populateHQLQuery(hibQuery, getSessionFactory());
        } catch (DataAccessLayerException e) {
            throw new org.hibernate.TransactionException("Error populating query", e);
        }
        // hibQuery.setCacheMode(CacheMode.NORMAL);
        // hibQuery.setCacheRegion(QUERY_CACHE_REGION);
        if (query.getMaxResults() != null) {
            hibQuery.setMaxResults(query.getMaxResults());
        }
        List<?> results = hibQuery.list();
        return results;
    }
}

I was able to get printouts of the query to the database to debug the problem, but it was messy. The best I could do was to call

org.hibernate.Query.getQueryString()

To get a template for a query, instead of the names of fields and values I got numbered substitution variables, ie " select param1, param2 from Yada where thisthing = param3".

I was able to call

org.hibernate.Query.getNamedParameters()

To get the values of the named parameters ( param1, param2, param3 ).

However, this was quite cumbersome as the queries were quite large and complicated.

What I would like to know if there is a way to get a query to print to standard out, that already has the substitution variables replaced with the values, the named parameters. Basically, a complete query as someone would manually type into a database.

Upvotes: 2

Views: 2889

Answers (3)

aha
aha

Reputation: 3759

If you want the statements written to your log, set the log level of org.hibernate.SQL to DEBUG. Bind parameters are logged if you set the log level of org.hibernate.type.descriptor.sql to TRACE.

As other answers suggest using hibernate.show_sql instructs Hibernate to write the SQL statements to STDOUT. As a consequence this cannot be controlled by the logging framework of your choice. Another major disadvantage is: the persistence.xml is usually part of your application's JAR and cannot be changed without repackaging your application.

Recommended reading: Hibernate Logging Guide – Use the right config for development and production

Upvotes: 0

Cork Kochi
Cork Kochi

Reputation: 1891

In hibernate configuration

If you are using JPA in persistence.xml

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

If you want parameter values

Configure the Log4j in Hibernate

Modify the Log4j properties file, and change the log level to “debug” or “trace” in “log4j.logger.org.hibernate.type” property.

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

# Hibernate logging options (INFO only shows startup messages)
log4j.logger.org.hibernate=INFO

# Log JDBC bind parameter runtime arguments
log4j.logger.org.hibernate.type=trace

Upvotes: 1

gati sahu
gati sahu

Reputation: 2626

Add to application properties 

 spring.jpa.properties.hibernate.show_sql=true
    spring.jpa.properties.hibernate.use_sql_comments=true
    spring.jpa.properties.hibernate.format_sql=true

To log values:

spring.jpa.properties.hibernate.type=trace

Upvotes: 0

Related Questions