HQL无法查询JSON数据
HQL无法查询JSON数据
提问于 2020-04-20 12:56:59
这是一个NativeSql工作很好：session.createSQLQuery(select json_length(fav_goods) from customer where id=1).uniqueResult()
，但如果我像下面这样将其更改为HQL，则会很好地引发 session.createQuery(select json_length(favGoods) from CustomerEntity where id=1).uniqueResult()错误。
误差
Caused by: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'json_length' {originalText=json_length}
\-[EXPR_LIST] SqlNode: 'exprList'
\-[DOT] DotNode: 'customeren0_.fav_goods' {propertyName=favGoods,dereferenceType=PRIMITIVE,getPropertyPath=favGoods,path={synthetic-alias}.favGoods,tableAlias=customeren0_,className=cn.phyer.bishe.entity.CustomerEntity,classAlias=null}
+-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}
\-[IDENT] IdentNode: 'favGoods' {originalText=favGoods}
[select json_length(favGoods) from cn.phyer.bishe.entity.CustomerEntity where id=?1]
at org.hibernate.QueryException.generateQueryException(QueryException.java:120)
at org.hibernate.QueryException.wrapWithQueryString(QueryException.java:103)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:220)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:144)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:113)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:73)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:155)
at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:600)
at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:709)
... 39 more
Caused by: org.hibernate.QueryException: No data type for node: org.hibernate.hql.internal.ast.tree.MethodNode
\-[METHOD_CALL] MethodNode: '('
+-[METHOD_NAME] IdentNode: 'json_length' {originalText=json_length}
\-[EXPR_LIST] SqlNode: 'exprList'
\-[DOT] DotNode: 'customeren0_.fav_goods' {propertyName=favGoods,dereferenceType=PRIMITIVE,getPropertyPath=favGoods,path={synthetic-alias}.favGoods,tableAlias=customeren0_,className=cn.phyer.bishe.entity.CustomerEntity,classAlias=null}
+-[IDENT] IdentNode: '{synthetic-alias}' {originalText={synthetic-alias}}
\-[IDENT] IdentNode: 'favGoods' {originalText=favGoods}在实体类CustomerEntity中，字段fav_goods被命名为favGoods
Stack Overflow用户
发布于 2021-07-14 16:06:07
我也遇到了类似的问题，我不得不使用JSON_EXTRACT mysql函数。
- Extend
MySQL5Dialect类以在Hibernate.
中注册SQL函数
public class CustomMySQLDialect extends MySQL5Dialect {
public CustomMySQLDialect(){
super();
registerFunction(
"JSON_EXTRACT",
new StandardSQLFunction(
"JSON_EXTRACT",
StandardBasicTypes.STRING
)
);
}
}- 在Hibernate cfg xml
中注册自定义MySQL方言
<property name="hibernate.dialect">com.testsigma.specification.CustomMySQLDialect</property>- 在Hibernate.
中使用CriteriaQuery(JPQL)和SQL函数。
Root<EntityType> subRoot = criteriaBuilder.from(Entity.class);
subQuery.select(builder.function("JSON_EXTRACT", String.class, subRoot.get("jsonData"), builder.literal("$.\"jsonPathField\"")));
query1.where(root.get("jsonKey").in(subQuery));取自https://vladmihalcea.com/hibernate-sql-function-jpql-criteria-api-query/
最后，Spring数据JPA 2.1.9 Hibernate
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/61323054
复制相关文章
相似问题
腾讯云开发者
