Написание запросов в Spring Data JPA

4 ноября 2020

Тэги: Collections, Hibernate, Kotlin, PostgreSQL, Spring Boot, Spring Data, SQL.

Содержание

Таблица, сущность и репозиторий
Фильтрация по одному параметру
Регистронезависимый поиск
Объединение условий через И
Объединение условий через ИЛИ
Фильтрация по множеству значений
Инверсия фильтрации
Фильтрация по null
Поиск по подстроке
Фильтрация по диапазонам чисел
Фильтрация по диапазонам дат
Сортировка
Постраничный вывод
Явное указание запросов
Выводы

В статье CrudRepository на Kotlin я рассказывал о том, как Spring Data позволяет быстро создавать слой взаимодействия с БД, поддерживающий все основные операции: создание, чтение, обновление и удаление. Для получения этой стандартной функциональности вам достаточно лишь определить класс-сущность, поля которой такие же как и в целевой таблице в БД, и интерфейс самого репозитория, который можно унаследовать от стандартного интерфейса CrudRepository. Реализовывать интерфейс при этом не нужно – Spring Data всё сделает за вас.

Помимо стандартных методов вы также можете добавить в этот интерфейс свои собственные. Причём если вы будете следовать соглашениям об именовании методов, то Spring Data будет автоматически генерировать по ним sql-запросы. То есть вы определяете запросы к БД в декларативном стиле. Это, во-первых, позволяет давать методам удобочитаемые имена, а во-вторых, позволяет абстрагироваться от конкретной СУБД и специфики написания запросов к ней.

Таблица, сущность и репозиторий

Итак, возьмём классы из уже упомянутой статьи. Приложение, рассмотренное в ней, работает с музыкальными группами. Напоминаю, что таблица в БД определяется следующим sql (пример для postgres):

create table band
(
  id serial,
  name character varying(50) not null,
  players_count integer, -- может содержать null
  created date not null,
  constraint band_pk primary key (id)
);

Класс-сущность к ней выглядит следующим образом:

@Entity
@Table(name = "band")
data class Band(

        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        val id: Int = 0,

        val name: String,

        @Column(name = "players_count")
        val playersCount: Int? = null,

        val created: LocalDate
)

Здесь поле playersCount допускает неопределённые значения (null). Если имя таблицы совпадает с названием класса, то аннотацию @Table можно не использовать. Аналогично можно сказать и про имена: если имя поля в таблице совпадает с именем поля в сущности, то аннотацию @Column можно пропустить.

Репозиторий, работающий с этой сущностью, представлен следующим интерфейсом:

interface BandDao : CrudRepository<Band, Int>

Как видите, он уже типизирован классом Band.

Для наглядности вы можете в application.properties установить параметр spring.jpa.properties.hibernate.show_sql в true. Тогда в консоли во время выполнения запроса вы будете видеть sql, который будет генерить Spring Data.

Фильтрация по одному параметру

Для того, чтобы выбрать все записи, одно из полей которых равно определённому значению, имя метода должно начинаться с findBy. Затем добавляем название нужного поля и соответствующий параметр:

interface BandDao : CrudRepository<Band, Int> {
// пример вызова: bandDao.findByName("Queen")
fun findByName(name: String): List<Band>
}

В данном случае мы выбираем все группы, название которых соответствует указанной строке (фактически, ищем определённую группу) и возвращаем список. Сгенерированный sql будет выглядеть примерно так:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ where band0_.name=?

Вы можете задаться вопросом, почему мы возвращаем список, если по факту имя группы практически однозначно определяет запись в таблице? Во-первых, это не всегда так. Согласитесь, теоретически могут существовать две группы с одинаковым названием (ну если только мы не повесим ограничение уникальности на уровне БД). Однозначное соответствие может дать только поиск по id. Во-вторых, возвращать список – это более универсальный подход, т.к. если бы мы в сигнатуре метода возвращали один объект, а в таблице нашлось бы более одного элемента, тогда Spring Data кидало бы исключение NonUniqueResultException. Если бы не нашлось ни одного элемента, то выбрасывалось бы исключение EmptyResultDataAccessException.

