51工具盒子1、简介 {#1简介}
Spring Data JPA 为与关系数据库交互提供了强大的抽象层。然而，传统的关系表可能并不适合存储复杂的、半结构化的数据，如产品详细信息或用户偏好。这就是 JSONB 数据类型的用武之地。
本文将带你学习使用 Spring Data JPA 查询 JSONB 列的各种方法。
2、JSONB 列 {#2jsonb-列}
JSONB （JavaScript Object Notation for Databases）是一种数据类型，专门用于在 PostgreSQL 等关系数据库中存储 JSON 数据。它允许我们在单列中使用键值对和嵌套对象来表示复杂的数据结构。通过 JPA Provider（如 Hibernate），Spring Data JPA 允许我们将这些 JSONB 列映射到实体类中的属性。
3、映射 JSONB 列 {#3映射-jsonb-列}
我们可以使用 @Column 注解的 columnDefinition 属性，在实体类中明确定义列类型：
@Column(columnDefinition = "jsonb")
private String attributes;
这种方法主要与 PostgreSQL 相关，因为 PostgreSQL 本身支持 jsonb 数据类型。通过在实体类的相应属性中添加此注解，我们就能为数据库提供所需列类型的提示。Spring Data JPA 通常会根据数据库列定义自动检测 jsonb 数据类型，因此在很多情况下，此注解是可选的。
4、设置项目依赖和测试数据 {#4设置项目依赖和测试数据}
创建一个基本的 Spring Boot 项目，其中包含测试 JSONB 查询所需的依赖和测试数据。
4.1、项目设置 {#41项目设置}
首先，在 Maven pom.xml 文件中添加必要的依赖：
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>
4.2、Entity 类 {#42entity-类}
接下来，创建一个名为 Product 的 Java 类来表示实体：
public class Product {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private String name;
// jsonb 类型
@Column(columnDefinition = "jsonb")
private String attributes;
// Getter / Setter 方法
}
Product 实体定义了 id、name 和 attributes 字段 。attributes 字段是一个字符串，用于保存产品详细信息的序列化 JSON 数据。我们使用 @Column(columnDefinition = "jsonb") 来提示数据库将属性列创建为 JSONB 类型。
定义 Product 实体类后，Hibernate 将在应用启动和初始化数据库 Schema 时生成以下 SQL：
CREATE TABLE Product (
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(255),
attributes JSONB
);
4.3、准备测试数据 {#43准备测试数据}
以下是在运行测试用例之前用来准备数据的 SQL 脚本，可以将该脚本保存为 .sql 文件，并将其放在项目的 src/test/resources 目录中：
DELETE FROM product;
INSERT INTO product (name, attributes)
VALUES ('Laptop', '{"color": "red", "size": "15 inch"}');
INSERT INTO product (name, attributes)
VALUES ('Phone', '{"color": "blue", "size": "6 inch"}');
INSERT INTO product (name, attributes)
VALUES ('Headphones', '{"brand": "Sony", "details": {"category": "electronics", "model": "WH-1000XM4"}}');
INSERT INTO product (name, attributes)
VALUES ('Laptop', '{"brand": "Dell", "details": {"category": "computers", "model": "XPS 13"}}');
然后，在测试类中使用 @Sql 注解，并将 executionPhase 属性设置为 BEFORE_TEST_METHOD，以便在每个测试方法执行之前将测试数据插入数据库：
@Sql(scripts = "/testdata.sql", executionPhase = Sql.ExecutionPhase.BEFORE_TEST_METHOD)
5、使用 @Query 原始查询 {#5使用-query-原始查询}
在本节中，我们将利用原生 SQL 查询和 PostgreSQL JSONB 操作符，根据 JSONB 列中的值过滤和检索数据。
使用 @Query 注解在 Spring Data JPA Repository 接口中定义这些原生查询。
5.1、使用 JSONB 操作符进行查询 {#51使用-jsonb-操作符进行查询}
@Query 注解允许我们使用原生 SQL 语法在 Repository 接口中定义自定义查询。这种方法在处理 JSONB 列时特别有用，因为它能让我们在查询中直接使用 PostgreSQL 的原生 JSONB 操作符。
使用 JSONB 操作符编写一个查询，以查找具有特定颜色的所有产品：
public interface ProductRepository extends JpaRepository<Product, Long> {
@Query(value = "SELECT * FROM product WHERE attributes ->> ?1 = ?2", nativeQuery = true)
List<Product> findByAttribute(String key, String value);
}
findByAttribute() 方法接受两个参数，表示 JSON 属性的键（例如 "color"），并通过值来过滤，指示该属性的期望值。@Query 注解定义了一个原生 SQL 查询，利用 ->> 运算符来访问 attributes JSONB 列中的键。
然后，查询中的占位符 ?1 和 ?2 将被替换为执行过程中作为方法参数提供的键和值。
创建一个测试用例来验证 findByAttribute() 方法：
List<Product> redProducts = productRepository.findByAttribute("color", "red");
assertEquals(1, redProducts.size());
assertEquals("Laptop", redProducts.get(0).getName());
5.2、使用 JSONB 操作符进行嵌套查询 {#52使用-jsonb-操作符进行嵌套查询}
使用 PostgreSQL 的 -> 和 ->> 操作符来查询嵌套的 JSONB 数据：
->操作符用于访问 JSONB 对象中的特定键。->>操作符用于以文本形式访问 JSONB 对象中特定键对应的值。
再写一个处理嵌套键的查询：
@Query(value = "SELECT * FROM product WHERE attributes -> ?1 ->> ?2 = ?3", nativeQuery = true)
List<Product> findByNestedAttribute(String key1, String key2, String value);
通过该查询，我们可以搜索特定嵌套属性与给定值相匹配的实体。例如，如果我们要查找 details.category 为 electronics 的产品，我们可以调用此方法，并将 details、category 和 electronics 作为参数：
List<Product> electronicProducts = productRepository.findByNestedAttribute("details", "category", "electronics");
assertEquals(1, electronicProducts.size());
assertEquals("Headphones", electronicProducts.get(0).getName());
5.3、使用 jsonb_extract_path_text 函数进行查询 {#53使用-jsonb_extract_path_text-函数进行查询}
我们还可以在原生 SQL 查询中利用 jsonb_extract_path_text 函数，从 JSONB 数据中提取特定值。jsonb_extract_path_text 是 PostgreSQL 函数，用于根据给定路径从 JSONB 列中提取特定值。
jsonb_extract_path_text 函数以文本形式返回提取的值。如果 JSONB 结构中不存在指定的路径，函数将返回 NULL。
来看一个使用 jsonb_extract_path_text 的示例：
public interface ProductRepository extends JpaRepository<Product, Long> {
@Query(value = "SELECT * FROM product WHERE jsonb_extract_path_text(attributes, ?1) = ?2", nativeQuery = true)
List<Product> findByJsonPath(String path, String value);
}
findByJsonPath() 方法接受两个参数。第一个参数是路径字符串，表示要提取的 JSONB 对象中的特定键。第二个参数表示提取属性的预期值：
List<Product> redProducts = productRepository.findByJsonPath("color", "red");
assertEquals(1, redProducts.size());
assertEquals("Laptop", redProducts.get(0).getName());
5.4、使用 jsonb_extract_path_text 函数进行嵌套查询 {#54使用-jsonb_extract_path_text-函数进行嵌套查询}
来看看如何使用 jsonb_extract_path_text 函数调整查询以处理嵌套键：
@Query(value = "SELECT * FROM product WHERE jsonb_extract_path_text(attributes, ?1, ?2) = ?3", nativeQuery = true)
List<Product> findByNestedJsonPath(String key1, String key2, String value);
提供 key1 和 key2 作为路径元素，以遍历嵌套的 JSONB 结构：
List<Product> electronicProducts = productService.findByNestedJsonPath("details", "category", "electronics");
assertEquals(1, electronicProducts.size());
assertEquals("Headphones", electronicProducts.get(0).getName());
在此示例中，findByNestedJsonPath("details", "category", "electronics") 将会定位到嵌套的 "details.category" 值为 "electronics" 的产品。
6、使用自定义 JPA Specification 的方法 {#6使用自定义-jpa-specification-的方法}
JPA Specification 是封装用于过滤数据的标准的接口。它们定义了需要根据特定条件检索的数据。Spring Data JPA 提供的这个接口允许 Repository 实现接受 Specification<T> 实例作为参数的自定义查询方法。
我们可以创建一个实现 Specification<T> 接口的类。该类定义了 toPredicate() 方法，该方法利用 CriteriaBuilder 根据提供的条件（用于筛选的键和值）构建实际的查询 Predicate：
public class ProductSpecification implements Specification<Product> {
private final String key;
private final String value;
public ProductSpecification(String key, String value) {
this.key = key;
this.value = value;
}
@Override
public Predicate toPredicate(Root root, CriteriaQuery<?> query, CriteriaBuilder cb) {
return cb.equal(
cb.function("jsonb_extract_path_text", String.class, root.get("attributes"), cb.literal(key)),
value
);
}
}
toPredicate() 方法定义了如何使用作为参数提供的 CriteriaBuilder 来构建过滤 Predicate。在本例中，我们假设键位于 JSONB 数据的顶层。
要使用此自定义 Specification，ProductRepository 需要继承 JpaSpecificationExecutor<Product>：
public interface ProductRepository extends JpaRepository<Product, Long>, JpaSpecificationExecutor<Product> {
}
下面是根据 JSONB 列中的属性过滤产品的自定义 Specification 的基本示例：
ProductSpecification spec = new ProductSpecification("color", "red");
Page<Product> redProducts = productRepository.findAll(spec, Pageable.unpaged());
assertEquals(1, redProducts.getContent().size());
assertEquals("Laptop", redProducts.getContent().get(0).getName());
7、总结 {#7总结}
本文详细地介绍了使用 Spring Data JPA 查询 JSONB 列的各种方法。对于基本的过滤条件，原生 SQL 查询可能提供了一个简单直接的解决方案。然而，当处理复杂的过滤逻辑或需要可重用性时，JPA Specification 提供了一个有吸引力的替代方案。
Ref：https://www.baeldung.com/spring-data-jpa-querying-jsonb-columns
