Hibernate - Creating Custom ImportSqlCommandExtractor to load scripts containing stored procedures/functions

In Hibernate the property hibernate.hbm2ddl.import_files_sql_extractor can be used to specify how to extract statements from sql script. This property expects the class name of an implementation of ImportSqlCommandExtractor. By default SingleLineSqlCommandExtractor is used which treats each line as a complete SQL statement. We can also specify MultipleLinesSqlCommandExtractor which supports instructions/comments and quoted strings spread over multiple lines but still each statement must end with semicolon. The problem is that the script which creates stored procedures and functions may have multiple semicolon, so this implementation of the extractor also does not work for them.

Following example shows how to create a custom ImportSqlCommandExtractor to load scripts containing stored procedures/functions.

Custom ImportSqlCommandExtractor

package com.logicbig.example;

import org.hibernate.tool.hbm2ddl.ImportSqlCommandExtractor;
import java.io.Reader;
import java.util.Arrays;

public class CustomHibernateSqlExtractor implements ImportSqlCommandExtractor {
  private static final String STATEMENT_DELIMITER = "/;";

  @Override
  public String[] extractCommands(Reader reader) {
      try {
          int charVal;
          String str = "";
          while ((charVal = reader.read()) != -1) {
              str += (char) charVal;
          }
          reader.close();

          String[] split = str.split(STATEMENT_DELIMITER);
          String[] statements = Arrays.stream(split)
                                      .map(String::trim)
                                      .filter(s -> s.length() > 0)
                                      .map(s -> s += ";")
                                      .toArray(String[]::new);
          return statements;

      } catch (Exception e) {
          throw new RuntimeException("Error during import script parsing.", e);
      }
  }
}

Above extractor expects each statement must be separated by '/;'.

Hibernate Configuration

src/main/resources/hibernate.cfg.xml

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
        "-//Hibernate/Hibernate Configuration DTD//EN"
        "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
        <property name="connection.driver_class">org.hsqldb.jdbc.JDBCDriver</property>
        <property name="connection.url">jdbc:hsqldb:mem:dataSource</property>
        <property name="dialect">org.hibernate.dialect.HSQLDialect</property>
        <!--<property name="show_sql">true</property>-->
        <property name="hbm2ddl.auto">create</property>
        <property name="hibernate.hbm2ddl.import_files">stored-procedure.sql</property>
        <property name="hibernate.hbm2ddl.import_files_sql_extractor">
            com.logicbig.example.CustomHibernateSqlExtractor
        </property>
    </session-factory>
</hibernate-configuration>

Example Script

In this example we are using HSQLDB.

src/main/resources/stored-procedure.sql

CREATE FUNCTION getMessage(name VARCHAR(100))
 RETURNS VARCHAR(100)
 BEGIN ATOMIC
  return CONCAT('Hello ', name);
 END
 /;

CREATE FUNCTION getSum(x INT, y INT)
 RETURNS INT
 BEGIN ATOMIC
  return x+y;
 END
 /;

Calling DB functions from Hibernate

public class ExampleMain {
  public static void main(String[] args) {
      SessionFactory sessionFactory = new Configuration().configure()
                                                         .buildSessionFactory();
      try {
          Session session = sessionFactory.openSession();
          //calling first function
          NativeQuery sqlQuery = session.createSQLQuery("CALL getMessage(:name)");
          sqlQuery.setParameter("name", "Joe");
          for (Object o : sqlQuery.list()) {
              System.out.println(o);
          }

          //calling second function
          NativeQuery sqlQuery2 = session.createSQLQuery("CALL getSum(:x, :y)");
          sqlQuery2.setParameter("x", 3);
          sqlQuery2.setParameter("y", 4);
          for (Object o : sqlQuery2.list()) {
              System.out.println(o);
          }
          session.close();
      } finally {
          sessionFactory.close();
      }
  }
}

Hello Joe
7

Example Project

Dependencies and Technologies Used:

• hibernate-core 5.3.6.Final: Hibernate's core ORM functionality.
  Implements javax.persistence:javax.persistence-api version 2.2
• hsqldb 2.4.1: HSQLDB - Lightweight 100% Java SQL Database Engine.
• JDK 1.8
• Maven 3.5.4