Query API






Query 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.

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.


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.

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.

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.

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.



 Python   SQL   Java   php   Perl 
 game development   web development   internet   *nix   graphics   hardware 
 telecommunications   C++ 
 Flash   Active Directory   Windows