Chapter 9. Queries and EJB QL(查询和EJB QL)
Querying is a fundamental feature of all relational databases. It allows you to pull complex reports, calculations, and information about intricately(杂乱的) related objects from persistence storage. Queries in Java Persistence are done using both the EJB QL query
language and native Structured Query Language (SQL).
查询是所有关系数据库的一个基本特征.他可以让你从持久化存储中得到复杂的报表,统计和复杂关系对象的信息.在java持久化中可以采用EJB QL查询语言,也可以是本地的结构化查询语言进行查询.
EJB QL is a declarative query language similar to the SQL used in relational databases, but it is tailored to work with Java objects rather than a relational schema. To execute queries, you reference the properties and relationships of your entity beans
rather than the underlying tables and columns these objects are mapped to. When an EJQ QL query is executed, the entity manager uses the information you provided through the mapping metadata, discussed in the previous two chapters, and automatically translates
it to one (or several) native SQL query. This generated native SQL is then executed through a JDBC driver directly on your database. Since EJB QL is a query language that represents Java objects, it is portable(轻便的) across vendor database implementations because
the entity manager handles the conversion to raw SQL for you.
EJB QL是一种公布了的查询语言,与关系数据库中的SQL很类似,但是它是专门用来协同java对象来工作的,而不是关系模式.执行查询时,你操作的是实体Bean的属性和关系,而不是它们映射到数据库中的表和列.
The EJB QL language is easy for developers to learn yet precise(精确的,准确的) enough to be interpreted(翻译,解释为) into native database code. This rich and flexible query language empowers(使能够) developers while executing in fast native code at runtime. Plus, because
EJB QL is object-oriented, queries are usually much more compact and readable than their SQL equivalent. EJB QL existed in the EJB 2.1 specification, and it is really the only feature that survived in the new release. Although it was well-formed, EJB QL in
EJB 2.1 was incomplete, forcing developers to escape to JDBC or write really inefficient code. EJB QL has been greatly improved and expanded to be more parallel to SQL and should now meet most of your needs. Things like projection, GROUP BY, and HAVING have
been added, as well as bulk(批量) updates and deletes.
Sometimes, though, EJB QL is not enough. Because it is a portable query language, it cannot always take advantage of specific proprietary features of your database vendor. EJB QL does not allow you to execute stored procedures, for instance. The EJB 3.0
Expert Group foresaw(预见) the need for this and has provided an API to map native SQL calls to your entity beans.
EJB QL and native SQL queries are executed through the javax.persistence.Query interface. The Query interface is very analogous(类似) to the java.sql.PreparedStatement interface. This Query API gives you methods for paging your result set, as well as passing
Java parameters to your query. Queries can be predeclared through annotations or XML, or created dynamically at runtime through EntityManager APIs.
9.1. Query API(查询API)
A query in Java Persistence is a full-blown Java interface that you obtain at runtime from the entity manager:
package javax.persistence;
public interface Query {
public List getResultList( );
public Object getSingleResult( );
public int executeUpdate( );
public Query setMaxResults(int maxResult);
public Query setFirstResult(int startPosition);
public Query setHint(String hintName, Object value);
public Query setParameter(String name, Object value);
public Query setParameter(String name, Date value, TemporalType temporalType);
public Query setParameter(String name, Calendar value, TemporalType temporalType);
public Query setParameter(int position, Object value);
public Query setParameter(int position, Date value, TemporalType temporalType);
public Query setParameter(int position, Calendar value, TemporalType temporalType);
public Query setFlushMode(FlushModeType flushMode);
}
Queries are created using these EntityManager methods:
package javax.persistence;
public interface EntityManager {
public Query createQuery(String ejbqlString);
public Query createNamedQuery(String name);
public Query createNativeQuery(String sqlString);
public Query createNativeQuery(String sqlString, Class resultClass);
public Query createNativeQuery(String sqlString, String resultSetMapping);
}
Let's first look at using EntityManager.createQuery( ) to create a query dynamically at runtime:
try {
Query query = entityManager.creatQuery(
"from Customer c where c.firstName='Bill' and c.lastName='Burke'");
Customer cust = (Customer)query.getSingleResult( );
} catch (EntityNotFoundException notFound) {
} catch (NonUniqueResultException nonUnique) {
}
The previous query looks for a single, unique Customer entity named Bill Burke. The query is executed when the getSingleResult( ) method is called. This method expects that the call will return only one result. If no result is returned, then the method throws
a javax.persistence.EntityNotFoundException runtime exception. If more than one result is found, then a javax.persistence.NonUniqueResultException runtime exception is thrown. Since both of these exceptions are RuntimeException s, the example code is not required
to have a full try/catch block.
There is a good chance that the NonUniqueResultException would be thrown by this example. Believe it or not, there are a lot of Bill Burkes in the world (try Googling him), and this name seems to be as common as John Smith is in the U.S. You can change the
query to use the getresultList( ) method to obtain a collection of results:
Query query = entityManager.creatQuery(
"from Customer c where c.firstName='Bill' and c.lastName='Burke'");
java.util.List bills = query.getResultList( );
The getresultList( ) method does not throw an exception if there are no Bill Burkes. The returned list would just be empty.
9.1.1. Parameters(参数)
Much like a java.sql.PreparedStatement in JDBC, EJB QL allows you to specify parameters in query declarations so that you can reuse and execute the query multiple times on different sets of parameters. Two syntaxes are provided: named parameters and positional
parameters. Let's modify our earlier Customer query to take both last name and first name as named parameters:
public List findByName(String first, String last) {
Query query = entityManager.createQuery(
"from Customer c where c.firstName=:first and c.lastName=:last");
query.setParameter("first", first);
query.setParameter("last", last);
return query.getResultList( );
}
The : character followed by the parameter name is used in EJB QL statements to identify a named parameter. The setParameter( ) method in this example takes the name of the parameter first, and then the actual value. EJB QL also supports positional parameters.
Let's modify the previous example to see this mode in action:
public List findByName(String first, String last) {
Query query = entityManager.createQuery(
"from Customer c where c.firstName=?1 and c.lastName=?2");
query.setParameter(1, first);
query.setParameter(2, last);
return query.getResultList( );
}
Instead of a string named parameter, setParameter( ) also takes a numeric parameter position. The ? character is used instead of the : character that is used with named parameters.
Using named parameters over positional parameters is recommended as the EJB QL code becomes self-documenting. This is especially useful when working with predeclared queries.
9.1.2. Date Parameters(日期参数)
If you need to pass java.util.Date or java.util.Calendar parameters into a query, you need to use special setParameter methods:
package javax.persistence;
public enum TemporalType {
DATE, //java.sql.Date
TIME, //java.sql.Time
TIMESTAMP //java.sql.Timestamp
}
public interface Query
{
Query setParameter(String name, java.util.Date value, TemporalType temporalType);
Query setParameter(String name, Calendar value, TemporalType temporalType);
Query setParameter(int position, Date value, TemporalType temporalType);
Query setParameter(int position, Calendar value, TemporalType temporalType);
}
A Date or Calendar object can represent a real date, a time of day, or a numeric timestamp. Because these object types can represent different things at the same time, you need to tell your Query object how it should use these parameters. The javax.persistence.TemporalType
passed in as a parameter to the setParameter( ) method tells the Query interface what database type to use when converting the java.util.Date or java.util.Calendar parameter to a native SQL type.
9.1.3. Paging Results(分页查询结果)
Sometimes an executed query returns too many results. For instance, maybe we're displaying a list of customers on a web page. The web page can display only so many customers, and maybe there are thousands or even millions of customers in the database. The Query
API has two built-in functions to solve this type of scenario: setMaxResults( ) and setFirstResult( ):
public List getCustomers(int max, int index) {
Query query = entityManager.createQuery("from Customer c");
return query.setMaxResults(max).
setFirstResult(index).
getResultList( );
}
The getCustomers( ) method executes a query that obtains all customers from the database. We limit the number of customers it returns by using the setMaxResults( ) method, passing in the max method parameter. The method is also designed so that you can define
an arbitrary(任意的) set of results that you want returned by the execution of the query. The setFirstResult( ) method tells the query what position in the executed query's result set you want returned. So, if you had a max result of 3 and a first result of 5,
customers 5, 6, and 7 would be returned. We set this value in the getCustomers( ) method with the index parameter. Let's take this method and write a code fragment that lists all customers in the database:
List results;
int first = 0;
int max = 10;
do {
results = getCustomers(max, first);
Iterator it = results.iterator( );
while (it.hasNext( )) {
Customer c = (Customer)it.next( );
System.out.println(c.getFirstName() + " " + c.getLastName( ));
}
entityManager.clear( );
first = first + results.getSize( );
} while (results.size( ) > 0);
In this example, we loop through all customers in the database and output their first and last names to the system output stream. If we had thousands or even millions of customers in the database, we could quickly run out of memory, as each execution of
the getCustomers( ) method would return customers that were still managed by the entity manager. So, after we are finished outputting a block of customers, we call EntityManager.clear( ) to detach these customers and let them be garbage-collected by the Java
VM. Use this pattern when you need to deal with a lot of entity objects within the same transaction.
9.1.4. Hints
Some Java Persistence vendors will provide additional add-on features that you can take advantage of when executing a query. For instance, the JBoss EJB 3.0 implementation allows you to define a timeout for the query. These types of add-on features can be specified
as hints using the setHint( ) method on the query. Here's an example of defining a JBoss query timeout using hints:
Query query = manager.createQuery("from Customer c");
query.setHint("org.hibernate.timeout", 1000);
The setHint( ) method takes a string name and an arbitrary object parameter.
9.1.5. FlushMode
In Chapter 5, we talked about flushing and flush modes. Sometimes you would like a different flush mode to be enforced for the duration(持续时间) of a query. For instance, maybe a query wants to make sure that the entity manager does not flush before the query
is executed (since the default value implies that the entity manager can). The Query interface provides a setFlushMode( ) method for this particular purpose:
Query query = manager.createQuery("from Customer c");
query.setFlushMode(FlushModeType.COMMIT);
In this example, we're telling the persistence provider that we do not want the query to do any automatic flushing before this particular query is executed. Using this commit mode can be dangerous if some correlated dirty entities are in the persistence
context. You might return wrong entities from your query. Therefore, it is recommended that you use the FlushModeType.AUTO.