Associations
ext-world-entities.png
ext-world-schema.png
fetch-eager.sql
-- Country germany = em.find(Country.class, 1);
select
    country0_.CTRY_ID as CTRY1_0_2_,
    country0_.CTRY_AREA as CTRY2_0_2_,
    country0_.CONT_ID as CONT5_0_2_,
    country0_.CTRY_NAME as CTRY3_0_2_,
    country0_.CTRY_POP as CTRY4_0_2_,
    cities1_.CTRY_ID as CTRY3_0_4_,
    cities1_.CITY_ID as CITY1_4_,
    cities1_.CITY_ID as CITY1_2_0_,
    cities1_.CITY_NAME as CITY2_2_0_,
    mayor2_.MAYOR_ID as MAYOR1_3_1_,
    mayor2_.CITY_ID as CITY3_3_1_,
    mayor2_.MAYOR_NAME as MAYOR2_3_1_ 
from
    CTRY country0_ 
left outer join
    City cities1_ 
        on country0_.CTRY_ID=cities1_.CTRY_ID 
left outer join
    Mayor mayor2_ 
        on cities1_.CITY_ID=mayor2_.CITY_ID 
where
    country0_.CTRY_ID=?
fetch-lazy.sql
-- Country germany = em.find(Country.class, 1);
select
    country0_.CTRY_ID as CTRY1_0_0_,
    country0_.CTRY_AREA as CTRY2_0_0_,
    country0_.CONT_ID as CONT5_0_0_,
    country0_.CTRY_NAME as CTRY3_0_0_,
    country0_.CTRY_POP as CTRY4_0_0_ 
from
    CTRY country0_ 
where
    country0_.CTRY_ID=?

-- int citiesCount = germany.getCities().size();
select
    cities0_.CTRY_ID as CTRY3_0_2_,
    cities0_.CITY_ID as CITY1_2_,
    cities0_.CITY_ID as CITY1_2_1_,
    cities0_.CITY_NAME as CITY2_2_1_,
    mayor1_.MAYOR_ID as MAYOR1_3_0_,
    mayor1_.CITY_ID as CITY3_3_0_,
    mayor1_.MAYOR_NAME as MAYOR2_3_0_ 
from
    City cities0_ 
left outer join
    Mayor mayor1_ 
        on cities0_.CITY_ID=mayor1_.CITY_ID 
where
    cities0_.CTRY_ID=?
paging_with_rownum.sql
select
    * 
from
    ( select
        row_.*,
        rownum rownum_ 
    from
        ( select
            city0_.CITY_NAME as col_0_0_ 
        from
            City city0_ 
        order by
            city0_.CITY_NAME ) row_ 
    where
        rownum <= ?
    ) 
where
    rownum_ > ?
sql-injection-wiki.notes
sql-parsing-blog.notes
using-lishys-notes.notes

Guide


The guide pane on the right contains information organized into sections.

Use the and buttons to move through the sections, or click an accordion tab (shown below) to go directly to a particular topic.



Code Reference


Each paragraph in the guide is usually associated with a particular piece of code. An orange arrow indicates that the code displayed in the middle pane is relevant to the guide text.



If the arrow is gray, simply click anywhere on the paragraph and the relevant code will be displayed.



Code Highlighter


The highlight feature identifies areas of the code which relate to a word or term in the guide. Hover the mouse over any underlined word and the relevant code will be highlighted.



You can disable the highlighting feature at any time by clicking on the button.

Don't forget to make the appropriate code page visible by clicking on any paragraph with a gray arrow.
create-border.sql
CREATE TABLE bord (
  cont_id         NUMBER,
  ocean_id        NUMBER,
  CONSTRAINT bord_pk  PRIMARY KEY (cont_id, ocean_id),
  CONSTRAINT bord_fk1 FOREIGN KEY (cont_id) REFERENCES cont,
  CONSTRAINT bord_fk2 FOREIGN KEY (ocean_id) REFERENCES ocean
);

-- Indian
INSERT INTO bord (cont_id, ocean_id) VALUES(1, 3);
INSERT INTO bord (cont_id, ocean_id) VALUES(2, 3);
INSERT INTO bord (cont_id, ocean_id) VALUES(6, 3);

