JPA Queries
The javax.persistence.Query interface is the mechanism for issuing queries in JPA. The
primary query language used is the Java Persistence Query Language, or JPQL. JPQL is syntactically very similar to SQL, but is
object-oriented rather than table-oriented.
The preceding
is a simple JPQL query for all Departments entities.
public Query createQuery (String jpql);
public List getResultList ();
Invoking Query.getResultList
executes the query and returns a List containing the
matching objects. The following example executes our Departments query above:
EntityManager em = ...
Query q = em.createQuery ("SELECT
x FROM Departments x");
List<Departments> results =
q.getResultList ();
A JPQL query has an internal namespace
declared in the from clause of the query. Arbitrary identifiers are assigned
to entities so that they can be referenced elsewhere in the query. In the query
example above, the identifier x is assigned to
the entity Departments.
Following the select clause of the query is the object or
objects that the query returns. In the case of the query above, the query's
result list will contain instances of the Departments class.
2.SELECT x FROM Departments x WHERE
x.title = 'JDJ'
Keywords in
JPQL expressions are case-insensitive, but entity, identifier, and member names
are not. For example, the expression above could also be expressed as:
SELECT x FROM Departments x WHERE
x.title = 'JDJ'
But it could
not be expressed as:
SELECT x FROM Departments x WHERE
x.TITLE = 'JDJ'
As with the select clause, alias names in the where clause are resolved to the entity
declared in the from clause. The
query above could be described in English as
"for all Departments instances x, return a list of every x such that x's title field is equal to 'JDJ'".
JPQL uses
SQL-like syntax for query criteria. The and and or logical operators chain multiple
criteria together:
SELECT x FROM Departments x WHERE
x.title = 'JDJ' OR x.title = 'JavaPro'
The = operator tests for equality. <> tests for inequality. JPQL also supports the following
arithmetic operators for numeric comparisons: >, >=, <, <=.
JPQL also
includes the following conditionals:
BETWEEN: Shorthand for expressing that a value falls between two
other values. The following two statements are synonymous:
·
SELECT x FROM Departments x WHERE x.fee >= 3.00 AND x.fee <= 5.00
·
SELECT x FROM Departments x WHERE x.fee BETWEEN 3.00 AND 5.00
LIKE: Performs a string comparison with wildcard support. The
special character '_' in the parameter means to match any single character, and
the special character '%' means to match any sequence of characters. The
following statement matches title fields "JDJ" and "JavaPro",
but not "IT Insider":
·
SELECT x FROM Departments x WHERE x.title LIKE 'J%'
The following statement matches the title field
"JDJ" but not "JavaPro":
SELECT x FROM Departments x WHERE
x.title LIKE 'J__'
IN: Specifies
that the member must be equal to one element of the provided list. The
following two statements are synonymous:
·
SELECT x FROM Departments x WHERE x.title IN ('JDJ', 'JavaPro', 'IT
Insider')
·
SELECT x FROM Departments x WHERE x.title = 'JDJ' OR x.title = 'JavaPro' OR
x.title = 'IT Insider'
·
SELECT x FROM Departments x WHERE x.articles is empty
This statement will return all Departmentss whose articles member contains no elements.
·
SELECT x FROM Departments x WHERE x.publisher is null
This statement will return all Departments instances
whose "publisher" field is set to null.
·
SELECT x FROM Departments x WHERE NOT(x.fee = 10.0)
·
SELECT x FROM Departments x WHERE x.fee <> 10.0
This query
returns all Departments instances
whose publisher field is set
to a Company instance whose
name is "Random House".
Relations
between objects can be traversed using Java-like syntax. For example, if the Departments
class has a field named "publisher" or type Company, that relation
can be queried as follows:
As well as supporting direct field and
relation comparisons, JPQL supports a pre-defined set of functions that you can
apply.
·
SELECT x FROM Departments x WHERE CONCAT(x.title, 's') = 'JDJs'
- SUBSTRING(string, startIndex, length): Returns the part of the string argument starting at startIndex (1-based) and ending at length characters past startIndex.
·
SELECT x FROM Departments x WHERE SUBSTRING(x.title, 1, 1) = 'J'
- TRIM([LEADING | TRAILING | BOTH] [character FROM] string: Trims the specified character from either the beginning (LEADING), the ending (TRAILING), or both (BOTH) the beginning and ending of the string argument. If no trim character is specified, the space character will be trimmed.
·
SELECT x FROM Departments x WHERE TRIM(BOTH 'J' FROM x.title) = 'D'
·
SELECT x FROM Departments x WHERE LOWER(x.title) = 'jdj'
·
SELECT x FROM Departments x WHERE UPPER(x.title) = 'JAVAPRO'
·
SELECT x FROM Departments x WHERE LENGTH(x.title) = 3
- LOCATE(searchString, candidateString [, startIndex]): Returns the first index of searchString in candidateString. Positions are 1-based. If the string is not found, returns 0.
·
SELECT x FROM Departments x WHERE LOCATE('D', x.title) = 2
·
SELECT x FROM Departments x WHERE ABS(x.fee) >= 5.00
·
SELECT x FROM Departments x WHERE SQRT(x.fee) >= 1.00
·
SELECT x FROM Departments x WHERE MOD(x.fee, 10) = 0
- CURRENT_DATE: Returns the current date.
- CURRENT_TIME: Returns the current time.
- CURRENT_TIMESTAMP: Returns the current timestamp.
All JPQL
queries are polymorphic, which means the from clause of a query includes not only instances of the
specific entity class to which it refers, but all subclasses of that class as
well. The instances returned by a query include instances of the subclasses
that satisfy the query conditions. For example, the following query may return
instances of Departments, as well as Tabloid and Digest instances, where Tabloid and Digest are Departments subclasses.
SELECT x FROM Departments x WHERE x.fee
< 5
4.
SELECT x FROM Departments
x WHERE x.title = ?1 and x.fee > ?2
EntityManager em = ...
Query q = em.createQuery ("SELECT
x FROM Departments x WHERE x.title = ?1 and x.fee > ?2");
q.setParameter (1,
"JDJ").setParameter (2, 50000.0);
List<Departments> results =
q.getResultList ();
Specify
positional parameters in your JPQL string using an integer prefixed by a
question mark. You can then populate the Query object with positional parameter values via calls to the
setParameter method above.
The method returns the Query instance for
optional method chaining.
This code will
substitute JDJ for the ?1 parameter and 50000.0 for the ?2 parameter, then
execute the query with those values.
EntityManager em = ...
Query q = em.createQuery ("SELECT
x FROM Departments x WHERE x.title = :titleParam and x.fee > :feeParam");
q.setParameter ("titleParam",
"JDJ").setParameter ("feeParam", 50000.0);
List<Departments> results =
q.getResultList ();
This code
substitutes JDJ for the :titleParam parameter and 50000.0 for the :feeParam parameter,
then executes the query with those values.
Ordering
JPQL queries
may optionally contain an order by clause which
specifies one or more fields to order by when returning query results. You may
follow the order by field clause with
the asc or desc keywords, which indicate that ordering
should be ascending or descending, respectively. If the direction is omitted,
ordering is ascending by default.
SELECT x FROM Departments x order by
x.title asc, x.fee desc
The query above
returns Departments instances
sorted by their title in ascending order. In cases where the titles of two or
more Departmentss are the same, those instances will be sorted by fee in
descending order.
Aggregates
JPQL queries
can select aggregate data as well as objects. JPQL includes the min, max, avg, and count aggregates. These functions can be
used for reporting and summary queries.
The following
query will return the average of all the fees of all the Departmentss:
EntityManager em = ...
Query q = em.createQuery ("SELECT
AVG(x.fee) FROM Departments x");
Number result = (Number)
q.getSingleResult ();
The following
query will return the highest fee of all the Departmentss titled
"JDJ":
EntityManager em = ...
Query q = em.createQuery ("SELECT
MAX(x.fee) FROM Departments x WHERE x.title = 'JDJ'");
Number result = (Number)
q.getSingleResult ();
Named Queries
Query templates
can be statically declared using the NamedQuery and NamedQueries annotations.
For example:
@Entity
@NamedQueries({
@NamedQuery(name="magsOverFee",
query="SELECT x FROM Departments x WHERE x.fee > ?1"),
@NamedQuery(name="magsByTitle",
query="SELECT x FROM Departments x WHERE x.title =
:titleParam")
})
public class Departments
{
...
}
These
declarations will define two named queries called magsOverFee and magsByTitle.
You retrieve
named queries with the above EntityManager method. For example:
EntityManager em = ...
Query q = em.createNamedQuery
("magsOverFee");
q.setParameter (1, 50000.0f);
List<Departments> results =
q.getResultList ();
EntityManager em = ...
Query q = em.createNamedQuery
("magsByTitle");
q.setParameter ("titleParam",
"JDJ");
List<Departments> results =
q.getResultList ();
5.
DELETE s FROM
Subscription s WHERE s.subscriptionDate < :today
Delete by query
uses the same JPQL syntax as normal queries, with one exception: begin your
query string with the delete keyword
instead of the select keyword. To
then execute the delete, you call the following Query method:
Query q = em.createQuery ("DELETE
s FROM Subscription s WHERE s.subscriptionDate < :today");
q.setParameter ("today", new
Date ());
int deleted = q.executeUpdate ();
Update
Query q = em.createQuery ("UPDATE
Subscription s SET s.paid = :paid WHERE s.subscriptionDate < :today");
q.setParameter ("today", new
Date ());
q.setParameter ("paid",
true);
int updated = q.executeUpdate ()
No comments:
Post a Comment