Предыстория

Эта статья будет полезной, если вам часто приходится искать более новые и изощренные решения в процессе реализации фичи. Мне, например, нужно было применить динамические фильтры к выборке из БД. При этом их нужно было использовать не в одном месте, а, например, в блоке WITH. Реализация через JPA Specification выглядела довольно сложно, а возможно, оказалась бы вообще невыполнимой. Даже при помощи JPA Repository потребовалось бы много лишних операций, маппингов и слияний. 

«Без паники», — сказал мой тимлид, полез в браузер, словно волшебник в карман за эликсиром, и достал оттуда ссылку на неприметную библиотеку Pebble Templates, которая оказалась довольно удобной для реализации необходимой фичи. 

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

«А почему бы и нет?», — подумал я и взялся за решение. Особенно заманчивым оказалось то, что отпала необходимость писать свою реализацию замены переменных на значения, обработку данных и так далее. Это позволило избежать ошибок, десятка строк лишнего кода и сэкономило массу времени. Зачем изобретать колесо, если жизнь сама дает в руки велосипед? И да, данная библиотека работает под Spring Boot, что было как нельзя кстати.

Посмотрим более подробно

Подключить библиотеку в проект очень просто — через Maven или Gradle. С сайта берем актуальную версию, на данный момент это версия 3.1.5

Maven

<dependency>
      <groupId>io.pebbletemplates</groupId>
      <artifactId>pebble-spring-boot-starter</artifactId>
      <version>3.1.5</version>
</dependency>

Gradle

implementation("io.pebbletemplates:pebble-spring-boot-starter:3.1.5")

Поддержка AutoConfiguration - это прекрасно: можно сконфигурировать в application.yml или application.properties под свои нужды. Для настройки доступно десяток параметров, про них можно почитать подробнее на официальном сайте, в разделе Spring Boot Integration

Но есть два параметра, о которых стоит упомянуть сейчас:

  • pebble.prefix: где хранятся шаблоны. По умолчанию /templates/

  • pebble.suffix: формат файлов шаблона. По умолчание он .pebble

Сами же переменные обернуты в двойные фигурные скобки и выглядят так - {{ name }}

Как это всё готовить?

Очень просто. Для начала создаем Spring Boot проект, подключаем библиотеку, по примеру выше.

Вторым шагом будет добавление параметров в application.yml(в моем случае)

Я буду хранить файл .sql в каталоге sql

pebble:
  prefix: /sql/
  suffix: .sql

В каталоге resources создаем необходимый каталог, в моем случае это sql

Структура resources
Структура resources

Структура resources

Пока все идет прекрасно. Что же дальше? А дальше создать необходимо сам шаблон.

Создаем в каталоге нужный файл формата, который вы указали в параметре suffix, в моем случае это .sql

Файл шаблона в каталоге
Файл шаблона в каталоге

Файл шаблона в каталоге

select f.*
from foo f
where {{ filter }}

Так как это всего-навсего пример для демонстрации и понимания, то не будем его усложнять лишней логикой. В реалиях шаблоны могут быть намного сложнее.

Пример из реальной реализации
with sla_ticket as (     
	select tt.id as ticketId     
  from  tickets tt     
  {{ actual_task_filter }}     
  where tt.ticket_type = 1 and tt.curator_uuid notnull {{ filter }}) 
select     
	u.name as author_name,     
  u.id as author_id,     
  count(tt1.id) as amount_of_tt,     
  round((count(tt1.id)*100)::decimal /((select count (*) from sla_ticket))::decimal,2) as percent 
from     
	tickets tt1         
  	join sla_ticket st on tt1.id = st.ticketId         
  	join users u on tt1.author_uuid = u.id         
  	where tt1.start_time < {{ filter_date }} 
group by u.name, u.id 
order by u.name {{ limit_offset }}

Как видим, тут необходимо применять несколько фильтров к разным кускам запроса.

Шаблон готов, перейдем к реализации. Я использую Kotlin, но вы можете писать на любом удобном вам языке.

Для начала необходимо составить Map с переменными шаблона и их значениями, которые в дальнейшем будут подставлены в итоговый запрос. Реализация очень проста: ключом будет имя переменной, значением - ее значение. В ключ filter, положили данные f.count = 10.

