How to write a function in an H2 database without using Java

Question

This question relates to the question "How to create a stored procedure using the H2 database?" (continuation of the same question).

Is there a way to write a function in H2 without using Java code? My requirement is to translate functions written in SQL into H2 without using Java code. I found many examples in different portals that do the same using Java code. Your help will be greatly appreciated.

Relations Arun

Answer 1

Currently, H2 only supports functions written in Java or a related language (e.g. Groovy or Scala). PL / SQL (Oracle) and T-SQL (MS SQL Server, Sybase) are not supported.

Answer 2

If your main task is to run SQL (or SQLesque) statements in your custom Java H2 functions, jOOQ may be like a “PL / Java” implementation . Of course, this would still be a Java solution.

An example of such a function can be seen in this blog post:

http://blog.jooq.org/2011/11/04/use-jooq-inside-your-h2-database

public class Functions { public static int countBooks(Connection connection, Integer authorId) throws SQLException { // Translate your T-SQL statements to jOOQ statements return DSL.using(connection, SQLDialect.H2) .selectCount() .from(BOOK) .where(BOOK.AUTHOR_ID.eq(authorId)) .fetchOne(0, int.class); } } 

Declare the above method as ALIAS for H2

 CREATE ALIAS countBooks FOR "org.example.Functions.countBooks"; 

Use function in SQL

 SELECT author.last_name, countBooks(author.id) FROM author 

A similar approach can be taken with JaQu's own SQL abstraction , of course. I think using JaQu would not add any extra dependency.