Use function array_position in hibernate criteria 6.4.1.Final

Hibernate ORM
1

Hello. I have a criteria expression for sorting by predefined array

SqmCollation arrayString = new SqmCollation("ARRAY['STARTED', 'PAUSED', 'FINISHED']", null, (NodeBuilder) cb);

Expression<Integer> expression = cb.function("array_position", Integer.class, arrayString, root.get("status").as(String.class));

After update to hibernate from 6.1.7 to 6.4.1 I received an error java.lang.NullPointerException: Cannot invoke “org.hibernate.query.sqm.SqmExpressible.getSqmType()” because the return value of “org.hibernate.query.sqm.tree.SqmTypedNode.getExpressible()” is null

As I understand that because of usage SqmCollation but how can I set predefined array to function array_position? I don’t found any expression for arrays

As I see validation added in class org.hibernate.dialect.function.array.ArrayArgumentValidator and method getPluralType

	protected BasicPluralType<?, ?> getPluralType(
			int arrayIndex,
			List<? extends SqmTypedNode<?>> arguments,
			String functionName,
			TypeConfiguration typeConfiguration) {
		final SqmTypedNode<?> arrayArgument = arguments.get( arrayIndex );
		final SqmExpressible<?> arrayType = arrayArgument.getExpressible().getSqmType();
		if ( arrayType == null ) {
			return null;
		}
		else if ( !( arrayType instanceof BasicPluralType<?, ?> ) ) {
			throw new FunctionArgumentException(
					String.format(
							"Parameter %d of function '%s()' requires an array type, but argument is of type '%s'",
							arrayIndex,
							functionName,
							arrayType.getTypeName()
					)
			);
		}
		return (BasicPluralType<?, ?>) arrayType;
	}
2

Take a look at how to use array functions with the Hibernate ORM JPA Criteria extensions: hibernate-orm/hibernate-core/src/test/java/org/hibernate/orm/test/function/array/ArrayPositionTest.java at 46545bbdb51458b830c02fd22a589ae7c52fc8e3 · hibernate/hibernate-orm · GitHub

i.e.

Expression<Integer> expression = cb.function("array_position", Integer.class, cb.arrayLiteral("STARTED", "PAUSED", "FINISHED"), root.get("status").as(String.class));
3

Hi all!

I’m also facing a similar situation where this used to work in 6.2.x :

predicates.add(cb.isTrue(cb.function("anyInArray", Boolean.class, cb.literal(array), path)));

But now with 6.4.1 it doesn’t.

Hibernate: 
    select
        *
    from
        my_table mp1_0 
    where
        and [Ljava.lang.String;@5a6c6ffc && array[mp1_0.users]

I’ve tried to look at the test class you suggested, but I don’t see cb.arrayLiteral. Is this the right class to find the example?

Edit: anyInArray is a custom Postgres FunctionContributor

4

You can do whatever you want in your custom function, and apparently you did something that was outside of the supported scope. Either you try to workaround this in your custom function, or you use the supported Hibernate ORM APIs. Also see HibernatCriteriaBuilder, which is the extension of Hibernate to the JPA CriteriaBuilder API: hibernate-orm/hibernate-core/src/main/java/org/hibernate/query/criteria/HibernateCriteriaBuilder.java at bb4ed4b000081705aac8edfb68cb50ba79cf55dd · hibernate/hibernate-orm · GitHub

5

Hi Christian,

Thank you for your help on investigating this issue on my side. To give a bit more context, here is the Custom FunctionContributor :

public class CustomPostgresFunctionContributor implements FunctionContributor {

    public void contributeFunctions(FunctionContributions functionContributions) {
        var booleanType = functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve(StandardBasicTypes.BOOLEAN);
        functionContributions.getFunctionRegistry().registerPattern("anyInArray", "?1 && array[?2]", booleanType);
    }

}

It used to work like this before (on HIbernate 6.2.17.Final):

org.hibernate.SQL                        : select * from my_table mp1_0 where mp1_0.status = ? and ? && array[mp1_0.users] 
org.hibernate.orm.jdbc.bind              : binding parameter [1] as [VARCHAR] - [activated]
org.hibernate.orm.jdbc.bind              : binding parameter [2] as [ARRAY] - [[Ljava.lang.String;@4f056eee]
org.hibernate.orm.jdbc.bind              : binding parameter [2] as [ARRAY] - [[user1, user2]]

But with Hibernate 6.4.3 :


org.hibernate.SQL                        : select * from my_table mp1_0 where mp1_0.status = ? and [Ljava.lang.String;@717dc557 && array[mp1_0.users] 
org.hibernate.orm.jdbc.bind              : binding parameter (1:VARCHAR) <- [activated]

o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: syntax error at or near "["

I suppose that I’m doing something wrong while passing the array values to the code (something that should’nt be working but worked on 6.2.x but not anymore)

            List<String> users = List.of("user1,"user2");
            var valuesArray = values.toArray(new String[0]);
            predicates.add(cb.isTrue(cb.function("anyInArray", Boolean.class, cb.literal(valuesArray), path)));

Maybe I need to rewrite the custompostgres function to handle String arrays (or may be a List)?

6

Just to let you know, It worked as you suggested with HIbernateCriteriaBuilder with this :

predicates.add(
    cb.isTrue(
        cb.function(
            "anyInArray", 
            Boolean.class, 
            ((HibernateCriteriaBuilder) cb).arrayLiteral(values.toArray(String[]::new)),
            path
        )
    )
);

Not sure if it’s a final working solution, because it’s based on the implementation and not the Interface (CriteriaBuilder).

Thanks for your help in any case!

7

Not sure if it’s a final working solution, because it’s based on the implementation and not the Interface (CriteriaBuilder).

HibernateCriteriaBuilder is not the implementation but rather the extension to the JPA contract CriteriaBuilder. This is fine and the appropriate solution.

1 Like