@Component
open class FilterContextForSqlBuilder {
    open fun filterContextBuild(): Map<String, Any> {
        val filterContext = mutableMapOf<String, Any>()
        filterContext["filter"] = "f.count = 10" 
        return filterContext
    }
}

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

const val SQL_TEMPLATE_FILE_NAME = "sql_example"
const val SQL_CONTEXT_FILTER_NAME = "filter"
const val SQL_CONDITION = "f.count = 10"
const val SQL_TEMPLATE_STRING_FORMAT = "select f.id\nfrom foo f\nwhere {{ filter }}"

Теперь наш класс выглядит так

@Component
open class FilterContextForSqlBuilder {
    open fun filterContextBuild(): Map<String, Any> {
        val filterContext = mutableMapOf<String, Any>()
        filterContext[SQL_CONTEXT_FILTER_NAME] = SQL_CONDITION
        return filterContext
    }
}

Далее реализуем сервис, где будет происходить вся "магия".

Я реализовал в примере два метода. В первом шаблон берется из resources. Во втором шаблон берется в виде строки, это полезно, если, например, шаблоны хранятся в БД, это могут быть шаблоны различных нотификаций.

В первую очередь необходимо заинжектить главный класс библиотеки - PebbleEngine. При помощи данного класса получим шаблон типа PebbleTemplate через вызов метода getTemplate: pebbleEngine.getTemplate("имя_шаблона").

В качестве параметра передаем имя шаблона без расширения, в моем случае это sql_example.

Далее получаем Map с параметрами, вызвав метод компонента, который реализовали ранее - filterContextForSqlBuilder.filterContextBuild(). После создаем объект типа Writer, с которым в дальнейшем и нужно будет работать.

Последний шаг — запись в write готового шаблона sqlTemplate.evaluate(writer, filterContext).

Код сервиса

@Service
open class PebbleTemplateService(
    private var pebbleEngine: PebbleEngine,
    private val filterContextForSqlBuilder: FilterContextForSqlBuilder
) {
    open fun prepareSqlTemplate(): String {
        val sqlTemplate = pebbleEngine.getTemplate(SQL_TEMPLATE_FILE_NAME)
        val filterContext = filterContextForSqlBuilder.filterContextBuild()
        val writer: Writer = StringWriter()
        sqlTemplate.evaluate(writer, filterContext)
        return writer.toString() // or JDBC call
    }

open fun prepareSqlTemplateFromString(): String {
    pebbleEngine = Builder().loader(StringLoader()).build()
    val sqlTemplate = pebbleEngine.getTemplate(SQL_TEMPLATE_STRING_FORMAT)
    val filterContext = filterContextForSqlBuilder.filterContextBuild()
    val writer: Writer = StringWriter()
    sqlTemplate.evaluate(writer, filterContext)
    return writer.toString() // or JDBC call
	}
}

Далее уже заполненный значениями шаблон можно, например, использовать как запрос к БД или отправить в качестве тела письма по email.

Для загрузки шаблона не из resources, нужно реализовать pebbleEngine кастомным образом через вcтроенный билдер: PebbleEngine.Builder().loader(StringLoader()).build()

Остальные действия остаются точно такими же. Более подробно можно так же почитать на официальном сайте.

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

Например, модель фильтра с фронта:

{
    "curatorId": 123456
}

На выходе маппинга получим curatorId = 123456, это выражение уже можно добавлять в мапу filterContext["curator"] = "curatorId = 123456".

Перейдем к тестам

Реализация простого теста на вызов сервиса:

@EnableAutoConfiguration
@SpringBootTest(classes = [PebbleTemplateService::class, FilterContextForSqlBuilder::class])
class PebbleTemplateServiceTest {
    @Autowired
    private lateinit var pebbleTemplateService: PebbleTemplateService
@Test
fun testSqlTemplate() {
    isTrue("select f.*\nfrom foo f\nwhere f.count = 10" == pebbleTemplateService.prepareSqlTemplate(), "")
}
@Test
fun testSqlTemplateFromString() {
    isTrue("select f.id\nfrom foo f\nwhere f.count = 10" == pebbleTemplateService.prepareSqlTemplateFromString(), "")
	}
}

