Query Language. JPQL

Query Languages

Query Languages

  • Native SQL Queries

  • Java Persistence Query Language (JPQL) as subset Hibernate Query Language (HQL)

  • Criteria API (legacy Hibernate Criteria Queries)

JPQL

JPQL

  • объектно-ориентированный язык запросов, похожий на SQL

  • вместо операций над таблицами работает с persistent objects (entities)

  • вместо операций над столбцами работает с fields

JPQL statements

FROM

  • условие FROM используется, если необходимо загрузить все объекты из DB в memory.

FROM

public class DaoTests {
    @Test
    public void selectTest() {
        EntityManager em = EMUtil.getEntityManager();
        Session session = em.unwrap(Session.class);
        Query query = session.createQuery("from Employee");
        query.list().forEach(System.out::println);
    }
}

FROM

Output
select employee0_.id as id1_2_,
    employee0_.age as age2_2_,
    employee0_.name as name3_2_,
    employee0_.salary as salary4_2_
from Employee employee0_

[Employee{id=1, name='Dmitry, age=30, salary=8500},
Employee{id=2, name='Alex, age=28, salary=5500},
Employee{id=3, name='Sergey, age=40, salary=7500},
Employee{id=4, name='Dmitry, age=40, salary=9500},
Employee{id=5, name='Maria, age=28, salary=3500}]

AS

  • условие AS используется для alias классов в HQL-query, особенно, если используются длинные запросы

SELECT

  • условие SELECT используется для предоставления большего контроля над результатом вывода чем условие FROM

  • если необходимо вывести не все поля объекта, тогда необходимо использовать SELECT

  • можно доставать объекты, которые являются field данной entity при помощи SELECT

WHERE

  • условие WHERE используется для выборки результата с фильтрацией

  • можно использовать ключевые слова после условия WHERE:

    • =, >=, ⇐, <>, !=, like

    • in, not in, between, is null, is not null, is empty, is not empty, member of, not member of

    • current_date(), current_time(), current_timestamp()

    • substring(), trim(), lower(), upper(), abs(), sqrt(), bit_length(), mod(), str()

SELECT CASE

  • CASE {operand} WHEN {test_value} THEN {match_result} ELSE {miss_result} END

  • CASE [ WHEN {test_conditional} THEN {match_result} ]* ELSE {miss_result} END

ORDER BY

  • условие ORDER BY используется для сортировки результата

  • может использовать один из следующих параметров сортировки:

    • ASC – по возрастанию

    • DESC – по убыванию

ORDER BY

public class DaoTests {
    @Test
    public void orderByTest() {
        EntityManager em = EMUtil.getEntityManager();
        Session session = em.unwrap(Session.class);
        Query query = session.createQuery("from Employee order by salary desc");
        query.list().forEach(System.out::println);
    }
}

ORDER BY

Output
select employee0_.id as id1_2_,
        employee0_.age as age2_2_,
        employee0_.name as name3_2_,
        employee0_.salary as salary4_2_
from Employee employee0_
order by employee0_.salary desc

Employee{id=4, name='Dmitry, age=40, salary=9500}
Employee{id=1, name='Dmitry, age=30, salary=8500}
Employee{id=3, name='Sergey, age=40, salary=7500}
Employee{id=2, name='Alex, age=28, salary=5500}
Employee{id=5, name='Maria, age=28, salary=3500}

GROUP BY

  • условие GROUP BY используется для группировки собранных данных по какому-либо field объекта

GROUP BY

public class DaoTests {
    @Test
    public void groupByTest() {
        EntityManager em = EMUtil.getEntityManager();
        javax.persistence.Query query = em.createQuery(
            "select count(e.name), e.name from Employee e group by e.name");
        query.getResultList().forEach(employees -> {
            Object[] emp = (Object[]) employees;
            System.out.println("Имя: " + emp[1] + " количество:" + emp[0]);
        });
    }
}

GROUP BY

Output
select count(employee0_.name) as col_0_0_,
        employee0_.name as col_1_0_
from Employee employee0_
group by employee0_.name

Имя: Dmitry количество:2
Имя: Sergey количество:1
Имя: Alex количество:1
Имя: Maria количество:1

Using Named Parameters

Using Named Parameters

  • Named Parameters используются для задания значения переменной в HQL-запрос

Using Named Parameters

public class DaoTests {
    @Test
    public void parameterTest() {
        EntityManager em = EMUtil.getEntityManager();
        javax.persistence.Query query = em.createQuery(
            "from Employee e where e.name= :name");
        query.setParameter("name", "Dmitry")
                .getResultList().forEach(System.out::println);
    }
}

Using Named Parameters

Output
select employee0_.id as id1_2_,
    employee0_.age as age2_2_,
    employee0_.name as name3_2_,
    employee0_.salary as salary4_2_
from Employee employee0_
where employee0_.name=?

Employee{id=1, name='Dmitry, age=30, salary=8500}
Employee{id=4, name='Dmitry, age=40, salary=9500}

Using Named Parameters

public class DaoTests {
    @Test
    public void parameterOrderTest() {
        EntityManager em = EMUtil.getEntityManager();
        javax.persistence.Query query = em.createQuery(
                "from Employee e where e.name=? and e.salary > :salary");
        query.setParameter(0, "Dmitry")
                .setParameter("salary", 5000)
                .getResultList().forEach(System.out::println);
    }
}

Using Named Parameters

Output
select employee0_.id as id1_6_,
    employee0_.age as age2_6_,
    employee0_.name as name3_6_,
    employee0_.salary as salary4_6_
from Employee employee0_
where employee0_.name=? and employee0_.salary>?

binding parameter [1] as [VARCHAR] - [Dmitry]
binding parameter [2] as [INTEGER] - [5000]

Employee{id=9, name='Dmitry, age=30, salary=8500}
Employee{id=12, name='Dmitry, age=40, salary=9500}

Using Named Parameters

public class DaoTests {
    @Test
    public void parameterListTest() {
        EntityManager em = EMUtil.getEntityManager();
        javax.persistence.Query query = em.createQuery(
            "from Employee e where e.id in(:ids)");
        query.setParameter("ids", Stream.of(1L,4L).collect(Collectors.toList()))
                .getResultList().forEach(System.out::println);
    }
}

Using Named Parameters

Output
select employee0_.id as id1_2_,
    employee0_.age as age2_2_,
    employee0_.name as name3_2_,
    employee0_.salary as salary4_2_
from Employee employee0_
where employee0_.id in (? , ?)

Employee{id=1, name='Dmitry, age=30, salary=8500}
Employee{id=4, name='Dmitry, age=40, salary=9500}

Another statements

UPDATE

  • условие UPDATE используется для обновления полей и свойств объектов в HQL

DELETE

  • условие DELETE используется для удаления одного или более объектов

INSERT

  • условие INSERT используется для внесения одной записи из другой, или другого объекта

Aggregate Methods

HQL содержит ряд агрегационных функций:

  • avg(property name)

  • max(property name)

  • min(property name)

  • sum(property name)

  • count(property name or *)

  • count(…​)

  • count(distinct …​)

  • count(all …​)

JOIN

JOIN

public class DaoTests {
    @Test
    public void joinTest() {
        EntityManager em = EMUtil.getEntityManager();
        List<Author> authors = em.createQuery(
                "select distinct a " +
                        "from Author a " +
                        "left join a.books b " +
                        "where b.title = 'War & Piece'", Author.class)
                .getResultList();
    }
}

JOIN

Output
select distinct author0_.id as id1_0_,
    author0_.name as name2_0_
from Author author0_
    left outer join Book books1_ on author0_.id=books1_.author_id
where books1_.title='War & Piece'

select books0_.author_id as author_i4_1_0_,
    books0_.id as id1_1_0_,
    books0_.id as id1_1_1_,
    books0_.author_id as author_i4_1_1_,
    books0_.title as title2_1_1_,
    books0_.year as year3_1_1_
from Book books0_
where books0_.author_id=?

Author(id=1, name=Tolstoy, books=[
          Book{id=2, title='Alice', year=1872, author=Tolstoy},
          Book{id=3, title='War & Piece', year=1869, author=Tolstoy},
          Book{id=4, title='Philipok', year=1865, author=Tolstoy}
])

JOIN …​ WITH/JOIN …​ ON

public class DaoTests {
    @Test
    public void withJoinTest() {
        EntityManager em = EMUtil.getEntityManager();
        List<Author> authors = em.createQuery(
                "select distinct a " +
                        "from Author a " +
                        "inner join a.books b on b.title = 'War & Piece'")
                .getResultList();
        authors.forEach(System.out::println);
    }
}

JOIN …​ WITH/JOIN …​ ON

Output
select distinct author0_.id as id1_0_,
    author0_.name as name2_0_
from Author author0_
    inner join Book books1_ on author0_.id=books1_.author_id
            and (books1_.title='War & Piece')

select books0_.author_id as author_i4_1_0_,
    books0_.id as id1_1_0_, books0_.id as id1_1_1_,
    books0_.author_id as author_i4_1_1_,
    books0_.title as title2_1_1_,
    books0_.year as year3_1_1_
from Book books0_
where books0_.author_id=?

Author(id=1, name=Tolstoy, books=[
    Book{id=2, title='Alice', year=1872, author=Tolstoy},
    Book{id=3, title='War & Piece', year=1869, author=Tolstoy},
    Book{id=4, title='Philipok', year=1865, author=Tolstoy}
])

Pagination using Query

  • Pagination - это разбиение результата на страницы, т.е. на коллекции части ограниченного размера.

  • Для пагинации в hibernate существуют следующие методы:

    • Query setFirstResult(int startPosition)

    • Query setMaxResults(int maxResult)