HQL-检查数组是否包含值 [英] HQL - Check if an Array contains a value

问题描述

我的第一个实体类中有一个数组字段，如下所示:

I have an array field in my first entity Class as below:

class Entity1{
       private Integer col1;
       private String col2;
       private Integer[] col3Arr;
}

我还有另一个实体类，如下所示:

I have another entity class as below:

class Entity2{
       private Integer col1;
       private String col2;
       private Integer col3;
}

如果col3Arr包含来自实体2的值col3，我将通过连接多个其他实体来获取记录，并且必须与之一起加入Entity1

I am fetching records by joining multiple other entities along with which I have to join Entity1 if col3Arr contains a value col3 from Entity 2

使用PSQL，我可以通过以下语句轻松实现这一目标

With PSQL, I could easily achieve this by following statement

//Other part of query
join Entity2 e2 on (//conditions from other joined tables//)
join Entity1 e1 on e2.col3=ANY(e1.col3Arr)

什么是HQL?还是HQL中还有其他方法可以检查数组是否包含特定值?

What is the HQL equivalent of ANY? Or is there any other way in HQL to check if an array contains a specific value?

推荐答案

要映射数组，您将需要一个自定义类型.您可以为此使用hibernate-types项目:

For mapping the arrays you will need a custom type. You can use the hibernate-types project for this: https://vladmihalcea.com/how-to-map-java-and-sql-arrays-with-jpa-and-hibernate/

您尝试使用 e2.col3 = FUNCTION('ANY'，e1.col3Arr)了吗?如果这不起作用，我建议您创建一个自定义的 SQLFunction ，以呈现所需的SQL，例如

Did you try to use e2.col3 = FUNCTION('ANY', e1.col3Arr) yet? If that doesn't work, I would suggest you create a custom SQLFunction that renders the SQL you desire e.g.

public class ArrayAny implements SQLFunction {

    @Override
    public boolean hasArguments() {
        return true;
    }

    @Override
    public boolean hasParenthesesIfNoArguments() {
        return true;
    }

    @Override
    public Type getReturnType(Type firstArgumentType, Mapping mapping) throws QueryException {
        return firstArgumentType;
    }

    @Override
    public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
        return "any(" + args.get(0) + ")";
    }
}

您将必须在方言中注册该功能.

You will have to register the function within the Dialect.

这篇关于HQL-检查数组是否包含值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！