9 JPQL

JPQL looks similar to SQL, but it is based on object model. JPQL query can be statically defined using annotations or XML mappings, or dynamically created at runtime. For example,

Annotation:

@NamedQueries({
    @NamedQuery(name = "jpqlListFullTimeEmployeesBySalary",
        query = "select e from FullTimeEmployee e where e.salary between ?1 and ?2 order by e.salary desc, e.name asc",
        lockMode = LockModeType.PESSIMISTIC_WRITE,
        hints = {@QueryHint(name = "com.cmobilecom.jpa.query.fetchMaxDepth", value = "2")})
})
@Entity
public class Employee {

}

XML mappings:

<entity name="Employee"> 
    <named-query name="jpqlListFullTimeEmployeesBySalary">
        <query>select e from FullTimeEmployee e where e.salary between ?1 and ?2 order by e.salary desc, e.name asc</query>
        <lock-mode>PESSIMISTIC_WRITE</lock-mode>
        <hint name="com.cmobilecom.jpa.query.fetchMaxDepth" value="2"></hint>
    </named-query>
</entity>

Like named native query and stored procedure query, a named JPQL query can be defined directly under <entity-mappings> element in a mapping file.

<entity-mappings>
    
    <named-query name="jpqlListFullTimeEmployeesBySalary">
        <query>select e from FullTimeEmployee e where e.salary between ?1 and ?2 order by e.salary desc, e.name asc</query>
        <lock-mode>PESSIMISTIC_WRITE</lock-mode>
        <hint name="com.cmobilecom.jpa.query.fetchMaxDepth" value="2"></hint>
    </named-query>
    
</entity-mappings>

Run JPQL query

    EntityManager em = emf.createEntityManager();
    TypedQuery<FullTimeEmployee> query = em.createNamedQuery("jpqlListFullTimeEmployeesBySalary", FullTimeEmployee.class); 
    List<FullTimeEmployee> results = query.getResultList();

A JPQL query can be dynamically created by EntityManager.

    EntityManager em = emf.createEntityManager();
    TypedQuery<Object[]> query = em.createQuery("select e.name, e.salary " +
        "from FullTimeEmployee e order by e.salary desc, e.name asc", Object[].class);
    List<Object[]> results = query.getResultList();

Parameter

A parameter can be positional or named.

Positional parameters: e.g., ?1, ?2
Named parameters: e.g., :parameterName

For example,

    EntityManager em = emf.createEntityManager();
    TypedQuery<FullTimeEmployee> query = em.createQuery("select e from FullTimeEmployee e " + 
        "where e.salary>=?1 and e.hiredDate between :date1 and :date2 " + 
        "order by e.salary desc", FullTimeEmployee.class);
    query.setParameter(1, 100000.00);
    
    DateFormat df = DateFormat.getDateInstance(DateFormat.SHORT, Locale.US);
    query.setParameter("date1", df.parse("1/1/2015"));
    query.setParameter("date2", df.parse("12/31/2018"));
    List<FullTimeEmployee> results = query.getResultList();

Literal

JPA supports the following literals:

String: e.g., 'Hello World'
Number: e.g., 100, 100.50
Date: e.g., date {d '1980-01-01'}, time {t '18:30:00'}, timestamp {ts '1980-01-01 18:30:00'}
Enum: e.g., com.cmobilecom.jpa.example.managed_classes.Office$Size.L
Entity Type: entity name, e.g., FullTimeEmployee

For example,

    EntityManager em = emf.createEntityManager();
    TypedQuery<FullTimeEmployee> query = em.createQuery("select e from FullTimeEmployee e " + 
        "where e.salary>=100000.00 and e.hiredDate between {d '2015-01-01'} and {d '2018-12-31'} " + 
        "order by e.salary desc", FullTimeEmployee.class);
    List<FullTimeEmployee> results = query.getResultList();

For other types of literals, use positional or named parameters.

Joins And Fetches

Example: select all employees whose employers' address zip code is 10001, fetching employee's projects and phone numbers.

    EntityManager em = emf.createEntityManager();
    TypedQuery<FullTimeEmployee> query = em.createQuery("select distinct e from Employee e " + 
        "inner join e.employer em left join fetch e.projects left join fetch e.phoneNumbers " +
        "where em.address.zipCode.majorCode='10001' " + 
        "order by e.hiredDate desc", Employee.class);
    List<Employee> results = query.getResultList();

Compound Selection

A selection can be a construct. For example,

    EntityManager em = emf.createEntityManager();
    TypedQuery<FullTimeEmployee> query = em.createQuery(
        "select e, new com.cmobilecom.jpa.example.managed_classes.Employer(em.id, em.name) from Employee e " + 
        "inner join e.employer em", Employee.class);
    List<Employee> results = query.getResultList();

The class Employer must have a corresponding constructor Employer(Long id, String name).

Treat

Treat is downcasting an entity type to one of its subclass entity type to access its attributes. For example,

    TypedQuery<Employee> query = em.createQuery("select distinct e from Employee e " +
        "where treat(e as FullTimeEmployee).salary between 50000 and 200000 " +
        "order by treat(e as FullTimeEmployee).salary asc", Employee.class);
    List<Employee> results = query.getResultList();

Downcast Employee type to subclass FullTimeEmployee type that defines "salary" attribute.

Update

Update attribute values of matched entities. For example,

    Query query = em.createQuery("update Employee e " +
        "set e.name='New Name', e.address.zipCode.majorCode='10002' " +
        "where e.id='123001'");
    int updated = query.executeUpdate();

Extensions: support joins. For example, increase the salaries of all the employees of the Employer(id=1) by 10000.

    Query query = em.createQuery("update Employee e inner join e.employer em " +
        "set e.salary = e.salary + 10000 " +
        "where em.id=1");
    int updated = query.executeUpdate();

Delete

Delete matched entities. For example,

    Query query = em.createQuery("delete from Employee e " +
        "where e.id='123001'");
    int updated = query.executeUpdate();

Extensions: support joins. For example, delete all the employees of the Employer(id=1).

    Query query = em.createQuery("delete from Employee e inner join e.employer em " +
        "where em.id=1");
    int updated = query.executeUpdate();

Extensions

JPQL update and delete extensions: support joins. See sections above.

Expression extensions:

cast_expression ::= CAST( scalar_expression as {STRING | INTEGER | DOUBLE | FLOAT} )

Examples:

    TypedQuery<Object[]> query = em.createQuery("select e.id, cast(e.salary as integer) " + 
        "from FullTimeEmployee e", Object[].class)

See Criteria API for more JPQL features and examples.

← Entity Identifier→ Criteria APIFrames / No Frames