Hibernate 6 custom function (in (select * from values ((?), (?)) as temp (property)

Hibernate ORM
1

Hi team!
We are trying to migrate from hibernate criteria to jpa with hibernate 6 and faced with a problem.
in hibernate criteria we have a custom Criterion which render something like this:

select * from (values (?), (?), (?), (?)) as f_tableAlias (property);

in hibernate criteria it looks like this:

@Override
public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
    final String[] columns = criteriaQuery.findColumns(propertyName, criteria);
    if (columns.length != 1) {
        return "";
    }
    var column = columns[0];
    var columnArray= column.split("\\.");
    var tableAlias = columnArray[0];
    var property = columnArray[1];
    var valuesResult = values.stream().map(v -> "?").collect(Collectors.joining("),("));
    return column + " in ( select * from (values (" + valuesResult + ")) as f_" + tableAlias + "(" + property + "))";
}

@Override
public TypedValue[] getTypedValues(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
    return values.stream().map(v -> criteriaQuery.getTypedValue(criteria, propertyName, v)).toArray(TypedValue[]::new);
}

then we adapt it to use in jpa criteria builder in hibernate 5.5.8
and it was created via implemetation of SQLFunction with override render like this:

@Override
public String render(Type firstArgumentType, List arguments, SessionFactoryImplementor factory) throws QueryException {
    //all of arguments here is "?" except first - which is "path" and received here as is
    var path = (String) arguments.get(0);
    var split = path.split("\\.");
    var tableAlias = split[0];
    var property = split[1];

    var values1 = ((List<?>) arguments).stream().skip(1)
            .map(this::replaceWithValidate)
            .collect(Collectors.joining("),("));
    var result = "select * from (values (" + values1 + ")) as f_" + tableAlias + "(" + property + ")";
    return result;
}

usage:

public <T> Predicate in(Expression<T> path, Set<T> values) {
        return path.in(
                criteriaBuilder.function(
                        FunctionsContributor.IN_SELECT_VALUES_FUNCTION,
                        path.getJavaType(),
                        path,
                        criteriaBuilder.literal(values)
                )
        );
    }

with parameter AvailableSettings.CRITERIA_LITERAL_HANDLING_MODE = BIND
both of this variants generates sql like above.

I was tried to create function that extends AbstractSqmSelfRenderingFunctionDescriptor
like this (simplified):

@Override
public void render(SqlAppender sqlAppender,
                   List<? extends SqlAstNode> sqlAstArguments,
                   ReturnableType<?> returnType,
                   SqlAstTranslator<?> walker) {
    var pathArg = sqlAstArguments.get(0);
    var columntRef = ((Expression) pathArg).getColumnReference();
    var tableAlias = columntRef.getQualifier();
    var fieldName = columntRef.getSelectableName();

    var valueArg = sqlAstArguments.get(1);
    var valuesCollection = (Collection<?>) ((QueryLiteral<?>) valueArg).getLiteralValue();
    var valuesStr = valuesCollection.stream().map(String::valueOf).collect(Collectors.joining("),("));
    var result = "select * from (values (" + valuesStr + ")) as f_" + tableAlias + "(" + fieldName + ")";
    sqlAppender.append(result);
}

usage:

public <T> Predicate in(Expression<T> path, Set<T> values) {
        return path.in(
                criteriaBuilder.function(
                        FunctionsContributor.IN_SELECT_VALUES_FUNCTION,
                        path.getJavaType(),
                        path,
                        criteriaBuilder.literal(values)
                )
        );
    }

but in such case values renders as is to query.

In total:

  • Is there any way to bind literals as parameters in extensions of AbstractSqmSelfRenderingFunctionDescriptor ?
    (in other functions i tried walker.render(sqlAstArguments.get(n), SqlAstNodeRenderingMode.NO_PLAIN_PARAMETER); with all SqlAstNodeRenderingMode but literal arguments always renders as is instead of parameters

  • Can you please give me some advices - how can i achieve the same behavior in hibernate 6.5.* which it was in 5.5.8 ?

2

Turned out to achieve the desired result like this:

@Override
public void render(SqlAppender sqlAppender,
                   List<? extends SqlAstNode> sqlAstArguments,
                   ReturnableType<?> returnType,
                   SqlAstTranslator<?> walker) {
    var pathArg = sqlAstArguments.get(0);
    var columntRef = ((Expression) pathArg).getColumnReference();
    var tableAlias = columntRef.getQualifier();
    var fieldName = columntRef.getSelectableName();

    var valueArg = sqlAstArguments.get(1);
    var valueExpressions = ((InListPredicate) valueArg).getListExpressions();
    sqlAppender.append("select * from (values (");
    var count = 0;
    for (var expr : valueExpressions) {
        if (count++ != 0) {
            sqlAppender.append("),(");
        }
        expr.accept(walker);
    }
    sqlAppender.append(")) as f_");
    sqlAppender.append(tableAlias);
    sqlAppender.append("(");
    sqlAppender.append(fieldName);
    sqlAppender.append(")");
}

usage

public <T> Predicate in(Expression<T> path, Set<T> values) {
        var inValues = criteriaBuilder.in(path);
        values.forEach(inValues::value);
        return path.in(
                criteriaBuilder.function(
                        DmsFunctionsContributor.IN_SELECT_VALUES_FUNCTION,
                        path.getJavaType(),
                        path,
                        inValues
                )
        );
    }

result:

select c1_0.id,c1_0.number from TABLE c1_0 where c1_0.id in (select * from (values (?),(?),(?),(?),(?),(?)) as f_c1_0(id)) fetch first ? rows only

but it looks strange and incorrect i guess…

3

You can use org.hibernate.query.criteria.HibernateCriteriaBuilder#value to create an expression from a value, which will be rendered as parameter or literal depending on hibernate.criteria.value_handling_mode.