Spring Data JPA 查询 JSOB 类型的列

2024-04-17

1、简介

Spring Data JPA 为与关系数据库交互提供了强大的抽象层。然而，传统的关系表可能并不适合存储复杂的、半结构化的数据，如产品详细信息或用户偏好。这就是 JSONB 数据类型的用武之地。

本文将带你学习使用 Spring Data JPA 查询 JSONB 列的各种方法。

2、JSONB 列

JSONB（JavaScript Object Notation for Databases）是一种数据类型，专门用于在 PostgreSQL 等关系数据库中存储 JSON 数据。它允许我们在单列中使用键值对和嵌套对象来表示复杂的数据结构。通过 JPA Provider（如 Hibernate），Spring Data JPA 允许我们将这些 JSONB 列映射到实体类中的属性。

3、映射 JSONB 列

我们可以使用 @Column 注解的 columnDefinition 属性，在实体类中明确定义列类型：

@Column(columnDefinition = "jsonb")
private String attributes;

这种方法主要与 PostgreSQL 相关，因为 PostgreSQL 本身支持 jsonb 数据类型。通过在实体类的相应属性中添加此注解，我们就能为数据库提供所需列类型的提示。Spring Data JPA 通常会根据数据库列定义自动检测 jsonb 数据类型，因此在很多情况下，此注解是可选的。

4、设置项目依赖和测试数据

创建一个基本的 Spring Boot 项目，其中包含测试 JSONB 查询所需的依赖和测试数据。

4.1、项目设置

首先，在 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 类

接下来，创建一个名为 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、准备测试数据

以下是在运行测试用例之前用来准备数据的 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 原始查询

在本节中，我们将利用原生 SQL 查询和 PostgreSQL JSONB 操作符，根据 JSONB 列中的值过滤和检索数据。

使用 @Query 注解在 Spring Data JPA Repository 接口中定义这些原生查询。

5.1、使用 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 操作符进行嵌套查询

使用 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 函数进行查询

我们还可以在原生 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 函数进行嵌套查询

来看看如何使用 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 的方法

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、总结

本文详细地介绍了使用 Spring Data JPA 查询 JSONB 列的各种方法。对于基本的过滤条件，原生 SQL 查询可能提供了一个简单直接的解决方案。然而，当处理复杂的过滤逻辑或需要可重用性时，JPA Specification 提供了一个有吸引力的替代方案。

Ref：https://www.baeldung.com/spring-data-jpa-querying-jsonb-columns