fun findByName(name: String): Band // ошибка в случае нескольких записей и в случае пустого результата
fun findByName(name: String): List<Band> // нет ошибок в любом случае

Так или иначе, в Kotlin всегда можно из списка получить первый элемент с помощью метода firstOrNull(). В случае, если список пустой – метод вернёт null. И тогда мы сможем это корректно обработать.

Регистронезависимый поиск

В рассмотренном выше примере поиска по названию группы очень важно соблюсти регистр названия. Например, если вместо «Queen» передать в качестве параметра «queen», то метод вернёт пустой результат. Чтобы этого избежать, добавим к имени метода IgnoreCase:

// bandDao.findByNameIgnoreCase("queen")
fun findByNameIgnoreCase(name: String): List<Band>

На уровне sql регистронезависимый поиск достигается путём приведения и параметра, и значения поля к верхнему регистру с помощью sql-функции upper():

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ where upper(band0_.name)=upper(?)

Объединение условий через И

Если мы хотим фильтровать не только по имени, а ещё и, например, по количеству участников музыкальной группы, то можно объединить два разных условия через And. В репозиторий добавим такой метод:

// bandDao.findByNameAndPlayersCount("Beatles", 4)
fun findByNameAndPlayersCount(name: String, playersCount: Int): List<Band>

Sql будет выглядеть так:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ where band0_.name=? and band0_.players_count=?

Параметры запроса должны следовать в том же порядке, в каком они перечислены в имени метода.

Объединение условий через ИЛИ

А что, если мы хотим выбрать две конкретные группы по их названию? Мы можем объединить условия через Or.

// bandDao.findByNameOrName("Queen", "Beatles")
fun findByNameOrName(name1: String, name2: String): List<Band>

Spring Data сгенерит такой sql:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ where band0_.name=? or band0_.name=?

Фильтрация по множеству значений

А как быть, если нам нужно выбрать по определённым названиям ещё больше групп? Можно было бы конечно добавить ещё больше Or, но это сделает имя метода нечитаемым. В sql это можно сделать с помощью конструкции in. Такому же соглашению следует и Spring Data.

// bandDao.findByNameIn(setOf("Beatles", "Queen", "Scorpions"))
fun findByNameIn(names: Set<String>): List<Band>

Параметр метода в данном случае я типизировал как Set, чтобы исключить наличие дублей при вызове метода. Однако вы можете типизировать такой параметр и более общим интерфейсом Collection. В любом случае сгенерится следующий sql:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ where band0_.name in (? , ?, ?)

При этом даже если мы передадим пустую коллекцию, ошибки не будет и метод ожидаемо вернёт пустой список в качестве результата.

Инверсия фильтрации

Если мы хотим выбрать все группы, кроме какой-то определённой, мы можем применить инверсию, добавив Not к имени метода. По смыслу это означает «не равно»:

// bandDao.findByNameNot("Beatles")
fun findByNameNot(name: String): List<Band>

Получим такой sql:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ where band0_.name<>?

Фильтрация по null

Поскольку значение поля playersCount может быть не определено, нам может потребоваться выбрать только те группы, для которых это значение указано. Нам поможет конструкция IsNotNull.

fun findByPlayersCountIsNotNull(): List<Band>

Если же нам нужно выбрать только группы, у которых количество участников не определено, просто убираем Not:

fun findByPlayersCountIsNull(): List<Band>

На уровне sql также будет использовать конструкция is null/is not null.

Поиск по подстроке

Давайте теперь найдём все группы, начинающиеся с определённой буквы. Воспользуемся конструкцией StartingWith:

// bandDao.findByNameStartingWith("Q")
fun findByNameStartingWith(prefix: String): List<Band>

Если подстроку нужно искать не в начале, а в середине имени, тогда нам поможет Containing:

