Функции JSON в PostgreSQL с Hibernate 5

Учебники
Hibernate PostgreSQL

Базы данных Postgres поддерживают несколько типов JSON и специальные операции для этих типов.

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

В этой статье мы сосредоточимся на том, как использовать операции JSON в проектах, использующих фреймворк Hibernate версии 5.

Пример Модели

Наша модель выглядит следующим образом:

Java

  

@Entity
@Table(name = "item")
public class Item {

    @Id
    private Long id;

    @Column(name = "jsonb_content", columnDefinition = "jsonb")
    private String jsonbContent;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getJsonbContent() {
        return jsonbContent;
    }

    public void setJsonbContent(String jsonbContent) {
        this.jsonbContent = jsonbContent;
    }
}

Важно!: Мы могли бы использовать конкретный тип JSON для свойства jsonbContent, но в версии Hibernate 5 это не даст каких-либо преимуществ с точки зрения операций.

Операция DDL:

SQL

  

create table item (
       id int8 not null,
        jsonb_content jsonb,
        primary key (id)
    )

Для целей презентации предположим, что наша база данных содержит такие записи:

SQL

  

INSERT INTO item (id, jsonb_content) VALUES (1, '{"top_element_with_set_of_values":["TAG1","TAG2","TAG11","TAG12","TAG21","TAG22"]}');
INSERT INTO item (id, jsonb_content) VALUES (2, '{"top_element_with_set_of_values":["TAG3"]}');
INSERT INTO item (id, jsonb_content) VALUES (3, '{"top_element_with_set_of_values":["TAG1","TAG3"]}');
INSERT INTO item (id, jsonb_content) VALUES (4, '{"top_element_with_set_of_values":["TAG22","TAG21"]}');
INSERT INTO item (id, jsonb_content) VALUES (5, '{"top_element_with_set_of_values":["TAG31","TAG32"]}');

-- элемент без каких-либо свойств, просто пустой json
INSERT INTO item (id, jsonb_content) VALUES (6, '{}');

-- целочисленные значения
INSERT INTO item (id, jsonb_content) VALUES (7, '{"integer_value": 132}');
INSERT INTO item (id, jsonb_content) VALUES (8, '{"integer_value": 562}');
INSERT INTO item (id, jsonb_content) VALUES (9, '{"integer_value": 1322}');

-- значения с плавающей запятой
INSERT INTO item (id, jsonb_content) VALUES (10, '{"double_value": 353.01}');
INSERT INTO item (id, jsonb_content) VALUES (11, '{"double_value": -1137.98}');
INSERT INTO item (id, jsonb_content) VALUES (12, '{"double_value": 20490.04}');

-- значения перечислений
INSERT INTO item (id, jsonb_content) VALUES (13, '{"enum_value": "SUPER"}');
INSERT INTO item (id, jsonb_content) VALUES (14, '{"enum_value": "USER"}');
INSERT INTO item (id, jsonb_content) VALUES (15, '{"enum_value": "ANONYMOUS"}');

-- строковые значения
INSERT INTO item (id, jsonb_content) VALUES (16, '{"string_value": "this is full sentence"}');
INSERT INTO item (id, jsonb_content) VALUES (17, '{"string_value": "this is part of sentence"}');
INSERT INTO item (id, jsonb_content) VALUES (18, '{"string_value": "the end of records"}');

-- вложенные элементы
INSERT INTO item (id, jsonb_content) VALUES (19, '{"child": {"pets" : ["dog"]}}');
INSERT INTO item (id, jsonb_content) VALUES (20, '{"child": {"pets" : ["cat"]}}');
INSERT INTO item (id, jsonb_content) VALUES (21, '{"child": {"pets" : ["dog", "cat"]}}');
INSERT INTO item (id, jsonb_content) VALUES (22, '{"child": {"pets" : ["hamster"]}}');

Подход с использованием Native Query

В Hibernate 5 можно использовать подход с использованием Native Query, где мы выполняем прямую команду SQL.

Важно!: Пожалуйста, для целей презентации опустите тот факт, что нижеприведенный код позволяет осуществить SQL-инъекцию для оператора LIKE. Конечно, для таких действий следует использовать параметры и PreparedStatement.

Java

  


private EntityManager entityManager;

public List<Item> findAllByStringValueAndLikeOperatorWithNativeQuery(String expression) {
        return entityManager.createNativeQuery("SELECT * FROM item i WHERE i.jsonb_content#>>'{string_value}' LIKE '" + expression + "'", Item.class).getResultList();
    }

В приведенном выше примере используется оператор #>>, который извлекает подобъект JSON по указанному пути как текст (подробности см. в документации Postgres).

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

Posjsonhelper

Hibernate 5 изначально не поддерживает функции Postgres JSON. К счастью, вы можете реализовать их самостоятельно или использовать библиотеку posjsonhelper, которая является проектом с открытым исходным кодом.

Проект существует в центральном репозитории Maven, поэтому вы можете легко добавить его, добавив как зависимость в ваш проект Maven.

XML

  

        <dependency>
            <groupId>com.github.starnowski.posjsonhelper</groupId>
            <artifactId>hibernate5</artifactId>
            <version>0.1.0</version>
        </dependency>

Чтобы использовать библиотеку posjsonhelper в вашем проекте, вам нужно использовать диалект Postgres, реализованный в проекте. Например:

com.github.starnowski.posjsonhelper.hibernate5.dialects.PostgreSQL95DialectWrapper ...

