EJBQL to SQL92 compiler

Since 3.2.4

Till 3.2.4 JBossCMP had only one QL compiler which didn't support SQL92 standard. And since EJBQL implies such SQL notions like OUTER and INNER joins, the generated SQL was in some cases not efficient or even wrong. A new compiler that generates SQL92 compliant queries from EJBQL and JBossQL queries was introduced in JBoss-3.2.4.

However, the old compiler is still the default one because our basic CMP tests at the moment rely on this compiler and the database which is used (HSQLDB) for them still has problems with SQL92 queries. It is expected that SQL92 compiler will be the default one in the future. The SQL92 compiler is EJBQL2.1 compatible.

The default compiler is specified in standardjbosscmp-jdbc.xml

<defaults>
...
   <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.JDBCEJBQLCompiler</ql-compiler>
</defaults>

To use SQL92 compiler as the default compiler change the line above to

<defaults>
...
  <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler>
</defaults>

The compiler can also be specified per element in jbosscmp-jdbc.xml like

<query>
   <query-method>
      <method-name>ejbSelectSelect</method-name>
      <method-params>
         <method-param>java.lang.String</method-param>
         <method-param>java.lang.Object[]</method-param>
      </method-params>
   </query-method>
   <dynamic-ql></dynamic-ql>
   <ql-compiler>org.jboss.ejb.plugins.cmp.jdbc.EJBQLToSQL92Compiler</ql-compiler>
</query>

Issues

EJBQLToSQL92Compiler always selects all the fields of the entity regardless the read-ahead strategy in use. E.g. if the query is configured with on-load read-ahead strategy,  the first query will include all the fields, not just primary key fields but only the primary key fields will be read from the ResultSet. Then, on load, other fields will be actually loaded into the read-ahead cache. On-find read-ahead with the default load group '' works as expected.

An example of using EJBQLToSQL92Compiler for finders with on-load strategy.

jbosscmp-jdbc.xml query declaration:

<query>
   <query-method>
      <method-name>findAll</method-name>
   </query-method>
   <jboss-ql><![CDATA[select object(o) from A o\]\]\></jboss-ql>
   <read-ahead>
      <strategy>on-load</strategy>
      <page-size>2</page-size>
      <eager-load-group>*</eager-load-group>
   </read-ahead>
</query>

Code:

for(Iterator ai = AUtil.getLocalHome().findAll().iterator(); ai.hasNext();)
{
   ALocal a = (ALocal) ai.next();
   log.info("a.name: " + a.getName());
}

server.log:

14:03:37,770 DEBUG [A#findAll] Executing SQL: SELECT t0_o.id, t0_o.name, t0_o.name2, t0_o.bin FROM A t0_o
14:03:37,770 DEBUG [A] Executing SQL: SELECT id, name, name2, bin FROM A WHERE (id=?) OR (id=?)
14:03:37,770 INFO  [FacadeSessionBean] a.name: Avoka
14:03:37,770 INFO  [FacadeSessionBean] a.name: Ataka
14:03:37,770 DEBUG [A] Executing SQL: SELECT id, name, name2, bin FROM A WHERE (id=?) OR (id=?)
14:03:37,770 INFO  [FacadeSessionBean] a.name: Irene
14:03:37,770 INFO  [FacadeSessionBean] a.name: Gregory
14:03:37,770 DEBUG [A] Executing SQL: SELECT name, name2, bin FROM A WHERE (id=?)
14:03:37,770 INFO  [FacadeSessionBean] a.name: Vladislavische
14:03:37,770 DEBUG [FacadeSessionBean] RUN> done in 0 ms.