// bandDao.findByNameContaining("ee")
fun findByNameContaining(substring: String): List<Band>

Если же нужно искать по окончанию имени, тогда будем использовать EndingWith

// bandDao.findByNameEndingWith("les")
fun findByNameEndingWith(postfix: String): List<Band>

На уровне sql во всех трёх случаях будет использоваться выражение like. При этом к самой подстроке добавлять символ процента, в отличие от sql, не требуется.

Фильтрация по диапазонам чисел

Если мы хотим фильтровать группы не по точному значению количества участников, а по некоторым допустимым интервалам, то нам помогут greaterThan, lessThan и between.

Выберем все группы, в которых больше четырёх участников (т.е. 5 и более):

// bandDao.findByPlayersCountGreaterThan(4)
fun findByPlayersCountGreaterThan(playersCount: Int): List<Band>

Сгенерится следующий sql:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ where band0_.players_count>?

Теперь выберем все группы, в которых меньше четырёх участников (от 0 до 3-х):

// bandDao.findByPlayersCountLessThan(4)
fun findByPlayersCountLessThan(playersCount: Int): List<Band>

Если же хотим включать граничное значение, просто добавим слово Equal:

fun findByPlayersCountLessThanEqual(playersCount: Int): List<Band>
fun findByPlayersCountGreaterThanEqual(playersCount: Int): List<Band>

А если мы хотим выбрать группы, у которых от 3 до 4 участников (включительно), то мы бы конечно могли назвать метод таким образом:

// bandDao.findByPlayersCountGreaterThanEqualAndPlayersCountLessThanEqual(3, 4)
fun findByPlayersCountGreaterThanEqualAndPlayersCountLessThanEqual(minCount: Int, maxCount: Int): List<Band>

И он превратился бы в такой sql:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ where band0_.players_count>=? and band0_.players_count<=?

Но это уже выглядит слишком громоздко. Поэтому воспользуемся эквивалентной конструкцией Between (минимальное и максимальное значения включаются):

// bandDao.findByPlayersCountBetween(3, 4)
fun findByPlayersCountBetween(minCount: Int, maxCount: Int): List<Band>

На уровне sql это также транслируется в between:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ where band0_.players_count between ? and ?

Фильтрация по диапазонам дат

Когда мы работаем с датами, в имени методов так же можно использовать LessThan, GreaterThan и т.п. Однако именно для даты удобнее использовать Before и After. Создадим пару методов, один из которых будет возвращать все группы, созданные до определённой даты, а другой – после:

// все группы, созданные до 1 января 1990
// bandDao.findByCreatedBefore(LocalDate.of(1990, 1, 1))
fun findByCreatedBefore(date: LocalDate): List<Band>

// все группы, созданные после 1 января 1990
// bandDao.findByCreatedAfter(LocalDate.of(1990, 1, 1))
fun findByCreatedAfter(date: LocalDate): List<Band>

На уровне sql эти методы будут генерить точно такой же sql, как и в предыдущем разделе:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ where band0_.created<?

Сортировка

Когда результат выборки мы получаем в виде списка, рано или поздно встанет вопрос, по какому полю нужно этот список сортировать. Сортировать можно конечно и в самом приложении, но лучше сделать это на уровне БД, т.к. она сделает это быстрее. Добавим к имени метода OrderBy. Например, выберем все группы и отсортируем их по названию в алфавитном порядке от «А» до «Я»:

fun findByOrderByName(): List<Band>

В БД будет выполнен следующий запрос:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ order by band0_.name asc

Можно также сортировать в обратном порядке, т.е. от «Я» до «А». Просто добавим слово Desc в конце:

fun findByOrderByNameDesc(): List<Band>

Получим такой sql:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ order by band0_.name desc

Постраничный вывод

