Projection in Hibernate Query Language

Projection

To specify exactly which properties are returned by a query, use the select clause. This is known as projection.

This statement returns all European countries:

List<Country> europeanCountries = session.createQuery(
      "select cont.countries " +
      "from Continent cont " +
      "where cont.name = 'Europe'")
      .list();

If the select clause is omitted, the query will return a Continent object instead.

The SQL generated by this query joins the COUNTRY and CONTINENT tables like so:

select
  COUNTRY.CTRY_ID,
  COUNTRY.AREA,
  CONTINENT.CONT_ID,
  CONTINENT.CONT_NAME,
  COUNTRY.CURRENCY,
  COUNTRY.CTRY_NAME,
  COUNTRY.POP,
  COUNTRY.POP_UPD_ON
from
  CONTINENT
inner join
  COUNTRY
    on CONTINENT.CONT_ID=COUNTRY.CONT_ID
where
  CONT_NAME='Europe'

This statement returns the continents for all the countries:

List<Continent> continents = session.createQuery(
      "select ctry.continent from Country ctry")
      .list();

This SQL is produced:

select
  CONTINENT.CONT_ID,
  CONTINENT.CONT_NAME
from
  COUNTRY
inner join
  CONTINENT
    on CONTINENT.CONT_ID=COUNTRY.CONT_ID

If an entity is referenced anywhere in a query, use an alias. Here we use cont in the first example and ctry in the second.

Scalar Values

We can also return scalar values too.

List<String> countryNames = session.createQuery(
      "select ctry.name from Country ctry")
      .list();

A single value is returned from the COUNTRY table like so:

select
  CTRY_NAME
from
  COUNTRY

The country names are retrieved into a List of Strings. A single scalar value in the select clause is always returned into a List with the appropriate type.

Multiple scalar values, on the other hand, are returned into a List of Object arrays.

List<Object[]> properties = session.createQuery(
      "select ctry.name, ctry.continent.name, ctry.currency " +
      "from Country ctry")
      .list();

This SQL is generated:

select
  COUNTRY.CTRY_NAME,
  CONTINENT.CONT_NAME,
  COUNTRY.CURRENCY
from
  COUNTRY,
  CONTINENT
where
  COUNTRY.CONT_ID=CONTINENT.CONT_ID

Dynamic Instantiation

Hibernate can dynamically instantiate an object for each row returned by a query.

First, create a class with a constructor consisting of all the values to be returned.

public class PopulationSummary {

  private String continentName;
  private long countryCount;

  public PopulationSummary (String continentName, long countryCount) {

  setContinentName(continentName);
  setCountryCount(countryCount);

  }

  // Accessors

}

Then, include new package.classname() in the query like this:

List<PopulationSummary> populationDetails = session.createQuery(
      "select new levelup.world.hql.PopulationSummary " +
      "(ctry.continent.name, count(*) )" +
      "from Country ctry " +
      "group by ctry.continent.name")
      .list();

We use an aggregate function to return a count of the countries for each continent. This query returns a List of PopulationSummary objects rather than the more cumbersome Object arrays.

joins