Hibernate Query by example

With Hibernate’s and JPA powerful query facilities, you can express almost everything you typically need to express in SQL, but in object-oriented terms—using classes and properties of classes. In this article, we will show you how to create and execute a Query with JPA and the Hibernate API.

JPA represents a query with a javax.persistence.Query interface. You create queries in different ways:

  • You can write the query in the Java Persistence Query Language (JPQL)
  • You can construct it with the CriteriaBuilder and CriteriaQuery APIs
  • Finally, you can use plain SQL for your queries

Using the Query interface

Firstly an example. Say you want to retrieve an Account from the database. Here is an example query:

@PersistenceContext 
private EntityManager em;	


public List<Account> findAccounts()  
{
   Query query = em.createQuery("from Account a");
   List<Item> items = query.getResultList();
   return items
}

This query uses JPQL which should be familiar to anyone with SQL experience. Instead of table and column names, JPQL relies on entity class and property names. Except for these class and property names, JPQL is case-insensitive, so it doesn’t matter whether you write SeLEct or select.

Before JPA existed, Hibernate applications, used the Hibernate Query Language (HQL) to execute a Query:

Query query = session.createQuery("from Account where code = '7277' ");

There are no syntactical differences between HQL and JPQL. However, to run a HQL Query you will need the Hibernate Session object reference, as you can see from the above snippet.

Using parameters in your Query

If you don’t need to return a full Table, you will include some parameters in your query. Here is how to bind a parameter to a column of your WHERE clause:

Query query = em.createQuery("FROM Account WHERE name= :name");
query.setParameter("name", name);
Account account = (Account)query.getSingleResult();

If you prefer, you can use positional parameters instead:

Query query = em.createQuery("select i from Item i where i.name = ?1 and i.auctionEnd > ?2");
query.setParameter(1, searchString);
query.setParameter(2, tomorrowDate, TemporalType.TIMESTAMP);

Paging your Query Result

You can also return a single page of your Result. For example, the following Query returns 10 Item starting from the Row 20:

Query query = em.createQuery("select i from Item i");
query.setFirstResult(20).setMaxResults(10);

Using a Named Query

Externalizing query strings lets you store all queries related to a particular persistent class with the other metadata for that. You reference and access an externalized query by its name. For example:

Externalizing query strings lets you store all queries related to a particular persistent class with the other metadata for that. You reference and access an externalized query by its name. For example:

public List<Customer> findAll() {
return entityManager.createNamedQuery("Customers.findAll", Customer.class)
        .getResultList();
}

@Entity
@NamedQuery(name = "Customers.findAll",
        query = "SELECT c FROM Customer c ORDER BY c.id")
public class Customer {
// ...
} 

To learn more about NamedQuery, check this article: Hibernate and JPA Named Query example

Hibernate Criteria interface

The Hibernate query by criteria (QBC) API allows a query to be built by manipulation of criteria objects at runtime. This lets you specify constraints dynamically without direct string manipulations:

Criteria criteria = session.createCriteria(Customer.class);
criteria.add( Restrictions.like("surname", "Frank") );
List result = criteria.list();

To learn more about Criteria Query, check this article: How to use JPA Criteria API

Native Queries:

Finally, if you need to use lots of native queries (maybe with useful db native optimizations) then you can use the createNativeQuery method as follows:

Query sqlQuery =session.                
             createNativeQuery("select c.customer_ID, c.customer_NAME, c.customer_SURNAME from Customer c",Customer.class);

To learn more about JPA Native Query, check this article: Using native Queries with Hibernate and JPA

Found the article helpful? if so please follow us on Socials