SQL Zone is brought to you in partnership with:

I am the founder and CEO of Data Geekery GmbH, located in Zurich, Switzerland. With our company, we have been selling database products and services around Java and SQL since 2013. Ever since my Master's studies at EPFL in 2006, I have been fascinated by the interaction of Java and SQL. Most of this experience I have obtained in the Swiss E-Banking field through various variants (JDBC, Hibernate, mostly with Oracle). I am happy to share this knowledge at various conferences, JUGs, in-house presentations and on our blog. Lukas is a DZone MVB and is not an employee of DZone and has posted 234 posts at DZone. You can read more from them at their website. View Full User Profile

Use jOOQ inside your H2 database

11.04.2011
| 4365 views |
  • submit to reddit

I recently became aware of an interesting use-case for jOOQ when I was optimising my own H2 database integration tests:

H2 stored functions

H2 knows two operation modes for stored functions:

  1. “Inline mode” with source code provided
  2. “Reference mode” referencing a public static method of a Java class on the databases’ classpath

The above terms are not official H2-speak. For more information, consider the documentation:

http://www.h2database.com/html/grammar.html#create_alias
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 java.sql.Connection;
import java.sql.SQLException;

import org.jooq.test.h2.generatedclasses.tables.TBook;
import org.jooq.util.h2.H2Factory;

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 {
    H2Factory create = new H2Factory(connection);

    return create.selectCount()
                 .from(TBook.T_BOOK)
                 .where(TBook.AUTHOR_ID.equal(authorId))
                 .fetchOne(0, Integer.class);
  }
}

Declare the above method as an ALIAS to H2

CREATE ALIAS countBooks FOR "org.jooq.test.h2.Functions.countBooks";

Use the function in SQL

	select t_author.last_name , countBooks(id) from t_author

… or with jOOQ’s generated classes

jOOQ generates a Routines class containing static access to all of your stored functions. This means that also your client code can make compile-safe use of your stored functions.

package org.jooq.test.h2;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.jooq.test.h2.generatedclasses.Routines;
import org.jooq.test.h2.generatedclasses.tables.TAuthor;
import org.jooq.util.h2.H2Factory;

public class Test {
  public static void main(String[] args) throws SQLException {
    Connection connection = DriverManager.getConnection(
      "jdbc:h2:~/test", "sa", "");
    H2Factory create = new H2Factory(connection);

    System.out.println(
    create.select(
                TAuthor.LAST_NAME,
                Routines.countbooks(TAuthor.ID))
          .from(TAuthor.T_AUTHOR)
          .fetch());
  }
}

The logged output of the above will be

+---------+---------------------+
|LAST_NAME|"PUBLIC"."COUNTBOOKS"|
+---------+---------------------+
|Orwell   |                    2|
|Coelho   |                    2|
|Hesse    |                    0|
+---------+---------------------+

Conclusion

jOOQ can also be used within your database, wherever Java is supported. In such a case, jOOQ acts like a PL/SQL-like extension to your favourite database’s Java stored procedure / stored function support. Give it a try, yourself!

 

From http://lukaseder.wordpress.com/2011/11/04/use-jooq-inside-your-h2-database/

Published at DZone with permission of Lukas Eder, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)