1 Reply Latest reply on Apr 19, 2012 1:09 PM by Avor Nadal

    JBoss AS 7, Hibernate, named queries, dynamic queries and prepared statements.

    Avor Nadal Newbie

      Hello everybody:


      I'm very confused about a topic related to JBoss AS 7 and Hibernate. Just until yesterday, I thought that the named JPQL queries were converted into prepared statements, whereas the dynamic JPQL queries (created on the fly) never were transformed into them. However, checking my database log (PostgreSQL), I could see that every query from JBoss AS 7 was executed as a prepared statement, regardless of its origin. This matter turned very interesting to me.


      My question is if there is some kind of intermediate pool or cache of prepared statements for all the resulting SQL queries or if, on the contrary, these prepared statements are compiled every time and thus, they're not re-used ever. If it exists, How does it work? Is it enable by default? Does it store the complete resulting SQL query as the key to find the object which represents the corresponding prepared statement? Because the logs from PostgreSQL show that no name is used to execute these prepared statements. And this only can mean, if I'm not mistaken, either that they're created every time or that they're internally referenced in JBoss AS 7 by the complete query text.


      If there were a prepared statement pool it would be fantastic, because I need to use a considerable amount of dynamic JPQLqueries in my projects, and was worried that they needed to be parsed and compiled "twice": first as the JPQL query by the JPA provider (which is unavoidable, obviously), and later, as the pure SQL query by the database engine.


      Thank you a lot.


      PS: I'm aware that the prepared statements are not always optimal compared to "plain" queries, because they lack the real parameters in the WHERE clause. But my queries use very simple ones and I believe that I can benefit from that.

        • 1. Re: JBoss AS 7, Hibernate, named queries, dynamic queries and prepared statements.
          Avor Nadal Newbie

          I've found the solution to my question. So I'm answering myself, just in case that this may help other people.


          As I said before, my assumption that JPQL dynamic queries couldn't be converted to SQL prepared statements was wrong. Both named/static queries and dynamic queries are susceptible of being converted. Indeed, in the case of Hibernate (or at least the implementation included in JBoss AS 7), it creates prepared statements for every type of query which I've tested.


          My mistake was to believe that, because Hibernate handles the transformation of the JPQL queries to prepared SQL statements, it also got some control over the treatment of these statements in lower levels of the process. However, it's the Java Connector Architecture (JCA) plus the corresponding JDBC driver the responsible ones of handling the connections and, if it's supported, also the pool of prepared statements. Checking the documentation avilable, I've found that JBoss AS 7 relies on IronJacamar as its JCA .


          After reading the documentation and doing lots of tests, I've configured the data sources appropriately. In the Data Source subsystem there is an option called <prepared-statement-cache-size> which by default isn't set, so it adopts the value 0. This is the reason why the prepared statements never were re-used.


          I've changed this to 100 and the statistics of the JBoss AS 7 control panel have started to move . However, the logs of PostgreSQL showed me that the prepared statements were not being re-used really! What? But then I've remembered that, when I checked the documentation of the PostgreSQL JDBC driver yesterday, I read that you could set a threshold to set from how many calls you wanted to start to re-use the existing prepared statements. And in this case the minimum quantity seems to be 5 by default (I don't know if it's configurable). Once you reach the 5th call, you can check the logs and see how the statement is executed without being re-parsed and re-planned every time .


          PS: I'm sorry if some data here is unnacurate or even wrong, but I've not much idea about all these matters. I just wanted to share my solution.