EJB 3.

0 QL Tutorial
www.sycorax.co.in

EJB 3.0 QL TUTORIAL

Queries and EJB QL

EJB QL and native SQL queries are executed through the javax.persistence.Query
interface. The Query interface is very analogous to the java.sql.PreparedStatement
interface. This Query API gives you methods for paging your result set, as well as passing Java
parameters to your query. Queries can be predeclared through annotations or XML, or created
dynamically at runtime through EntityManager APIs.

Query API

query in Java Persistence is a full-blown Java interface that you obtain at runtime from the entity
manager:

package javax.persistence;

public interface Query {

public List getResultList( );
public Object getSingleResult( );
public int executeUpdate( );
public Query setMaxResults(int maxResult);
public Query setFirstResult(int startPosition);
public Query setHint(String hintName, Object value);
public Query setParameter(String name, Object value);
public Query setParameter(String name, Date value, TemporalType
temporalType);
public Query setParameter(String name, Calendar value, TemporalType
temporalType);
public Query setParameter(int position, Object value);
public Query setParameter(int position, Date value, TemporalType
temporalType);
public Query setParameter(int position, Calendar value, TemporalType
temporalType);
public Query setFlushMode(FlushModeType flushMode);
}

Queries are created using these EntityManager methods:

package javax.persistence;

public interface EntityManager {

public Query createQuery(String ejbqlString);
public Query createNamedQuery(String name);
public Query createNativeQuery(String sqlString);

1
EJB 3.0 QL Tutorial
www.sycorax.co.in

public Query createNativeQuery(String sqlString, Class resultClass);

public Query createNativeQuery(String sqlString, String resultSetMapping);
}

Let's first look at using EntityManager.createQuery( ) to create a query dynamically at

runtime:

