Query API for Queries and EJB QL (query and EJB QL)

Chapter 9. Queries and EJB QL (query and EJB QL)
Querying is a fundamental feature the all relational databases. It allows to pull complex reports, calculations, and information about intricately (messy) related objects from Persisten CE storage. Queries in Java persistence are do using both the EJB QL query language and native Structured Query Language (SQL). A
query is a basic feature of all relational databases. He can get you from the persistent storage for complex reports, statistics, and complex relational objects. In Java persistence, you can use the EJB QL query language or the local Structured Query language for querying.

EJB QL is a declarative query language similar to of SQL used in relational databases, but it are tailored to work with Java objects rather than a relational schema. To execute queries, reference the properties and relationships of your entity beans rather than the underlying tables and columns these objects are mapped to. When a ejq QL query is executed, the Entity manager uses the information you provided through the mapping metadata Ssed in the previous two chapters, and automatically translates it to one (or several) native SQL query. This generated native SQL are then executed through a JDBC driver directly on your database. Since EJB QL is a query language this represents Java objects, it is portable (lightweight) across vendor database implementations Because the entity manager handles the conversion to raw SQL for you. The
EJB QL is a published query language, similar to SQL in a relational database, but it is designed to work with Java objects rather than relational schemas. When you execute a query, you are manipulating the properties and relationships of the entity bean, rather than the tables and columns they map to the database.

The EJB QL language is easy for developers to learn yet precise (exact, accurate) enough to be interpreted (translated, interpreted as) into NAT Ive database code. This rich and flexible query Language empowers (enables) developers while executing to 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 to the new release. Although it is well-formed, EJB QL in EJB 2.1 is incomplete, forcing developers to escape to JDBC or write really ineffi Cient code. EJB QL has been greatly improved and expanded to is more parallel to SQL and should now meet most of your. Things like projection, GROUP by, and has have been added, as as and as bulk (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 Vendor. EJB QL does not allow to execute stored procedures, for instance. The EJB 3.0 Expert Group foresaw (foreseen) the need for this and has provided a API to map native SQL calls to your entity bean S.

EJB QL and native SQL queries are executed through the javax.persistence.Query. The Query interface is very analogous (similar) to the Java.sql.PreparedStatement interface. This query API gives your methods for paging your result set, as as a passing Java parameters to your query. Queries can be predeclared through annotations or XML, or created dynamically in runtime through APIs.

9.1. Query API (Query API)
A query in Java persistence is a full-blown Java interface, obtain at runtime to 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-look in 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 () is called. This method expects that call would return only one result. If No is returned, then the method throws a Javax.persistence.EntityNotFoundException runtime exception. If more than one to found, then a javax.persistence.NonUniqueResultException runtime exception is thrown. Since Both of these exceptions are runtimeexception s, the example code isn't required to have a full try/catch block.

There is a good chance the nonuniqueresultexception would to be thrown by this example. Believe it or not, there are a lot of Bill Burkes into the world (try Googling him), and this name seems to is as common as John Smith is in the U.S. 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 is not throw a exception if there are no Bill burkes. The returned list would just be empty.

9.1.1. Parameters (Parameters)
Much like a java.sql.PreparedStatement in JDBC, EJB QL allows you to specify parameters in query declarations so 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 the last name and name as named parameters:

Public List Findbyname (string A, string last) {
Query query = entitymanager.createquery (
"From Customer C where C.firstname=:first and C.lastname=:last");
Query.setparameter ("A", the "a");
Query.setparameter ("Last", last);
return Query.getresultlist ();
}

The:character followed by the parameter name are used in EJB QL statements to identify a named. The Setparameter () method In this example takes the name of the parameter a, and then the actual value. EJB QL also supports positional parameters. Let's modify the previous example to the?

Public List Findbyname (string A, string last) {
Query query = entitymanager.createquery (
"From Customer C where c.firstname=?1 and c.lastname=?2");
Query.setparameter (1, a);
Query.setparameter (2, last);
return Query.getresultlist ();
}

Instead of a string named parameter, Setparameter () also takes a numeric parameter. The? Character is used instead of the:character this 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 as working with predeclared queries.

9.1.2. Date Parameters (date parameter)
If you are need to pass java.util.Date or Java.util.Calendar parameters into a query, you are need to use special Setparameter met Hods:

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 in the same time, your need to tell your Query object how it Shou LD use these parameters. The javax.persistence.TemporalType passed in as a parameter to the Setparameter () method tells the Query interface what D Atabase type to converting the java.util.Date or Java.util.Calendar parameter to a native SQL type.

9.1.3. Paging Results (paged query 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 Databa Se. 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 which 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, can define an arbitrary (arbitrary) set of results that want N of the query. The Setfirstresult () method tells the query what position in the executed query ' s result set for you want returned. So, if you are had a max result of 3 and a of the 5, customers 5, 6, and 7 would be returned. We set this value into the GetCustomers () method with the index parameter. Let's take this method and write a code fragment which lists all customers in the database:

List results;
int-i = 0;
int max = 10;

do {
Results = GetCustomers (max, a);
Iterator it = Results.iterator ();
while (It.hasnext ()) {
Customer C = (customer) it.next ();
System.out.println (C.getfirstname () + "" + c.getlastname ());
}
Entitymanager.clear ();
The Results.getsize ();
while (Results.size () > 0);

In this example, we loop through all customers in the database and output their The-I and last names to the system OUTP UT stream. If we had thousands or even millions of customers in the database, we could quickly run out of memory, as each execution O f the GetCustomers () method would return customers this were still managed by the entity manager. So, after we are finished outputting a blocks of customers, we call Entitymanager.clear () to detach these customers and LE T them be garbage-collected by the Java VM. Use this pattern when you are need to deal with a lot of entity objects within the same transaction.

9.1.4. Hints
Some Java Persistence vendors'll provide additional add-on features that you can take advantage the when executing a quer Y. For instance, the JBoss EJB 3.0 implementation allows your to define a timeout for the query. These types of add-on features can is specified as hints using the Sethint () method on the query. Here ' s a 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 a 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 is enforced for the duration (duration) of a query. For instance, maybe a query wants to make sure this entity manager does not flush before the "query is executed" (since The default value implies that Entity manager can. The Query interface provides a Setflushmode () method to this particular purpose:

Query query = Manager.createquery ("from Customer C");
Query.setflushmode (Flushmodetype.commit);

In this example, we ' re telling the persistence provider we don't want the query to does any automatic flushing This particular the query is executed. Using This commit mode can is dangerous if some correlated dirty entities are in the persistence context. You are might return wrong entities from your query. Therefore, it is recommended the Flushmodetype.auto.