-- Pacific
INSERT INTO bord (cont_id, ocean_id) VALUES(2, 4);
INSERT INTO bord (cont_id, ocean_id) VALUES(4, 4);
INSERT INTO bord (cont_id, ocean_id) VALUES(5, 4);
INSERT INTO bord (cont_id, ocean_id) VALUES(6, 4);
INSERT INTO bord (cont_id, ocean_id) VALUES(7, 4);

--Atlantic
INSERT INTO bord (cont_id, ocean_id) VALUES(1, 2);
INSERT INTO bord (cont_id, ocean_id) VALUES(3, 2);
INSERT INTO bord (cont_id, ocean_id) VALUES(4, 2);
INSERT INTO bord (cont_id, ocean_id) VALUES(5, 2);

COMMIT;
create-city.sql
CREATE TABLE city (
  city_id         NUMBER,
  city_name       VARCHAR2(50)  NOT NULL,
  ctry_id         NUMBER        NOT NULL,
  CONSTRAINT city_pk  PRIMARY KEY (city_id),
  CONSTRAINT city_fk1 FOREIGN KEY (ctry_id) REFERENCES ctry
);

CREATE SEQUENCE city_seq1;
CREATE OR REPLACE TRIGGER city_trg1
  BEFORE INSERT ON city FOR EACH ROW
BEGIN 
    IF :NEW.city_id IS NULL THEN
      SELECT city_seq1.NEXTVAL INTO :NEW.city_id FROM DUAL;
    END IF;
END;
/

INSERT INTO city (city_name, ctry_id)  VALUES('Berlin', 1);
INSERT INTO city (city_name, ctry_id)  VALUES('Hamburg', 1);
INSERT INTO city (city_name, ctry_id)  VALUES('Munich', 1);
INSERT INTO city (city_name, ctry_id)  VALUES('Accra', 2);
INSERT INTO city (city_name, ctry_id)  VALUES('Kumasi', 2);
INSERT INTO city (city_name, ctry_id)  VALUES('Tamale', 2);
INSERT INTO city (city_name, ctry_id)  VALUES('Aukland', 6);
INSERT INTO city (city_name, ctry_id)  VALUES('Christchurch', 6);
INSERT INTO city (city_name, ctry_id)  VALUES('Wellington', 6);

COMMIT;
create-continent.sql
CREATE TABLE cont (
  cont_id         NUMBER,
  cont_name       VARCHAR2(50)  NOT NULL,
  CONSTRAINT cont_pk  PRIMARY KEY (cont_id),
  CONSTRAINT cont_uk1 UNIQUE (cont_name)
);

CREATE SEQUENCE cont_seq1;
CREATE OR REPLACE TRIGGER cont_trg1
  BEFORE INSERT ON cont FOR EACH ROW
BEGIN 
    IF :NEW.cont_id IS NULL THEN
      SELECT cont_seq1.NEXTVAL INTO :NEW.cont_id FROM DUAL;
    END IF;
END;
/

INSERT INTO cont (cont_name) VALUES('Africa');
INSERT INTO cont (cont_name) VALUES('Asia');
INSERT INTO cont (cont_name) VALUES('Europe');
INSERT INTO cont (cont_name) VALUES('North America');
INSERT INTO cont (cont_name) VALUES('South America');
INSERT INTO cont (cont_name) VALUES('Oceania');
INSERT INTO cont (cont_name) VALUES('Antarctica');

COMMIT;
create-country.sql
CREATE TABLE ctry (
  ctry_id         NUMBER,
  ctry_name       VARCHAR2(50)  NOT NULL,
  ctry_area       NUMBER        NOT NULL,
  ctry_pop        NUMBER        NOT NULL,
  cont_id         NUMBER        NOT NULL,
  CONSTRAINT ctry_pk  PRIMARY KEY (ctry_id),
  CONSTRAINT ctry_uk1 UNIQUE (ctry_name),
  CONSTRAINT ctry_fk1 FOREIGN KEY (cont_id) REFERENCES cont
  
);

CREATE SEQUENCE ctry_seq1;
CREATE OR REPLACE TRIGGER ctry_trg1
  BEFORE INSERT ON ctry FOR EACH ROW