В случае, если в вашем проекте уже имеется пользовательский класс диалекта, то также возможно использование:

com.github.starnowski.posjsonhelper.hibernate5.PostgreSQLDialectEnricher;

Использование Компонентов Критериев

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

Java

  

	private EntityManager entityManager;

    public List<Item> findAllByStringValueAndLikeOperator(String expression) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Item> query = cb.createQuery(Item.class);
        Root<Item> root = query.from(Item.class);
        query.select(root);
        query.where(cb.like(new JsonBExtractPathText((CriteriaBuilderImpl) cb, singletonList("string_value"), root.get("jsonbContent")), expression));
        return entityManager.createQuery(query).getResultList();
    }

Hibernate создаст SQL-код, как показано ниже:

SQL

  

select
            item0_.id as id1_0_,
            item0_.jsonb_content as jsonb_co2_0_ 
        from
            item item0_ 
        where
            jsonb_extract_path_text(item0_.jsonb_content,?) like ?

Функция jsonb_extract_path_text является функцией Postgres, эквивалентной оператору #>> (подробности см. в документации Postgres, которую вы ранее просматривали).

Операции над Массивами

Библиотека поддерживает несколько операторов функций JSON Postgres, таких как:

  • ?& – Проверяет, существуют ли все строки в текстовом массиве в качестве ключей верхнего уровня или элементов массива. Таким образом, если у нас есть свойство JSON, содержащее массив, то можно проверить, содержит ли он все элементы, которые вы ищете.
  • ?| – Проверяет, существует ли хотя бы одна из строк в текстовом массиве в качестве ключа верхнего уровня или элемента массива. Таким образом, если у нас есть свойство JSON, содержащее массив, то можно проверить, содержит ли он хотя бы один из элементов, которые вы ищете.

Требуемые изменения DDL

Оператор выше не может быть использован в HQL из-за специальных символов. Поэтому их необходимо оборачивать, например, в пользовательской SQL-функции. Библиотека Posjsonhelper требует две пользовательские SQL-функции, которые будут оборачивать эти операторы. Для стандартных настроек эти функции будут иметь следующую реализацию.

PLSQL

  

CREATE OR REPLACE FUNCTION jsonb_all_array_strings_exist(jsonb, text[]) RETURNS boolean AS $$
SELECT $1 ?& $2;
$$ LANGUAGE SQL;

CREATE OR REPLACE FUNCTION jsonb_any_array_strings_exist(jsonb, text[]) RETURNS boolean AS $$
SELECT $1 ?| $2;
$$ LANGUAGE SQL;

Для получения более подробной информации о том, как настроить или добавить программно требуемые изменения DDL, проверьте раздел “Применение изменений DDL“.

“?&” Обертка

Ниже приведен пример кода, иллюстрирующий, как создать запрос, который просматривает записи, для которых JSON-свойство, содержащее массив, имеет все строковые элементы, которые мы ищем.

Java

  

    
	private EntityManager entityManager;

	public List<Item> findAllByAllMatchingTags(Set<String> tags) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Item> query = cb.createQuery(Item.class);
        Root<Item> root = query.from(Item.class);
        query.select(root);
        query.where(new JsonbAllArrayStringsExistPredicate(hibernateContext, (CriteriaBuilderImpl) cb, new JsonBExtractPath((CriteriaBuilderImpl) cb, singletonList("top_element_with_set_of_values"), root.get("jsonbContent")), tags.toArray(new String[0])));
        return entityManager.createQuery(query).getResultList();
    }

В случае, если теги содержали бы два элемента, то Hibernate сгенерировал бы следующий SQL:

SQL

  

select
            item0_.id as id1_0_,
            item0_.jsonb_content as jsonb_co2_0_ 
        from
            item item0_ 
        where
            jsonb_all_array_strings_exist(jsonb_extract_path(item0_.jsonb_content,?), array[?,?])=true

“?|” Обертка

Ниже приведен пример кода, иллюстрирующий, как создать запрос, который просматривает записи, для которых JSON-свойство, содержащее массив, имеет хотя бы один строковый элемент, который мы ищем.

Java

  

	private EntityManager entityManager;
    public List<Item> findAllByAnyMatchingTags(HashSet<String> tags) {
        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Item> query = cb.createQuery(Item.class);
        Root<Item> root = query.from(Item.class);
        query.select(root);
        query.where(new JsonbAnyArrayStringsExistPredicate(hibernateContext, (CriteriaBuilderImpl) cb, new JsonBExtractPath((CriteriaBuilderImpl) cb, singletonList("top_element_with_set_of_values"), root.get("jsonbContent")), tags.toArray(new String[0])));
        return entityManager.createQuery(query).getResultList();
    }

В случае, если теги содержали бы два элемента, то Hibernate сгенерировал бы следующий SQL:

SQL

  

select
            item0_.id as id1_0_,
            item0_.jsonb_content as jsonb_co2_0_ 
        from
            item item0_ 
        where
            jsonb_any_array_strings_exist(jsonb_extract_path(item0_.jsonb_content,?), array[?,?])=true

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

Заключение

В некоторых случаях типы JSON и функции PostgreSQL могут быть хорошими альтернативами базам данных NoSQL. Это может сэкономить нам время на принятие решения о добавлении NoSQL-решений в наш технологический стек, что также может добавить больше сложности и дополнительных затрат.

Source:
https://dzone.com/articles/postgres-json-functions-with-hibernate-5