{code:java} public static <T> Predicate contains(Root<T> root, CriteriaBuilder cb, String propertyName, String item) {
Assert.doesNotContain(item, ",", "'item' should not contains comma");
Dialect dialect = getDialect(cb);
if (dialect instanceof MySQLDialect) {
return cb.greaterThan(cb.function("find_in_set", Integer.class, cb.literal(item), root.get(propertyName)),
0);
}
if (dialect instanceof PostgreSQLDialect) {
return cb.equal(cb.literal(item), cb.function("any", String.class,
cb.function("string_to_array", String[].class, root.get(propertyName), cb.literal(","))));
}
return cb.like(cb.concat(cb.concat(",", root.get(propertyName)), ","), '%' + item + '%');
}{code}
This method will generate sql for PostgresQL like:
{code:sql}select count(t1_0.id) from test_entity t1_0 where 'name0'=any(string_to_array(t1_0.names,',')){code}
It works fine with hibernate 5.x, but failed with 6.x, It comes from two issues.
# {{cb.function("string_to_array", String[].class, ... )}} will throw {{java.lang.IllegalArgumentException: Passed `invariantType` for function return cannot be null}}, because {{SqmCriteriaNodeBuilder::function}} doesn’t recognize array type {{String[].class}}, It may resolved by
{code:java} final BasicType<T> resultType;
if (type.isArray()) {
BasicType<?> componentType = getTypeConfiguration().getBasicTypeForJavaType(type.getComponentType());
resultType = new BasicArrayType(componentType, new ArrayJdbcType( componentType.getJdbcType() ) ,
new ArrayJavaType<>( componentType.getJdbcJavaType()) ) ;
}
else {
resultType = getTypeConfiguration().getBasicTypeForJavaType(type);
}{code}
# {{cb.function("any", ... )}} will throw {{org.hibernate.QueryException: Parameter 1 of function bool_or() has type BOOLEAN, but argument is of type java.lang.String}}, It seems conflict with built-in function from {{CommonFunctionFactory}} |
|
