lishman levelup
«previous  next»


Getting Started
Associations
HQL



Hibernate Query Language Quick Start


Get up and running with the Hiberate query language in just a few short steps.
The following example uses an Oracle 10g database.

1. Download

Download and install the latest Hibernate Core and Hibernate Annotations packages.

Make sure that the database driver is on the classpath.

2. Database Tables

Create and populate the database tables required for this example.
DROP TABLE visit;
DROP TABLE head_of_state;
DROP TABLE country;
DROP TABLE continent;
DROP SEQUENCE hibernate_sequence;

CREATE SEQUENCE hibernate_sequence START WITH 1000;

CREATE TABLE continent (
  cont_id    NUMBER        NOT NULL,
  cont_name  VARCHAR2(20)  NOT NULL,
  CONSTRAINT continent_pk PRIMARY KEY (cont_id),
  CONSTRAINT continent_uk UNIQUE (cont_name)
);

INSERT INTO continent (cont_id, cont_name) VALUES (1, 'Africa');
INSERT INTO continent (cont_id, cont_name) VALUES (2, 'Asia');
INSERT INTO continent (cont_id, cont_name) VALUES (3, 'Europe');
INSERT INTO continent (cont_id, cont_name) VALUES (4, 'North America');
INSERT INTO continent (cont_id, cont_name) VALUES (5, 'South America');
INSERT INTO continent (cont_id, cont_name) VALUES (6, 'Oceania');
INSERT INTO continent (cont_id, cont_name) VALUES (7, 'Antarctica');
COMMIT;

CREATE TABLE country (
  ctry_id         NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL,
  ctry_name       VARCHAR2(50)  NOT NULL,
  area            NUMBER        NOT NULL,
  pop             NUMBER        NOT NULL,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)  NOT NULL,
  CONSTRAINT country_pk PRIMARY KEY (ctry_id),
  CONSTRAINT country_uk UNIQUE (ctry_name),
  CONSTRAINT country_fk FOREIGN KEY (cont_id) REFERENCES continent
);

INSERT INTO country (ctry_id, cont_id, ctry_name, area, pop, pop_upd_on, currency)
VALUES(14, 3, 'Germany', 137847, 82046000, '30-nov-08', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, area, pop, pop_upd_on, currency)
  VALUES(48, 1, 'Ghana', 92098, 23837000, null, 'Cedi');
INSERT INTO country (ctry_id, cont_id, ctry_name, area, pop, pop_upd_on, currency)
  VALUES(53, 6, 'Australia', 2966200, 21884000, '4-sep-09', 'Australian Dollar');
INSERT INTO country (ctry_id, cont_id, ctry_name, area, pop, pop_upd_on, currency)
  VALUES(73, 3, 'Greece', 50949, 11257285, '1-jan-09', 'Euro');
INSERT INTO country (ctry_id, cont_id, ctry_name, area, pop, pop_upd_on, currency)
  VALUES(122, 3, 'Georgia', 26900, 4382100, '1-jan-09', 'Lari');
INSERT INTO country (ctry_id, cont_id, ctry_name, area, pop, pop_upd_on, currency)
  VALUES(123, 6, 'New Zealand', 104454, 4320300, '4-sep-09', 'New Zealand Dollar');
INSERT INTO country (ctry_id, cont_id, ctry_name, area, pop, pop_upd_on, currency)
  VALUES(147, 1, 'Gambia', 4361, 1705000, null, 'Dalasi');
INSERT INTO country (ctry_id, cont_id, ctry_name, area, pop, pop_upd_on, currency)
  VALUES(149, 1, 'Gabon', 103347, 1475000, null, 'CFA franc');
COMMIT;

3. Hibernate Configuration File

Create a Hibernate configuration file named hibernate.cfg.xml in the root of your classpath. This is the same file as the one we created in Hibernate Quick Start except for the mapped entites at the end.
<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
    "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
    "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
  <session-factory>

    <!-- Database dialect -->
    <property name="dialect">
      org.hibernate.dialect.Oracle10gDialect
    </property>

    <!-- Database connection settings -->
    <property name="connection.provider_class">
      org.hibernate.connection.C3P0ConnectionProvider
    </property>
    <property name="connection.driver_class">
      oracle.jdbc.driver.OracleDriver
    </property>
    <property name="connection.url">
      jdbc:oracle:thin:@localhost:1521:test01
    </property>
    <property name="connection.username">lish</property>
    <property name="connection.password">secret</property>

    <!-- C3P0 connection pool -->
    <property name="hibernate.c3p0.min_size">5</property>
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.timeout">300</property>
    <property name="hibernate.c3p0.max_statements">50 </property>
    <property name="hibernate.c3p0.idle_test_period">3000 </property>

    <!-- Enable Hibernate's automatic session context management -->
    <property name="current_session_context_class">thread</property>

    <!-- Print all generated SQL to the console -->
    <property name="show_sql">true</property>
    <property name="format_sql">true</property>

    <!-- Mapped entities -->
    <mapping class="levelup.world.hql.Continent"/>
    <mapping class="levelup.world.hql.Country"/>

  </session-factory>
