H2 stored functions
H2 knows two operation modes for stored functions:- “Inline mode” with source code provided
- “Reference mode” referencing a public static method of a Java class on the databases’ classpath
http://www.h2database.com/html/features.html#user_defined_functions Now, regardless of the operation mode, your H2 stored functions are always written in Java. There is currently no PL/SQL-like procedural language. When your function needs to access the database again for further data processing, you’re back to JDBC, and that’s a pity. That makes writing stored functions quite verbose again.
Using jOOQ within H2 stored functions
… so why not just use jOOQ within the H2 database? Here’s a simple example about how you could do that: Write your stored function When writing your own stored function, you can use source code previously generated by jOOQ. This means that the SQL within your stored functions (triggers, etc) will actually compile!
package org.jooq.test.h2;
import static test.generated.Tables.*;
import java.sql.Connection;
import java.sql.SQLException;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import test.generated.tables.TBook;
public class Functions {
/**
* This function returns the number of
* books written by a given author.
*/
public static int countBooks(
Connection connection,
Integer authorId)
throws SQLException {
return DSL.using(connection, SQLDialect.H2)
.selectCount()
.from(T_BOOK)
.where(T_BOOK.AUTHOR_ID.eq(authorId))
.fetchOne(0, int.class);
}
}
CREATE ALIAS countBooks
FOR "org.jooq.test.h2.Functions.countBooks";
select t_author.last_name, countBooks(id)
from t_author
package org.jooq.test.h2;
import static test.generated.Tables.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import test.generated.Routines;
public class Test {
public static void main(String[] args)
throws SQLException {
Connection connection =
DriverManager.getConnection(
"jdbc:h2:~/test", "sa", "");
System.out.println(
DLS.using(connection, SQLDialect.H2)
.select(
T_AUTHOR.LAST_NAME,
Routines.countbooks(T_AUTHOR.ID))
.from(T_AUTHOR)
.fetch());
}
}
+---------+---------------------+ |LAST_NAME|"PUBLIC"."COUNTBOOKS"| +---------+---------------------+ |Orwell | 2| |Coelho | 2| |Hesse | 0| +---------+---------------------+