Defining a SELECT Clause

Defining a SELECT Clause

A SELECT clause is always required when you define an EJB QL query. It is the SELECT clause that determines the type of values returned by a query, so it must be consistent with the declaration of the select or finder method that it's supporting. For a finder method, this means that the SELECT clause must always return the abstract schema type of the entity bean for which the method is defined. The container takes care of returning local or remote interface references as necessary. You've seen examples of SELECT clauses such as the following throughout this chapter:

 SELECT OBJECT(auction) FROM EnglishAuction auction 

The SELECT clause in this example references a range variable associated with the EnglishAuction abstract schema type. This approach allows you to start with all objects of a given abstract schema type and filter them using a conditional expression in a WHERE clause.

When you use a standalone identification variable such as auction as the return value in a SELECT clause, you have to apply the OBJECT operator to it.

In addition to range variables , you can declare a SELECT clause for a finder method using a single-valued path expression. The result of this expression must be a CMR field that evaluates to the abstract schema type required by the finder. For example, you could declare a findAllWinningBids method for AuctionBid using

 SELECT auction.winningBid FROM EnglishAuction auction 

This query returns a collection of all the winning bids assigned to auctions in the system. Because a path expression is used in this SELECT clause, you don't use the OBJECT operator with it. The syntax changes when you encounter a collection-valued path expression. As an example, you could retrieve all bids for a particular bidder using

 SELECT OBJECT(bids) FROM Bidder b, IN(b.myBids) bids    WHERE b.usernameField = 'jsmith' 

Notice in this example that the identification variable bids is used in the SELECT clause instead of b.myBids . The SELECT clause allows only single-valued path expressions, and b.myBids is a collection value. You must use IN to obtain a corresponding identification variable to return in this situation.

The return value of a select method isn't as constrained as that for a finder. A select method can return entities of any abstract schema type or the value of a CMP field (including those declared using Java primitive types). If a select method returns entity references, you specify whether local or remote interfaces are returned using the result- type-mapping element in the deployment descriptor as shown in the following:

 <query>    <query-method>      <method-name>ejbSelectAuctionedItems</method-name>      <method-params/>    </query-method>    <result-type-mapping>Local</result-type-mapping>    <ejb-ql>      <![CDATA[ SELECT OBJECT(i) FROM EnglishAuction AS a, IN(a.item) i]]>    </ejb-ql>  </query> 

If you omit the result-type-mapping , Local is assumed. You also could express the query shown in the deployment descriptor using SELECT a.item instead of declaring the second identification variable. As another example of a select method, you could obtain all the auction names using

 SELECT auction.nameField FROM EnglishAuction auction 

Because nameField is a String -valued CMP field, this select method query would return either a Collection or a Set of String objects. The choice of Collection or Set is made based on the declaration of the select method in the bean implementation class. When you implement a query for a method that is declared to return a Set , the container takes care of removing any duplicate values in the query results for you. If the method returns a Collection , you can use the DISTINCT qualifier in the SELECT clause to prevent duplicates. This can be used both with range variables and path expressions. The following query would return a list of home states for all auction winners:

 SELECT DISTINCT b.stateField FROM EnglishAuction auction,    IN(auction.winningBid.bidder) b 

Self Joins

If you're familiar with SQL statements that compare rows of the same table to each other, you know that you have to reference the table twice in the FROM clause of the statement. The same is true for EJB QL. For example, you could use the following query to be sure that no two auctions are offering the same item:

 SELECT OBJECT(a1) FROM EnglishAuction a1, EnglishAuction a2    WHERE (a1.item IS NOT NULL) AND    (a1.id <> a2.id) AND    (a1.item.id = a2.item.id) 

Notice that the only test for a null value is the one used to make sure that only auctions with an item assigned are considered . In the other path expressions, a null is returned if a null is encountered anywhere in the navigation, so you don't have to worry about any intermediate tests yourself.