3.4. Querying objects

If you do not know the identifier values of the objects you are looking for, you need a query. The Hibernate EntityManager implementation supports an easy-to-use but powerful object-oriented query language (EJB3-QL) which has been inspired by HQL (and vice-versa). Both query languages are portable across databases, they use entity and property names as identifiers (instead of table and column names). You may also express your query in the native SQL of your database, with optional support from EJB3 for result set conversion into Java business objects.

3.4.1. Executing queries

EJB3QL and SQL queries are represented by an instance of javax.persistence.Query. This interface offers methods for parameter binding, result set handling, and for execution of the query. Queries are always created using the current entity manager.
A query is usually executed by invoking getResultList(). This method loads the resulting instances of the query completely into memory. Entity instances retrieved by a query are in persistent state. The getSingleResult() method offers a shortcut if you know your query will only return a single object.

3.4.1.1. Projection

An EJB3QL query queries can return tuples of objects if projection is used. Each result tuple is returned as an object array: 
Iterator<Cat[]> kittensAndMothers = 
  em.createQuery("select kitten, mother from Cat kitten join kitten.mother mother").getResultList().iterator();
  while (kittensAndMothers.hasNext()) {
     Cat[] tuple = kittensAndMothers.next();
     Cat kitten = tuple[0];
     Cat mother = tuple[1];
  } 
    ....
}

3.4.1.2. Scalar results

Queries may specify a particular property of an entity in the select clause, instead of an entity alias. You may call SQL aggregate functions as well. Returned non-transactional objects or aggregation results are considered "scalar" results and are not entities in persistent state (in other words, they are considered "read only"): 
Iterator<Object[]> results = em.createQuery(
        "select cat.color, min(cat.birthdate), count(cat) from Cat cat " +
        "group by cat.color")
        .getResultList()
        .iterator();

while ( results.hasNext() ) {
    Object[] row = results.next();
    Color type = (Color) row[0];
    Date oldest = (Date) row[1];
    Integer count = (Integer) row[2];
    .....
}

3.4.1.3. Bind parameters

Both named and positional query parameters are supported, the Query API offers several methods to bind arguments. The EJB3 specification numbers positional parameters from one. Named parameters are identifiers of the form :paramname in the query string. Named parameters should be preferred, they are more robust and easier to read and understand: 
// Named parameter (preferred)
Query q = em.createQuery("select cat from DomesticCat cat where cat.name = :name");
q.setParameter("name", "Fritz");
List cats = q.getResultList();

// Positional parameter
Query q = em.createQuery("select cat from DomesticCat cat where cat.name = ?1");
q.setParameter(1, "Izi");
List cats = q.getResultList();

// Named parameter list
List names = new ArrayList();
names.add("Izi");
names.add("Fritz");
Query q = em.createQuery("select cat from DomesticCat cat where cat.name in (:namesList)");
q.setParameter("namesList", names);
List cats = q.getResultList();

3.4.1.4. Pagination

If you need to specify bounds upon your result set (the maximum number of rows you want to retrieve and/or the first row you want to retrieve), use the following methods: 
Query q = em.createQuery("select cat from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.getResultList(); //return cats from the 20th position to 29th
Hibernate knows how to translate this limit query into the native SQL of your DBMS.

3.4.1.5. Externalizing named queries

You may also define named queries through annotations: 
@javax.persistence.NamedQuery(name="eg.DomesticCat.by.name.and.minimum.weight", 
  query="select cat from eg.DomesticCat as cat  where cat.name = ?1 and cat.weight > ?2")
Parameters are bound programmatically to the named query, before it is executed: 
Query q = em.createNamedQuery("eg.DomesticCat.by.name.and.minimum.weight");
q.setParameter(1, name);
q.setParameter(2, minWeight);
List cats = q.getResultList();
Note that the actual program code is independent of the query language that is used, you may also define native SQL queries in metadata, or use Hibernate's native facilities by placing them in XML mapping files.

3.4.1.6. Native queries

You may express a query in SQL, using createNativeQuery() and let Hibernate take care mapping from JDBC result sets to business objects. Use the @SqlResultSetMapping (please see the Hibernate Annotations reference documentation on how to map a SQL resultset mapping) or the entity mapping (if the column names of the query result are the same as the names declared in the entity mapping; remember that all entity columns have to be returned for this mechanism to work): 
@SqlResultSetMapping(name="getItem", entities = 
        @EntityResult(entityClass=org.hibernate.ejb.test.Item.class, fields= {
            @FieldResult(name="name", column="itemname"),
            @FieldResult(name="descr", column="itemdescription")
        })
)

Query q = em.createNativeQuery("select name as itemname, descr as itemdescription from Item", "getItem");
item = (Item) q.getSingleResult(); //from a resultset

Query q = em.createNativeQuery("select * from Item", Item.class);
item = (Item) q.getSingleResult(); //from a class columns names match the mapping

Note

For more information about scalar support in named queries, please refers to the Hibernate Annotations documentation

3.4.1.7. Query hints

Query hints (for performance optimization, usually) are implementation specific. Hints are declared using the query.setHint(String name, Object value) method, or through the @Named(Native)Query(hints) annotation Note that these are not SQL query hints! The Hibernate EJB3 implementation offers the following query hints:

Table 3.1. Hibernate query hints

Hint Description
org.hibernate.timeout Query timeout in seconds ( eg. new Integer(10) )
org.hibernate.fetchSize Number of rows fetched by the JDBC driver per roundtrip ( eg. new Integer(50) )
org.hibernate.comment Add a comment to the SQL query, useful for the DBA ( e.g. new String("fetch all orders in 1 statement") )
org.hibernate.cacheable Whether or not a query is cacheable ( eg. new Boolean(true) ), defaults to false
org.hibernate.cacheMode Override the cache mode for this query ( eg. CacheMode.REFRESH )
org.hibernate.cacheRegion Cache region of this query ( eg. new String("regionName") )
org.hibernate.readOnly Entities retrieved by this query will be loaded in a read-only mode where Hibernate will never dirty-check them or make changes persistent ( eg. new Boolean(true) ), default to false
org.hibernate.flushMode Flush mode used for this query
org.hibernate.cacheMode Cache mode used for this query
The value object accept both the native type or its string equivalent (eg. CaheMode.REFRESH or “REFRESH”). Please refer to the Hibernate reference documentation for more information.