Want to see SQL with values in Hibernate?
One of the early problems I had to work on in a Spring application was with Hibernate. Since I was not sure what SQL was being generated by Hibernate, I turned on the hibernate configuration setting:
File named either hibernate.cfg.xml or persistence.xml
This will show the SQL but since it uses Prepared Statements all the values are simply displayed as question marks. Where is the data? Maybe we can do this through logging with Log4j / SLF4J / LogBack.
Logging Hibernate Features
Most java projects use Log4j for their logging needs. Recently, demands for enterprise level logging have given us wrapper implementations for Log4J. For faster and more configurable logging, use LogBack. LogBack was written by the same person as Log4J which can and should be wrapped by SLF4J.
Below is an example of a log4j.properties file with options for different Hibernate log settings.
log4j.rootLogger=INFO, stdout log4j.appender.stdout=org.apache.log4j.ConsoleAppender log4j.appender.stdout.layout=org.apache.log4j.PatternLayout log4j.appender.stdout.layout.ConversionPattern=%d %-5p %c - %m%n log4j.logger.org.springframework.batch=DEBUG ############################# ### Spring JDBC Classes ### ############################# log4j.logger.org.springframework.jdbc=DEBUG log4j.logger.org.springframework.transaction=INFO ############################# ### Spring JPA logging ### ############################# log4j.logger.org.springframework.orm.jpa=DEBUG # for debugging datasource initialization #log4j.logger.test.jdbc=DEBUG ############################# ### All Hibernate Classes ### ############################# log4j.logger.org.hibernate=info ############################# ### SQL Fragments & Statements ### ############################# log4j.logger.org.hibernate.sql=debug ############################# ### Mapping Java to JDBC types ### ############################# log4j.logger.org.hibernate.type=debug ############################# ### Schema Gen and DDL Info ### ############################# log4j.logger.org.hibernate.tool.hbm2ddl=info ############################# ### 2nd Level and Query Caching ### ############################# log4j.logger.org.hibernate.cache=info #############################
What if I want the values logged IN the SQL statements?
Well that is helpful but it still does NOT give us the data values in the SQL statements.
Ask many developers what they would use and, like a pull string on the back of a doll they would say … “dude, use P6Spy“;. So I did… and, it failed. Why? First, that project hasn’t been updated since 2003. Second, we had a feature in our application that used LOB data and that is not supported in P6Spy. So, did some searching and found…
Ladies and Gentlemen, introducing Elvyx
Elvyx is a tool that monitors our JDBC activity and can report details on your queries to you. At a minimum we get to see the actual data sent with each query. Elvyx intercepts your SQL calls capturing the queries with the data values.
We also get a visual graph of all the queries executed, how many times, how long they took etc. The client tool also provides a nice graph with the grid displaying all the queries.
Configuring Elvyx
Using the Elvyx client tool, we enter your database URL and Elvyx creates the replacement database URL for our database.properties file.
Below are the steps to Elvyx Installation and Configuration:
- Download
elvyx-1.0.24_beta.zip– this file contains executable scripts - Make a directory for Elvyx
mkdir /opt/elvyx - Unzip the
elvyx-1.0.24_beta.zipinto our new/opt/elvyxdirectory - Change directory into the
cd /opt/elvyx/lib - From the command line execute
java -jar elvyx-1.0.24.jar - From the Elvyx Client, select
Options > Help to Generate Elvyx URL:
- Enter the
Real Database DriverandReal JDBCURL values
- Copy the value from the
Elvyx URLfield - Open the application’s
database.propertiesfile - Set the URL field to be the copied value
- Set the Database Driver field to:
com.elvyx.Driver - Start our application
- Start the Elvyx Client from the
/opt/elvyx/libdirectory withjava -jar elvyx-1.0.24.jar
Note: There is a text based URL generator executed from java -classpath elvyx-1.0.24.jar com.elvyx.client.util.ElvyxURL
Below is an example of what our database settings would look like.
datasource.jdbc.driverclassname=com.elvyx.Driver datasource.jdbc.url=jdbc:elvyx://localhost:4448/?elvyx.real_driver= oracle.jdbc.driver.OracleDriver&elvyx.real_jdbc= jdbc%3Aoracle%3Athin%3A%40localhost%3A1521%3AXE&user= MYUSERNAME&password=MYPASSWORD
Note: The jdbc URL should be on a single line, the above content is wrapped for readability only.
Elvyx Status
- Has gone over 2 years without an update
- Licensing is on Sourceforge site as Apache2
- Not available in Maven Repositories
- Source code is out of date, has Maven file with out of date dependencies
