Postgres数据库支持一些JSON类型以及这些类型的特殊操作。
在某些情况下，这些操作可能是MongoDB或其他NoSQL数据库等文档数据库的良好替代方法。当然，像MongoDB这样的数据库可能有更好的复制过程，但这个主题不在本文的讨论范围之内。
在本文中，我们将重点介绍如何在使用版本 5 的 Hibernate 框架的项目中使用 JSON 操作。
示例模型
我们的模型如下所示：
@Entity@Table(name = "item")public class Item {@Idprivate 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 jsonINSERT INTO item (id, jsonb_content) VALUES (6, '{}');-- int valuesINSERT 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 valuesINSERT 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 valuesINSERT 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 valuesINSERT 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 elementsINSERT 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代码，如下所示：
selectitem0_.id as id1_0_,item0_.jsonb_content as jsonb_co2_0_fromitem item0_wherejsonb_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：
selectitem0_.id as id1_0_,item0_.jsonb_content as jsonb_co2_0_fromitem item0_wherejsonb_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：
selectitem0_.id as id1_0_,item0_.jsonb_content as jsonb_co2_0_fromitem item0_wherejsonb_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开发工具等。
