Hibernate 6 SQLFunctionTemplate Alternative

Question

We have HibernateMetadataBuilderContributor Like below. Which works in Hibernate 5 or Spring boot 2.7. But not working when we migrate to Hibernate 6 or Spring boot 3.

public class HibernateMetadataBuilderContributor implements MetadataBuilderContributor {

    public static final String STRING_AGG = "string_agg";
    public static final String STRING_AGG_ORDER_BY = "string_agg_order_by";
    public static final String STRING_AGG_DISTINCT = "string_agg_distinct";

    @Override
    public void contribute(final MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(STRING_AGG, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(?1, ?2)"));
        metadataBuilder.applySqlFunction(STRING_AGG_ORDER_BY, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(?1, ?2 order by ?3)"));
        metadataBuilder.applySqlFunction(STRING_AGG_DISTINCT, new SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(distinct ?1, ?2)"));

    }
}

SQLFunctionTemplate is not found in Hibernate 6, what is the alternative.

score 3 · Answer 1 · answered Apr 05 '23 at 06:04

Hibernate 5

In Hibernate 5, this is how we used to register an SQL function with multiple parameters:

public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {
 
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "date_trunc",
            new SQLFunctionTemplate(
                StandardBasicTypes.TIMESTAMP,
                "date_trunc('day', (?1 AT TIME ZONE ?2))"
            )
        );
    }
}

Hibernate 6

In Hibernate 6, the same function can be registered like this:

public class SqlFunctionsMetadataBuilderContributor implements MetadataBuilderContributor {
 
    @Override
    public void contribute(MetadataBuilder metadataBuilder) {
        metadataBuilder.applySqlFunction(
            "date_trunc",
            DateTruncFunction.INSTANCE
        );
    }
 
    public static class DateTruncFunction extends NamedSqmFunctionDescriptor {
         
        public static final DateTruncFunction INSTANCE = new DateTruncFunction();
 
        public DateTruncFunction() {
            super(
                "date_trunc",
                false,
                StandardArgumentsValidators.exactly(2),
                null
            );
        }
 
        public void render(
                SqlAppender sqlAppender,
                List<? extends SqlAstNode> arguments,
                SqlAstTranslator<?> walker) {
            Expression timestamp = (Expression) arguments.get(0);
            Expression timezone = (Expression) arguments.get(1);
            sqlAppender.appendSql("date_trunc('day', (");
            walker.render(timestamp, SqlAstNodeRenderingMode.DEFAULT);
            sqlAppender.appendSql(" AT TIME ZONE ");
            walker.render(timezone, SqlAstNodeRenderingMode.DEFAULT);
            sqlAppender.appendSql("))");
        }
    }
}

But still there will be a depreciation warning! Do we have any other alternative to use {@link TypeContributor}, {@link FunctionContributor} or * {@link AdditionalMappingContributor} instead depending on need — Arunkumar Pushparaj, Jun 14 '23 at 06:24
@ArunkumarPushparaj Be the change you want to see in the world! The Hibernate source code is open source so feel free to investigate an alternative solution and provide it as an answer here. — Vlad Mihalcea, Jun 14 '23 at 07:58

Jana · Answer 2 · 2022-12-30T13:12:52.757

1

Using in the below way seems working.

new PatternBasedSqmFunctionDescriptor(
                new PatternRenderer(pattern),
                null,
                null,
                null,
                name,
                FunctionKind.NORMAL,
                null
        );

edited Dec 30 '22 at 13:12

answered Dec 30 '22 at 13:12

Jana

239
2
11

Yeah I guess that would probebly work, but you won't get any nice typechecking or anything. If you fill in those null arguments with something sensible, HQL will do a much better job with the typing. – Gavin King Dec 30 '22 at 13:22
Seems except these, others arguments in PatternBasedSqmFunctionDescriptor are interfaces, I have to implement, which I am not sure, what to use. – Jana Dec 30 '22 at 13:26
Well, like I commented below, you can find implementations in `StandardArgumentsValidators`, `StandardFunctionReturnTypeResolvers`, and `StandardFunctionArgumentTypeResolvers`. (They're very trivial.) – Gavin King Dec 30 '22 at 13:41

score 0 · Answer 3 · answered Dec 29 '22 at 22:46

0

First: you don't need to do this in H6!

HQL now comes with a built-in listagg() function which is automatically translated to string_agg() on those databases where that's what it's called. Please check the User Guide chapter on HQL for a full list of built-in portable functions.

https://docs.jboss.org/hibernate/orm/6.2/userguide/html_single/Hibernate_User_Guide.html#hql-aggregate-functions-orderedset

Now, with that said...

The equivalent interface to SQLFunction in H6 is SqmFunctionDescriptor, which has lots of built-in implementations you could look at for inspiration, including PatternBasedSqmFunctionDescriptor.

But the basic, simplified, "user-friendly" implementation we provide is still org.hibernate.dialect.function.StandardSQLFunction, which has now been adapted to the new, much more-powerful SqmFunctionDescriptor framwork.

answered Dec 29 '22 at 22:46

Gavin King

3,182
1
13
11

We have Postgres's array_agg function also, is there any hibernate 6 function for this. And any examples how to implement PatternBasedSqmFunctionDescriptor – Jana Dec 30 '22 at 04:59
Ah, right, `array_agg` is already on the todo list: https://github.com/hibernate/hibernate-orm/discussions/5562 – Gavin King Dec 30 '22 at 13:05
I don't think you need to *implement* `PatternBasedSqmFunctionDescriptor` ... I think you can just instantiate it, can't you? – Gavin King Dec 30 '22 at 13:06
You might appreciate the help of `StandardArgumentsValidators`, `StandardFunctionReturnTypeResolvers`, and `StandardFunctionArgumentTypeResolvers`, but it's basically just a matter of `new`ing it. – Gavin King Dec 30 '22 at 13:10
@GavinKing I found this thread here and I am just wondering if hibernate 6 has something built in to replace a custom CastInterval function that I created by overwriting the render function of SQLFunctions like so: override fun render( firstArgumentType: Type?, args: List<*>, factory: SessionFactoryImplementor? ): String { return "cast(" + args[0] + " as interval)" } – Tim Feb 07 '23 at 15:26
Good question. So *currently* in HQL6, you can cast *from* a `Duration` to a numeric type, but because of the intricacies of mapping `Duration` to SQL, there is no built-in way to typecast something *to* a `Duration`. But it is on my radar as a thing we would like to have. If it's very important to you, opening an issue might help. – Gavin King Feb 07 '23 at 16:12

Hibernate 6 SQLFunctionTemplate Alternative

3 Answers3

Hibernate 5

Hibernate 6