JPA Select Statements and NamedQuery ~ IT BLOG

Select Statements

A select query has six clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY. The SELECT and FROM clauses are required, but the WHERE, GROUP BY, HAVING, and ORDER BY clauses are optional. Here is the high-level BNF syntax of a query language select query:

Note: some of the terms referred to in this chapter.

Abstract schema: The persistent schema abstraction (persistent entities, their state, and their relationships) over which queries operate. The query language translates queries over this persistent schema abstraction into queries that are executed over the database schema to which entities are mapped.
Abstract schema type: The type to which the persistent property of an entity evaluates in the abstract schema. That is, each persistent field or property in an entity has a corresponding state field of the same type in the abstract schema. The abstract schema type of an entity is derived from the entity class and the metadata information provided by Java language annotations.
Backus-Naur Form (BNF): A notation that describes the syntax of high-level languages. The syntax diagrams in this chapter are in BNF notation.
Navigation: The traversal of relationships in a query language expression. The navigation operator is a period.
Path expression: An expression that navigates to an entity's state or relationship field.
State field: A persistent field of an entity.
Relationship field: A persistent field of an entity whose type is the abstract schema type of the related entity.

QL_statement ::= select_clause from_clause 
  [where_clause][groupby_clause][having_clause][orderby_clause]

Ví dụ:

SELECT p
FROM Player p

SELECT DISTINCT p
FROM Player p
WHERE p.position = ?1

Data retrieved: The players with the position specified by the query's parameter.
Description: The DISTINCT keyword eliminates duplicate values.

SELECT DISTINCT p
FROM Player p
WHERE p.position = :position AND p.name = :name

Data retrieved: The players having the specified positions and names.
Description: The position and name elements are persistent fields of the Player entity. The WHERE clause compares the values of these fields with the named parameters of the query, set using the Query.setNamedParameter method. The q

SELECT DISTINCT p
FROM Player p, IN (p.teams) t

Data retrieved: All players who belong to a team.
Description: The FROM clause declares two identification variables: p and t. The p variable represents the Player entity, and the t variable represents the related Team entity. The declaration for t references the previously declared p variable. The IN keyword signifies that teams is a collection of related entities. The p.teams expression navigates from a Player to its related Team. The period in the p.teams expression is the navigation operator.

You may also use the JOIN statement to write the same query:

SELECT DISTINCT p
FROM Player p JOIN p.teams t

This query could also be rewritten as:

SELECT DISTINCT p
FROM Player p
WHERE p.team IS NOT EMPTY

Use the JOIN clause statement to navigate to a single-valued relationship field:

SELECT t
FROM Team t JOIN t.league l
WHERE l.sport = 'soccer' OR l.sport ='football'

In this example, the query will return all teams that are in either soccer or football leagues.

Traversing Relationships with an Input Parameter

SELECT DISTINCT p
FROM Player p, IN (p.teams) AS t
WHERE t.city = :city

Data retrieved: The players whose teams belong to the specified city.
Description: This query is similar to the previous example but adds an input parameter. The AS keyword in the FROM clause is optional. In the WHERE clause, the period preceding the persistent variable city is a delimiter, not a navigation operator. Strictly speaking, expressions can navigate to relationship fields (related entities) but not to persistent fields. To access a persistent field, an expression uses the period as a delimiter.

Expressions cannot navigate beyond (or further qualify) relationship fields that are collections. In the syntax of an expression, a collection-valued field is a terminal symbol. Because the teams field is a collection, the WHERE clause cannot specify p.teams.city (an illegal expression).
See also: Path Expressions.

Traversing Multiple Relationships

SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league = :league

Data retrieved: The players who belong to the specified league.
Description: The expressions in this query navigate over two relationships. The p.teams expression navigates the Player-Team relationship, and the t.league expression navigates the Team-League relationship.

In the other examples, the input parameters are String objects; in this example, the parameter is an object whose type is a League. This type matches the leaguerelationship field in the comparison expression of the WHERE clause.