Вызов первого метода сервиса вернул готовый sql запрос. Как видно из результата, вместо filter подставилось ожидаемое условие:

select f.*
from foo f
where f.count = 10

При вызове второго метода, где шаблон получался из строковой константы "select f.id\nfrom foo f\nwhere {{ filter }}" также можно видеть, что в результате мы получили ожидаемый запрос:

select f.id
from foo f
where f.count = 10

Как итог: я сэкономил нервы, время и строки кода. Эта библиотека может быть действительно полезной, особенно если ее правильно приготовить. 

Пример проекта на github

Комментарии (14)


  1. aleksandy
    30.09.2021 15:46
    +1

    Как итог

    На ровном месте получил кучу геморроя с необходимостью самостоятельного экранирования входящих строк и прочих прелестей борьбы с sql-инъекцией. А также невозможности оптимизации запросов планировщиком.

    Нечто подобное в одном проекте реализовывал на freemarker. Но там препроцессинг на основе переданных параметров просто вырезал лишние куски запроса. Шаблон, грубо говоря, содержал запрос со всеми возможными параметрами, но если какой-то из них отсутствовал, то его подстановка в конечный запрос вырезалась.

    Получалось примерно следующее.

    select *
      from person p
      where 1 = 1
        <if test="name">and p.name = :name</if>
        <if test="age">and p.age > :age</if>

    "1 = 1", конечно, не нужно, если имеются какие-либо обязательные условия, которые должны присутствовать в каждом запросе.

    P.S. За корректность синтаксиса тэгов не ручаюсь :).


  1. aerowalk
    30.09.2021 15:51

    Хотелось бы затронуть тему экранирования подставляемых значений, в некотрых случаях это значения колонок для фильтрации, где-то куски SQL. Или за экранирование полностью отвечает пользователь?


    1. vladislavK777 Автор
      30.09.2021 15:57

      да, за экранирование отвечает пользователь. Так же спецсимволы могут перекодироваться в UNICODE, поэтому для безопасности я еще прогонял итоговый Writer через HtmlUtils.htmlUnescape();

      jdbcTemplate.query(htmlUnescape(writer.toString())


  1. headliner1985
    01.10.2021 11:30

    Вы изобрели mybatis?


    1. vladislavK777 Автор
      01.10.2021 11:30

      да вроде нет)


      1. headliner1985
        01.10.2021 11:41

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


        1. vladislavK777 Автор
          01.10.2021 12:48

          да, я знаю про mybatis, и даже работал с ним, но как я помню, там нельзя например вставить целые куски, там вроде по пропертям работа идет.

          Тут же я описал поверхностно, здесь вы сами себе шаблон генерируете, и можете вставить даже пол слова) sel {{ var1 }} from; var1="ect";

          Поэтому считать это велосипедом некорректно)


          1. headliner1985
            01.10.2021 13:50

            можно и куски, и циклы и if-ы, просто надо использовать xml мапперы а не аннотации.


  1. plumqqz
    01.10.2021 23:04

    Вообще в спринге из коробки есть SpEL и совершенно непонятно, чего б его не применить.

    Во-вторых, как уже справедливо отметили выше, для параметров запросов есть параметры.


    1. vladislavK777 Автор
      01.10.2021 23:17

      про другое рассказывать нельзя теперь? Есть spring, mybaris, все караул, рассказали про другое ????

      Ну юзайте спринг, кто не дает -то:) мне вот он не помог в решении задачи.


      1. headliner1985
        02.10.2021 11:53
        +1

        Основная претензия в том, что приходишь на проект, а там куча самописных велосипедов, которые никто не поддерживает и потом приходится банальные вещи не стандартными средствами, а через одно место делать)


        1. vladislavK777 Автор
          02.10.2021 13:03

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


      1. plumqqz
        02.10.2021 16:10

        "Ну юзайте спринг"

        Так и вы его сами используете.


        1. vladislavK777 Автор
          02.10.2021 17:41

          Так я про конкретную фичу, статья же не про спринг)