В случае больших списков встаёт также вопрос и в разбиении результата на страницы для удобства отображения (т.н. «пагинация»). То есть вы можете указать порядковый номер страницы (начиная с нуля) и количество записей на странице и в результате получите фрагмент списка. Spring Data позволяет сделать это путём добавления в метод всего одного параметра типа Pageable. При вызове метода указания номера страницы и размер странице делается с помощью PageReqest.

// bandDao.findByOrderByName(PageRequest.of(1, 10)) - разбить результат на страницы по 10 записей и вернуть вторую страницу
fun findByOrderByName(page: Pageable): List<Band>

На уровне sql для postgres постраничный вывод достигается путём использования limit и offset:

select band0_.id as id1_0_, band0_.created as created2_0_, band0_.name as name3_0_, band0_.players_count as players_4_0_ from band band0_ order by band0_.name asc limit ? offset ?

Обратите внимание, что для постраничного вывода необходимо использовать сортировку, иначе один конкретный элемент при выполнении двух одинаковых запросов может оказываться то на одной странице, то на другой, т.е. сортировка по факту окажется случайной.

Если вам требуется кроме самих элементов данной страницы также дополнительная мета-информация, вроде общего количества записей, просто замените List на Page в типе возвращаемого значения:

fun findByOrderByName(page: Pageable): Page<Band>

Объект Page содержит несколько полезных полей:

content – сам список значений для данной страницы
totalElements – общее количество элементов, соответствующих заданным условиям
totalPages – всего страниц (по факту это общее количество элементов, делённое на размер страницы)

Явное указание запросов

Если следовать рассмотренным выше соглашениям об именовании методов по каким-то причинам невозможно, например, название становится слишком длинным, вы можете указать JPA-запрос в явном виде с помощью аннотации @Query. От sql-запроса jpa-запрос отличается тем, что вы оперируете не полями таблицы, а полями объекта, и выборку вы также делаете как бы из объекта. Например, аналог фильтрации группы по имени, рассмотренной выше, будет выглядеть так:

@Query("select b from Band b where b.name = :name")
fun filterByName(name: String): List<Band>

Алиас b, который используется в этом запросе, может быть любым. Он определяется сразу после Band. Если вы используете явное указание запроса через @Query, то само имя метода вы можете выбрать произвольно, исходя из своих потребностей. В данном случае следовать соглашениям об именовании уже не требуется.

Если нам нужно фильтровать по двум полям, то просто добавляем в метод ещё один параметр и добавляем условие в запрос. При этом порядок следования параметров в сигнатуре метода может быть произвольным. Главное, чтобы его имя совпадало с именем параметра в jpa-запросе.

@Query("select b from Band b where b.name = :name and b.playersCount = :count")
fun filterByCountAndName(count: Int, name: String): List<Band>

Однако если вам потребуется изменить имя параметра в сигнатуре метода, это также возможно. Просто укажите через аннотацию @Param имя этого параметра в jpa-запросе:

@Query("select b from Band b where b.name = :name and b.playersCount = :count")
fun filterByCountAndName(@Param("count") someParam: Int, name: String): List<Band>

Как видите, Spring Data при помощи аннотаций поддаётся довольно гибкой настройке. Однако в какой-то момент таких аннотаций может стать слишком много и преимущества декларативного написания запросов могут быть потеряны.

Выводы

Spring Data JPA предоставляет широкие возможности по кастомизации запросов к БД благодаря соглашениям об именовании. Декларативный способ объявления запросов позволяет абстрагироваться от особенностей конкретной СУБД, что делает ваше приложении более гибким. К недостаткам такого подхода можно отнести порой слишком длинные имена методов. В таком случае бывает удобнее явно указать запрос через аннотацию @Query и дать методу более краткое название.

Облако тэгов

Kotlin, Java, Spring, Spring Boot, Spring Data, SQL, PostgreSQL, Oracle, Linux, Hibernate, Collections, Stream API, многопоточность, файлы, Nginx, Apache, maven, gradle, JUnit, YouTube, новости, руководство, ООП, алгоритмы, головоломки, rest, GraphQL, Excel, XML, json, yaml.