try {
Query query = entityManager.creatQuery(
"from Customer c where c.firstName='Bill' and
c.lastName='Burke'");
Customer cust = (Customer)query.getSingleResult( );
} catch (EntityNotFoundException notFound) {
} catch (NonUniqueResultException nonUnique) {
}

The query is executed when the getSingleResult( ) method is called. This method expects
that the call will return only one result. If no result is returned, then the method throws a
javax.persistence.EntityNotFoundException runtime exception. If more than one result
is found, then a javax.persistence.NonUniqueResultException runtime exception is
thrown. Since both of these exceptions are RuntimeException s, the example code is not
required to have a full try/catch block.

You can change the query to use the getresultList( ) method to obtain a collection of results:

Query query = entityManager.creatQuery(

"from Customer c where c.firstName='Bill' and
c.lastName='Burke'");
java.util.List bills = query.getResultList( );

Parameters

Much like a java.sql.PreparedStatement in JDBC, EJB QL allows you to specify

parameters in query declarations so that you can reuse and execute the query multiple times on
different sets of parameters. Two syntaxes are provided: named parameters and positional
parameters. Let's modify our earlier Customer query to take both last name and first name as
named parameters:

public List findByName(String first, String last) {

Query query = entityManager.createQuery(

2
EJB 3.0 QL Tutorial
www.sycorax.co.in

"from Customer c where c.firstName=:first and

c.lastName=:last");
query.setParameter("first", first);
query.setParameter("last", last);
return query.getResultList( );
}

The : character followed by the parameter name is used in EJB QL statements to identify a
named parameter. The setParameter( ) method in this example takes the name of the
parameter first, and then the actual value. EJB QL also supports positional parameters. Let's
modify the previous example to see this mode in action:

public List findByName(String first, String last) {

Query query = entityManager.createQuery(
"from Customer c where c.firstName=?1 and c.lastName=?2");
query.setParameter(1, first);
query.setParameter(2, last);
return query.getResultList( );
}

Hints

Some Java Persistence vendors will provide additional add-on features that you can take
advantage of when executing a query. For instance, the JBoss EJB 3.0 implementation allows
you to define a timeout for the query. These types of add-on features can be specified as hints
using the setHint( ) method on the query. Here's an example of defining a JBoss query
timeout using hints:

Query query = manager.createQuery("from Customer c");

query.setHint("org.hibernate.timeout", 1000);

FlushMode

Sometimes you would like a different flush mode to be enforced for the duration of a query. For
instance, maybe a query wants to make sure that the entity manager does not flush before the
query is executed (since the default value implies that the entity manager can). The Query
interface provides a setFlushMode( ) method for this particular purpose:

Query query = manager.createQuery("from Customer c");

query.setFlushMode(FlushModeType.COMMIT);

3
EJB 3.0 QL Tutorial
www.sycorax.co.in

Example:
@Entity
public class Customer {
private ind id;
private String first;

private String last;

@Id
public int getId( ) { return id; }
public String getFirstName( ) { return first; }
public String getLastName( ) { return first; }

In this example, we are using get and set methods to define our persistent properties. The
SELECT clause would look like this:

SELECT c.firstName, c.lastName FROM Customer AS c

When a query returns more than one item, you must use the Query.getResultList( ) method.
If the SELECT clause queries more than one column or entity, the results are aggregated in an
object array (Object[]) in the java.util.List returned by geTResultList( ). The following
code shows how to access the returned results:

Query query = manager.createQuery(

"SELECT c.firstName, c.lastName FROM Customer AS c");
List results = query.getResultList( );
Iterator it = results.iterator( );
while (it.hasNext( )) {
Object[] result = (Object[])it.next( );
String first = (String)result[0];
String last = (String)result[1];
}

Using DISTINCT

If a customer has more than one reservation, there will be duplicate references to that customer
in the result. Using the DISTINCT keyword ensures that each customer is represented only once
in the result:

SELECT DISTINCT c.firstName, c.lastName FROM Customer ;

4
EJB 3.0 QL Tutorial
www.sycorax.co.in

WHERE Clause

This is accomplished through the WHERE clause, which behaves in much the same way as the
WHERE clause in SQL.

For example, you can define an EJB QL statement that selects all the Customer entities that use a
specific brand of credit card. The literal in this case is a String literal. Literal strings are
enclosed by single quotes. Literal values that include a single quote, like the restaurant name
Wendy's, use two single quotes to escape the quote: Wendy''s. The following statement returns
customers that use Capital One credit cards. (If you don't want to bother with such details, use a
query parameter; the job will be done for you by the query API.)

SELECT c
FROM Customer AS c
WHERE c.creditCard.creditCompany.name = 'Capital One'

WHERE Clause and LIKE

The LIKE comparison operator allows the query to select String type fields that match a
specified pattern. For example, the following EJB QL statement selects all the customers with
hyphenated names, like "Monson-Haefel" and "Berners-Lee":

SELECT OBJECT( c ) FROM Customer AS c

WHERE c.lastName LIKE '%-%'

Aggregate functions in the SELECT clause

COUNT (identifier or path expression)

This function returns the number of items in the query's final result set. The return type is a
java.lang.Long, depending on whether it is the return type of the query method. For example,
the following query provides a count of all the customers who live in Wisconsin:

SELECT COUNT( c )
FROM Customers AS c
WHERE c.address.state = 'WI'

5
EJB 3.0 QL Tutorial
www.sycorax.co.in

MAX( path expression), MIN( path expression)

These functions can be used to find the largest or smallest value from a collection of any type of
field. They cannot be used with identifiers or paths that terminate in a relationship field. The
result type will be the type of field that is being evaluated. For example, the following query
returns the highest price paid for a reservation:

SELECT MAX( r.amountPaid )

FROM Reservation AS r

The MAX( ) and MIN( ) functions can be applied to any valid value, including primitive
types, strings, and even serializable objects.

AVG( numeric ), SUM( numeric)

The AVG( ) and SUM( ) functions can be applied only to path expressions that terminate in a
numeric primitive field (byte, long, float, etc.) or in one of their corresponding numeric
wrappers (Byte, Long, Float, etc.). The result of a query that uses the SUM( ) function has the
same type as the numeric type it's evaluating. The result type of the AVG( ) function is a
java.lang.Double, depending on whether it is used in the return type of the SELECT method.

For example, the following query uses the SUM( ) function to get the total amount paid by all
customers for a specific cruise (specified by the input parameter):

SELECT SUM( r.amountPaid)

FROM Cruise c join c.reservations r
WHERE c = :cr

ORDER BY Clause

The ORDER BY clause allows you to specify the order of the entities in the collection returned by
a query. The semantics of the ORDER BY clause are basically the same as in SQL. For example,
we can construct a simple query that uses the ORDER BY clause to return an alphabetical list of all
of Titan Cruises' customers:

SELECT c
FROM Customers AS c
ORDER BY c.lastName

6
EJB 3.0 QL Tutorial
www.sycorax.co.in

The default order of an item listed in the ORDER BY clause is always ascending, which means
that the lesser values are listed first and the greater values last. You can explicitly specify
the order as ascending or descending by using the keywords ASC and DESC. The default is ASC.

GROUP BY and HAVING

The GROUP BY and HAVING clauses are commonly used to apply stricter organization to a query
and to narrow the results for aggregate functions. The GROUP BY clause is usually used in
combination with aggregate functions, because it allows you to cluster data by category.

SELECT cr.name, COUNT (res) FROM Customer cr

GROUP BY cr.name

The HAVING clause is used with a GROUP BY clause and acts as a filter, restricting the final output.
The HAVING clause employs aggregate functional expressions using only the identifiers used in
the SELECT clause. You can restrict the GROUP BY result by using the HAVING syntax.

SELECT cr.name, COUNT (res) FROM Customer cr

GROUP BY cr.name
HAVING cr.age > 60

Functional expressions

EJB QL has seven functional expressions that allow for simple String manipulation and three
functional expressions for basic numeric operations. The String functions are:

LOWER(String)

Converts a string to lowercase.

UPPER(String)

Converts a string to uppercase.

TRIM([[LEADING | TRAILING | BOTH] [trim_char] FROM] String)

7
EJB 3.0 QL Tutorial
www.sycorax.co.in

Allows you to trim a specified character from the beginning (LEADING), end (trAILING),
or both (BOTH). If you do not specify a trim character, the space character will be
assumed.

CONCAT(String1, String2)

Returns the String that results from concatenating String1 and String2.

LENGTH(String)

Returns an int indicating the length of the string.

LOCATE(String1, String2 [, start])

Returns an int indicating the position at which String1 is found within String2. If it's
present, start indicates the character position in String2 at which the search should
start. Support for the start parameter is optional; some containers will support it, and
others will not. Don't use it if you want to ensure that the query is portable.

SUBSTRING(String1, start, length)

Returns the String consisting of length characters taken from String1, starting at the
position given by start.

ABS(number)

Returns the absolute value of a number (int, float, or double)

SQRT(double)

Returns the square root of a double

MOD(int, int)

Returns the remainder for the first parameter divided by the second (i.e., MOD(7, 5) is
equal to 2)

8
EJB 3.0 QL Tutorial
www.sycorax.co.in