Red Hat Summit Red Hat SummitSupportConsoleDevelopersStart a trialContact

10.5. Querying

10.5.1. About Querying
Copy link

If you do not know the identifiers of the objects you are looking for, you need a query. Hibernate supports an easy-to-use but powerful object oriented query language (HQL). For programmatic query creation, Hibernate supports a sophisticated Criteria and Example query feature (QBC and QBE). You can also express your query in the native SQL of your database, with optional support from Hibernate for result set conversion into objects.
Report a bug

10.5.2. Executing Queries
Copy link

HQL and native SQL queries are represented with an instance of org.hibernate.Query. This interface offers methods for parameter binding, result set handling, and for the execution of the actual query. You always obtain a Query using the current Session: 
List cats = session.createQuery(
    "from Cat as cat where cat.birthdate < ?")
    .setDate(0, date)
    .list();

List mothers = session.createQuery(
    "select mother from Cat as cat join cat.mother as mother where cat.name = ?")
    .setString(0, name)
    .list();

List kittens = session.createQuery(
    "from Cat as cat where cat.mother = ?")
    .setEntity(0, pk)
    .list();

Cat mother = (Cat) session.createQuery(
    "select cat.mother from Cat as cat where cat = ?")
    .setEntity(0, izi)
    .uniqueResult();

Query mothersWithKittens = session.createQuery(
    "select mother from Cat as mother left join fetch mother.kittens");
Set uniqueMothers = new HashSet(mothersWithKittens.list());
Copy to Clipboard Toggle word wrap
A query is usually executed by invoking list(). The result of the query will be loaded completely into a collection in memory. Entity instances retrieved by a query are in a persistent state. The uniqueResult() method offers a shortcut if you know your query will only return a single object. Queries that make use of eager fetching of collections usually return duplicates of the root objects, but with their collections initialized. You can filter these duplicates through a Set.
Report a bug

10.5.3. Iterating Results
Copy link

Occasionally, you might be able to achieve better performance by executing the query using the iterate() method. This will usually be the case if you expect that the actual entity instances returned by the query will already be in the session or second-level cache. If they are not already cached, iterate() will be slower than list() and might require many database hits for a simple query, usually 1 for the initial select which only returns identifiers, and n additional selects to initialize the actual instances. 
// fetch ids
Iterator iter = sess.createQuery("from eg.Qux q order by q.likeliness").iterate();
while ( iter.hasNext() ) {
    Qux qux = (Qux) iter.next();  // fetch the object
    // something we couldn't express in the query
    if ( qux.calculateComplicatedAlgorithm() ) {
        // delete the current instance
        iter.remove();
        // don't need to process the rest
        break;
    }
}
Copy to Clipboard Toggle word wrap
Report a bug

10.5.4. Queries that Return Tuples
Copy link

Hibernate queries sometimes return tuples of objects. Each tuple is returned as an array: 
Iterator kittensAndMothers = sess.createQuery(
            "select kitten, mother from Cat kitten join kitten.mother mother")
            .list()
            .iterator();

while ( kittensAndMothers.hasNext() ) {
    Object[] tuple = (Object[]) kittensAndMothers.next();
    Cat kitten = (Cat) tuple[0];
    Cat mother = (Cat) tuple[1];
    ....
}
Copy to Clipboard Toggle word wrap
Report a bug

10.5.5. Scalar Results
Copy link

Queries can specify a property of a class in the select clause. They can even call SQL aggregate functions. Properties or aggregates are considered "scalar" results and not entities in persistent state. 
Iterator results = sess.createQuery(
        "select cat.color, min(cat.birthdate), count(cat) from Cat cat " +
        "group by cat.color")
        .list()
        .iterator();

while ( results.hasNext() ) {
    Object[] row = (Object[]) results.next();
    Color type = (Color) row[0];
    Date oldest = (Date) row[1];
    Integer count = (Integer) row[2];
    .....
}
Copy to Clipboard Toggle word wrap
Report a bug

10.5.6. Bind Parameters
Copy link

Methods on Query are provided for binding values to named parameters or JDBC-style ? parameters. Contrary to JDBC, Hibernate numbers parameters from zero. Named parameters are identifiers of the form :name in the query string. The advantages of named parameters are as follows:
  • named parameters are insensitive to the order they occur in the query string
  • they can occur multiple times in the same query
  • they are self-documenting 
//named parameter (preferred)
Query q = sess.createQuery("from DomesticCat cat where cat.name = :name");
q.setString("name", "Fritz");
Iterator cats = q.iterate();
Copy to Clipboard Toggle word wrap 
//positional parameter
Query q = sess.createQuery("from DomesticCat cat where cat.name = ?");
q.setString(0, "Izi");
Iterator cats = q.iterate();
Copy to Clipboard Toggle word wrap 
//named parameter list
List names = new ArrayList();
names.add("Izi");
names.add("Fritz");
Query q = sess.createQuery("from DomesticCat cat where cat.name in (:namesList)");
q.setParameterList("namesList", names);
List cats = q.list();
Copy to Clipboard Toggle word wrap
Report a bug

10.5.7. Pagination
Copy link

If you need to specify bounds upon your result set, that is, the maximum number of rows you want to retrieve and/or the first row you want to retrieve, you can use methods of the Query interface: 
Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();
Copy to Clipboard Toggle word wrap
Hibernate knows how to translate this limit query into the native SQL of your DBMS.
Report a bug