</hibernate-configuration>
Be sure to change the connection settings (url, username, password) for your database.

4. HibernateUtil

Create the HibernateUtil helper class. This is exactly the same as the one we created in Hibernate Quick Start
package levelup.world;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.AnnotationConfiguration;

public class HibernateUtil {

  private static SessionFactory sessionFactory;

  static {
  try {

    sessionFactory =
      new AnnotationConfiguration()
          .configure()
          .buildSessionFactory();

  } catch (Throwable ex) {
    System.err.println("Initial SessionFactory creation failed." + ex);
    throw new ExceptionInInitializerError(ex);
  }
  }

  public static SessionFactory getSessionFactory() {
  return sessionFactory;
  }

  public static void shutdown() {
  getSessionFactory().close();
  }
}

5. Continent Class

Create the Continent class.
package levelup.world.hql;

import java.util.HashSet;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToMany;
import javax.persistence.Table;


@Entity
@Table(name = "CONTINENT")
public class Continent {

  @Id
  @GeneratedValue
  @Column(name = "CONT_ID")
  private Integer id;

  @Column(name = "CONT_NAME")
  private String name;

  @OneToMany(mappedBy = "continent")
  @JoinColumn(name = "CONT_ID")
  private Set<Country> countries = new HashSet<Country>();

  public Integer getId() {
  return id;
  }

  public void setName(String name) {
  this.name = name;
  }

  public String getName() {
  return name;
  }

  public Set<Country> getCountries() {
  return countries;
  }

  public void addCountry(Country country) {
  country.setContinent(this);
  getCountries().add(country);
  }

  public String toString() {
  return getName();
  }

}

6. PopulationSummary Class

Create the PopulationSummary class.
package levelup.world.hql;

public class PopulationSummary {

  private String continentName;
  private long countryCount;

  public PopulationSummary (String continentName,
              long countryCount) {

  setContinentName(continentName);
  setCountryCount(countryCount);

  }

  public void setContinentName(String continentName) {
  this.continentName = continentName;
  }

  public String getContinentName() {
  return continentName;
  }

  public void setCountryCount(long countryCount) {
  this.countryCount = countryCount;
  }

  public long getCountryCount() {
  return countryCount;
  }

}

7. Country Class

Create the Country class.
package levelup.world.hql;

import java.text.ParseException;
import java.text.SimpleDateFormat;

import java.util.Date;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Transient;

import levelup.world.HibernateUtil;

import org.hibernate.Query;
import org.hibernate.Session;


@NamedQueries ({
  @NamedQuery(
  name = "mostPopulous",
  query = "from Country where population > 10000000 order by population desc")
})

@Entity
@Table(name="COUNTRY")
public class Country {

  @Id
  @GeneratedValue
  @Column(name="CTRY_ID")
  private Integer id;

  @Column(name="CTRY_NAME")
  private String name;

  private int area;

  @Column(name="POP")
  private long population;

  @Column(name="POP_UPD_ON")
  private Date populationUpdatedOn;

  @Transient
  private int rank;

  private String currency;

  // Lazy loading to make SQL more readable
  @ManyToOne (fetch = FetchType.LAZY)
  @JoinColumn(name = "CONT_ID")
  private Continent continent;

  public Integer getId() {
  return id;
  }

  public void setName(String name) {
  this.name = name;
  }

  public String getName() {
  return name;
  }

  public void setArea(int area) {
  this.area = area;
  }

  public int getArea() {
  return area;
  }

  public void setPopulation(long population) {
  this.population = population;
  }

  public long getPopulation() {
  return population;
  }

  public void setPopulationUpdatedOn(Date populationUpdatedOn) {
  this.populationUpdatedOn = populationUpdatedOn;
  }

  public Date getPopulationUpdatedOn() {
  return populationUpdatedOn;
  }

  public void setRank(int rank) {
  this.rank = rank;
  }

  public int getRank() {
  return rank;
  }

  public void setCurrency(String currency) {
  this.currency = currency;
  }

  public String getCurrency() {
  return currency;
  }

  public void setContinent(Continent continent) {
  this.continent = continent;
  }

  public Continent getContinent() {
  return continent;
  }

