Migrate Custom Function to Hibernate 6

Hibernate ORM
1

I’m migrating from Hibernate 5 to Hibernate 6 and I have couple problems with Custom Functions.
I have Custom function SINGLE_COLUMN_FROM_JSON_TABLE which is registered in two dialects Oracle and H2.

Functions is used in In Clause

select t from TestTable t WHERE t.id IN ( SINGLE_COLUMN_FROM_JSON_TABLE(?1) )

and in oracle Dialect it translates to

select t from TestTable t WHERE t.id IN (
    select single_value from json_table(?1 , '$[*]' COLUMNS ( single_value number PATH '$')) sinval_table)

)

And it’s works fine

In H2 Dialect I just want to function translate to

select t from TestTable t WHERE t.id IN ( ?,?,? )

But I have a problem becouse parameters are wrapped in additonal () as

  select t from TestTable t WHERE t.id IN (( ?,?,?))

which is causing error

in ((?,?))"], “params”:[[“1”,“2”]]}
ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Data conversion error converting “ROW (1, 2)”; SQL statement:

My function implementation for H2 Dialect is very simple

 @Override
    public void render(SqlAppender sqlAppender, List<? extends SqlAstNode> arguments, ReturnableType<?> returnType, SqlAstTranslator<?> translator) {
        translator.render(arguments.get(0), SqlAstNodeRenderingMode.DEFAULT);
    }

Any easy way to render parameters without additional () in H2 Dialect ?

Also I want to use function for String and Integers

string IN ( SINGLE_COLUMN_FROM_JSON_TABLE(?1) )
integer IN ( SINGLE_COLUMN_FROM_JSON_TABLE(?1) )

But such a queries do not pass validation.

As Returned type For Now I have

    public ReturnableType<?> resolveFunctionReturnType(ReturnableType<?> impliedType, List<? extends SqmTypedNode<?>> arguments, TypeConfiguration typeConfiguration) {
            return typeConfiguration.getBasicTypeRegistry().getRegisteredType(Long.class);
        }

Can I implement some custom return type so validation will pass for String and Integers ?

2

Debug your render function and try to understand how Hibernate ORM renders things. Then you will figure out how to get rid of the parenthesis.

I would recommend you to create a function single_column_from_json_table_contains(?1, ?2) which returns a boolean though. Then you can base your argument validation on the types of the two args being equal and are in full control of how the predicate is rendered.

3

To ny state of health@Bartun ,

By adjusting your render method for the H2 dialect to avoid extra parentheses and implementing a custom ReturnableType resolver to handle different return types, you should be able to resolve the issues you’re facing during migration to Hibernate 6. These changes ensure compatibility across different dialects and support for various return types in your custom function implementation.