Как сопоставить столбцы JSON SQL Server с помощью JPA и гибернации

Вступление

В этой статье мы увидим, насколько легко сопоставить столбец JSON SQL Server при использовании проекта “Типы гибернации”.

Проект hibernate-types поддерживает типы столбцов JSON для PostgreSQL и MySQL и Oracle , и, как вы увидите в этой статье, JsonType отлично работает с Microsoft SQL Server.

Как сопоставить столбцы JSON SQL Server с помощью JPA и гибернации @vlad_mihalcea Подробнее: https://t.co/NVJFkDegFs pic.twitter.com/Z5THTsNm9V

Хранилище JSON SQL Server

При использовании SQL Server вы можете использовать тип столбца VARCHAR для сохранения объектов JSON. Преимущество хранения JSON в столбце NVARCHAR заключается в том, что запись или чтение всего объекта JSON будет быстрым. Однако оценка выражений пути требует анализа объекта JSON при каждом выполнении.

Тип хранилища JSON SQL Server аналогичен типу столбца json в PostgreSQL, а не типу jsonb , в котором хранится двоичное представление объекта JSON.

Если размер документа JSON не превышает 8 КБ, то лучше использовать тип столбца VARCHAR2(4000) , так как весь объект JSON поместится на одной странице базы данных. Если размер документа JSON превышает 8 КБ, вместо этого можно использовать тип столбца NVARCHAR(MAX) , что позволит хранить документы JSON размером до 2 ГБ.

Сохранение JSON в виде NVARCHAR

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

Для создания таблицы book мы можем использовать следующую инструкцию DDL:

CREATE TABLE book (
    id BIGINT NOT NULL PRIMARY KEY,
    isbn VARCHAR(15),
    properties NVARCHAR(4000) CHECK(
        ISJSON(properties) = 1
    )
)

Обратите внимание , что свойства тип столбца NVARCHAR(4000) , и мы определили проверку ограничений на уровне столбца, которая использует функцию ISJSON SQL Server для проверки того, хранит ли столбец свойства правильный объект JSON.

Чтобы сопоставить таблицу book с сущностью JPA, у нас есть несколько вариантов представления столбца JSON в качестве атрибута сущности. Мы могли бы отобразить его как DTO, если у него есть предопределенная внутренняя схема.

Наиболее гибким способом сопоставления столбца JSON является использование атрибута String entity.

@Entity(name = "Book")
@Table(name = "book")
@TypeDef(
    name = "json", 
    typeClass = JsonType.class
)
public class Book {

    @Id
    private Long id;

    @NaturalId
    @Column(length = 15)
    private String isbn;

    @Type(type = "json")
    private String properties;

    public Long getId() {
        return id;
    }

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

    public String getIsbn() {
        return isbn;
    }

    public Book setIsbn(String isbn) {
        this.isbn = isbn;
        return this;
    }

    public String getProperties() {
        return properties;
    }

    public Book setProperties(String properties) {
        this.properties = properties;
        return this;
    }

    public JsonNode getJsonNodeProperties() {
        return JacksonUtil.toJsonNode(properties);
    }
}

Обратите внимание, что мы используем API в стиле Fluent для сеттеров, что позволит нам упростить процесс создания объекта.

Для получения более подробной информации об использовании построителей сущностей API в стиле Fluent ознакомьтесь с этой статьей .

Тип Json – это тот же тип гибернации, который мы ранее использовали для Oracle или MySQL, и он предлагается проектом “Типы гибернации”.

Теперь, когда сохраняется Книга сущность:

entityManager.persist(
    new Book()
        .setId(1L)
        .setIsbn("978-9730228236")
        .setProperties(
            "{" +
            "   \"title\": \"High-Performance Java Persistence\"," +
            "   \"author\": \"Vlad Mihalcea\"," +
            "   \"publisher\": \"Amazon\"," +
            "   \"price\": 44.99" +
            "}"
        )
);

Hibernate генерирует соответствующую инструкцию SQL INSERT:

INSERT INTO book (
    isbn, 
    properties, 
    id
) 
VALUES (
    '978-9730228236', 
    '{   
        "title": "High-Performance Java Persistence",   
        "author": "Vlad Mihalcea",   
        "publisher": "Amazon",   
        "price": 44.99
    }', 
    1
)

При извлечении сущности Book через ее естественный идентификатор мы видим , что Hibernate извлекает сущность просто отлично:

