如何在Hibernate 5框架的项目中使用Postgres JSON函数

Postgres数据库支持一些JSON类型以及这些类型的特殊操作。

在某些情况下，这些操作可能是MongoDB或其他NoSQL数据库等文档数据库的良好替代方法。当然，像MongoDB这样的数据库可能有更好的复制过程，但这个主题不在本文的讨论范围之内。

在本文中，我们将重点介绍如何在使用版本 5 的 Hibernate 框架的项目中使用 JSON 操作。

示例模型

我们的模型如下所示：

@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;
    }
}
复制

重要提示：我们可以为jsonbContent属性使用特定的JSON类型，但在Hibernate 5版本中，这不会从操作的角度带来任何好处。

DDL 操作：

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

出于演示目的，我们假设我们的数据库包含以下记录：

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"]}');
-- item without any properties, just an empty json
INSERT INTO item (id, jsonb_content) VALUES (6, '{}');
-- int values
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}');
-- double values
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}');
-- enum values
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"}');
-- string values
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"}');
-- inner elements
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"]}}');复制

本地查询方法

在Hibernate 5中，我们可以使用本地查询方法，直接执行SQL命令。 

重要提示：为了演示目的，请省略下面的代码允许使用LIKE运算符的表达式进行SQL注入的事实。当然，对于这样的操作，我们应该使用参数和PreparedStatement。

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 项目来轻松添加它。

   <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复制

使用Criteria组件

下面的示例具有与使用本地查询的先前示例类似的行为。但是，在这种情况下，我们将使用条件构建器。

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代码，如下所示：

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文档以获取更多详细信息）。 

数组操作 

该库支持一些Postgres JSON函数运算符，例如：

• ?&- 检查文本数组中的所有字符串是否存在为顶级键或数组元素。因此，通常如果我们有包含数组的JSON属性，则可以检查它是否包含您正在搜索的所有元素。 

• ?| - 检查文本数组中的任何字符串是否存在为顶级键或数组元素。因此，通常如果我们有包含数组的JSON属性，则可以检查它是否至少包含您正在搜索的元素之一。 

所需的DDL更改 

由于特殊字符的原因，上述运算符不能在HQL中使用。这就是为什么我们需要将它们包装在自定义SQL函数中的原因。Posjsonhelper库需要两个自定义SQL函数来包装这些运算符。对于默认设置，这些函数将具有以下实现。

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属性的记录，其中所有字符串元素都是我们正在搜索的元素。

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：

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属性的记录，其中至少有一个字符串元素是我们正在搜索的元素。

  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：

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测试。 

结论 

在某些情况下，Postgres JSON类型和函数可以成为NoSQL数据库的良好替代方案。这可以使我们免于决定将NoSQL解决方案添加到我们的技术堆栈中，这也可能增加更多的复杂性和额外的成本。

作者：Szymon Tarnowski

更多技术干货请关注公号“云原生数据库”

squids.cn，目前可体验全网zui低价RDS，免费的迁移工具DBMotion、SQL开发工具等。