BEGIN 
    IF :NEW.ctry_id IS NULL THEN
      SELECT ctry_seq1.NEXTVAL INTO :NEW.ctry_id FROM DUAL;
    END IF;
END;
/

INSERT INTO ctry (ctry_name, ctry_area, ctry_pop, cont_id)
  VALUES('Germany', 137847, 82046000, 3);
INSERT INTO ctry (ctry_name, ctry_area, ctry_pop, cont_id)
  VALUES('Ghana', 92098, 23837000, 1);
INSERT INTO ctry (ctry_name, ctry_area, ctry_pop, cont_id)
  VALUES('Australia', 2966200, 21884000, 6);
INSERT INTO ctry (ctry_name, ctry_area, ctry_pop, cont_id)
  VALUES('Greece', 50949, 11257285, 3);
INSERT INTO ctry (ctry_name, ctry_area, ctry_pop, cont_id)
  VALUES('Georgia', 26900, 4382100, 3);
INSERT INTO ctry (ctry_name, ctry_area, ctry_pop, cont_id)
  VALUES('New Zealand', 104454, 4320300, 6);
INSERT INTO ctry (ctry_name, ctry_area, ctry_pop, cont_id)
  VALUES('Gambia', 4361, 1705000, 1);
INSERT INTO ctry (ctry_name, ctry_area, ctry_pop, cont_id)
  VALUES('Gabon', 103347, 1475000, 1);
  
COMMIT;
create-mayor.sql
CREATE TABLE mayor (
  mayor_id         NUMBER,
  mayor_name       VARCHAR2(50)  NOT NULL,
  city_id          NUMBER        NOT NULL, 
  CONSTRAINT mayor_pk  PRIMARY KEY (mayor_id),
  CONSTRAINT mayor_uk1 UNIQUE (city_id),
  CONSTRAINT mayor_fk1 FOREIGN KEY (city_id) REFERENCES city
);

CREATE SEQUENCE mayor_seq1;
CREATE OR REPLACE TRIGGER mayor_trg1
  BEFORE INSERT ON mayor FOR EACH ROW
BEGIN 
    IF :NEW.mayor_id IS NULL THEN
      SELECT mayor_seq1.NEXTVAL INTO :NEW.mayor_id FROM DUAL;
    END IF;
END;
/

INSERT INTO mayor (mayor_name, city_id)  VALUES('Klaus Wowereit', 1);
INSERT INTO mayor (mayor_name, city_id)  VALUES('Dr. Alfred Oko Vanderpuije', 4);
INSERT INTO mayor (mayor_name, city_id)  VALUES('Nicholas C Geary', 8);

COMMIT;
create-ocean.sql
CREATE TABLE ocean (
  ocean_id        NUMBER,
  ocean_name      VARCHAR2(10)  NOT NULL,
  ocean_area      NUMBER        NOT NULL,
  CONSTRAINT ocean_pk  PRIMARY KEY (ocean_id),
  CONSTRAINT ocean_uk1 UNIQUE (ocean_name)
);

CREATE SEQUENCE ocean_seq1;
CREATE OR REPLACE TRIGGER ocean_trg1
  BEFORE INSERT ON ocean FOR EACH ROW
BEGIN 
    IF :NEW.ocean_id IS NULL THEN
      SELECT ocean_seq1.NEXTVAL INTO :NEW.ocean_id FROM DUAL;
    END IF;
END;
/

INSERT INTO ocean (ocean_name, ocean_area) VALUES('Artic', 5426000);
INSERT INTO ocean (ocean_name, ocean_area) VALUES('Atlantic', 29630000);
INSERT INTO ocean (ocean_name, ocean_area) VALUES('Indian', 26463000);
INSERT INTO ocean (ocean_name, ocean_area) VALUES('Pacific', 60045000);
INSERT INTO ocean (ocean_name, ocean_area) VALUES('Southern', 7846000);

COMMIT;
create-world.sql
@reset-world.sql
@create-continent.sql
@create-ocean.sql
@create-border.sql
@create-country.sql
@create-city.sql
@create-mayor.sql
reset-world.sql
DROP TABLE mayor;
DROP TABLE city;
DROP TABLE ctry;
DROP TABLE bord;
DROP TABLE ocean;
DROP TABLE cont;

