Using Queries
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>

Running Queries

Queries are executed using the Java Persistence Query Language (JPQL).

JPQL is a language very similar to SQL, but instead of using tables and columns in our queries, we use entity names and properties.
Queries are defined using createQuerycreateQuery:eq(0), which accepts a Java persistence query string, or createNamedQuerycreateNamedQuery:eq(0), which specifies a named query.
Named queries@NamedQueries are defined on an entity and identified by a unique nameOcean.find within the persistence unit.
Queries are executed using getResultListgetResultList:eq(0) or getSingleResultgetSingleResult:eq(0).

getResultListgetResultList:eq(0) populates a java.util.List with all the rows returned from a query.

getSingleResultgetSingleResult:eq(0) expects exactly one row to be returned, and throws an exception if this is not the case.
Pagination is supported by the setFirstResult and setMaxResults methods.
With Oracle, this means wrapping the query with a rownum condition to limit the results.
The result type of a query can be an entityContinent:gt(0):lt(4):not(:eq(1)):not(:eq(1)), a scalarString:gt(0):lt(2) or an array of ObjectsObject:lt(2), depending on the type of data that is selected.

Results can also be mapped to a custom objectMayorAndCity:eq(0) by specifying the NEW operatorNEW, and adding the results to the object constructor.

Java Persistence Query Language (JPQL)

Based on SQL, JPQL includes the standard SELECTSELECT:eq(0), FROMFROM:eq(0), WHEREWHERE:eq(0) and ORDER BYORDER BY:eq(0) clauses, as you would expect.
Projection allows one or more entity properties to be returned into scalar valuesString:eq(1) or object arraysObject[]:eq(0).

As we have seen, these results can be mapped to a custom object using the NEW operatorNEW.
Aggregate functionsAVG (AVG, COUNT, MIN, MAX, and SUM) can also be used in a query.

Like SQL, aggregate results can be grouped and filtered using the GROUP BY and HAVING clauses.
Associations between entities can be referenced within a query.

For example, we can access Continent and Oceanc.continent.ocean directly from the Country entity.
JPQL also supports joinsJOIN:gt(0) in order to navigate entity associations.



Parameter Binding

Parameter binding allows values to be substituted into a query at runtime.

For example, :name and :pop are placeholders for the country name and population. The real values are substituted at runtime using the setParametersetParameter:lt(2) method.
Entities can be used as parameter values as well as a scalars.

For example, we can return a list of counties in Africa using the Continentafrica entity as the parameter value.
JPQL also supports positional?1 parameters and parameter binding for named queriescreateNamedQuery.
Parameter binding is essential for security and performance.
Parameters help to safeguard against the SQL injection security vulnerability.
Performance can also be improved enormously using parameter binding by avoiding hard parses in the database.
Home  |  Getting Started  |  Associations  |  Using Queries  |   |  lishblog  |  Email Lishy