10.5.8. Scrollable Iteration
Copy link

If your JDBC driver supports scrollable ResultSets, the Query interface can be used to obtain a ScrollableResults object that allows flexible navigation of the query results. 
Query q = sess.createQuery("select cat.name, cat from DomesticCat cat " +
                            "order by cat.name");
ScrollableResults cats = q.scroll();
if ( cats.first() ) {

    // find the first name on each page of an alphabetical list of cats by name
    firstNamesOfPages = new ArrayList();
    do {
        String name = cats.getString(0);
        firstNamesOfPages.add(name);
    }
    while ( cats.scroll(PAGE_SIZE) );

    // Now get the first page of cats
    pageOfCats = new ArrayList();
    cats.beforeFirst();
    int i=0;
    while( ( PAGE_SIZE > i++ ) && cats.next() ) pageOfCats.add( cats.get(1) );

}
cats.close();
Copy to Clipboard Toggle word wrap
Note that an open database connection and cursor is required for this functionality. Use setMaxResult()/setFirstResult() if you need offline pagination functionality.
Report a bug

10.5.9. Externalizing Named Queries
Copy link

You can also define named queries in the mapping document. Remember to use a CDATA section if your query contains characters that could be interpreted as markup. 
<query name="ByNameAndMaximumWeight"><![CDATA[
    from eg.DomesticCat as cat
        where cat.name = ?
        and cat.weight > ?
] ]></query>
Copy to Clipboard Toggle word wrap
Parameter binding and executing is done programatically: 
Query q = sess.getNamedQuery("ByNameAndMaximumWeight");
q.setString(0, name);
q.setInteger(1, minWeight);
List cats = q.list();
Copy to Clipboard Toggle word wrap
The actual program code is independent of the query language that is used. You can also define native SQL queries in metadata, or migrate existing queries to Hibernate by placing them in mapping files.
Also note that a query declaration inside a <hibernate-mapping> element requires a global unique name for the query, while a query declaration inside a <class> element is made unique automatically by prepending the fully qualified name of the class. For example eg.Cat.ByNameAndMaximumWeight.
Report a bug

10.5.10. Filtering Collections
Copy link

A collection filter is a special type of query that can be applied to a persistent collection or array. The query string can refer to this, meaning the current collection element. 
Collection blackKittens = session.createFilter(
    pk.getKittens(), 
    "where this.color = ?")
    .setParameter( Color.BLACK, Hibernate.custom(ColorUserType.class) )
    .list()
Copy to Clipboard Toggle word wrap
The returned collection is considered a bag that is a copy of the given collection. The original collection is not modified. This is contrary to the implication of the name "filter", but consistent with expected behavior.
Observe that filters do not require a from clause, although they can have one if required. Filters are not limited to returning the collection elements themselves. 
Collection blackKittenMates = session.createFilter(
    pk.getKittens(), 
    "select this.mate where this.color = eg.Color.BLACK.intValue")
    .list();
Copy to Clipboard Toggle word wrap
Even an empty filter query is useful, e.g. to load a subset of elements in a large collection: 
Collection tenKittens = session.createFilter(
    mother.getKittens(), "")
    .setFirstResult(0).setMaxResults(10)
    .list();
Copy to Clipboard Toggle word wrap
Report a bug

10.5.11. Criteria Queries
Copy link

HQL is extremely powerful, but some developers prefer to build queries dynamically using an object-oriented API, rather than building query strings. Hibernate provides an intuitive Criteria query API for these cases: 
Criteria crit = session.createCriteria(Cat.class);
crit.add( Restrictions.eq( "color", eg.Color.BLACK ) );
crit.setMaxResults(10);
List cats = crit.list();
Copy to Clipboard Toggle word wrap
Report a bug

10.5.12. Queries in Native SQL
Copy link

You can express a query in SQL, using createSQLQuery() and let Hibernate manage the mapping from result sets to objects. You can at any time call session.connection() and use the JDBC Connection directly. If you choose to use the Hibernate API, you must enclose SQL aliases in braces: 
List cats = session.createSQLQuery("SELECT {cat.*} FROM CAT {cat} WHERE ROWNUM<10")
    .addEntity("cat", Cat.class)
.list();
Copy to Clipboard Toggle word wrap 
List cats = session.createSQLQuery(
    "SELECT {cat}.ID AS {cat.id}, {cat}.SEX AS {cat.sex}, " +
           "{cat}.MATE AS {cat.mate}, {cat}.SUBCLASS AS {cat.class}, ... " +
    "FROM CAT {cat} WHERE ROWNUM<10")
    .addEntity("cat", Cat.class)
.list();
Copy to Clipboard Toggle word wrap
SQL queries can contain named and positional parameters, just like Hibernate queries. .
Report a bug
Back to top
Red Hat logoGithubredditYoutubeTwitter

Learn

Try, buy, & sell

Communities

About Red Hat Documentation

We help Red Hat users innovate and achieve their goals with our products and services with content they can trust. Explore our recent updates.

Making open source more inclusive

Red Hat is committed to replacing problematic language in our code, documentation, and web properties. For more details, see the Red Hat Blog.

About Red Hat

We deliver hardened solutions that make it easier for enterprises to work across platforms and environments, from the core datacenter to the network edge.

Theme

© 2025 Red Hat