what would the jooq equivalent of a DDL view be?

Question

I have an app going that is now implemented as a handful of views, and some jooq queries that depend on the views

why not move the whole layer into jooq? that is, implement the views in jooq? what would the smart way to do this be? and what would the performance implications be?

Answer 1

There is a pending, low-priority feature request in jOOQ to support views written in jOOQ:

• Feature request #1969.
• Discussion on the mailing list.

You could already create the equivalent of database views by extending jOOQ:

• http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/

Depending on your database, moving view code from the DB up to the application layer may indeed have a slight performance impact, as the DB can no longer collect statistics for a (named) view. Few databases actually do this, though, unless you create materialised views.

The advantage of keeping views written with jOOQ in Java is:

• Easier migration. You don't have to write DDL to migrate views between different versions of your application
• Advanced jOOQ feature application to views. For example, you can transform your view's SQL if need be through jOOQ's SQL transformation SPI.