Using Hibernate 3.6...

Building a query through the Criteria API. Mixing traditional SQL with XML in DB2 9.7 (same stuff works for Oralce, Postgresql) where the column housing the XML as an SQL type of XML. Compilation internal to DB2 for an XPATH XMLEXISTS clause takes over 50% of the total SQL execution time. If there is a high amount of XPATH statements that are the same differing only in comparison values the compilation becomes a problem. A XMLEXISTS clause gets recompiled if the values aren't treated as variables as int explicit casting.

So I have adjusted a Criterion class (i.e. "implements Criterion") for XPath to pull out all the comparison values and replace them with explicit casting. Added the values as Hibernate string types. But the resulting query is rejected with a SQLSTATE 2200M ("*SQLSTATE 2200M: A value failed to parse as a well-formed XML document*").

Without explicit casting the resulting SQL (i.e. as see by the IBM DB2 JDBC driver) is:


where CONFIGURATION is the XML column and the "?" placeholder is the entire XPATH statement.

With explicit casting in the Hibernate CriteriaQueryTranslator.getQueryParameters method the processed SQL is:


And the positional type parameters are in the right order (obs. the entire statement isn't shown above but the parameters are of string and delimited correctly).

Is it that parameters "inside" a functional clause (XMLEXISTS) can't be set by Hibernate? My Criterion class is here.

One thing to note is that when not doing explicit casting the SQL according to Hibernate looks like:


which is different than what is picked up by my monitor (Introscope) over what SQL is sent to DB2 being as above:


An explicit without trying to replace variables looks like:


So not 100% that "actual" SQL handed over to DB2 will be as fine grained as I would like namely: