Query Extensions


The EJB 2.0 specification introduces the concept of an EJB Query Language (EJB QL). This provides a standardized mechanism for implementing finders. Finder implementations are now portable across database backends. These implementations are done using the terminology of beans and fields in beans rather than using JDBC statements, which require knowledge of database tables and columns.

This basic EJB QL capability sets the stage for a number of query extensions. These further enhance the power of EJB QL to drive queries to backend systems. Dynamic query changes the point at which queries are executed. This is described first. WebSphere Application Server also introduces a set of extensions to the EJB QL to make it more powerful. This is the second part of the Query Extensions section of this chapter. Finally, a quick look under the covers of WebSphere's query capabilities completes the section.

Dynamic Query

The query function defined by J2EE is a static query capability, that is, queries are defined in the deployment descriptor and each query is associated with either select or finder methods. The EJB QL syntax is used to define queries for entity beans with CMP. The specification means that the bean providers describe queries in terms of container-managed fields instead of database columns. This also implies that the specifications of these finders are portable across different database vendors. With all this capability, what could be lacking?

Statically defined queries mean that all queries need to be defined at development time. Substitution variables are filled into the right-hand side of the operators, using notation such as the ?1 below:

 SELECT OBJECT(o) FROM OrderBean o WHERE o.total > ?1 

If we want a query where beans are returned, where the total is greater than some specified minimum and less than some specified maximum, then the following finder would be needed:

 SELECT OBJECT(o) FROM OrderBean o WHERE o.total > ?1 AND o.total < ?2 

Each container-managed field in a CMP 2.0 entity bean might participate in queries with various operators being applied in arbitrary combinations. In fact, some highly flexible user interfaces will allow arbitrary queries to be entered against a collection of entity beans. Statically defining all of these queries and placing them in a deployment descriptor at development time could become a very tedious task.

Add CMRs into the picture and the ability to do joins across various fields and the ability to apply predicates in various combinations in this context and it becomes clearer that statically defined queries do have some practical limits.

The Dynamic Query Service (DQS), of WebSphere Application Server Enterprise, introduces an interface that accepts query strings at run time. Using the same parser that transforms EJB QL to the underlying database syntax at development time, the dynamic query service makes those transformations happen at run time. While this is very flexible, there is obviously a runtime overhead incurred. Therefore, a combination of static queries and dynamic queries is the recommended approach.

There are two primary interfaces to DQS located in the com.ibm.ObjectQuery package. One is a remote interface and the other is a local interface.

The remote interface has a method to accept the query string that looks like this:

public com.ibm.ObjectQuery.QueryIterator executeQuery(       java.lang.String queryStatement,       java.lang.Object[] parameterVars,       java.util.Properties queryDomain,       int skipRows,       int maxRows)     throws java.rmi.RemoteException, com.ibm.ObjectQuery.QueryException;

The local interface for dynamic query looks like this:

public com.ibm.ObjectQuery.QueryLocalIterator executeQuery(       java.lang.String queryStatement,       java.lang.Object[] parameterVars,       java.util.Properties queryDomain)     throws com.ibm.ObjectQuery.QueryException;

The five input parameters of the executeQuery() method are as follows:

  • queryStatement
    A string that contains a query statement:

    SELECT OBJECT(e) FROM EmpBean e WHERE e.salary < 80000
  • parameterVars
    An array of objects used as a value holder for literal values for input parameters, which are numbered starting from 1. An example of a query containing a parameter is:

    SELECT OBJECT(e) FROM EmpBean e WHERE e.salary = ?1
  • queryDomain
    A set of pairs of Abstract Schema Names (ASN) and EJBHomes or EJBLocalHomes. This second parameter is used only if the ASN name specified in the query statement is not unique across applications. Otherwise, this parameter should be null. An abstract schema name is a standard J2EE artifact for representing types of entity beans in EJB QL queries. It is captured in the standard deployment descriptor.

  • skipRows and maxRows
    These are used to request a subset of results from the complete result collection and control the size of the result set. If skipRows=20, then the result collection will not include the first 20 tuples. maxRows places a limit on the size of the final result collection.

Iterators contain the results of the query. An iterator is an interface over a collection of EJBs, either remote or local, depending on the interface used. Iterators returned from the remote query interface use an eager policy, which means that the iterator is fully populated from the results returned by the database when returned to the caller. In database terms, this means that the cursor is drained and closed. Iterators returned from the local query interface support lazy evaluation if query optimization allows it. Lazy, in this context, means that the entire result set is not retrieved from the backend system upon query execution, but rather is returned incrementally as the iterator is used.

It is the generally the case that when working with entities, the local query interface will be used. This interface returns local EJBs and is the most efficient. Remote EJBs are returned from the remote interface. This remote interface can be used both locally and remotely. Using it locally is sensible in some cases, most notably those when a reference to the EJB will be exchanged with applications or components that may be running in other servers.

Dynamic Query Example

Let's begin by asserting that we have two beans, EmpBean and DeptBean. EmpBean has attributes named empid, name, salary, bonus, hireDate, hireTime, hireTimestamp, isManager, and dept, where dept is a one-to-one CMR with DeptBean. DeptBean has attributes of deptno, name, budget, emps, and mgr, where emps is a one-to-many CMR to employees, and mgr is a one-to-one CMR back to a specific employee.

This means that each department has many employees and one manager, and that each employee has exactly one department. This is shown below:

click to expand

The basic examples are not anything different from what could be placed into static finders. The only thing different is when they execute. They execute at run time using one of the interfaces described in the previous section.

Select method to retrieve all department numbers:

 SELECT d.deptno    FROM DeptBean d 

Select the IDs of employees with employee IDs less than 10:

 SELECT e.empid    FROM EmpBean e    WHERE e.empid < 10 

Note that the two queries above only return data and not objects, as is allowed by the EJB QL syntax.

The query interfaces are set up to handle the results of the query being either data or objects. The previously described iterator classes actually return a tuple object of class IQueryTuple. IQueryTuple has a single method on it called getObject(), which takes an integer and returns the object that is in that position in the tuple. The number of values in a tuple, and the labels for them can be found from the iterator, using methods getFieldCount() and getFieldName(int i) on the iterator interface.

Printing out the results of the query above should look like this in your code:

 String query = "SELECT e.empid FROM EmpBean e WHERE e.empid < 10"; QueryLocalIterator it = qb.executeQuery(query, parms, null); while (it.hasNext() ) {    IQueryTuple t  = (IQueryTuple) it.next();   System.out.print( it.getFieldName(1) +"="+ t.getObject(1) ); } 

Select all employee objects with employee IDs less than 10. The only difference between this query and the previous query is that this query returns EJB objects instead of just the data:

 SELECT e    FROM EmpBean e    WHERE e.empid < 10 

Select the employee objects that are in department 1. The emps relationship is navigated in this example:

 SELECT e    FROM DeptBean d,    IN (d.emps) e    WHERE d.deptno = 1 

Select employee objects from department 1 that are managers. This navigates both the emps relationship and the mgr relationship to determine the result set:

 SELECT e    FROM EmpBean e, DeptBean d     WHERE e.dept.mgr Member OF d.emps AND d.deptno = 1 

Each of the above queries represents something that can be done in static queries today. The examples here are just a reminder of what static EJB QL can do.

Basic Dynamic Query Summarized

The use of this dynamic run-time query service can make client application development easier and in some cases, potentially even increase overall run-time performances. While a dynamic query, with the same predicate as a static or pre-deployed query, will always be slower, the opportunity for the performance gain comes in the surrounding business logic that might accompany static queries.

Without DQS, applications tend to use static finders that may return more objects than needed by the application. Applications tend to define a findAll() finder method, and if there is not a suitable finder, the application will call the findAll() method. The application then filters out the objects not needed. While functionally this will work, it requires more application code and is expensive, because unneeded data is retrieved from the datastore and unwanted objects are activated. The DQS allows an application to specify more precisely the objects that are to be returned without the needed to define a large number of predefined finder and select methods.

Dynamic query bridges the gap between the finder methods supplied by the bean developer, and the actual needs of a client application. This is especially true for client applications that require unusual search criteria.

Dynamic query reduces the number of finder methods that the bean developer must provide, especially those that are infrequently used. It allows users to develop and test the predefined queries in the finder and select methods by having a test client that takes a predicate and executes it against a bean.

EJB components that are not equipped with a full set of finders can still be successfully reused in the presence of dynamic query. No additional negotiations with the component provider are necessary.

Now that the concept of running standard EJB QL statements dynamically has been described, we can move on to more of the features provided in the context of query that go beyond the basic specification requirements.

Additional Query Extensions

The base query implementation in the WebSphere Application Server and the Dynamic Query Service just described make a powerful combination. This section describes some additional features that complement these already powerful concepts. The first set of features are usable from static queries as well as dynamic queries, while the second set become possible only in the presence of a dynamic query.

Extensions for Static Queries

There are a set of extensions to the EJB 2.0 query service that are applicable to static queries as well as dynamic queries. These are explained in this section.

Delimited Identifiers

This is used to solve keyword problems. It is legal in the WebSphere Query Service to have a statement such as:

 SELECT x."from" FROM myBean x 

The word from is of course a keyword and would not be accepted if not delimited by the quotes. The standard does not require supporting the use of keywords in this manner.

String Comparisons

The EJB 2.0 specification for EJB QL requires that the "=" and "<>" operators be supported for string comparisons. The WebSphere Query Service supports ">" and "<" as well. An example of this is:

 SELECT x FROM PersonBean x WHERE x.name > 'Dave'  

Scalar Functions

A variety of scalar functions are supported by the WebSphere Query Service. Some of these are ABS, SQRT, CONCAT, LENGTH, LOCATE, and SUBSTRING. Other scalar functions are type casting (integer, decimal, char, double, float, smallint, and so on) or deal with date-time expressions (date, days, hour, seconds, year). Casting and date-time scalars only work with a DB2 backend.

ORDER BY

The ORDER BY clause is supported by the WebSphere Query Service. This is a very useful feature and should be available in the EJB 2.1 specification. For example, to find a list of employees who earn less than $30,000 and have the results returned in descending order, the following would be specified as the query:

 SELECT object(e) FROM EmpBean e    WHERE e.salary < 30000   ORDER BY e.salary desc 

SQL Date/time Expressions

These are supported by WebSphere Query Service and not required by the specification. For example, to find employees who resigned after less than two years on the job, the query would look like this:

 SELECT object(e) FROM EmpBean e   WHERE years( e.termDate - e.hireDate) < 2 

This example requires that java.sql.* types (not java.util.Date) be mapped to date, time, or timestamp columns. The datastore must be DB2 in this case.

Inheritance

VisualAge for Java introduced an inheritance pattern for EJBs. It is a single inheritance model, as that is what Java allows. The depth of the hierarchy is not bounded. This pattern, now also supported by WebSphere Studio is supported by the WebSphere Query Service as well. There is a way to extract objects that are members of a base class and all subclasses, as well as to distinctly extract objects of a particular subclass.

Subqueries, Aggregation, Group By, and Having Clauses

These are all supported by WebSphere and not required by the specification. An example of a subquery that would return objects representing the employees that got the maximum salary could be as follows:

 SELECT object(e) FROM EmpBean e   WHERE e.salary = (SELECT max(m.salary) FROM EmpBean m) 

Notice the inner SELECT statement within the outer SELECT statement.

EXISTS Predicate

The EXISTS predicate tests for the presence or absence of a condition specified by a subselect. The result of EXISTS is true if the subselect returns at least one value, or the path expression evaluates to a non-empty collection. Otherwise, the result is false. NOT can be used to negate an EXISTS. The following example shows how to retrieve all departments that do not have any employees:

 SELECT OBJECT(d) FROM DeptBean d    WHERE NOT EXISTS  ( SELECT 1 FROM IN (d.emps) e) 

The "1" is a convention because the content returned by the subselect does not matter; it is the true or false that counts.

Value Object Access

In the snippet below, home is a dependent value object, which is mapped to individual fields in the underlying database:

 SELECT object(e) FROM EmpBean e   WHERE  e.home.city = 'Gilroy' AND  e.home.state = 'CA' 

You must use a composer to map home address to columns. This will not work using a default top-down mapping, which uses object serialization, which would serialize dependent objects into a BLOB or other type that would make accessing individual fields impracticable.

Additional Extensions

In some cases, there are extensions that cannot be supported via statically defined queries. This is often because the extensions do not fit into the current description of finders that must go into the deployment descriptor. Rather than introducing specific deployment descriptor extensions for statically defining the following extensions, they are only supported for dynamic queries.

Bean Methods

It is possible for a dynamic query to contain not only attribute or container-managed field getters in the operators, but also any other method that has a return value. It is in this case that the evaluation of these methods needs to be done in object space. Object space query means that part, or all, of the query is done by the application server by executing methods against objects. This requires that the objects, to which the method is applied, must be activated. The WebSphere Query Service does support the idea of partial pushdown. This means that a portion of a query can be pushed down into the database and then the result set from that portion of the query is further evaluated in object space. This partial pushdown idea lets the smallest possible set of objects be subjected to activation and object space evaluation. This suggests an economical use of resources while retaining tremendous flexibility in specifying queries.

Partial pushdown does ensure that the query is executed in the most efficient way possible. However, whenever a large set of objects must be activated in the EJB container, the performance cost will be much higher than the cost of just pushing down a query and letting the database do the work. Cases where methods are used in the predicates must be carefully selected.

The real value here is that the methods that participate in a query can encapsulate reasonably complex business logic and that logic can easily participate in the query evaluation. Those participating methods are regular entity bean methods with access to the full J2EE programming model. Complex date calculations are an example of something that might be encapsulated in a method that would then be used as part of a query predicate.

Dependent Value Methods

It is also possible to have arbitrary non-getter and non-setter methods on dependent value classes as part of the query. This means that one can reach into a dependent value and describe a query that includes arbitrary methods within that dependent value. This makes dependent values more powerful and allows them to be used in more situations. This is good if the simplicity and efficiency of dependent values are otherwise appropriate for the application being constructed.

Multiple Element SELECT Clauses

This is a very powerful concept. The EJB 2.0 specification introduces the idea that one can have an EJB QL statement in a SELECT method that returns not an object, but data. For example, a SELECT method could specify:

 SELECT e.lastname    FROM EmpBean e   WHERE e.age > ?1 

This eliminates the cost of activating the object and is appropriate if all that is needed by the application is some data.

However, it is often the case that more than just as single data element is needed to meet the contract of the presentation logic. The dynamic query capability of WebSphere allows multiple elements to be returned by a select statement. For example:

 SELECT e.lastname, e.firstname, e.customerNumber    FROM EmpBean e    WHERE e.age > ?1 

The results can then be extracted by leveraging the IQueryTuple object as demonstrated by the previous example. The only difference is that more values are in the tuple as shown here:

 String query = "SELECT e.lastname, e.firstname, e.customerNUmber FROM EmpBean e WHERE e.age > ?1"; QueryLocalIterator it = qb.executeQuery(query, parms, null); while (it.hasNext() ) {    IQueryTuple t  = (IQueryTuple) it.next();   System.out.println( it.getFieldName(1) + "=" + t.getObject(1) );   System.out.println( it.getFieldName(2) + "=" + t.getObject(2) );   System.out.println( it.getFieldName(3) + "=" + t.getObject(3) ); } 

This example returns enough data for the application to return what is needed by the presentation logic. It is then common for a selection to be made on the presentation logic that will later be the only object that needs to be activated and transacted on.

Ability to Return Aggregation Values

Static finder and select methods allow objects or CMP values to be returned. Dynamic query allows arbitrary expressions including aggregate values to be returned. To compute MAX and MIN salary for a department, the following dynamic EJB query can be used:

 SELECT MAX (e.salary), MIN(e.salary) FROM DeptBean d,    IN (d.emps) e    WHERE d.deptno=12 

Additional Examples

Now that all of the query capabilities of WebSphere Application Server Enterprise have been described, a couple of more complex examples can be provided that demonstrate the power of dynamic query. These examples will use the entities department and employee that have been used in some of the previous snippets.

In this example, we show a result set that is a combination of objects (e in this case) and data (e.empid) being returned. This allowed combination was not shown previously:

 SELECT e, e.empid    FROM EmpBean e    WHERE e.empid < 3 

In this example, the dynamic query uses multiple arbitrary expressions in the SELECT clause:

 SELECT e.name, e.salary+e.bonus as total_pay, object(e), e.dept.mgr    FROM EmpBean e   ORDER BY 2 

This next dynamic query returns the number of employees in each department:

 SELECT e.dept.deptno as department_number, count(*) as employee_count   FROM EmpBean e   GROUP BY e.dept.deptno   ORDER BY 1 

Finally, the example below uses a dynamic query using a method, in this case format(), on a value object which is one of the CMP fields of the EmpBean. This means that, if we looked at the Java class that implements address, we would find a format() method. Here, it is being used directly in the SELECT clause:

 SELECT e.name, e.address.format()   FROM EmpBean e   WHERE e.dept.deptno=12 

Summarizing Query Extensions

The following table provides a summary of the query topics we have covered in this chapter:

Capability

EJB 2.0 Specification

WebSphere Application Server (base function)

WebSphere Application Server Enterprise

Dynamic Query (local and remote)

no

no

yes

Delimited Identifiers

no

yes

yes

String Comparisons

no

yes

yes

Scalar functions

ABS, SQRT, CONCAT, LENGTH, LOCATE, SUBSTRING

ABS, SQRT, CONCAT, LENGTH, LOCATE, SUBSTRING and additional type conversion, string manipulation and date-time manipulation functions

ABS, SQRT, CONCAT, LENGTH, LOCATE, SUBSTRING and additional type conversion, string manipulation and date-time manipulation functions

Order by

no

yes

yes

SQL date/time expression

no

yes

yes

Inheritance

no

yes

yes

Subqueries, aggregation, group by, and having clauses

no

yes

yes

EXISTS predicate

no

yes

yes

Dependent Value object attribute access

no

yes

yes

Bean method

no

no

yes

Dependent value methods

no

no

yes

Multiple Element Select clauses

no

no

yes

Returning aggregration values

no

no

yes




Professional IBM WebSphere 5. 0 Applicationa Server
Professional IBM WebSphere 5. 0 Applicationa Server
ISBN: N/A
EAN: N/A
Year: 2001
Pages: 135

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net