Book book = entityManager
    .unwrap(Session.class)
    .bySimpleNaturalId(Book.class)
    .load("978-9730228236");

assertEquals(
    "High-Performance Java Persistence",
    book.getJsonNodeProperties().get("title").asText()
);

Мы также можем изменить свойство сущности JSON:

book.setProperties(
    "{" +
    "   \"title\": \"High-Performance Java Persistence\"," +
    "   \"author\": \"Vlad Mihalcea\"," +
    "   \"publisher\": \"Amazon\"," +
    "   \"price\": 44.99," +
    "   \"url\": \"https://amzn.com/973022823X\"" +
    "}"
);

И, Hibernate выдаст соответствующую инструкцию обновления SQL:

UPDATE 
    book 
SET 
    properties =  
    '{   
        "title": "High-Performance Java Persistence",   
        "author": "Vlad Mihalcea",   
        "publisher": "Amazon",   
        "price": 44.99,   
        "url": "https://amzn.com/973022823X"
    }' 
WHERE 
    id = 1

Вы не ограничены использованием атрибута String entity. Вы также можете использовать POJO, учитывая, что свойства POJO соответствуют атрибутам JSON:

На этот раз атрибут свойства сущности будет отображен следующим образом:

@Type(type = "json")
private BookProperties properties;

Использование POJO вместо атрибута JSON на основе строк позволяет нам упростить операции чтения и записи на стороне приложения.

Обратите внимание, как хорошо мы можем создать экземпляр Книги сущности благодаря API в стиле Fluent, используемому как сущностью, так и классом POJO:

entityManager.persist(
    new Book()
        .setId(1L)
        .setIsbn("978-9730228236")
        .setProperties(
            new BookProperties()
                .setTitle("High-Performance Java Persistence")
                .setAuthor("Vlad Mihalcea")
                .setPublisher("Amazon")
                .setPrice(44.99D)
        )
);

Изменение атрибута свойства сущности также намного проще при использовании POJO:

Book book = entityManager
    .unwrap(Session.class)
    .bySimpleNaturalId(Book.class)
    .load("978-9730228236");

book.getProperties().setUrl(
    "https://amzn.com/973022823X"
);

Операторы SQL одинаковы независимо от того, используем ли мы строку или POJO на стороне JPA.

Запрос столбца JSON

Теперь вы также можете запросить содержимое столбца JSON с помощью собственного SQL-запроса. Например, чтобы извлечь книгу цена для заданного значения атрибута название , мы можем выполнить следующий SQL-запрос:

Tuple tuple = (Tuple) entityManager
.createNativeQuery(
    "SELECT " +
    "  id, " +
    "  CAST(JSON_VALUE(properties, '$.price') AS FLOAT) AS price " +
    "FROM book " +
    "WHERE " +
    "  JSON_VALUE(properties, '$.title') = :title", Tuple.class)
.setParameter("title", "High-Performance Java Persistence")
.getSingleResult();

Или вы можете извлечь массив reviews JSON и сопоставить его с JsonNode , как показано в следующем собственном SQL-запросе:

Tuple tuple = (Tuple) entityManager
.createNativeQuery(
    "SELECT " +
    "  id, " +
    "  JSON_QUERY(properties, '$.reviews') AS reviews " +
    "FROM book " +
    "WHERE " +
    "  isbn = :isbn", Tuple.class)
.setParameter("isbn", "978-9730228236")
.unwrap(NativeQuery.class)
.addScalar("id", LongType.INSTANCE)
.addScalar("reviews", new JsonType(JsonNode.class))
.getSingleResult();

Обратите внимание, что мы передали тип Json в запрос Hibernate Native , чтобы Hibernate знал, как обрабатывать сопоставление массива JSON.

Вывод

Сопоставление столбца JSON SQL server довольно просто при использовании проекта “Типы гибернации”, и у вас есть возможность гибко использовать либо POJO, либо атрибут String entity.

Проект “Типы гибернации” предлагает поддержку сопоставления столбцов JSON с атрибутами сущностей JPA во всех 4 ведущих системах баз данных: Oracle, MySQL, SQL Server и PostgreSQL.

Помимо JSON, вы можете сопоставлять многие другие типы баз данных, такие как МАССИВ, Хранилище, диапазон, набор или пользовательские перечисления.

Вступление

Хранилище JSON SQL Server

Сохранение JSON в виде NVARCHAR

Запрос столбца JSON

Вывод

Читайте ещё по теме: