Business Component Development Using EJB Technologies
Objectives
In this session, you will learn to:
Describe querying over entities and their persistence state
Examine query objects
Create and use query objects
Declare Java persistence query language statements
Ver. 1.0
Slide 1 of 28
Business Component Development Using EJB Technologies
Introducing Querying Over Entities and Their Persistence State
The following steps outline the process required to use QL
statements and query objects in a Java EE application:
1. Create QL statement string:
String queryString = SELECT o FROM Order o +
WHERE o.shippingAddress.state = CA;
2. Create a query object wrapper for the QL string, of type
javax.persistence.Query interface:
// Assume entityManager references an EntityManager
object Query query =
entityManager.createQuery(queryString); // create
//Query
3. Execute the query object:
Collection <Order> orders = query.getResultList();
Ver. 1.0
Slide 2 of 28
Business Component Development Using EJB Technologies
Examining Query Objects
A query object:
Is an instance of the javax.persistence.Query interface.
Encapsulates either a QL query string or a native (SQL) query
string.
A query is created by using the createQuery, as shown in the
following code:
Query query = entityManager.createQuery
(select o from Order o where customer.id = :custId +
order by o.createdDatetime);
Ver. 1.0
Slide 3 of 28
Business Component Development Using EJB Technologies
Examining Query Objects (Contd.)
The following code shows several operations that are
commonly invoked on a query object:
1 public List findOrdersByCustomer(Customer cust, int
max){
2 return entityManager.createQuery(
3
select o from Order o where customer.id=:custId +
4
order by o.createdDatetime)
5
.setParameter(custId, cust.getId())
6
.setMaxResults(max)
7
.getResultList();
8 }
Ver. 1.0
Slide 4 of 28
Business Component Development Using EJB Technologies
Examining Query Objects (Contd.)
Named queries:
Are the static queries expressed in metadata.
Can be defined in QL or in SQL.
The following code shows a named query declaration:
1
2
3
4
5
6
7
Ver. 1.0
@Entity
@NamedQuery(name="FindAllClosedAuctions",
query=" SELECT OBJECT(a) FROM Auction AS a +
WHERE a.status= CLOSED")
public class Auction implements Serializable {
// ...
}
Slide 5 of 28
Business Component Development Using EJB Technologies
Examining Query Objects (Contd.)
The following code shows the use of a named query:
1 // This is a code snippet
2 // Assume entityManager references an instance of an
EntityManager
3 Query query =
4
entityManager.createNamedQuery
("FindAllClosedAuctions");
5 Collection<Auction> auctions = query.getResultList();
6 // ...
Ver. 1.0
Slide 6 of 28
Business Component Development Using EJB Technologies
Examining Query Objects (Contd.)
The following code shows the creation of a native query:
Query q = em.createNativeQuery("SELECT o.id,
o.quantity, o.item + "FROM Order o, Item i " +
"WHERE (o.item = i.id) AND (i.name = widget)",
com.acme.Order.class);
Ver. 1.0
Slide 7 of 28
Business Component Development Using EJB Technologies
Creating and Using Query Objects
The following steps outline the actions needed to create and
use a query object:
1. Identify the task you need to perform: select, update, or delete
information from the database.
2. Determine if the task can be performed using an QL query
string. If not you would need to create a SQL query string.
3. Create the appropriate query string:
select o from Order o where customer.id = :custId
+ order by o.createdDatetime
4. Use the query string to create an instance of the Query
interface:
Query query = entityManager.createQuery
(select o from Order o where customer.id =
:custId + order by o.createdDatetime);
Ver. 1.0
Slide 8 of 28
Business Component Development Using EJB Technologies
Creating and Using Query Objects (Contd.)
5. Configure the query object for execution:
int id = 24;
int max = 20;
query.setParameter(custId, id)
query.setMaxResults(max)
Collection orders = query.getResultList();
6. Execute the query using one of the Query interfaces query
execute methods:
Collection orders = query.getResultList();
Ver. 1.0
Slide 9 of 28
Business Component Development Using EJB Technologies
Declaring and Using Positional Parameters
The following code shows the declaration of a query:
Query query = entityManager.createQuery
(select o from Order o where customer.id = ?1 +
order by o.createdDatetime);
Ver. 1.0
Slide 10 of 28
Business Component Development Using EJB Technologies
Declaring and Using Positional Parameters (Contd.)
The following code invokes the query, declared in the
preceding code:
int id = 24;
int max = 20;
query.setParameter(1, id);
query.setMaxResults(max);
Collection orders = query.getResultList();
Ver. 1.0
Slide 11 of 28
Business Component Development Using EJB Technologies
Introducing the Java Persistence Query Language
QL is a query specification language for dynamic queries
and for static queries expressed through metadata.
QL supports the following statement types:
Select statement
Update statement
Delete statement
Ver. 1.0
Slide 12 of 28
Business Component Development Using EJB Technologies
Declaring Query Strings: The SELECT Statement
The following syntax shows the structure of a SELECT
statement:
SELECT_clause FROM_clause [WHERE_clause]
[GROUPBY_clause]
[HAVING_clause] [ORDERBY_clause]
Ver. 1.0
Slide 13 of 28
Business Component Development Using EJB Technologies
Examining the FROM Clause
The FROM clause:
Specifies the search domain in the persistence tier.
Is specified using one or more path expressions.
The following code shows the use of a single path
expression in the FROM clause:
SELECT o.quantity FROM Order o
The following code shows the FROM clause containing two
path expressions:
SELECT DISTINCT o1 FROM Order o1, Order o2 WHERE
o1.quantity > o2.quantity
AND o2.customer.lastname = Smith AND
o2.customer.firstname = John
Ver. 1.0
Slide 14 of 28
Business Component Development Using EJB Technologies
Examining the FROM Clause (Contd.)
A path expression associates a variable with a persistence
entity or a persisted field (or property) of a persistence
entity:
SELECT DISTINCT o FROM Order o, IN(o.lineItems) l
WHERE l.product.productType = officeSupplies
The path expressions are classified as:
Range variable path expressions
Collection member path expressions
Association traversing path expressions
Ver. 1.0
Slide 15 of 28
Business Component Development Using EJB Technologies
Examining the FROM Clause (Contd.)
The Java persistence specification supports the following
association traversing query capabilities:
Inner joins
Left outer joins
Fetch joins
Ver. 1.0
Slide 16 of 28
Business Component Development Using EJB Technologies
Examining the FROM Clause (Contd.)
The following syntax shows the structure of inner join:
[INNER]JOIN join_association_path_expression [AS]
identification_variable
The following syntax shows the structure of left outer inner
join:
LEFT [OUTER]JOIN join_association_path_expression [AS]
identification_variable
The following syntax shows the structure of fetch join:
[LEFT [OUTER] |INNER]JOIN FETCH
join_association_path_expression
Ver. 1.0
Slide 17 of 28
Business Component Development Using EJB Technologies
Examining the WHERE Clause
The following syntax shows structure of a WHERE clause:
WHERE Simple_Conditional_Expression
WHERE Complex_Conditional_Expression
The following syntax shows the structure of a simple
conditional expression:
Operand_1 Conditional_Expression_Operator Operand_2
Ver. 1.0
Slide 18 of 28
Business Component Development Using EJB Technologies
Examining the WHERE Clause (Contd.)
The following table lists the conditional expression types
and the conditional expression operators.
Ver. 1.0
Conditional Expression Type
Conditional Expression
Operators
Operand Type
Comparison expression
=, >, >=, <, <=, <>
Arithmetic
Between expression
[NOT] BETWEEN
Arithmetic, string,
date_time
Like expression
[NOT] LIKE
String
In expression
IS [NOT] NULL
All types
Null comparison expression
IS [NOT] EMPTY
Collection
Collection member expression
[NOT] MEMBER OF
Collection
Exist expression
[NOT] LIKE
String
Slide 19 of 28
Business Component Development Using EJB Technologies
Examining the WHERE Clause (Contd.)
The following syntax shows the structure of a complex
conditional expression:
Simple_Cond_Expr_1 Binary_Relational_Operator
Simple_Cond_Expr_2
Ver. 1.0
Slide 20 of 28
Business Component Development Using EJB Technologies
Examining the WHERE Clause (Contd.)
You can use the following types of literals in conditional
expressions:
String literals
Numeric Literals
Boolean Literals
Ver. 1.0
Slide 21 of 28
Business Component Development Using EJB Technologies
Examining the WHERE Clause (Contd.)
The following table lists the QL functions together with the
input parameters and the return types.
Ver. 1.0
Function Name and Parameters
Return Type
CONCAT(String, String)
String
SUBSTRING(String, String)
String
TRIM(String, String)
String
LOWER(String)
String
UPPER(String)
String
LOCATE(String, String [,simple arith expr])
int
LENGTH(String)
int
ABS(number)
int, float or double
LOWER(String)
double
SQRT(double)
double
MOD(int, int)
int
SIZE(Collection_valued_path_expr)
int
Slide 22 of 28
Business Component Development Using EJB Technologies
Examining the SELECT Clause
The SELECT clause can contain one or more of the following
elements:
A single identification variable:
SELECT o FROM Order o WHERE o.quantity > 500
A single-valued path expression:
SELECT c.id, c.status FROM Customer c JOIN c.orders o
WHERE o.count > 100
An aggregate select expression:
SELECT AVG(o.quantity) FROM Order o
A constructor expression:
SELECT NEW com.acme.example.CustomerDetails(c.id,
c.status, o.count) FROM
Customer c JOIN c.orders o WHERE o.count > 100
Ver. 1.0
Slide 23 of 28
Business Component Development Using EJB Technologies
Examining the GROUP BY and HAVING Clauses
The following example shows the use of GROUP BY clause:
SELECT c.country, COUNT(c) FROM Customer c GROUP BY
c.country HAVING
COUNT(c) > 3
SELECT c.status, avg(c.filledOrderCount), COUNT(c)
FROM Customer c GROUP BY
c.status HAVING c.status IN (1, 2)
The following example shows the use of HAVING clause:
SELECT c.country, COUNT(c) FROM Customer c GROUP BY
c.country HAVING
COUNT(c) > 3
SELECT c.status, avg(c.filledOrderCount), COUNT(c)
FROM Customer c GROUP BY
c.status HAVING c.status IN (1, 2)
Ver. 1.0
Slide 24 of 28
Business Component Development Using EJB Technologies
Examining the ORDER BY Clause
The following syntax shows the structure of ORDER BY
clause:
ORDER BY orderby_item [ASC | DESC]{, orderby_item [ASC
| DESC]}*
You can specify the order, ascending or descending order
[ASC or DESC], in the ORDER BY clause:
SELECT o FROM Customer c JOIN c.orders o JOIN
c.address a WHERE a.state = CA ORDER BY o.quantity
DESC, o.totalcost ASC
Ver. 1.0
Slide 25 of 28
Business Component Development Using EJB Technologies
Declaring Query Strings: The BULK UPDATE Statement
The following syntax shows the structure of the UPDATE
clause:
UPDATE_clause [WHERE_clause]
The following code shows a single field update:
UPDATE customer c SET c.status = outstanding WHERE
c.balance < 10000
Ver. 1.0
Slide 26 of 28
Business Component Development Using EJB Technologies
Declaring Query Strings: The DELETE Statement
The following syntax shows the structure of the DELETE
clause:
DELETE_clause [WHERE_clause]
The following code shows the use of the DELETE clause:
DELETE FROM Customer c WHERE c.status = inactive
Ver. 1.0
Slide 27 of 28
Business Component Development Using EJB Technologies
Summary
In this session, you learned that:
Java Persistence query language is a database-independent
and entity based query language.
A query object is an instance of the javax.persistence.Query
interface that encapsulates either a QL query string or a native
(SQL) query string.
Named queries are the static queries expressed in metadata.
QL is a query specification language for dynamic queries and
for static queries expressed through metadata.
A path expression associates a variable with a persistence
entity or a persisted field (or property) of a persistence entity.
Ver. 1.0
Slide 28 of 28
