WebLogic QL (WLQL)

WebLogic QL or WLQL is a language similar to EJB QL in that it builds upon the EJB QL language and adds certain extra features. WLQL is proprietary to the WebLogic Server.

WLQL was initially created with the WebLogic Server supporting EJB 1.1 specifications. Because the newly introduced EJB QL does not provide all the ease and operations of a full-fledged SQL query language, WLQL enhances the already existing EJB QL language.

Because WLQL is specific to WebLogic Server, the queries for WLQL are specified in the weblogic-cmp-rdbms-jar.xml file. Keep in mind that for every query element in the weblogic-cmp-rdbms-jar.xml, a corresponding query element must exist in the ejb-jar.xml file. However, the weblogic-cmp-rdbms-jar.xml file settings take precedence.

Now you will look at the enhancements provided in WLQL.

DISTINCT

If you have a workable knowledge of standard SQL language, you will be familiar with the DISTINCT clause. The DISTINCT clause operates on a SELECT clause and results in the retrieval of only distinct rows of data.

EJB QL also provides a DISTINCT functionality. The use of this DISTINCT function ensures that the container will not consider the duplicate rows obtained via the select query while it is sorting.

If you were to implement this DISTINCT functionality using WLQL in the restaurant example, the following code would be the structure of the weblogic-cmp-rdbms-jar.xml file:

 <weblogic-cmp-rdbms-jar>      <weblogic-rdbms-bean>         ...         <weblogic-query>             <query-method>                 <method-name>findIngredientsByItemName</method-name>                     <method-params>                         <method-param>java.lang.String</method-param>                     </method-params>             </query-method>             <weblogic-ql>                 ...weblogic ql specific query goes here...             </weblogic-ql>             <sql-select-distinct>TRUE</sql-select-distinct>         </weblogic-query>     <weblogic-rdbms-bean> <weblogic-cmp-rdbms-jar> 

You will find this listing familiar, because a similar listing exists in the ejb-jar.xml file. However, the query specified in the <weblogic-ql> tag in the weblogic-cmp-rdbms-jar.xml file should have a WLQL specific construct, or else it should be placed in the ejb-jar.xml file.

The ORDERBY Clause

Use the ORDERBY clause to arrange the CMP fields retrieved from a query in a finder method into a particular order. Developers familiar with normal SQL language will grasp the importance of this clause because it is absent from the EJB QL language.

In the restaurant example, if you had a column itemPrice in your Item bean, and if you had a method called findItemPrices(), and you needed to retrieve the fields on the basis of the order of the prices, you would write a query like the following:

 <weblogic-ql>      SELECT Object(a) FROM Item AS a         ORDER BY a.itemPrice </weblogic-ql> 

The container delegates the sorting (that is, ordering) functionality to the DBMS where the query is executed. The order in which the results are retrieved depends upon the way the DBMS handles the sorting.

The ORDERBY clause can also be coupled with the ASC or DESC qualifiers. ASC arranges the retrieved values in ascending order, and DESC arranges them in descending order. The qualifier is placed immediately after the element that is ordered. You can have multiple elements in the ORDERBY clause. The retrieved data will be sorted based on the sequence of the elements specified in the ORDERBY clause. Take a look at a code snippet to understand this better:

 <weblogic-ql>  ORDER BY     SELECT Object(a) FROM Item AS a         ORDER BY a.itemPrice ASC </weblogic-ql> 

Subqueries

Another interesting enhancement provided by WLQL is the ability to use subqueries within the queries used in WLQL. Subquery is an important feature in an SQL language that enables you to write nested queries to combine data and process it without requiring that the data be reprocessed outside of the query.

The different ways that subqueries can be used are

• As operands

• In correlated subqueries

• Combined with the DISTINCT clause

• As uncorrelated subqueries

• With multiple return types such as single CMP fields, aggregate functions, and beans with simple primary keys.

An example of using subqueries would be to find items that are priced greater than the minimum price of all the items using the findByItemPrice() method. You would then have a query like the following:

 SELECT Object(a) FROM Item as a WHERE a.itemPrice > (SELECT MIN(Object(b)) FROM         Item b)  

Dynamic Queries

Dynamic queries enable you to write EJB QL queries programmatically instead of declaratively, that is, listing the EJB QL query in the weblogic-ejb-jar.xml deployment descriptor file.

The dynamic query feature can be activated using the following tag in the weblogic-ejb-jar.xml file:

 <enable-dynamic-queries>True</enable-dynamic-queries>  

A code snippet follows:

 //lookup the initial context and obtain the QueryHome object reference  ... // obtain the reference to the Query object from the QueryHome object Query itemQry = myQh.createQuery(); String myEJBQry = "SELECT Object(a) FROM Item AS a ORDER BY a.itemPrice ASC" // execute the query itemQry.find(myEJBQry); 

Queries That Return a ResultSet

Finally, WLQL also allows the returning of a ResultSet object whose columns can be listed by the developer in the SELECT clause. A code snippet for this follows:

 SELECT item.itemPrice, item.itemDesc FROM Item AS a ORDER BY a.itemPRice ASC  

The preceding code snippet shows that a java.sql.ResultSet object with the columns itemPrice and itemDesc will be returned as a result of executing the query.