Java Persistence Query Language
Native Query in Java Persistence Query Language
Queries may be expressed in native SQL. The result of a native query may
consist of entities, scalar values, or a combination of two.
Native query is intended to provide support for those cases where it is
necessary to use the native SQL of the target database in use. Native
queries are not expected to be portable across databases.
When multiple entites are returned by a native query, the entities must
be specified and mapped to the column results of the SQL statement in a
SqlResultSetMapping metadata definition. This result set mapping
metadata can then be used by the persistence runtime to map the JDBC
results into the expected objects.
The use of named parameters is not defined for native queries. Only
positional parameter binding for
SQL queries may be used by portable applications.
Support for joins is currently limited to single-valued relationships.
Below is several examples to show how to define SqlResultSetMapping
metadata acoording to the result return type and entity class.
I. Native query that returns entities of a single entity class
If the results of the query are limited to entites of a single entity
class, a simpler form may be used and SqlResultSetMapping metadata is
not required.
This example in which a native SQL query returns entites of a
single entity class does not use SqlResultSetMapping and the entity class that specifies the type of the
result is passed in as an argument.
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);
List orders = q.getResultList();
When executed, this query will return a Collection of all Order entites
for items named "widget".
II. Native query that returns mutiple entity types
The following query and SqlResultSetMapping metadata illustrates the
return of multiple entity types and assumes default metadata and column
name defaults.
@SqlResultSetMapping(name="OrderItemResults",
entities={
@EntityResult(entityClass=com.acme.Order.class),
@EntityResutl(entityClass=com.acme.Item.class)
}
)
@Entity
public class Employee {
...
}
Note that the SqlResultSetMapping is always defined before entity class definition.
And the following is the execution of native query.
Query q = em.createNativeQuery(
"SELECT o.id, o.quantity, o.item, i.id, i.name, i.description " +
"FROM Order o, Item i " +
"WHERE (o.quantity > 25) AND (o.item = i.id)",
"OrderItemResults");
List result = q.getResultList();
int s = result.size();
for(int i=0; i<s; i++){
Object obj = result.get(i);
Object[] objectArray = (Object[]) obj;
Object object1 = objectArray[0];
Object object2 = objectArray[1];
Order order = (Order) object1;
Item item = (Item)object2;
}
When an entity is being returned, the SQL statement should select all of
the columns that are mapped to the entity object. This should include
foreign key columns to related entities. Note that column aliases must
be used in the SQL SELECT clause where the SQL result would otherwise
contain multiple columns of the same name.
III. Native query that includes aliases in SQL statement
An example of combining multiple entity types and that includes aliases
in the SQL statement requires that the column names be explicitly mapped
to the entity fields. The FieldResult annotation is used for this purpose.
Query q = em.createNativeQuery(
"SELECT o.id AS order_id, " +
"o.quantity AS order_quantity, " +
"o.item AS order_item, " +
"i.id, i.name, i.description " +
"FROM Order o, Item i " +
"WHERE (order_quantity > 25) AND (order_item = i.id)",
"OrderItemResults");
@SqlResultSetMapping(name="OrderItemResults",
entities={
@EntityResult(entityClass=com.acme.Order.class, fields={
@FieldResult(name="id", column="order_id"),
@FieldResult(name="quantity", column="order_quantity"),
@FieldResult(name="item", column="order_item")}),
@EntityResult(entityClass=com.acme.Item.class)
}
)
IV. Native query that has scalar result types in query result
Scalar result types can be included in the query result by specifying
the ColumnResult annotation in
the metadata.
Query q = em.createNativeQuery(
"SELECT o.id AS order_id, " +
"o.quantity AS order_quantity, " +
"o.item AS order_item, " +
"i.name AS item_name, " +
"FROM Order o, Item i " +
"WHERE (order_quantity > 25) AND (order_item = i.id)",
"OrderResults");
@SqlResultSetMapping(name="OrderResults",
entities={
@EntityResult(entityClass=com.acme.Order.class, fields={
@FieldResult(name="id", column="order_id"),
@FieldResult(name="quantity", column="order_quantity"),
@FieldResult(name="item", column="order_item")})},
columns={
@ColumnResult(name="item_name")}
)
V. Entity class that has composite foreign key
When the returned entity type is the owner of a single-valued
relationship and the foreign key is a composite
foreign key (composed of multiple columns), a FieldColumn element should
be used for each
of the foreign key columns. The FieldColumn element must use a dot (".")
notation form to indicate
which column maps to each property or field of the target entity primary
key. The dot-notation form
described below is not required to be supported for any usage other than
for composite foreign keys.
If the target entity has a primary key of type IdClass, this
specification takes the form of the name of
the field or property for the relationship, followed by a dot ("."),
followed by the name of the field or
property of the primary key in the target entity.
Example:
Query q = em.createNativeQuery(
"SELECT o.id AS order_id, " +
"o.quantity AS order_quantity, " +
"o.item_id AS order_item_id, " +
"o.item_name AS order_item_name, " +
"i.id, i.name, i.description " +
"FROM Order o, Item i " +
"WHERE (order_quantity > 25) AND (order_item_id = i.id) AND
(order_item_name = i.name)",
"OrderItemResults");
@SqlResultSetMapping(name="OrderItemResults",
entities={
@EntityResult(entityClass=com.acme.Order.class, fields={
@FieldResult(name="id", column="order_id"),
@FieldResult(name="quantity", column="order_quantity"),
@FieldResult(name="item.id", column="order_item_id")}),
@FieldResult(name="item.name", column="order_item_name")}),
@EntityResult(entityClass=com.acme.Item.class)
}
)
VI. Entity class that has a primary key if type EmbeddedId
If the target entity has a primary key of type EmbeddedId, this
specification is composed of the name
of the field or property for the relationship, followed by a dot ("."),
followed by the name or the field or
property of the primary key (i.e., the name of the field or property
annotated as EmbeddedId), followed
by the name of the corresponding field or property of the embedded
primary key class.
Example:
String sqlString="SELECT e.FNAME AS EMP_FNAME, " +
"e.LNAME AS EMP_LNAME, " +
"e.FK_DEPTNO AS EMP_DEPT_DNO, "+
"e.FK_DEPTNAME AS EMP_DEPT_DNAME, "+
"d.DESCRIPTION AS DEPT_DESC "+
"FROM DEPARTMENTENTITY d, EMPLOYEEENTITY e "+
"WHERE (e.FK_DEPTNO=d.DEPTNO) AND (e.FK_DEPTNAME = d.DEPTNAME)";
@SqlResultSetMapping(name="EmployeeDepartmentResults",
entities={
@EntityResult(entityClass=pe.ejb.ejb30.entity.ejb.Employee.class, fields={
@FieldResult(name="employeePk.fname", column="EMP_FNAME"),
@FieldResult(name="employeePk.lname", column="EMP_LNAME"),
@FieldResult(name="department.departmentPk.dno", column="EMP_DEPT_DNO"),
@FieldResult(name="department.departmentPk.dname",
column="EMP_DEPT_DNAME")})},
columns={
@ColumnResult(name="DEPT_DESC")}
)
The FieldResult elements for the composite foreign key are combined to
form the primary key
EmbeddedId class for the target entity. This may then be used to
subsequently retrieve the entity if
the relationship is to be eagerly loaded.
Posted at 10:26AM Sep 01, 2006 by jielin in Sun | Comments[6]
Named Query in Java Persistence Query Language
Named queries are static queries expressed in metadata. Named queries
can be defined in Java Persistence Query Language or in SQL.
The following is an example of the definition of an Java Persistence named query:
@NamedQuery (
name = "findAllCustomerByName",
query = "SELECT c FROM Customer c WHERE c.name LIKE :custName"
)
public class Customer {
...
}
Query names are scoped to the persistence unit. Its definition should
be put before the entity class definition. The above named query is
before Customer class definition.
The following is an example of the use of a named query:
@PersistenceContext public EntityManager em;
List customers = em.createNamedQuery("findAllCustomersByName")
.setParameter("custName", "Smith")
.getResultList();
Named queries also can be group together. Use @NamedQueries annotation to group mutiple named queries together. For example,
@NamedQueries({
@NamedQuery(
name="findAllCustomersWithLike",
queryString="SELECT
OBJECT(thecust) FROM Customer thecust where thecust.name like :name and
thecust.city like :city"),
@NamedQuery(
name="findAllCustomers",
queryString="SELECT OBJECT(thecust) FROM Customer thecust")
})
Posted at 01:00PM Jun 15, 2006 by jielin in Sun | Comments[5]
More good examples in Java Persistence Query Language
- Use of query parameters
- GROUP BY and HAVING queries
- NULL handling
- IN expression
Input Parameters can be positional parameters or named parameters. Positional and named parameters may not be mixed in a single query.
For positional parameters, input parameters are numbered starting from 1. A positional parameter can be used more than once and also the order does not matter.
Some working examples,
String ejbql = "SELECT i FROM Item i WHERE i.name = ?1 AND i.itemId = ?2";
Query query = em.createQuery(ejbql);
query.setParameter(1, "foo");
query.setParameter(2, new Integer(1));
String ejbql = "SELECT i FROM Item i WHERE i.name = ?2 and i.itemId = ?1";
Query query = em.createQuery(ejbql);
query.setParameter(2, "foo");
query.setParameter(1, new Integer(1));
A named parameter is an identifier that is prefixed by the ":" symbol. Named parameters are case sensitive.
For Example,
String ejbql = "SELECT i FROM Item i WHERE i.name = :name AND i.itemId = :id";
Query query = em.createQuery(ejbql);
query.setParameter("name", "foo");
query.setParameter("id", new Integer(1));
When using setParameter() to binding input parameters, the binding values should have the same type as the input parameters. Also all input parameters must have a binding value.
II. GROUP BY and HAVING queries
The GROUP BY clause enables the aggregation of values according to a set of properties. The HAVING clause enables conditions to be specified that further restrict the query result.
The syntax of the GROUP BY and HAVING clauses is as follows:
groupby_clause ::= GROUP BY groupby_item {, groupby_item}*
groupby_item ::= single_valued_path_expression | identification_variable
having_clause ::= HAVING conditional_expression
The requirements for GROUP BY clause is that any item that appears in the SELECT clause (other than as an argument to an aggregate function) must also appear in the GROUP BY clause.
The requirement for HAVING clause is that the conditional_expression must specify over the grouping items or aggregate functions applying to grouping items.
The specification of the Java Persistence Query Language says that support for HAVING without GROUP BY is permitted but not required. So the query compiler allows a HAVING query without GROUP BY clause and generates a SQL statement. But some databases do not support this (e.g. oracle) and then the Java Persistence query results in a SQLException.
Here are some working examples:
a. Group the orders by their customer and for each group return the customer and the average totalPrice:
SELECT o.customer, AVG(o.totalPrice)
FROM Order o
GROUP BY o.customer
b. Group the customers by their country. For each group return the country and the number of customers in that group, but only if there are more than 3:
SELECT c.country, COUNT(c.country)
FROM Customer c
GROUP BY c.country HAVING COUNT(c.country) > 3
c. Group the employees with a salary over a certain limit per the name of their department, but consider only sales departments (to be more precise: where the department name starts with 'Sales'). For each group return the department name, the average and maximum salary.
SELECT e.department.name, AVG(e.salary), MAX(e.salary)
FROM Employee WHERE e.salary > :limit
GROUP BY e.department.name HAVING e.department.name LIKE 'Sales%'
III. NULL handling
NULL value handling is a little tricking. Basically, according to the specification of the Java Persistence Query Language, 'IS NULL' and '= NULL' have different semantics.
In general, the following is a brief description of NULL value semantics:
- Comparison or arithmetic operations with a NULL value always yield an unknown value.
- Two NULL values are not considered to be equal, the comparison yields an unknown value.
- Comparison or arithmetic operations with an unknown value always yield an unknown value.
- The IS NULL and IS NOT NULL operators convert a NULL state-field or single-valued association-field value into the respective TRUE or FALSE value.
SELECT p FROM Product p WHERE p.name = NULL
is legal, but it would not return any products, even if there are products without name. The reason is that the expression p.name = NULL does not evaluate to true if the name is null. It evaluates to an unknown value.
The same holds true for a query using an input parameter
SELECT p FROM Product p WHERE p.name = :name
when setting the actual value of the input parameter to null.
The correct syntax for checking whether or not the singled-valued path expression or input parameter is a NULL value is as:
{single_valued_path_expression | input_parameter} IS [NOT] NULL
For example,
SELECT p FROM Product p WHERE p.name IS NULL
will return all the products which are without name.
IV. IN expression
The syntax for the use of the comparison operator [NOT] IN in a conditional is as follows:
in_expression ::= state_field_path_expression [NOT] IN (in_item {, in_item}* | subquery)
in_item ::= literal | input_parameter
When the IN expression is used, there are three things need to keep in mind:
First, the state_field_path_expression must have a string, numeric or enum value. Other types will be not supported for IN expression.
Second, the in_item or set of in_item must have the same type as state_field_path_expression.
Third, the results of the subquery must have the same type as the state_field_path_expression.
Here are some working examples:
SELECT o FROM Order o
WHERE o.customer.name IN
(SELECT c.name FROM Customer c WHERE c.customerId = 3)
SELECT o FROM Order o
WHERE o.customer.customerId IN (1, 2)
SELECT o FROM Order o
WHERE o.customer.name IN ('foo', 'bar')
Here are some examples with wrong queries:
a. The type of in_item does not match the type of o.customer.name.
SELECT o FROM Order o WHERE o.customer.name IN (1.0)
b. The results of subquery is not match the type of o.customer.name
SELECT o from Order o where o.customer.name IN (select c from Customer c where c.customerId = 3)
Posted at 03:00PM Jun 09, 2006 by jielin in Sun | Comments[2]
Enumerated Annotation in GlassFish Persistence
Several users ran into problems using Enumerated annotation in combination with Java Persistence Query Language (JPQL). Here is an example showing user how to use @Enumerated annotation correctly.
An Enumerated annotation specifies that a persistent property or field should be persisted as an enumerated type. An enum can be mapped as either a string or an integer. There are two enumerated types: ORDINAL and STRING. If the enumerated type is not specified, the enumerated type is assumed to be ORDINAL.
Here is an example about how to define enum and use Enumerated annotation in persistence class:
In persistence class Item.java, EmployeeStatus is an enum. And the field status in Item is defined as EmployeeStatus and is mapped to an integer.
@Entity
@Table(name="ITEM_TABLE")
public class Item implements java.io.Serializable {
public enum EmployeeStatus { FULL_TIME, PART_TIME, CONTRACT };
@Id
@Column(name="ID")
private Integer itemId;
private String name;
@Enumerated(ORDINAL)
private EmployeeStatus status;
public Item() {}
public Integer getItemId() {
return itemId;
}
public void setItemId(Integer id) {
this.itemId = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public void setStatus(EmployeeStatus status) {
this.status = status;
}
public EmployeeStatus getStatus() {
return status;
}
}
When using JPQL to query status from client, below is the sample code in j2se environment.
// Create EMF for a persistence unit called j2seEnvironment.
EntityManagerFactory emf =
Persistence.createEntityManagerFactory("j2seEnvironment");
// create EM
EntityManager em = emf.createEntityManager();
// get Transaction
EntityTransaction tx = em.getTransaction();
// create Item and insert into database
Item item = new Item();
item.setItemId(new Integer(1));
item.setName("Jie Leng");
item.setStatus(Item.EmployeeStatus.FULL_TIME);
tx.begin();
em.persist(item);
tx.commit();
// run a JPQL query
String ejbql = "SELECT i FROM Item i WHERE i.status = :status";
Query query = em.createQuery(ejbql);
query.setParameter("status", Item.EmployeeStatus.FULL_TIME);
List result = query.getResultList();
I saw some users using wrong JPQL query like following code and got errors:
// wrong code
String ejbql2 = "SELECT i FROM Item i WHERE i.status = 0";
Query query2 = em.createQuery(ejbql2);
List result2 = query2.getResultList();
Since status is enum type, you can not just using integer directly in where clause.
Posted at 04:50AM May 01, 2006 by jielin in Sun | Comments[8]
How to use LIKE expression in Java Persistence Query Language
Java Persistence Query Language (JPQL) is used to define queries over entities and their persistent state. JPQL enables the application developer to specify the semantics of queries in a portable way, independent of the particular database in use in an enterprise environment.
Here comparison operator LIKE expression is further discussed because it is a bit trick in Java environment.
The syntax for the use of the comparison operator LIKE in a conditional expression is as follows:
string_expression LIKE pattern_value [ESCAPE escape_character]
The string_expression must have a string value. The pattern_value is a string literal or a string-valued input parameter in which an underscore (_) stands for any single character, a percent (%) character stands for any sequence of characters. The optional escape_character is a single-character string literal or a character-valued input parameter and is used to escape the special meaning of the underscore and percent characters in pattern_value.
For example,
aword.underscored LIKE '\_%' ESCAPE '\' is true for '_foo" and false for 'bar'
In Java environment, since '\' is also an escape character, to make the above query working, the code should be like:
// Create EMF for a persistence unit called j2seEnvironment.
EntityManagerFactory emf =
Persistence.createEntityManagerFactory("j2seEnvironment");
// create EM
EntityManager em = emf.createEntityManager();
// get Transaction
EntityTransaction tx = em.getTransaction();
// create Item and insert into database
Item item = new Item();
item.setItemId(new Integer(1));
item.setName("Jie Leng");
item.setStatus(Item.EmployeeStatus.FULL_TIME);
tx.begin();
em.persist(item);
tx.commit();
// run an EJBQL query using input parameters
String ejbql = "SELECT i from Item i WHERE i.name LIKE :pattern ESCAPE
:esc";
Query query = em.createQuery(ejbql);
query.setParameter("pattern", "\\_%");
query.setParameter("esc", '\\');
List result = query.getResultList();
As you can see, two backslashes are used in setParameter(). The first backslash is processed by Java compiler and the second backslash is processed by JPQL compiler. And the result is to match string having '_'.
Here is another piece of code working without using input parameters:
String ejbql = "SELECT i FROM Item i WHERE i.name LIKE '\\_%' ESCAPE '\\'";
Query query = em.createQuery(ejbql);
List result = query.getResultList();
If your code is like:
String ejbql = "SELECT i FROM Item i WHERE i.name LIKE '\_%' ESCAPE '\'";
Or like:
// run an EJBQL query using input parameters
String ejbql = "SELECT i from Item i WHERE i.name LIKE :pattern ESCAPE
:esc";
Query query = em.createQuery(ejbql);
query.setParameter("pattern", "\_%");
query.setParameter("esc", '\');
An illegal escape character error is thrown.
Posted at 05:00AM Apr 07, 2006 by jielin in Sun | Comments[3]