DROP SEQUENCE mayor_seq1;
DROP SEQUENCE city_seq1;
DROP SEQUENCE ctry_seq1;
DROP SEQUENCE ocean_seq1;
DROP SEQUENCE cont_seq1;
City.java
package com.lishman.world.domain;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.OneToOne;
import javax.persistence.SequenceGenerator;

@Entity
public class City implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "CITY_ID_GENERATOR", sequenceName = "CITY_SEQ1")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CITY_ID_GENERATOR")
    @Column(name = "CITY_ID")
    private Integer id;

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

    @OneToOne(mappedBy = "city")
    private Mayor mayor;

    public City() {
    }

    public Integer getId() {
        return this.id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return this.name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Mayor getMayor() {
        return this.mayor;
    }
    public void setMayor(Mayor mayor) {
        this.mayor = mayor;
    }
}
Continent.java
package com.lishman.world.domain;

import java.io.Serializable;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Table(name = "CONT")
public class Continent implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "CONT_ID_GENERATOR", sequenceName = "CONT_SEQ1")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CONT_ID_GENERATOR")
    @Column(name = "CONT_ID")
    private Integer id;

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

    @ManyToMany(mappedBy = "continents")
    private Set<Ocean> oceans;

    @OneToMany(mappedBy = "continent", cascade={CascadeType.ALL})
    private Set<Country> countries;

    public Continent() {
    }

    public Integer getId() {
        return this.id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return this.name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Set<Ocean> getOceans() {
        return this.oceans;
    }
    public void setOceans(Set<Ocean> oceans) {
        this.oceans = oceans;
    }
    public Set<Country> getCountries() {
        return this.countries;
    }
    public void setCountries(Set<Country> countries) {
        this.countries = countries;
    }
}
Country.java
package com.lishman.world.domain;

import java.io.Serializable;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Table(name = "CTRY")
public class Country implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "CTRY_ID_GENERATOR", sequenceName = "CTRY_SEQ1")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "CTRY_ID_GENERATOR")
    @Column(name = "CTRY_ID")
    private Integer id;

    @Column(name = "CTRY_AREA")
    private Integer area;

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

    @Column(name = "CTRY_POP")
    private Integer population;
    
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CONT_ID")
    private Continent continent;
    
    @OneToMany(fetch = FetchType.EAGER)
    @JoinColumn(name = "CTRY_ID")
    private List<City> cities;

    public Country() {
    }

    public Integer getId() {
        return this.id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public Integer getArea() {
        return this.area;
    }
    public void setArea(Integer area) {
        this.area = area;
    }
    public String getName() {
        return this.name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Integer getPopulation() {
        return this.population;
    }
    public void setPopulation(Integer population) {
        this.population = population;
    }
    public Continent getContinent() {
        return this.continent;
    }
    public void setContinent(Continent continent) {
        this.continent = continent;
    }
    public List<City> getCities() {
        return this.cities;
    }
    public void setCities(List<City> cities) {
        this.cities = cities;
    }
}
Mayor.java
package com.lishman.world.domain;

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.OneToOne;
import javax.persistence.SequenceGenerator;

@Entity
public class Mayor implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "MAYOR_ID_GENERATOR", sequenceName = "MAYOR_SEQ1")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "MAYOR_ID_GENERATOR")
    @Column(name = "MAYOR_ID")
    private Integer id;

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

    @OneToOne
    @JoinColumn(name = "CITY_ID")
    private City city;

    public Mayor() {
    }

    public Integer getId() {
        return this.id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return this.name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public City getCity() {
        return this.city;
    }
    public void setCity(City city) {
        this.city = city;
    }
}
Ocean.java
package com.lishman.world.domain;

import java.io.Serializable;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.JoinColumn;
import javax.persistence.JoinTable;
import javax.persistence.ManyToMany;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.SequenceGenerator;

