如何使用查询DSL处理mysql json列？

Question

想知道如何看待mysql json专栏？

product table:
...
    category_ids                     json                         null,
...

category_ids例如） [19， 102， 108]

如果我想搜索包含类别id 102的产品列表，如何使用queryDSL进行查询？

我尝试了JsonNode类型，但它不起作用。

Answer 1

根据这些标记，我假设您使用查询列表创建 JPQL（或Hibernate HQL）查询。这一点很重要，因为为了让JSON类型与查询一起使用，它们不仅必须使用查询和MySQL，而且最重要的是使用查询语言本身，在这种情况下来自Hibernate。

我假设您已经成功地将json列正确地映射到JacksonsJsonNode类型。如果没有，那么重要的是要注意，对于任何非标准类型，都需要在Hibernate中注册自定义类型。您可以制作自己的自定义类型，也可以使用现有的实现。我强烈推荐Vlad开发的hibernate-type库（包括我自己在内的一些人的贡献）。如何将hibernate-type集成到您的项目中，在他对Stackoverflow问题的回答中得到了很好的解释：https://stackoverflow.com/a/37946530/2104280

您需要做的第一件事是在项目pom中设置以下Hibernate Types Maven依赖项。xml配置文件：

   <dependency>
       <groupId>com.vladmihalcea</groupId>
       <artifactId>hibernate-types-52</artifactId>
       <version>${hibernate-types.version}</version>
   </dependency>

现在，您需要在类级别或package-info.java包级别描述符中声明JsonType，如下所示：

   @TypeDef(name = "json", typeClass = JsonType.class)

并且，实体映射将如下所示:

   @Type(type = "json")
   @Column(columnDefinition = "jsonb")
   private Location location;

现在，您将能够在查询中的简单操作中使用 JSON 属性。允许的操作基本上包括基本比较（=、！= 和 IS（非）空）。其他操作不可用，因为这些操作仅在 MySQL 中定义，而不是在 JPQL 或 HQL 查询语言中定义。

您要检查 JSON 数组是否包含特定元素。我假设category_ids表示一个 JSON 数组。那么这意味着你想要生成的MySQL代码段很可能是category_ids？ ：类别Id。

我想指出的是，这仅适用于存储为字符串的ID，而不是JSON数组中的数字。您可能希望将category_ids映射为双关语[]。与 JSON 类型一样，Hibernate类型库也支持数组类型，因此答案对于数组的工作方式相同。

但是，JSON_CONTAINS（category_ids，：categoryId）不是有效的HQL/JPQL，因为该查询语言中不存在该函数。如果我们想在HQL/JPQL中呈现JSON_CONTAINS（category_ids，：categoryId），我们需要定义一个自定义函数，将JSON_CCONTAINS。

有两种方法可以在Hibernate中声明自定义函数。

1. 扩展您正在使用的方言，并在那里声明函数。https://stackoverflow.com/a/42486291/2104280 中很好地描述了这种方法。
2. 使用元数据生成器初始化程序 SPI 在引导期间注册自定义函数。https://stackoverflow.com/a/41369853/2104280 中介绍了此方法。

使用< code > MetadataBuilderInitializer 方法，代码将如下所示:

public class JSONMetadataBuilderInitializer
        implements MetadataBuilderInitializer {
    @Override
    public void contribute(MetadataBuilder metadataBuilder,
            StandardServiceRegistry serviceRegistry) {
        metadataBuilder.applySqlFunction("json_contains_key",
            new SQLFunctionTemplate(BooleanType.INSTANCE,
                "JSON_CONTAINS(?1, ?2, '$'"));
    }
}

最后，您最终会为您的JsonNode属性获得一个Hibernate类型，以及一个用于检查是否存在密钥的自定义函数。密钥可以在JPQL/HQL中如下使用：

SELECT product FROM Product product WHERE json_contains_key(product.categories, '1234') = true;

这将产生以下 SQL：

SELECT * FROM product WHERE JSON_CONTAINS(categories, '1234', '$') = true;

然而，您希望通过Querydsl构建这个JPQL/HQL查询，这意味着还需要几个步骤。我们需要为创建的函数构造新的表达式类型。基本上有两种选择:

1. 创建一个新的运算符实现，并在扩展的 JPQL 模板中为该运算符注册一个模板。
2. 创建一个模板表达式（字面意思是 JPQL 的片段）。

第二种方法更简单，所以我将在这里使用。使用TemplateExpression，Querydsl中的以下代码将生成上述JPQL查询：

queryFactory.select(QProduct.product)
    .from(QProduct.product)
    .where(Expressions.booleanTemplate(
        "json_contains_key({0}, {1})",
        QProduct.product.categories,
        Expressions.constants("1234").isTrue())
    .fetch();

然而，这并不像您在Querydsl中习惯的那样流畅。可以使用自定义表达式类型扩展Querydsl，并在此假设引入JsonExpression。还可以扩展querydsl apt，以便在静态元模型（Q-classes）中自动生成此表达式类型的路径。我专门为此构建了扩展库hibernate types querydsl apt。它为Hibernate types项目中的自定义类型添加了各种自定义表达式类型（例如范围、数组、json、hstore、间隔）。它还自动注册主操作的自定义函数。使用我的扩展，数组的用例将非常简单：

@Entity
@TypeDefs({
        @TypeDef(name = "int-array", typeClass = IntArrayType.class, defaultForType = int[].class)
})
public class ArrayEntity {
    @Id
    Long id;

    @Type(type = "int-array")
    @Column(name = "sensor_values", columnDefinition = "integer[]")
    int[] sensorValues;
}

List<Tuple> fetch = new JPAQuery<>(entityManager, ExtendedHQLTemplates.DEFAULT)
    .from(arrayEntity)
    .select(arrayEntity)
    .where(arrayEntity.sensorValues.contains(123))
    .fetch();

更多示例请访问：https://github.com/jwgmeligmeyling/hibernate-types-querydsl-apt/blob/master/querydsl-ext-testsuite/src/test/java/com/pallasathenagroup/querydsl/ArrayEntityPathTest.java .