Navigating According to Related Fields

SELECT DISTINCT p
FROM Player p, IN (p.teams) t
WHERE t.league.sport = :sport

Data retrieved: The players who participate in the specified sport.
Description: The sport persistent field belongs to the League entity. To reach the sport field, the query must first navigate from the Player entity to Team(p.teams) and then from Team to the League entity (t.league). Because it is not a collection, the league relationship field can be followed by the sportpersistent field.

The LIKE Expression

SELECT p
FROM Player p
WHERE p.name LIKE 'Mich%'

Data retrieved: All players whose names begin with "Mich."
Description: The LIKE expression uses wildcard characters to search for strings that match the wildcard pattern. In this case, the query uses the LIKEexpression and the % wildcard to find all players whose names begin with the string "Mich." For example, "Michael" and "Michelle" both match the wildcard pattern.
See also: LIKE Expressions.

The IS NULL Expression

SELECT t
FROM Team t
WHERE t.league IS NULL

Data retrieved: All teams not associated with a league.
Description: The IS NULL expression can be used to check whether a relationship has been set between two entities. In this case, the query checks whether the teams are associated with any leagues and returns the teams that do not have a league.
See also: NULL Comparison Expressions and NULL Values.

The IS EMPTY Expression

SELECT p
FROM Player p
WHERE p.teams IS EMPTY

Data retrieved: All players who do not belong to a team.
Description: The teams relationship field of the Player entity is a collection. If a player does not belong to a team, the teams collection is empty, and the conditional expression is TRUE.

The BETWEEN Expression

SELECT DISTINCT p
FROM Player p
WHERE p.salary BETWEEN :lowerSalary AND :higherSalary

Data retrieved: The players whose salaries fall within the range of the specified salaries.
Description: This BETWEEN expression has three arithmetic expressions: a persistent field (p.salary) and the two input parameters (:lowerSalary and :higherSalary). The following expression is equivalent to the BETWEEN expression:

p.salary >= :lowerSalary AND p.salary <= :higherSalary

See also: BETWEEN Expressions.

Comparison Operators

SELECT DISTINCT p1
FROM Player p1, Player p2
WHERE p1.salary > p2.salary AND p2.name = :name

Data retrieved: All players whose salaries are higher than the salary of the player with the specified name.
Description: The FROM clause declares two identification variables (p1 and p2) of the same type (Player). Two identification variables are needed because the WHERE clause compares the salary of one player (p2) with that of the other players (p1).
See also: Identification Variables.

Update Queries

UPDATE Player p
SET p.status = 'inactive'
WHERE p.lastPlayed < :inactiveThresholdDate

Delete Queries

DELETE
FROM Player p
WHERE p.status = 'inactive'
AND p.teams IS EMPTY

Description: This query deletes all inactive players who are not on a team.

Joins

The JOIN operator is used to traverse over relationships between entities and is functionally similar to the IN operator.

In the following example, the query joins over the relationship between customers and orders:

SELECT c
FROM Customer c JOIN c.orders o
WHERE c.status = 1 AND o.totalPrice > 10000

The INNER keyword is optional:

SELECT c
FROM Customer c INNER JOIN c.orders o
WHERE c.status = 1 AND o.totalPrice > 10000

These examples are equivalent to the following query, which uses the IN operator:

SELECT c
FROM Customer c, IN (c.orders) o
WHERE c.status = 1 AND o.totalPrice > 10000

You can also join a single-valued relationship:

SELECT t
FROM Team t JOIN t.league l
WHERE l.sport = :sport

A LEFT JOIN or LEFT OUTER JOIN retrieves a set of entities where matching values in the join condition may be absent. The OUTER keyword is optional:

SELECT c.name, o.totalPrice
FROM CustomerOrder o LEFT JOIN o.customer c

A FETCH JOIN is a join operation that returns associated entities as a side effect of running the query. In the following example, the query returns a set of departments and, as a side effect, the associated employees of the departments, even though the employees were not explicitly retrieved by the SELECT clause:

SELECT d
FROM Department d LEFT JOIN FETCH d.employees
WHERE d.deptno = 1

Expression Types

The type of a path expression is the type of the object represented by the ending element, which can be one of the following:

Persistent field
Single-valued relationship field
Collection-valued relationship field

For example, the type of the expression p.salary is double because the terminating persistent field (salary) is a double.

In the expression p.teams, the terminating element is a collection-valued relationship field (teams). This expression's type is a collection of the abstract schema type named Team. Because Team is the abstract schema name for the Team entity, this type maps to the entity. For more information on the type mapping of abstract schemas, see Return Types.

SELECT p.name
CASE TYPE(p)
    WHEN Student THEN 'kid'
    WHEN Guardian THEN 'adult'
    WHEN Staff THEN 'adult'
    ELSE 'unknown'
END
FROM Person p

SELECT AVG(o.quantity)
FROM CustomerOrder o

The following example returns the total cost of the items ordered by Roxane Coss:

SELECT SUM(l.price)
FROM CustomerOrder o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = 'Coss' AND c.firstname = 'Roxane'

The following example returns the total number of orders:

SELECT COUNT(o)
FROM CustomerOrder o

The following example returns the total number of items that have prices in Hal Incandenza's order:

SELECT COUNT(l.price)
FROM CustomerOrder o JOIN o.lineItems l JOIN o.customer c
WHERE c.lastname = 'Incandenza' AND c.firstname = 'Hal'

SELECT NEW com.example.CustomerDetail(c.name, c.country.name)
FROM customer c
WHERE c.lastname = 'Coss' AND c.firstname = 'Roxane'

SELECT o
FROM Customer c JOIN c.orders o JOIN c.address a
WHERE a.state = 'CA'
ORDER BY o.quantity, o.totalcost

The following example is not valid, because the ORDER BY clause uses a value not returned by the SELECT clause:

SELECT p.product_name
FROM CustomerOrder o, IN(o.lineItems) l JOIN o.customer c
WHERE c.lastname = 'Faehmel' AND c.firstname = 'Robert'
ORDER BY o.quantity

SELECT c.country, COUNT(c)
FROM Customer c GROUP BY c.country

The HAVING clause is used with the GROUP BY clause to further restrict the returned result of a query.

The following query groups orders by the status of their customer and returns the customer status plus the average totalPrice for all orders where the corresponding customers have the same status. In addition, it considers only customers with status 1, 2, or 3, so orders of other customers are not taken into account:

SELECT c.status, AVG(o.totalPrice)
FROM CustomerOrder o JOIN o.customer c
GROUP BY c.status HAVING c.status IN (1, 2, 3)

createQuery method

The EntityManager.createQuery and EntityManager.createNamedQuery methods are used to query the datastore by using Java Persistence query language queries.

The createQuery method is used to create dynamic queries, which are queries defined directly within an application's business logic:

public List findWithName(String name) {
return em.createQuery(
    "SELECT c FROM Customer c WHERE c.name LIKE :custName")
    .setParameter("custName", name)
    .setMaxResults(10)
    .getResultList();
}

Named Parameters in Queries

Named parameters are query parameters that are prefixed with a colon (:). Named parameters in a query are bound to an argument by the following method:

javax.persistence.Query.setParameter(String name, Object value)

In the following example, the name argument to the findWithName business method is bound to the :custName named parameter in the query by calling Query.setParameter:

public List findWithName(String name) {
    return em.createQuery(
        "SELECT c FROM Customer c WHERE c.name LIKE :custName")
        .setParameter("custName", name)
        .getResultList();
}

Named parameters are case-sensitive and may be used by both dynamic and static queries.

Positional Parameters in Queries

You may use positional parameters instead of named parameters in queries. Positional parameters are prefixed with a question mark (?) followed by the numeric position of the parameter in the query. The method Query.setParameter(integer position, Object value) is used to set the parameter values.

In the following example, the findWithName business method is rewritten to use input parameters:

public List findWithName(String name) {
    return em.createQuery(
        "SELECT c FROM Customer c WHERE c.name LIKE ?1")
        .setParameter(1, name)
        .getResultList();
}

Input parameters are numbered starting from 1. Input parameters are case-sensitive, and may be used by both dynamic and static queries.

Named Queries in JPA

The createNamedQuery method is used to create static queries, or queries that are defined in metadata by using the javax.persistence.NamedQueryannotation. The name element of @NamedQuery specifies the name of the query that will be used with the createNamedQuery method. The query element of @NamedQuery is the query:

@NamedQuery(
    name="findAllCustomersWithName",
    query="SELECT c FROM Customer c WHERE c.name LIKE :custName"
)

Here's an example of createNamedQuery, which uses the @NamedQuery:

@PersistenceContext
public EntityManager em;
...
customers = em.createNamedQuery("findAllCustomersWithName")
    .setParameter("custName", "Smith")
    .getResultList();

Implementing a JPA Named Query

A named query is a predefined query that you create and associate with a container-managed entity (see "Using Annotations"). At deployment time, OC4J stores named queries on the EntityManager. At run time, you can use the EntityManager to acquire, configure, and execute a named query.
For more information, see the following:

Using Annotations

Example 8-1 shows how to use the @NamedQuery annotation to define a Java persistence query language query that you can acquire by name findAllEmployeesByFirstName at run time using the EntityManager.

Example 8-1 Implementing a Query Using @NamedQuery

@Entity
@NamedQuery(
    name="findAllEmployeesByFirstName",
    queryString="SELECT OBJECT(emp) FROM Employee emp WHERE emp.firstName = 'John'"
)
public class Employee implements Serializable {
...
}

Example 8-2 shows how to use the @NamedQuery annotation to define a Java persistence query language query that takes a parameter named firstname. Example 8-3 shows how you use the EntityManager to acquire this query and use Query method setParameter to set the firstname parameter. For more information on using the EntityManager with named queries, see "Querying for a JPA Entity Using the EntityManager".
Optionally, you can configure your named query with query hints to use JPA persistence provider vendor extensions (see "Configuring TopLink Query Hints in a JPA Query").

Example 8-2 Implementing a Query With Parameters Using @NamedQuery

@Entity
@NamedQuery(
    name="findAllEmployeesByFirstName",
    queryString="SELECT OBJECT(emp) FROM Employee emp WHERE emp.firstName = :firstname"
)
public class Employee implements Serializable {
...
}

Example 8-3 Setting Parameters in a Named Query

Query queryEmployeesByFirstName = em.createNamedQuery("findAllEmployeesByFirstName");
queryEmployeeByFirstName.setParameter("firstName", "John");
Collection employees = queryEmployessByFirstName.getResultList();

Named Queries

Query templates can be statically declared using the NamedQuery and NamedQueries annotations. For example:

@Entity
@NamedQueries({
    @NamedQuery(name="magsOverPrice",
        query="SELECT x FROM Magazine x WHERE x.price > ?1"),
    @NamedQuery(name="magsByTitle",
        query="SELECT x FROM Magazine x WHERE x.title = :titleParam")
})
public class Magazine
{
    ...
}

These declarations will define two named queries called magsOverPrice and magsByTitle.

public Query createNamedQuery (String name);

You retrieve named queries with the above EntityManager method. For example:

EntityManager em = ...
Query q = em.createNamedQuery ("magsOverPrice");
q.setParameter (1, 5.0f);
List<Magazine> results = (List<Magazine>) q.getResultList ();

EntityManager em = ...
Query q = em.createNamedQuery ("magsByTitle");
q.setParameter ("titleParam", "JDJ");
List<Magazine> results = (List<Magazine>) q.getResultList ();

Source: https://docs.oracle.com/javaee/7/tutorial/partpersist.htm

IT BLOG

Sharing IT Knowledge and Life – Chia sẻ kiến thức IT và đời sống

Sunday, July 23, 2017