EJB-QL extensions for the Borland CMP engine

EJB-QL

EJB-QL allows you to specify queries in an object oriented query language, EJB-QL. The Borland CMP engine translates these queries into SQL queries. The Borland Enterprise Server provides some extensions to the EJB-QL functionality described in the Sun Microsystems EJB 2.0 Specification.

Support for ORDER BY

The EJB 2.0 Specification supports three SQL clauses in EJB-QL: SELECT, FROM, and WHERE.

The Borland CMP engine also supports the SQL clause ORDER BY in the same EJB-QL statement, provided it is placed after the WHERE clause. This is done in the standard ejb-jar.xml deployment descriptor in the <ejb-ql> entity. For example:

<query>

	<description></description>

	<query-method>

		<method-name>findCustomerByNumber</method-name>

		<method-params />

	<ejb-ql>SELECT Distinct Object(c) from CustomerBean c WHERE c.no > 1000 ORDER BY c.LNAME</eql-ql>

	</query-method>

<query>

Overriding SQL generated from EJB-QL by the CMP engine

Important: This feature is for advanced users only!

The Borland CMP engine generates SQL calls to your database based on the EJB-QL you enter in your deployment descriptors. Depending on your database implementation, the generated SQL may be less than optimal. You can capture the generated SQL using tools supplied by your backing-store implementation or another development tool. If the generated SQL is not optimal, you can replace it with your own. However, we offer no validation on the user SQL.

Note: A problem with your SQL may generate an exception which can potentially crash the system.

You specify your own optimized SQL in the Borland proprietary deployment descriptor, ejb-borland.xml. The XML grammar is identical to that found in ejb-jar.xml, except that the <ejb-ql> element is replaced with a <user-sql> element. This proprietary element contains a SQL-92 statement (not an EJB-QL statement) that is used to access the database instead of the CMP engine-generated SQL.

Important: The SELECT clause for this statement must be identical to the SELECT clause generated by the Borland CMP engine.

Subsequent clauses are user-optimized. The ordering of the fields in the SELECT clause is proprietary to the CMP engine and therefore must be preserved.

For example:

<entity>

	<ejb-name>EmployeeBean</ejb-name>

	...

	<query>

		<query-method>

			<method-name>findWealthyEmployees</method-name>

			<method-params />

		</query-method>

		<user-sql>SELECT E.DEPT_NO, E.EMP_NO, E.FIRST_NAME, E.FULL_NAME, 
          E.HIRE_DATE, E.JOB_CODE, E.JOB_COUNTRY, 
          E.JOB_GRADE, E.LAST_NAME, E.PHONE_EXT, E.SALARY 
   		  FROM EMPLOYEE E WHERE E.SALARY > 200000
		</user-sql>

	</query>

	...

</entity>

Note: The extensive SELECT statement reflects the type of SQL generated by the CMP engine.

When the CMP engine encounters an EJB-QL statement in the ejb-jar.xml deployment descriptor, it checks ejb-borland.xml to see if there is any user SQL provided in the same bean's descriptor.

If none is present, the CMP engine generates its own SQL and executes it.

If the ejb-borland.xml descriptor does contain a query element, it uses the SQL within the <user-sql> tags instead.

Important: The <query> element in ejb-borland.xml does not replace the <query> element in the standard ejb-jar.xml deployment descriptor. If you want to override the CMP engine's SQL, you must provide the elements in both descriptors.