Pass value of type 'Set' to custom sql function

Hibernate ORM
1

I am trying to create a containsAll custom SQL-function for PostgreSQL but I have difficulties with passing the value to the SQL.

The part of the output in the SQL is:
(kba1_0.labels @> bytea ‘\xaced0005737200116a6176612e7574696c2e48617368536574ba44859596b8b7340300007870770c000000103f400000000000017400066c6162656c3178’).

So argument(0) seems to be fine and the issue is about how argument(1) is treated. It converts the set into this binary which I don’t want.

My code looks like the following:

public class ContainsAll extends StandardSQLFunction {
    public static final String NAME = "__containsAll";

    public ContainsAll() {
        super(NAME, false, StandardBasicTypes.BOOLEAN);
    }

  @Override
      public void render(
          SqlAppender sqlAppender, List<? extends SqlAstNode> arguments, ReturnableType<?> returnType,
          SqlAstTranslator<?> translator
      ) {
          sqlAppender.append('(');
          arguments.get(0).accept(translator);
          sqlAppender.append(" @> ");
          arguments.get(1).accept(translator); // How to do this correctly here?
          sqlAppender.append(")");
      }
}

Here is how i call the function (value is the 'Set") please don’t wonder about the weird method signature:

public static <T extends BaseEntity, A extends Serializable> Specification<T> containsAll2(
        SingularAttribute<? super T, ? extends Collection> attribute, Collection<A> value
    ) {
        return (root, query, builder) -> {
            return builder.isTrue(builder.function(ContainsAll.NAME,
                Boolean.class,
                root.get(attribute),
                builder.literal(value)
            ));
        };
    }
2

Why don’t you update to Hibernate ORM 6.4+ which already has these functions?
I don’t know how your model looks like or what the value of the collection actually is, but maybe there is a bug hiding. So please try to create a reproducer with our test case template and if you are able to reproduce the issue, create a bug ticket in our issue tracker and attach that reproducer.

3

I cannot use the built in functions because I want to use a set in the entity and not an array. But the built-in functions in Hibernate 6.4 expect an array type.

The field of the generated Meta Model looks like that:

public static volatile SingularAttribute<KnowledgeBaseArticle, Set<String>> labels;

Thats the first argument of the function containsAll2(). The second Argument is a Set of strings.

I mean is the actual way correct to do arguments.get(1).accept(translator);?

and

return builder.isTrue(builder.function(ContainsAll.NAME,
                Boolean.class,
                root.get(attribute),
                **builder.literal(value)** // <------ ?
            ));
4

How did you come to that conclusion? Apparently you didn’t try it, because it just works. Hibernate ORM supports collections just as well.

Yes, that’s correct.

5

Thank you very much Sir. The new built in functions (I did not know that they exist) really helped us to migrate our application successfully.

But it is still strange that in my custom function the Set was translated to a bytea.... However, when using the built in functions collectionContains etc. everything works fine.

6

Like I wrote, if you think there is a bug, please provide a reproducer and attach that to a newly created Jira issue.