Hello jOOQ users
I have had a very interesting enquiry about how jOOQ can be used in a
single application with several different RDBMS (e.g. MySQL, Oracle,
Sybase), and I would like to have some feedback from the user group on
this topic.
In principle, jOOQ supports generating source code for an Oracle
database, and then running database queries using that generated
source code on any other database with an equivalent schema installed,
for instance HSQLDB. Different schema/database names can be mapped
using SchemaMapping. I'm also running integration tests to make sure
this actually works. Of course, Oracle-specific features, such as the
CONNECT BY clause will not be available any longer in HSQLDB. I have
been thinking about this use case and it struck me that one might find
it very difficult to know from the jOOQ API whether any given method
can be used for all of the required databases, or not. For example:
- org.jooq.Sequence.currval() won't work with DB2
- Factory.rollup() won't work with H2
- Factory.cube() won't work with H2, MySQL
- connectBy() will only work with Oracle
These things can be seen from the integration tests, from the source
code, and are sometimes documented in the Javadocs, but not always and
not in a standardised way. I think there is need for a more formal
definition for every API method, to immediately see whether it is safe
to use in any given dialect. This could be achieved using annotations,
for example.
A method safe for any dialect doesn't have any annotations:
- Factory.exists()
- Factory.select()
A method that is only supported in some dialects will be annotated
with @org.jooq.Supports
@Supports({DB2, MYSQL, ORACLE, SQLSERVER, SYBASE})
public static Field<?> rollup()
In a later step, I could implement a Maven plugin (and/or and ant
task) processing the jOOQ source code and allowing for users to mark
not supported API methods as @Deprecated, before building jOOQ
themselves. For instance, user digulla from Github could require DB2,
Oracle, Sybase, H2, MySQL and build jOOQ. The plugin would then mark
all methods as @Deprecated which are not supported by all 5 databases.
That way, he can be sure not to use any features that he shouldn't use
with his specific setup. When he wants to add also SQLite support,
later, he could re-build jOOQ and see all the newly deprecated method
calls.
What do you guys think? Any feedback welcome
Cheers
Lukas
This is filed as feature request #1007:
https://sourceforge.net/apps/trac/jooq/ticket/1007
The original thread can be seen here:
https://github.com/lukaseder/jOOQ/issues/9
