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.sqlCREATE 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 ProjectDependencies 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
|