Search This Blog

Loading...

Friday, 13 July 2012

Hibernate Queries and the Question Mark

The one thing we see in logs when we use Hibernate is the Question Mark. The question mark makes its appearance in all queries generated whether you use HQL or Criteria or do a load on the object identifier. If we need to debug a query, we need to actually copy the values from bind parameters and then replace them for the question marks. This (and I am sure developers will agree with me ) is a pain. Real PAIN.
The reason for all those question marks is Hibernate's use of Prepared Statements for its queries. But why ??
From wikipedia:
The typical workflow of using a prepared statement is as follows:
1. Prepare: The statement template is created by the application 
            and sent to the database management system (DBMS). 
            Certain values are left unspecified, called parameters, 
            placeholders or bind variables (labelled "?" below):
        INSERT INTO PRODUCT (name, price) VALUES (?, ?)
2. The DBMS parses, compiles, and performs query optimization on the 
   statement template, and stores the result without executing it.
3. Execute: At a later time, the application supplies (or binds) values 
            for the parameters, and the DBMS executes the statement 
            (possibly returning a result). The application may execute 
            the statement as many times as it wants with different values.
            In this example, it might supply 'Bread' for the first 
            parameter and '1.00' for the second parameter.
While the advantage of the above is good performance, the slight pain is you cannot see the actual SQL statement with values.
Yesterday while googling on the issue, I came across the blog post which describes the same issues and possible solutions. One of the solutions that appealed to me is the use of Elvyx. It is an open source tool that actually intercepts database bound requests thereby being in possession of the unbound queries (the ones with the ?) and the runtime bound values. Getting all excited I downloaded the latest build and decided to try it out. So far so good.
Unfortunately, the Evlyx code-base has not been updated since 2008. It has no maven dependency that you can automatically add. The dependent jars that it uses have also been upgraded several times over. I got the standalone client running but getting the tool to work with my web archive proved impossible.
I finally had to download the source code (Apache licensed so no problem there) and get it setup locally.
The maven dependencies seen are the latest versions of the jars used. The JTattoo jar was not available in the maven dependency, so had to download it from the site and install it to the local repository.
D:\>mvn install:install-file -Dfile=D:\\JTattoo.jar 
-DgroupId=com.JTattoo -DartifactId=JTattoo -Dversion=1.0 -Dpackaging=jar  -DgeneratePom=true
As I use 1.6 the JDBC interfaces have added a number of methods. So the code was full of compile errors. So I upgraded the implementations with what seemed to me "safe code" (My intention was to get this running and not make it production ready)
I cleaned up the code, added generics and also suppressed compiler warnings (instead of fixing them :( there were way too many)
A final maven build got the application ready. A few jars, a bat file and the app was running on my machine.
Elvyx screen
The next step is to configure our applications so that all our db calls are sent to the Elvyx application.
This means changing our db url. Elvyx provides a UI which accepts our db settings and generates the Elvyx db url.
Once we have this url we update our application database settings to point to the new URL and the database driver to point to com.elvyx.Driver (the Elvyx tool's driver interface). This means we need Elvyx as a part of our own applications pom file. So I installed The Elvyx jar in my repository.
D:\>mvn install:install-file -Dfile=D:\\elvyx-1.0.24.jar 
-DgroupId=com.elvyx -DartifactId=elvyx -Dversion=1.0.24 -Dpackaging=jar  -DgeneratePom=true
Now to include this in my project's maven dependencies:
<dependency>
    <groupId>com.elvyx</groupId>
    <artifactId>elvyx</artifactId>
    <version>1.0.24</version>
</dependency>
Now everything is ready - On starting the application, the results start appearing on the Elvyx UI.
The tool includes query count and analysis( although I don't know how perfect they are - considering I did add some of my own code) But the best part was that it showed all queries fired - and with their parameter values.
I have uploaded the cleaned code (its an Eclipse project - you can directly import and use) for download. Also the final running jar can also be downloaded here. The zip includes a bat file run.bat that can be used to execute the code.

2 comments:

  1. Why don't you enable TRACE logs for 'org.hibernate.type'? You could see all values bound to those question marks in your logs.

    see http://grepcode.com/file/repository.jboss.org/maven2/org.hibernate/hibernate-core/3.3.0.SP1/org/hibernate/type/NullableType.java#NullableType.nullSafeGet%28java.sql.ResultSet%2Cjava.lang.String%29

    ReplyDelete
    Replies
    1. Hi,
      Yes the logs do show the parameters, however it is not shown as a part of the query but as separate log statements. Also in a multi-user applications, there is a good chance of logs for different execution threads getting mixed.
      The above UI is much more easier to work with and reduces the effort of replacing the question marks with the log values

      Delete