Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы увидим, насколько легко сопоставить столбец 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, вы можете сопоставлять многие другие типы баз данных, такие как МАССИВ, Хранилище, диапазон, набор или пользовательские перечисления.