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

**URL:** <https://discourse.hibernate.org/t/hibernate-6-custom-function-in-select-from-values-as-temp-property/9922>\
**Category:** Hibernate ORM\
**Created:** [July 9, 2024, 6:23pm UTC](https://discourse.hibernate.org/t/hibernate-6-custom-function-in-select-from-values-as-temp-property/9922 "2024-07-09T18:23:02Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![deerge](https://avatars.discourse-cdn.com/v4/letter/d/ce73a5/32.png) [@deerge](https://discourse.hibernate.org/u/deerge)\
**Post date:** [July 9, 2024, 6:23pm UTC](https://discourse.hibernate.org/t/hibernate-6-custom-function-in-select-from-values-as-temp-property/9922/1 "2024-07-09T18:23:02Z")

</div>

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:

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

```

in hibernate criteria it looks like this:

```auto
@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:

```auto
@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:

```auto
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):

```auto
@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:

```auto
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 ?

---

<div class="post-metadata">

**Author:** ![deerge](https://avatars.discourse-cdn.com/v4/letter/d/ce73a5/32.png) [@deerge](https://discourse.hibernate.org/u/deerge)\
**Post date:** [July 9, 2024, 6:55pm UTC](https://discourse.hibernate.org/t/hibernate-6-custom-function-in-select-from-values-as-temp-property/9922/2 "2024-07-09T18:55:27Z")

</div>

Turned out to achieve the desired result like this:

```auto
@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

```auto
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:

```auto
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…

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [July 10, 2024, 6:27pm UTC](https://discourse.hibernate.org/t/hibernate-6-custom-function-in-select-from-values-as-temp-property/9922/3 "2024-07-10T18:27:37Z")

</div>

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`](https://docs.jboss.org/hibernate/stable/orm/userguide/html_single/Hibernate_User_Guide.html#settings-hibernate.criteria.value_handling_mode).

---

<div class="post-metadata">

**Author:** ![rhochreiter](https://avatars.discourse-cdn.com/v4/letter/r/8edcca/32.png) [@rhochreiter](https://discourse.hibernate.org/u/rhochreiter)\
**Post date:** [June 16, 2026, 6:13am UTC](https://discourse.hibernate.org/t/hibernate-6-custom-function-in-select-from-values-as-temp-property/9922/4 "2026-06-16T06:13:21Z")

</div>

Can you please post the complete code of your IN\_SELECT\_VALUES\_FUNCTION, because I want to implement also such a thing 😉