Month: September 2011

How to use H2 with jOOQ

Living through the latest developments of the H2 and HSQLDB databases has been very exciting for me as jOOQ developer. Those two databases have a common heritage and share a lot of functionality. Both are progressing at a high pace with a very active community around their respective lead developers. I have recently posted an article about how those databases treat variable binding in a similar manner, one with respect to the other. Both infer a lot of types at compile time, but only few at bind / execution time:

https://lukaseder.wordpress.com/2011/08/31/rdbms-bind-variable-casting-madness

While these details may seem like a minor lack of functionality compared to the big ones (mainly DB2, Oracle, Postgres, SQL Server, Sybase) the two databases really make it up in terms of speed and flexibility. Both H2 and HSQLDB mimick a big set of functions, syntax clauses and other specialties from the “big” databases, which means they can easily be used as test database on integration tests systems or in development environments. This is mainly true for mimicking

  • MySQL
  • Ingres
  • Oracle
  • Postgres

A bit less though, for

  • DB2 (Type system is probably too strong to mimick)
  • SQL Server (T-SQL is a bit different from SQL-92)
  • Sybase SQL Anywhere (T-SQL again…)

When running integration tests for jOOQ, I really like the fact that H2 and HSQLDB are embeddable and high-performing Java databases as well. In the near future, I want to roll out a fully-fledged ready-to-run integration combining any of these tools:

  • Play ! Framework, Wicket, Vaadin as the GUI layer
  • jOOQ as the intermediary
  • H2 / HSQLDB as the data layer

Until then, I’m proud to see the H2 tutorial section about how to use jOOQ with H2:

http://www.h2database.com/html/tutorial.html#using_jooq

Vaadin, a very nice web framework

The recent hype about the Play ! Framework seemed to have lead to a lot of people thinking “That’s it! That’s how it ought to be”. And after trying to reproduce the Play ! tutuorial, I immediately agreed. Web development should be as easy as in Play !

But Play ! is just simplifying the daily work with a request-response, HTML-based website. There are other frameworks out there, that aim to simplify the work with stateful, component-based thinking, which is often the case with web applications. So I stumbled upon this gem here, doing precisely that:

https://vaadin.com/

Vaadin builds upon the GWT (Google Web Toolkit), which is a solid base for web frameworks. It has a free, open-source version, as well as a “pro version”, which gives you access to better support. In both cases, users can take advantage of a nice set of add-ons, and the community seems to be non-negligible:

http://stackoverflow.com/questions/tagged/vaadin

Maybe another candidate for a jOOQ-web integration?

 

Stored procedures returning multiple cursors

When adding support for Sybase ASE to jOOQ, I came across a peculiar procedure that can be used instead of the dictionary tables, to query for schema meta information (see also my previous post about database schema navigation). With Sybase ASE, you can call a procedure (or pragma?) called sp_help, which will return a cursor containing all tables in the schema:

> sp_help

+--------------------+-----+------------+
|Name                |Owner|Object_type |
+--------------------+-----+------------+
|sysquerymetrics     |dbo  |view        |
|v_author            |dbo  |view        |
|v_book              |dbo  |view        |
|v_library           |dbo  |view        |
|t_639_numbers_table |dbo  |user table  |
+--------------------+-----+------------+

Now you can also specify the name of one of your tables as a parameter. Then the procedure returns detail information about that table, as well as a list of columns. In some cases, it may even return constraint and index information. For instance, to know more about t_author:

> sp_help 't_author'

+--------+-----+-----------+-------------+-------------------+
|Name    |Owner|Object_type|Object_status|Create_date        |
+--------+-----+-----------+-------------+-------------------+
|t_author|dbo  |user table | -- none --  |Sep 22 2011 11:20PM|
+--------+-----+-----------+-------------+-------------------+

+-------------+-------+------+----+-----+-----+
|Column_name  |Type   |Length|Prec|Scale|...  |
+-------------+-------+------+----+-----+-----+
|id           |int    |     4|NULL| NULL|    0|
|first_name   |varchar|    50|NULL| NULL|    1|
|last_name    |varchar|    50|NULL| NULL|    0|
|date_of_birth|date   |     4|NULL| NULL|    1|
|year_of_birth|int    |     4|NULL| NULL|    1|
+-------------+-------+------+----+-----+-----+

Luckily, the people who designed JDBC have taken this feature into account in their API design and all of the following RDBMS’ JDBC drivers seem to implement it correctly (even if not all of them support stored procedures):

  • DB2
  • Derby
  • H2
  • HSQLDB
  • Ingres
  • MySQL
  • Postgres
  • SQLServer
  • Sybase ASE (with the jTDS driver)

These drivers don’t implement this API:

  • Oracle
  • SQLite
  • Sybase SQL Anywhere (with the jconn3 driver)

Here’s how fetching several cursors can be done with JDBC:

ResultSet rs = statement.executeQuery();

// Repeat until there are no more result sets
for (;;) {

  // Empty the current result set
  while (rs.next()) {
    // [ .. do something with it .. ]
  }

  // Get the next result set, if available
  if (statement.getMoreResults()) {
    rs = statement.getResultSet();
  }
  else {
    break;
  }
}

// Be sure that all result sets are closed
statement.getMoreResults(Statement.CLOSE_ALL_RESULTS);
statement.close();

Of course, this would be a very nice enhancement for jOOQ. With version 1.6.7, it is possible to call the above “sp_help” procedure in Sybase ASE directly, using this piece of code

Factory create = new ASEFactory(connection);

// Get a list of tables, a list of user types, etc
List<Result<Record>> tables = create.fetchMany("sp_help");

