Stored procedures in HQL

Question

How can I write stored procedure or function in HQL? Is it possible? I haven't found any information about it.

The problem is that my app works with several Databases(Oracle, MSSQL, PostgreSQL) and I need to count Levenshtein distance in my query. Can I do it without writing 3 native SQL functions and queries for each database?

Maybe [this](https://docs.jboss.org/hibernate/stable/orm/userguide/html_single/Hibernate_User_Guide.html#sql-sp) will help. — SternK, Jun 22 '20 at 13:55
In example we call MySql prcedure through hibernate, but will it work with other databases? I need to write pocedure that generic for all databases. — Artyom Zajtsev, Jun 22 '20 at 14:08
HQL does not support Stored Procedures. And how do you think this would help? Levenshtein is implemented differently in these three databases — Simon Martinelli, Jun 22 '20 at 14:08

score 0 · Accepted Answer · answered Jun 24 '20 at 18:50

You can try to encapsulate discrepancy between Levenshtein function names in different databases in hibernate dialect. Below I will provide an example for Oracle and PostgreSQL. (I did not work with MSSQL)

The extended Oracle dialect:

public class MyOracleDialect extends Oracle12cDialect
{
   public MyOracleDialect()
   {
      super();
      registerFunction( "levenshtein", new SQLFunctionTemplate( StandardBasicTypes.INTEGER, "UTL_MATCH.EDIT_DISTANCE(?1,?2)" ) );
   }
}

The extended PostgreSQL dialect:

public class MyPostgreSQLDialect extends PostgreSQL95Dialect
{
   public MyPostgreSQLDialect()
   {
      super();
      registerFunction( "levenshtein", new SQLFunctionTemplate(StandardBasicTypes.INTEGER, "levenshtein(?1, ?2)"));
   }
}

And now you can use the levenshtein function in your HQL.

List<Integer> result = session.createQuery(
   "select levenshtein(word1, word2) from TestEntity",
   Integer.class
).getResultList();

P.S. I have faced with the following problem for PostgreSQL: If the extension fuzzystrmatch was installed for the particular schema TEST_SCHEMA:

SQL> create extension fuzzystrmatch;

then you should specify this schema in the connection url:

<property name="hibernate.connection.url">jdbc:postgresql://localhost:5432/postgres?currentSchema=TEST_SCHEMA</property>

otherwise you will get an exception:

org.postgresql.util.PSQLException: ERROR: function levenshtein(character varying, character varying) does not exist. No function matches the given name and argument types. You might need to add explicit type casts.

Stored procedures in HQL

1 Answers1