如何用QueryDSL在PostgreSQL中获取JSON列的isOption1属性

项目环境 #

• Spring Boot :: (v2.6.0)
• Java 11.0.15
• Hibernate ORM core version 5.6.1.Final
• com.vladmihalcea:hibernate-types-55:2.20.0

问题说明 #

需从实体TranOrdTest的JSON类型列tranCarOption中提取isOption1属性，使用QueryDSL编写查询后抛出QuerySyntaxException，不含JSON属性提取逻辑的查询可正常执行，要求不使用原生SQL实现。

报错信息 #

org.springframework.dao.InvalidDataAccessApiUsageException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: with near line 2, column 63 [select tranOrdTest.uid, tranOrdTest.tranOrdKey, tranOrdTest.tranCarOption, tranOrdTest_tranCarOption_0 as isOption1
from tranOrdTest.tranCarOption as tranOrdTest_tranCarOption_0 with key(tranOrdTest_tranCarOption_0) = ?1, kr.co.conc.deliveryserver.biz.tr.tranOrd.entity.TranOrdTest tranOrdTest]; nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: with near line 2, column 63 [select tranOrdTest.uid, tranOrdTest.tranOrdKey, tranOrdTest.tranCarOption, tranOrdTest_tranCarOption_0 as isOption1
from tranOrdTest.tranCarOption as tranOrdTest_tranCarOption_0 with key(tranOrdTest_tranCarOption_0) = ?1, kr.co.conc.deliveryserver.biz.tr.tranOrd.entity.TranOrdTest tranOrdTest]

解决方案 #

错误原因 #

原查询生成的HQL使用了with key(...)语法，这不是HQL支持的JSON属性提取方式，导致语法解析失败。以下是基于现有技术栈的几种可行方案：

方案1：利用Hibernate Types的JSON映射+QueryDSL路径访问

如果将JSON列映射为Map<String, Object>类型，可直接通过QueryDSL的路径表达式访问属性：

1. 实体类配置：

import com.vladmihalcea.hibernate.type.json.JsonType;
import org.hibernate.annotations.Type;
import javax.persistence.Column;
import javax.persistence.Entity;
import java.util.Map;

@Entity
public class TranOrdTest {
    // 其他字段...
    
    @Type(type = "json")
    @Column(columnDefinition = "json")
    private Map<String, Object> tranCarOption;
    
    // getter/setter
}

2. QueryDSL查询代码：

import com.querydsl.core.Tuple;
import com.querydsl.jpa.impl.JPAQueryFactory;
import static kr.co.conc.deliveryserver.biz.tr.tranOrd.entity.QTranOrdTest.tranOrdTest;

// 注入JPAQueryFactory
private final JPAQueryFactory queryFactory;

public List<Tuple> getTranOrdWithOption1() {
    return queryFactory.select(
            tranOrdTest.uid,
            tranOrdTest.tranOrdKey,
            tranOrdTest.tranCarOption,
            // 直接通过Map的get方法访问JSON属性，QueryDSL会生成正确的HQL
            tranOrdTest.tranCarOption.get("isOption1").as(Boolean.class).as("isOption1")
        )
        .from(tranOrdTest)
        .fetch();
}

方案2：使用QueryDSL自定义函数模板适配数据库JSON函数

如果JSON列映射为JsonNode（Jackson类型），可通过自定义函数模板调用数据库原生JSON提取函数：

1. 实体类配置：

import com.vladmihalcea.hibernate.type.json.JsonNodeBinaryType;
import org.hibernate.annotations.Type;
import com.fasterxml.jackson.databind.JsonNode;
import javax.persistence.Column;
import javax.persistence.Entity;

@Entity
public class TranOrdTest {
    // 其他字段...
    
    @Type(type = "json") // 或根据数据库类型用jsonb
    @Column(columnDefinition = "json")
    private JsonNode tranCarOption;
    
    // getter/setter
}

2. QueryDSL查询代码：
   根据使用的数据库选择对应函数：

• MySQL：使用JSON_EXTRACT

import com.querydsl.core.types.Expression;
import com.querydsl.core.types.dsl.Expressions;

public List<Tuple> getTranOrdWithOption1() {
    // 自定义表达式提取isOption1
    Expression<Boolean> isOption1 = Expressions.numberTemplate(Boolean.class,
        "JSON_EXTRACT({0}, '$.isOption1')", tranOrdTest.tranCarOption);
    
    return queryFactory.select(
            tranOrdTest.uid,
            tranOrdTest.tranOrdKey,
            tranOrdTest.tranCarOption,
            isOption1.as("isOption1")
        )
        .from(tranOrdTest)
        .fetch();
}

• PostgreSQL：使用->>操作符

Expression<Boolean> isOption1 = Expressions.numberTemplate(Boolean.class,
    "{0}->>'isOption1'", tranOrdTest.tranCarOption);

方案3：扩展QueryDSL支持Hibernate Types的JSON函数

Hibernate Types提供了JsonExtractFunction等扩展函数，可注册到QueryDSL中使用：

1. 注册自定义函数：

import com.vladmihalcea.hibernate.type.json.function.JsonExtractFunction;
import com.querydsl.jpa.JPQLTemplates;
import com.querydsl.jpa.impl.JPAQueryFactory;
import javax.persistence.EntityManager;

// 在配置类中创建JPAQueryFactory时注册函数
@Bean
public JPAQueryFactory jpaQueryFactory(EntityManager entityManager) {
    JPQLTemplates templates = JPQLTemplates.DEFAULT;
    templates.addFunction("json_extract", new JsonExtractFunction());
    return new JPAQueryFactory(templates, entityManager);
}

2. 查询代码：

public List<Tuple> getTranOrdWithOption1() {
    Expression<Boolean> isOption1 = Expressions.numberTemplate(Boolean.class,
        "json_extract({0}, '$.isOption1')", tranOrdTest.tranCarOption);
    
    return queryFactory.select(
            tranOrdTest.uid,
            tranOrdTest.tranOrdKey,
            tranOrdTest.tranCarOption,
            isOption1.as("isOption1")
        )
        .from(tranOrdTest)
        .fetch();
}

内容的提问来源于stack exchange，提问作者Alvin