// Get some information about the t_author table, its
// columns, keys, indexes, etc
List<Result<Record>> results = create.fetchMany("sp_help 't_author'");

jOOQ has a new home page

I am proud to announce the launch of jOOQ’s new home page:

http://www.jooq.org/

I’m moving away from the very slow hosting on source forge and its quite technical-looking Trac Wiki. Any feedback appreciated!

Suggestions for the Java, SQL and jOOQ Blog

This blog is picking up momentum and thus, relevance. With jOOQ, I have come a long way, working with Java, JDBC, and SQL. I have solved many problems that are worth discussion, and I know quite a few things about various RDBMS, and how they compare with each other. Some examples:

What are YOU interested in? Maybe there’s a topic in jOOQ, that is worth explaining from a jOOQ-independent point of view. Some aspect of JDBC, stored procedures, user defined types, etc. Feel free to comment on this post with suggestions

Cheers
Lukas

Annotatiomania™ in Struts 2.0

I have to admit, I have not made any experience with Struts 1.0. Neither with 2.0. From what I understand, Struts 2.0 tried to move away from XML to use annotations instead. Just like Hibernate/JPA did. They have published Struts 2.0 with this title:

“Apache Struts 2 is an elegant, extensible framework for building enterprise-ready Java web applications.”

Elegant… ELEGANT! My untrained eye must be oblivious of some relevant facts. But can anyone explain to me what’s so elegant about the following code block (I can see how it is “extensible”, though)?

@Validation()
public class SimpleAnnotationAction extends ActionSupport {

    @RequiredFieldValidator(type = ValidatorType.FIELD, message = "You must enter a value for bar.")
    @IntRangeFieldValidator(type = ValidatorType.FIELD, min = "6", max = "10", message = "bar must be between ${min} and ${max}, current value is ${bar}.")
    public void setBar(int bar) {
        this.bar = bar;
    }

    public int getBar() {
        return bar;
    }

    @Validations(
            requiredFields =
                    {@RequiredFieldValidator(type = ValidatorType.SIMPLE, fieldName = "customfield", message = "You must enter a value for field.")},
            requiredStrings =
                    {@RequiredStringValidator(type = ValidatorType.SIMPLE, fieldName = "stringisrequired", message = "You must enter a value for string.")},
            emails =
                    { @EmailValidator(type = ValidatorType.SIMPLE, fieldName = "emailaddress", message = "You must enter a value for email.")},
            urls =
                    { @UrlValidator(type = ValidatorType.SIMPLE, fieldName = "hreflocation", message = "You must enter a value for email.")},
            stringLengthFields =
                    {@StringLengthFieldValidator(type = ValidatorType.SIMPLE, trim = true, minLength="10" , maxLength = "12", fieldName = "needstringlength", message = "You must enter a stringlength.")},
            intRangeFields =
                    { @IntRangeFieldValidator(type = ValidatorType.SIMPLE, fieldName = "intfield", min = "6", max = "10", message = "bar must be between ${min} and ${max}, current value is ${bar}.")},
            dateRangeFields =
                    {@DateRangeFieldValidator(type = ValidatorType.SIMPLE, fieldName = "datefield", min = "-1", max = "99", message = "bar must be between ${min} and ${max}, current value is ${bar}.")},
            expressions = {
                @ExpressionValidator(expression = "foo > 1", message = "Foo must be greater than Bar 1. Foo = ${foo}, Bar = ${bar}."),
                @ExpressionValidator(expression = "foo > 2", message = "Foo must be greater than Bar 2. Foo = ${foo}, Bar = ${bar}."),
                @ExpressionValidator(expression = "foo > 3", message = "Foo must be greater than Bar 3. Foo = ${foo}, Bar = ${bar}."),
                @ExpressionValidator(expression = "foo > 4", message = "Foo must be greater than Bar 4. Foo = ${foo}, Bar = ${bar}."),
                @ExpressionValidator(expression = "foo > 5", message = "Foo must be greater than Bar 5. Foo = ${foo}, Bar = ${bar}.")
    }
    )
    public String execute() throws Exception {
        return SUCCESS;
    }
}

Taken from the docs, here: http://struts.apache.org/2.x/docs/validation-annotation.html. Thanks to Sergio for sharing this link. I guess you can actually put Java code in annotation strings, that is compiled at run-time using some Eclipse compiler or something… Who knows? 🙂

MentaBean, the ORM/SQL Builder of Mentawai

I ran across an incredible discovery just now. I’m always closely observing jOOQ-related topics on Stack Overflow, so I immediately saw these somewhat advertising answers to a dedicated jOOQ user’s question:

MentaBean is an ORM/SQL Builder that has recently been made independent from Mentawai, a library building on top of the Servlet specification to simplify the lives of thousands of brazilian developers (counting the number of messages in the forum).

I’ve had a Stack Overflow chat with Sergio Oliveira Jr., one of the developers behind MentaBean, as I always find it interesting to chat with other people suffering from the heaviness and complexity of the Hibernate/JPA stack. His maxim is remarkable, if I may quote him quoting St. Exupéry: “Perfection is achieved, not when there is nothing more to add, but when there is nothing left to take away.” This is a very poetic way of saying the same thing in eXtreme Programming lingo: Refactor Mercilessly. With this being your primary paradigm, I believe great, fun software can evolve. No need to say that I started to like Sergio 🙂

While it doesn’t look at first as though Mentawai (and its associated MentaBean) will outperform the currently hyped Play ! Framework, or the well-established Wicket library, I still find it nice to see how much effort is put in OSS, worldwide.

See the Mentawai homepage for more details:

http://www.mentaframework.org/quick-start.jsp