@Entity
@NamedQueries ({
    @NamedQuery(name="Ocean.findAll", query="select o from Ocean o order by o.name"),
    @NamedQuery(name="Ocean.findById", query="select o from Ocean o where o.id = :oceanId")
})
public class Ocean implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "OCEAN_ID_GENERATOR", sequenceName = "OCEAN_SEQ1")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "OCEAN_ID_GENERATOR")
    @Column(name = "OCEAN_ID")
    private Integer id;

    @Column(name = "OCEAN_AREA")
    private Integer area;

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

    @ManyToMany
    @JoinTable(name = "BORD", joinColumns = { @JoinColumn(name = "OCEAN_ID") }, 
                              inverseJoinColumns = { @JoinColumn(name = "CONT_ID") })
    private Set<Continent> continents;

    public Ocean() {
    }

    public Integer getId() {
        return this.id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public Integer getArea() {
        return this.area;
    }
    public void setArea(Integer area) {
        this.area = area;
    }
    public String getName() {
        return this.name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Set<Continent> getContinents() {
        return this.continents;
    }
    public void setContinents(Set<Continent> continents) {
        this.continents = continents;
    }
}
MayorAndCity.java
package com.lishman.world.queries;

public class MayorAndCity {
    
    private String mayorName;
    private String cityName;
    
    public MayorAndCity(String mayorName, String cityName) {
        this.mayorName = mayorName;
        this.cityName = cityName;
    }
    public String getMayorName() {
        return mayorName;
    }
    public void setMayorName(String mayorName) {
        this.mayorName = mayorName;
    }
    public String getCityName() {
        return cityName;
    }
    public void setCityName(String cityName) {
        this.cityName = cityName;
    }

}
QueryLanguage.java
package com.lishman.world.queries;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Persistence;

import com.lishman.world.domain.Ocean;

public class QueryLanguage {
    
    @SuppressWarnings("unchecked")
    public static void main(String[] args) {
        
        EntityManager em = Persistence
                            .createEntityManagerFactory("world")
                            .createEntityManager();
      
        // Simple query
        List<Ocean> smallOceans = em.createQuery(
                "SELECT o " +
                "FROM Ocean o " +
                "WHERE o.area < 10000000 " +
                "ORDER BY o.area", Ocean.class)
                    .getResultList();
       
        // Projection
        Integer largestOceanArea = em.createQuery(
                "SELECT MAX(o.area) FROM Ocean o", Integer.class)
                    .getSingleResult();
       
        // Aggregates
        List<Object[]> continentStats = em.createQuery(
                "SELECT cont.name, MAX(ctry.population), AVG(ctry.area) " +
                "FROM Continent cont " +
                " JOIN cont.countries ctry " +
                "GROUP BY cont.name " +
                "HAVING COUNT(ctry) > 2")
                    .getResultList();

        // Associations
        List<Ocean> oceans = em.createQuery(
                "SELECT c.continent.oceans " +
                "FROM Country c " +
                "WHERE c.id = 2")
                    .getResultList();

        // Join
        List<Object[]> cityList = em.createQuery(
                "SELECT city, ctry, cont " +
                "FROM Continent cont " +
                " JOIN cont.countries ctry " +
                " JOIN ctry.cities city " +
                "WHERE cont.id IN (1,3)")
                    .getResultList();

        em.close();
    }
}
QueryParameters.java
package com.lishman.world.queries;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Persistence;

import com.lishman.world.domain.Continent;
import com.lishman.world.domain.Country;
import com.lishman.world.domain.Mayor;
import com.lishman.world.domain.Ocean;

public class QueryParameters {
    
    @SuppressWarnings("unchecked")
    public static void main(String[] args) {
        
        EntityManager em = Persistence
                            .createEntityManagerFactory("world")
                            .createEntityManager();

        // Named parameter binding
        List<Object[]> countryDetails = em.createQuery(
                "SELECT c.name, c.area, c.population " + 
                "FROM Country c " + 
                "WHERE c.name LIKE :name " + 
                "AND c.population > :pop")
                    .setParameter("name", "G%")
                    .setParameter("pop", 10000000)
                    .getResultList();

        // Entity type parameter 
        Continent africa = em.find(Continent.class, 1);
        List<Country> countries = em.createQuery(
                "SELECT ctry " +
                "FROM Country ctry " +
                "WHERE ctry.continent = :cont", Country.class)
                    .setParameter("cont", africa)
                    .getResultList();
     
        // Positional parameter binding
        Mayor mayor = em.createQuery(
                "SELECT m " + 
                "FROM Mayor m " + 
                "WHERE m.id = ?1", Mayor.class)
                    .setParameter(1, 3)
                    .getSingleResult();

        // Named query with parameter binding
        Ocean ocean = em.createNamedQuery(
                "Ocean.findById", Ocean.class)
                    .setParameter("oceanId", 3)
                    .getSingleResult(); 

        em.close();
    }
}
RunningQueries.java
package com.lishman.world.queries;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Persistence;

import com.lishman.world.domain.Continent;
import com.lishman.world.domain.Mayor;
import com.lishman.world.domain.Ocean;

public class RunningQueries {
    
    @SuppressWarnings("unchecked")
    public static void main(String[] args) {
        
        EntityManager em = Persistence
                            .createEntityManagerFactory("world")
                            .createEntityManager();

        // List of entities
        List<Continent> allContinents = em.createQuery(
                "SELECT c FROM Continent c", Continent.class)
                    .getResultList();

        // Named query
        List<Ocean> allOceans = em.createNamedQuery(
                "Ocean.findAll", Ocean.class)
                    .getResultList();
             
        // List of scalars (with pagination)
        List<String> pageOfCityNames = em.createQuery(
                "SELECT c.name FROM City c ORDER BY c.name", String.class)
                    .setFirstResult(6)
                    .setMaxResults(3)
                    .getResultList();

        // Single entity
        Mayor mayor = em.createQuery(
                "SELECT m FROM Mayor m WHERE m.id = 2", Mayor.class)
                    .getSingleResult();      
       
        // List of object arrays
        List<Object[]> countryDetails = em.createQuery(
                "SELECT c.name, c.area, c.continent FROM Country c") 
                    .getResultList();

        // Constructor expression
        List<MayorAndCity> mayorsAndCities = em.createQuery(
                "SELECT NEW com.lishman.world.queries.MayorAndCity(m.name, m.city.name) " + 
                "FROM Mayor m ")
                    .getResultList();
       
        em.close();
    }
}
WorldAccess.java
package com.lishman.world;

import javax.persistence.EntityManager;
import javax.persistence.Persistence;

import com.lishman.world.domain.City;
import com.lishman.world.domain.Continent;
import com.lishman.world.domain.Country;

public class WorldAccess {
    
    public static void main(String[] args) {
        
        EntityManager em = Persistence
                            .createEntityManagerFactory("world")
                            .createEntityManager();

        // One-to-Many
        Country germany = em.find(Country.class, 1);
        int citiesCount = germany.getCities().size();
    
        // One-to-One
        City city = germany.getCities().get(0);
        String name = city.getMayor().getName();     
        
        // Many-to-Many
        Continent asia = em.find(Continent.class, 2);
        int oceanCount = asia.getOceans().size();
        
        // Cascading persistence
        em.getTransaction().begin();
        Continent europe = (Continent) em.find(Continent.class, 3);
        
        Country italy = new Country();
        italy.setName("Italy");
        italy.setArea(116346);
        italy.setPopulation(56995744);
        italy.setContinent(europe);
        
        europe.getCountries().add(italy);
        
        em.getTransaction().commit();          
        
    }
}
orm.xml
<?xml version="1.0" encoding="UTF-8"?>

<entity-mappings xmlns="http://java.sun.com/xml/ns/persistence/orm"
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 version="1.0"
                 xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm http://java.sun.com/xml/ns/persistence/orm_1_0.xsd">
</entity-mappings>
persistence.xml
<?xml version="1.0" encoding="UTF-8"?>

<persistence xmlns="http://java.sun.com/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             version="1.0"
             xsi:schemaLocation="
           http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
                
  <persistence-unit name="world">

    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>com.lishman.world.domain.Country</class>
    <class>com.lishman.world.domain.Continent</class>
    <class>com.lishman.world.domain.City</class>
    <class>com.lishman.world.domain.Mayor</class>
    <class>com.lishman.world.domain.Ocean</class>
    
    <properties>
        <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect"/>
        <property name="hibernate.connection.url" value="jdbc:oracle:thin:@my_server:1521:my_db"/>
        <property name="hibernate.connection.driver_class" value="oracle.jdbc.OracleDriver"/>
        <property name="hibernate.connection.username" value="my_user"/>
        <property name="hibernate.connection.password" value="my_password"/>
        <property name="hibernate.show_sql" value="true"/>
        <property name="hibernate.format_sql" value="true"/>
    </properties>

  </persistence-unit>
</persistence>

Overview

JPA lets you map associations between persistent classes to reflect the relationships between the underlying database tables.
This schema allows us to examine the different types of relationships between entities:
  • CONT (Continent)
  • BORD (Border)
  • OCEAN (Ocean)
  • CTRY (Country)
  • CITY (City)
  • MAYOR (Mayor)
We will map these tables to the equivalent JPA entities.

One-to-Many

The @OneToMany annotation maps the association between Country and City with one-to-many multiplicity; a country has many cities, but a city can only exist in one country.

We use a typed collection interfaceList:eq(1) to reference the 'many' side of the relationship.

The @JoinColumn@JoinColumn:eq(1) annotation specifies the foreign key column""CTRY_ID"":eq(1) on the CITY table. This tells Hibernate how the two tables are to be joined in the database.
So a Country can reference its citiesCity.
However, if we switch to the City class we see that the reverse is not true. A City cannot reference the owning Country.
This is an example of a unidirectional relationship, where only one side of the association can access the otherCity.

The next section (Many-to-One) shows how a bidirectional relationship is implemented.
We have also specified that the City collection is to be eagerlyEAGER loaded.
This means that Hibernate will fetch the City data using a joinjoin:eq(0) to the CITY table when the Country entity is initially loadedfind.
Alternatively, if the fetch type is set to lazy (the default on a @OneToMany), then the City data will only be retrieved when the association is accessedgetCities.

Many-to-One

The Continent class also has a one-to-many@OneToMany relationship, with Country.
However, this time we have a bidirectional relationship because Country has a many-to-one@ManyToOne association back to the Continent.
In a bidirectional association, one side must be the owner. This is usually the side that defines the foreign key constraintFOREIGN on its underlying table.
The owner includes the @JoinColumn@JoinColumn:eq(0) annotation, which tells Hibernate the name of the foreign key columnCONT_ID in the database.
The non-owning side is called the inverse side of the relationship.

This entity indicates that the other side is the owner by naming the owning propertycontinent" of the relationship, in the @OneToMany annotation.
By default, if a child entity of a many-to-one relationship (ie an entity on the 'many' side) is retrieved, then the parent data is also retrieved using a join. In other words, the default fetch behavior for a many-to-one association is eager.

Use a fetch type of LAZY to override this default behavior, and only load the parent object if it is accessed from the child.

One-to-One

City defines a one-to-one@OneToOne relationship with Mayor.
If we take a look at the Mayor class we see that there is also a @OneToOne association with City. This is another example of a bi-directional relationship.

With a bi-directional one-to-one association, one side must own the relationship. In this case, Mayor is the owner so the foreign key columnCITY_ID is specified here.
On the City side, the @OneToOne annotation indicates that the city property on the Mayor entity is the owner of the relationship.
Incidentally, the City entity does not include the @Table annotation. In this case, Hibernate will attempt to map the entity to a table with the same name as the classCity:eq(0).

Many-to-Many

Finally, we map a many-to-many@ManyToMany relationship between Continent and Ocean (an ocean can border many continents and a continent can be bordered by many oceans).
To support this in the database we create an association table called BORD.
BORD contains two foreign keysFOREIGN which reference the tables on both sides of the many-to-many relationship.
Ocean is the owner of this relationship so we use the @JoinTable annotation to name the association tableBORD, the foreign key column of the owning entityOCEAN_ID":eq(1), and the foreign key column of the inverse entityCONT_ID.
The Continent class identifies the continents field on the Ocean class as the owner of the relationship.

API

Hibernate automatically retrieves data for associated entities as we navigate the object graphget:lt(6):not(:eq(1)):not(:eq(1)):not(:eq(2)).
State changes made to persistent objects can be automatically propagated to associated objects. This is known as transitive persistence.

For example, when Continenteurope:last is updated in a transaction, all the related Countryitaly:last objects are automatically updated too.
This is made possible because we have told Hibernate to cascade all persistence operations on Continent to the Country collection.

Individual persistence operations such as PERSIST, REFRESH and REMOVE can be specified for more fine grained control.
Home  |  Getting Started  |  Associations  |  Using Queries  |   |  lishblog  |  Email Lishy