A select statement is a string which consists of the following clauses:
a SELECT clause, which determines the type of the objects or values to be selected.
a FROM clause, which provides declarations that designate the domain to which the expressions specified in the other clauses of the query apply.
an optional WHERE clause, which may be used to restrict the results that are returned by the query.
an optional GROUP BY clause, which allows query results to be aggregated in terms of groups.
an optional HAVING clause, which allows filtering over aggregated groups.
an optional ORDER BY clause, which may be used to order the results that are returned by the query.
A select statement must always have a SELECT and a FROM clause. The other clauses are optional.
Example. Find all orders:
SELECT o FROM Order o
Entities are designated in query strings by their entity names. The entity name is defined by the name element of the @Entity annotation (or the entity-name XML descriptor element), and defaults to the unqualified name of the entity class. Entity names are scoped within the persistence unit and must be unique within the persistence unit.
This example assumes that the application developer provides several entity classes, representing orders, products, line items, shipping addresses, and billing addresses. The abstract schema types for these entities are Order, Product, LineItem, ShippingAddress, and BillingAddress respectively. These entities are logically in the same persistence unit, as shown in the figure below:
The entities ShippingAddress and BillingAddress each have one-to-many relationships with Order. There is also a one-to-many relationship between Order and Lineitem. The entity LineItem is related to Product in a many-to-one relationship.
Queries to select orders can be defined by navigating over the association-fields and state-fields defined by Order and LineItem. A query to find all orders with pending line items might be written as follows:
SELECT DISTINCT o FROM Order AS o JOIN o.lineItems AS l WHERE l.shipped = FALSE
This query navigates over the association-field lineItems of the abstract schema type Order to find line items, and uses the state-field shipped of LineItem to select those orders that have at least one line item that has not yet shipped. (Note that this query does not select orders that have no line items.)
Although predefined reserved identifiers, such as DISTINCT, FROM, AS, JOIN, WHERE, and FALSE appear in upper case in this example, predefined reserved identifiers are case insensitive.
The SELECT clause of this example designates the return type of this query to be of type Order.
Because the same persistence unit defines the abstract persistence schemas of the related entities, the developer can also specify a query over orders that utilizes the abstract schema type for products, and hence the state-fields and association-fields of both the abstract schema types Order and Product. For example, if the abstract schema type Product has a state-field named productType, a query over orders can be specified using this state-field. Such a query might be to find all orders for products with product type office supplies. A query for this might be as follows:
SELECT DISTINCT o FROM Order o JOIN o.lineItems l JOIN l.product p WHERE p.productType = 'office_supplies'
Because Order is related to Product by means of the relationships between Order and LineItem and between LineItem and Product, navigation using the association-fields lineItems and product is used to express the query. This query is specified by using the abstract schema name Order, which designates the abstract schema type over which the query ranges. The basis for the navigation is provided by the association-fields lineItems and product of the abstract schema types Order and LineItem respectively.
The SELECT clause denotes the query result. MORE THAN ONE value may be returned from the SELECT clause of a query.
The SELECT clause may contain one or more of the following elements: a single range variable or identification variable that ranges over an entity abstract schema type, a single-valued path expression, an aggregate select expression, a constructor expression.
SELECT c.id, c.status FROM Customer c JOIN c.orders o WHERE o.count > 100
Note that the SELECT clause must be specified to return ONLY single-valued expressions. The query below is therefore NOT VALID:
SELECT o.lineItems FROM Order AS o
The DISTINCT keyword is used to specify that duplicate values must be eliminated from the query result. If DISTINCT is not specified, duplicate values are not eliminated.
Result Type of the SELECT Clause
The type of the query result specified by the SELECT clause of a query is an entity abstract schema type, a state-field type, the result of an aggregate function, the result of a construction operation, or some sequence of these.
The result type of the SELECT clause is defined by the the result types of the select_expressions contained in it. When multiple select_expressions are used in the SELECT clause, the result of the query is of type Object, and the elements in this result correspond in order to the order of their specification in the SELECT clause and in type to the result types of each of the select_expressions.
Query q = em.createQuery("SELECT MIN(o.totalcost), MAX(o.totalcost) FROM Order o"); Object stats = (Object) q.getSingleResult();
Constructor Expressions in the SELECT Clause
A constructor may be used in the SELECT list to return one or more Java instances. The specified class is not required to be an entity or to be mapped to the database. The constructor name must be FULLY qualified:
Query q = em.createQuery("SELECT NEW OrderCostData(MIN(o.totalcost), MAX(o.totalcost)) FROM Order o"); OrderCostData stats = (OrderCostData) q.getSingleResult();
If an entity class name is specified in the SELECT NEW clause, the resulting entity instances are in the NEW state:
SELECT NEW com.acme.example.CustomerDetails(c.id, c.status, o.count) FROM Customer c JOIN c.orders o WHERE o.count > 100
Aggregate Functions in the SELECT Clause
The result of a query may be the result of an aggregate function applied to a path expression.
The following aggregate functions can be used in the SELECT clause of a query: AVG, COUNT, MAX, MIN, SUM.
For all aggregate functions except COUNT, the path expression that is the argument to the aggregate function must terminate in a state-field. The path expression argument to COUNT may terminate in either a state-field or a association-field, or the argument to COUNT may be an identification variable.
Arguments to the functions SUM and AVG must be numeric. Arguments to the functions MAX and MIN must correspond to orderable state-field types (i.e., numeric types, string types, character types, or date types).
The Java type that is contained in the result of a query using an aggregate function is as follows:
COUNT returns Long.
The following query returns the total number of magazines:
SELECT COUNT(mag) FROM Magazine mag
MAX, MIN return the type of the state-field to which they are applied.
The following query will return the highest price of all the magazines titled "JDJ":
EntityManager em = ... Query q = em.createQuery("SELECT MAX(x.price) FROM Magazine x WHERE x.title = 'JDJ'"); Number result = (Number) q.getSingleResult();
AVG returns Double.
The following query will return the average of all the prices of all the magazines:
EntityManager em = ... Query q = em.createQuery("SELECT AVG(x.price) FROM Magazine x"); Number result = (Number) q.getSingleResult();
SUM returns Long when applied to state-fields of integral types (other than BigInteger); Double when applied to state-fields of floating point types; BigInteger when applied to state-fields of type BigInteger; and BigDecimal when applied to state-fields of type BigDecimal.
The following query returns the sum total cost of all the prices from all the magazines published by 'Larry':
SELECT SUM(mag.price) FROM Publisher pub JOIN pub.magazines mag WHERE pub.firstName = 'Larry'
If SUM, AVG, MAX, or MIN is used, and there are no values to which the aggregate function can be applied, the result of the aggregate function is NULL.
If COUNT is used, and there are no values to which COUNT can be applied, the result of the aggregate function is 0.
The argument to an aggregate function may be preceded by the keyword DISTINCT to specify that duplicate values are to be eliminated before the aggregate function is applied.
Null values are eliminated before the aggregate function is applied, regardless of whether the keyword DISTINCT is specified.
The following query returns the average order quantity:
SELECT AVG(o.quantity) FROM Order o
The following query returns the total cost of the items that Mikalai Zaikin has ordered:
SELECT SUM(l.price) FROM Order o JOIN o.lineItems l JOIN o.customer c WHERE c.lastname = 'Zaikin' AND c.firstname = 'Mikalai'
The following query returns the total number of orders:
SELECT COUNT(o) FROM Order o
The following query counts the number of items in Mikalai Zaikin's order for which prices have been specified:
SELECT COUNT(l.price) FROM Order o JOIN o.lineItems l JOIN o.customer c WHERE c.lastname = 'Zaikin' AND c.firstname = 'Mikalai'
Note that this is equivalent to:
SELECT COUNT(l) FROM Order o JOIN o.lineItems l JOIN o.customer c WHERE c.lastname = 'Zaikin' AND c.firstname = 'Mikalai' AND l.price IS NOT NULL