User Defined Functions in Dialect

Hibernate ORM
1

Looking to migrate from Hibernate 5.X to 6.1.
I have a custom Dialect where I register a number of functions in the constructor: e.g

registerFunction(“ecfts”, new PgFullTextSearchFunction());

This approach is no longer supported. From what I can find, it looks like I will need to implement something along the lines of a FunctionContributor. However, I have not had any luck finding a working example, and if this is actually the best practice going forward.
Any suggestions?

Thanks,

Tim

2

I just recently answered a similar question: Migration of dialect to Hibernate 6

3

Ok, I have now read that thread. They are not registering an actual user defined function, and they are not manipulating the generated SQL to call and utilize a user defined function in the database. All they are doing is registering known functions of the database and changing return types.

However, I cannot find any documentation, or examples to register a user defined function. I have searched on FunctionContributor and Hibernate, user defined functions… All answers point back to Hibernate 4 or 5.

Even going through the code, I have yet to find an example where someone has implemented the equivalent of the render method from SQLFunction in Hibernate 5.X

An example of how I used render in the 4.X and 5.X versions.

	@Override
	public String render(Type firstArgumentType, @SuppressWarnings("rawtypes") List args, SessionFactoryImplementor factory) throws QueryException {
		if (args.size() != 2) throw new IllegalArgumentException("The function must be passed 2 arguments");
		String field = (String) args.get(0);
		String value = (String) args.get(1);
		value = value.replaceAll("\\s+", " & ").replaceAll("'", "");
		value = org.apache.commons.lang3.StringUtils.stripAccents(value);
		StringBuilder fragment = new StringBuilder();
		fragment.append("to_tsvector(").append(field).append(")");
		fragment.append(" @@ ");
		fragment.append("websearch_to_tsquery(" + value + ")");
		if (log.isDebugEnabled()) log.debug("FullTextSearch fragment:" + fragment);
		return fragment.toString();				
	}
4

@beikov

I believe I found your code in org.hibernate.dialect.function.SqlFunction
I think this will likely be a good starting point. I still need to back trace the code enough to understand enough to implement my own function. I did not dig yet into the META-INF/services/org.hibernate.boot.model.FunctionContributor aspect mentioned in the other thread.

I believe you implied the META-INF is the direction intended for best practice for user defined functions

Am I going in the correct direction?

Thanks,

Tim

5

Yes, registering functions should happens through the FunctionContributor. Note that when the function return type is Boolean, you can use it as predicate directly i.e. in HQL where matches(...).
You can render the fragment in a custom implementation, or you register a pattern function:

functionContributions.getFunctionRegistry().registerPattern(
    "myFunction", 
    "to_tsvector(?1) @@ websearch_to_tsquery(?2)",
    functionContributions.getTypeConfiguration().getBasicTypeRegistry()
        .resolve(StandardBasicTypes.BOOLEAN)
);
6

I looked at the registerPattern actually. It does not work since I need to remove accents, and other special characters which break the PostgreSQL text search functions.

I know I am missing something trying to back trace the code since my FunctionContributor does not work. With the many levels of indirection utilized in the core Hibernate code, (the abstractions tend to make sense once you understand them, but getting there…) I think this will take me a couple days to figure out.

7

Maybe you should use the replace SQL function to let PostgreSQL do this? Otherwise your implementation only works if you pass literals.

functionContributions.getFunctionRegistry().registerPattern(
    "myFunction", 
    "to_tsvector(?1) @@ websearch_to_tsquery(unaccent(replace(regexp_replace(?2,'\\s+', ' & '),'''', '')))",
    functionContributions.getTypeConfiguration().getBasicTypeRegistry()
        .resolve(StandardBasicTypes.BOOLEAN)
);

with PostgreSQL: Documentation: 15: F.48. unaccent

You can implement a custom function similar to SqlFunction that tries to unwrap arguments though if you really want to do the same. You just cast the argument to QueryLiteral and append the value to the SqlAppender directly.

8

lol, I did not consider the PostgreSQL unaccent function.
Hibernate starts now, and does not complain. However I cannot test yet, since I ran into a totally separate issue with my SpringBoot 3 upgrade.
I will report back, after I get the other items resolved.

1 Like
9

@beikov

I ended up switching paths a little and using your pattern function idea. I can give the gory details of why i switched if curious.

Documentation for the next person trying to solve user defined functions in Hibernate.

Here is my final class to register my two full text search functions:

public class PgFunctionContributor implements FunctionContributor {
	@Override
	public void contributeFunctions(FunctionContributions functionContributions) {
		BasicType<Boolean> resolveType = functionContributions.getTypeConfiguration().getBasicTypeRegistry().resolve(StandardBasicTypes.BOOLEAN);
		functionContributions.getFunctionRegistry().registerPattern("ects","to_tsvector(?1) @@ websearch_to_tsquery(?2)",resolveType);
		functionContributions.getFunctionRegistry().registerPattern("ecfts","?1 @@ websearch_to_tsquery(?2)",resolveType);
	}
}

To register the functions:
A text file with the fully qualified class names of all defined function contributors is required.
The filename is /META-INF/services/org.hibernate.boot.model.FunctionContributor

Tim

1 Like
10

@tspear @beikov could you please share the github repo link for the complete working of the code.

PS : do i need resiter PgFunctionContributor class in application.properties file?

1 Like
11

It’s a Java ServiceLoader contract, so read here about how to enabled this: ServiceLoader (Java SE 9 & JDK 9 )