Month: September 2011

How to use H2 with jOOQ

Posted on by lukaseder

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

Posted on by lukaseder

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

Posted on by lukaseder

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

Posted on by lukaseder

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

Posted on by lukaseder

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

Posted on by lukaseder

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

Posted on by lukaseder

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

Database schema navigation in Java

Posted on by lukaseder

An important part of jOOQ is jooq-meta, the database schema navigation module. This is used by the code generator to discover relevant schema objects. I was asked several times why I rolled my own instead of using other libraries, such as SchemaCrawler or SchemaSpy, and indeed it’s a pity I cannot rely on other stable third party products. Here are some thoughts on database schema navigation:

Standards

The SQL-92 standard defines how RDBMS should implement an INFORMATION_SCHEMA containing their dictionary tables. And indeed, some RDBMS do implement parts of the standard specification. These RDBMS ship with some implementation of the standard.

Close to the standard

  • HSQLDB: very close to the true standard
  • Postgres: close to the standard, with some tweaks (also has proprietary dictionary tables)
  • SQL Server: close to the standard but quite incomplete (also has proprietary dictionary tables)

Liberal interpretation of the standard

  • H2 (some backwards-incompatible changes, recently)
  • MySQL (only since 5.0, also has proprietary dictionary tables)

Other RDBMS provide their own idea of dictionary tables. This is something very tricky for schema navigation tools like jOOQ, to get a hold of. The dictionary table landscape can be described like this (my biased opinion):

Neat and well-documented dictionary tables

  • DB2: These dictionary tables somehow look like the standard, with different names. They feel intuitive.
  • Oracle: In my opinion has a better set of dictionary views than the ones proposed by the standard. Very easy to understand and well-documented all over the Internet
  • SQLite: There are no dictionary tables, but the SQLite stored procedures are very simple to use. It’s a simple database, after all

Hard to understand, not well-documented dictionary tables

  • Derby: Created the notion of conglomerates instead of using normal database-speak, such as relations, keys, etc.
  • MySQL: the old mysql schema was quite a pain. Fortunately, this is no longer true with MySQL 5.0
  • Ingres: Well… Ingres is an old database. Usability was not one of the main things in the 70’s…
  • Sybase SQL Anywhere: Lots of objects that have to be joined in complicated relations. Documentation is scarce
  • Sybase ASE: Even more difficult than SQL Anywhere. Some data can only be obtained with “tricks”

JDBC abstraction

The variety of dictionary tables seems to scream for standard abstraction. While the SQL-92 standard could in fact be implemented on most of these RDBMS, JDBC abstraction is even better. JDBC knows of the DatabaseMetaData object and allows for navigating database schemata easily. Unfortunately, every now and then, this API will throw a SQLFeatureNotSupportedException. There is no general rule about which JDBC driver implements how much of this API and when a workaround is needed. For jOOQ code generation, these facts make this API quite useless.

Other tools

There are some other tools in the open source world, as mentioned previously. Here are some drawbacks of using those tools in jOOQ:

  • Both tools that I know of are licensed with LGPL, which is not nicely compatible with jOOQ’s Apache 2 license.
  • Both tools navigate the entity-relationships very well, but seem to lack support for many non-standard constructs, such as UDT’s, advanced stored procedure usage (e.g. returning cursors, UDT’s, etc), ARRAY’s
  • SchemaCrawler supports only 8 RDBMS, jOOQ has 12 now
  • Both tools are rather inactive. See here and here

For more information, visit their sites:

jooq-meta

Because of the above reasons, jOOQ ships with its own database schema navigation: jooq-meta. This module can be used independently as an alternative to JDBC’s DatabaseMetaData, SchemaCrawler or SchemaSpy. jooq-meta uses jOOQ-crafted queries to navigate database meta-data, hence it is also part of the integration test suite. As an example, see how the Ingres foreign key relationships are navigated with jooq-meta:

Result<Record> result = create()
    .select(
        IirefConstraints.REF_CONSTRAINT_NAME.trim(),
        IirefConstraints.UNIQUE_CONSTRAINT_NAME.trim(),
        IirefConstraints.REF_TABLE_NAME.trim(),
        IiindexColumns.COLUMN_NAME.trim())
    .from(IICONSTRAINTS)
    .join(IIREF_CONSTRAINTS)
    .on(Iiconstraints.CONSTRAINT_NAME.equal(IirefConstraints.REF_CONSTRAINT_NAME))
    .and(Iiconstraints.SCHEMA_NAME.equal(IirefConstraints.REF_SCHEMA_NAME))
    .join(IICONSTRAINT_INDEXES)
    .on(Iiconstraints.CONSTRAINT_NAME.equal(IiconstraintIndexes.CONSTRAINT_NAME))
    .and(Iiconstraints.SCHEMA_NAME.equal(IiconstraintIndexes.SCHEMA_NAME))
    .join(IIINDEXES)
    .on(IiconstraintIndexes.INDEX_NAME.equal(Iiindexes.INDEX_NAME))
    .and(IiconstraintIndexes.SCHEMA_NAME.equal(Iiindexes.INDEX_OWNER))
    .join(IIINDEX_COLUMNS)
    .on(Iiindexes.INDEX_NAME.equal(IiindexColumns.INDEX_NAME))
    .and(Iiindexes.INDEX_OWNER.equal(IiindexColumns.INDEX_OWNER))
    .where(Iiconstraints.SCHEMA_NAME.equal(getSchemaName()))
    .and(Iiconstraints.CONSTRAINT_TYPE.equal("R"))
    .orderBy(
        IirefConstraints.REF_TABLE_NAME.asc(),
        IirefConstraints.REF_CONSTRAINT_NAME.asc(),
        IiindexColumns.KEY_SEQUENCE.asc())
    .fetch();

Conclusion

Once more it can be said that the world of RDBMS is very heterogeneous. Database abstraction in Java is established only to a certain degree in technologies such as JDBC, Hibernate/JPA, and third party libraries such as SchemaCrawler, SchemaSpy, and jooq-meta.

Annotatiomania™, or why did Hibernate/JPA get so complex?

Posted on by lukaseder

I was curious about good answers to this question on Stack Overflow:

http://stackoverflow.com/questions/7332904/how-to-store-a-collection-of-dates-in-hibernate

And a good answer came up, pointing at this Hibernate documentation page:

http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/collections.html#collections-ofvalues

Is anyone out there actually willing to mess around with gazillions of annotations on simple methods? Who ever writes this stuff:

@Entity
public class User {
   [...]
   public String getLastname() { ...}

   @ElementCollection
   @CollectionTable(
      name="Addresses", 
      joinColumns=@JoinColumn(name="user_id"))
   @AttributeOverrides({
      @AttributeOverride(
         name="street1", 
         column=@Column(name="fld_street"))
   })
   public Set<Address> getAddresses() { ... }
}

@Embeddable
public class Address {
   public String getStreet1() {...}
   [...]
}

Do some developers despise SQL so much that they’re willing to put up with “annotatiomania”? Whatever happened with the good old

SELECT u.first_name, u.last_name, a.street1
FROM users u
JOIN addresses a ON a.user_id = u.id

Guys. If a technology starts doing things like “@AttributeOverrides”, then something IS smelling fishy