  public String toString() {
  return getName();
  }


  public static void main(String[] args) {

  Session session = HibernateUtil.getSessionFactory().getCurrentSession();
  session.beginTransaction();


  // from clause
  // All properties for all continents
  List<Continent> allContinents = session.createQuery("from Continent").list();
  showContinents(allContinents);

  // where clause (selection)
  // Continents with 'America' anywhere in the name
  List<Continent> theAmericas = session.createQuery(
        "from Continent where name like '%America%'")
        .list();
  showContinents(theAmericas);

  // Complex selection - most common SQL expressions are supported
  // Countries matching a particular name or with no population updated date
  // within a specific area range
  List<Country> countries = session.createQuery(
        "from Country " +
        "where (populationUpdatedOn is not null " +
        "or lower(name) in ('gabon', 'gambia')) " +
        "and area between 100000 AND 100000000")
        .list();
  showCountries(countries);

  // Implicit association joining
  // All countries in Africa
  List<Country> africanCountries = session.createQuery(
        "from Country where continent.name = 'Africa'")
        .list();
  showCountries(africanCountries);

  // Single instance returned with uniqueResult
  // Get the continent with an id of 7
  Continent antarctica = (Continent) session.createQuery(
        "from Continent where id = 7")
        .uniqueResult();
  System.out.println("The continent with an id of 7 is " + antarctica.getName());

  // Limit the number of objects returned setMaxResults
  // Get the country with the highest population
  Country highestPopulation = (Country) session.createQuery(
        "from Country order by population desc")
        .setMaxResults(1)
        .uniqueResult();
  System.out.println(highestPopulation.getName() +
             " has the highest population with " +
             highestPopulation.getPopulation());

  // Sorting
  // Sort the results into ascending (the default) country name within descending area
  List<Country> sortedCountries = session.createQuery(
        "from Country order by area desc, name")
        .list();
  showCountries(sortedCountries);

  // Paging
  // Get page 3 of a list of countries with 2 items per page
  List<Country> page3 = session.createQuery(
        "from Country order by name")
        .setFirstResult(4)
        .setMaxResults(2)
        .list();
  showCountries(page3);

  // SQL Injection
  // Get African countries which contain specified text (in this case 'Ga')
  String userInput = "Ga";
  String searchFor = "%" + userInput.toLowerCase() + "%";
  List<Country> onlyAfricanCountries = session.createQuery(
        "from Country " +
        "where continent.name = 'Africa' " +
        "and lower(name) like '" + searchFor + "'")
        .list();
  showCountries(onlyAfricanCountries);

  // Malicious user retrieves ALL countries
  userInput = "' or '%'='".toLowerCase();
  searchFor = "%" + userInput.toLowerCase() + "%";
  onlyAfricanCountries = session.createQuery(
        "from Country " +
        "where continent.name = 'Africa' " +
        "and lower(name) like '" + searchFor + "'")
        .list();
  showCountries(onlyAfricanCountries);

  // Parameter binding
  // Get African countries which contain specified text, safely
  userInput = "' or '%'='".toLowerCase();
  searchFor = "%" + userInput.toLowerCase() + "%";
  onlyAfricanCountries = session.createQuery(
        "from Country " +
        "where continent.name = 'Africa' " +
        "and lower(name) like :ctry_name")
        .setParameter("ctry_name", searchFor)
        .list();
  showCountries(onlyAfricanCountries);

  // Parameter binding and fixed literals
  // Countries within a size range (variable) with a populated date after 1-Jan-09 (fixed)
  countries = session.createQuery(
        "from Country " +
        "where area between :smallest and :largest " +
        "and populationUpdatedOn >= '1-Jan-09'")
        .setParameter("smallest", 20000)
        .setParameter("largest", 500000)
        .list();
  showCountries(countries);

  // Parameter binding with persistent entities
  // Get the european countries using a previously retrieved continent entity
  Continent europe = (Continent) session.load(Continent.class, 3);
  List<Country> europeanCountries = session.createQuery(
        "from Country where continent = :cont")
        .setParameter("cont", europe)
        .list();
  showCountries(europeanCountries);

  // Null comparison
  // Attempt to get countries where the population updated on is missing (FAILS!)
  List<Country> missingUpdatedOn = session.createQuery(
        "from Country where populationUpdatedOn = :upd_on")
        .setParameter("upd_on", null)
        .list();
  showCountries(missingUpdatedOn);
  // Evaluates to POP_UPD_ON = null which returns nothing

  // Use IS NULL instead
  try {
    Date popUpdOn = new SimpleDateFormat("dd-MMM-yyyy").parse("4-sep-2009");
    popUpdOn = null;

    Query hqlQuery = session.createQuery("from Country where populationUpdatedOn " +
                      (popUpdOn==null ? "is null" : "= :upd_on"));
    if (popUpdOn != null) {
    hqlQuery.setParameter("upd_on", popUpdOn);
    }
    showCountries(hqlQuery.list());
  } catch (ParseException e) {
    e.printStackTrace();
  }

  // select clause (projection)
  // Get the European countries
  europeanCountries = session.createQuery(
        "select cont.countries " +
        "from Continent cont " +
        "where cont.name = 'Europe'")
        .list();
  showCountries(europeanCountries);

  // Get the continents for all the countries
  List<Continent> continents = session.createQuery(
        "select ctry.continent " +
        "from Country ctry")
        .list();
  showContinents(continents);

  // Single scalar value
  // Get all the country names
  List<String> countryNames = session.createQuery(
        "select ctry.name from Country ctry")
        .list();
  showStrings(countryNames);

  // Multiple scalar values
  // Get the country name, continent name and currency for each country
  List<Object[]> countryProperties = session.createQuery(
        "select ctry.name, ctry.continent.name, ctry.currency " +
        "from Country ctry")
        .list();
  showObjectArray(countryProperties);

  // Dynamic object instantiation
  // A count of the number of countries in each continent
  List<PopulationSummary> populationDetails = session.createQuery(
        "select new levelup.world.hql.PopulationSummary " +
        "(ctry.continent.name, count(*) )" +
        "from Country ctry " +
        "group by ctry.continent.name")
        .list();
  showPopulationSummary(populationDetails);

  // Inner join without projection
  // African countries
  List<Object[]> continentsWithBigCountries = session.createQuery(
        "from Continent cont join cont.countries ctry " +
        "where cont.name = 'Africa'")
        .list();
  showObjectArray(continentsWithBigCountries);

  // Inner join with projection
  // Names of continents with large contries
  List<String> continentNamesWithBigCountries = session.createQuery(
        "select distinct cont.name " +
        "from Continent cont join cont.countries ctry " +
        "where ctry.area > 100000")
        .list();
  showStrings(continentNamesWithBigCountries);

  // Left outer join
  // Continents with large countries or '[none]' if all countries are small
  List<Object[]> allContinentsAndCountries = session.createQuery(
        "select cont.name, nvl(ctry.name, '[none]') " +
        "from Continent cont left join cont.countries ctry " +
        "with ctry.area > 100000 " +
        "order by cont.name")
        .list();
  showObjectArray(allContinentsAndCountries);

  // Theta-style joins
  // Continents with the same name as a country (of which there are none)
  List<Continent> duplicateNames = session.createQuery(
        "select cont from Continent cont, Country ctry where cont.name = ctry.name")
        .list();
  System.out.println("There area " + duplicateNames.size() +
             " continents with same name as a country");

  // Without fetch
  // Get Europe - proxy object is returned for countries
  europe = (Continent) session.createQuery(
        "select cont " +
        "from Continent cont join cont.countries " +
        "where cont.name = 'Europe'")
        .uniqueResult();
  System.out.println("Europe has " +
             europe.getCountries().size() +
             " countries");

  // With fetch
  // Use fetch to load the Country objects
  europe = (Continent) session.createQuery(
        "select cont " +
        "from Continent cont join fetch cont.countries " +
        "where cont.name = 'Europe'")
        .uniqueResult();
  System.out.println("Europe has " +
             europe.getCountries().size() +
             " countries");

  session.getTransaction().commit();

  HibernateUtil.shutdown();

  }

  private static void showContinents (List<Continent> continents) {
  for (Continent continent : continents) {
    System.out.println(continent.getName());
  }
  }

  private static void showCountries (List<Country> countries) {
  for (Country country : countries) {
    System.out.println(country.getName());
  }
  }

  private static void showPopulationSummary (List<PopulationSummary> popSumms) {
  for (PopulationSummary popSum : popSumms) {
    System.out.println("Continent " + popSum.getContinentName() +
             " has " + popSum.getCountryCount() + " countries");

  }
  }

  private static void showStrings (List<String> strings) {
  for (String s : strings) {
    System.out.println(s);
  }
  }

  private static void showObjectArray (List<Object[]> objects) {
  for (Object[] o : objects) {
    StringBuffer row = new StringBuffer();
    for (int i=0; i<o.length; i++) {
    row.append((o[i] == null ? "[null]" : o[i].toString()) + ", ");
    }
    System.out.println(row.deleteCharAt(row.length()-2));
  }
  }

}

The application structure should look like this.

Associations Application Structure

Run the Country class.
»