Hello Ravi,
Thanks for your feedback. Concerning your questions:
We don't use any DB specific features like data types,
syntax, stored procedures, functions, views etc. It is essential that we
have a single code base.
With jOOQ, you will be able to use some db-specific features, as jOOQ
will render db-specific SQL from common jOOQ constructs. For instance,
if you wish to use the SQL-1992 standard EXTRACT() function to extract
date parts from dates, you can use Factory.extract(), which will
translate to:
- TO_CHAR() in Oracle
- DATEPART() in SQL Server
- EXTRACT() in Postgres
Keep an eye out for the org.jooq.Support annotation to indicate if
some function / SQL clause is supported in all your databases.
1. Can JOOQ be used to generate classes that will work across databases? I
guess so long as the data mappings are common, it should. Also I see a
generic Factory method that takes in a dialect which can be used instead of
the generated Factory.
Yes, this will work. However, to rule out side-effects, you should
choose one of your three databases as your "master" database (or
"development" database) from which you'll generate source code. This
probably shouldn't be Oracle, as it has the least expressive common
type system (TIME and BOOLEAN/BIT are missing, DATE and TIMESTAMP are
both java.sql.Timestamp, there are no BINARY/VARBINARY types, only
BLOB, etc)
The generated artefacts will be SQL dialect independent, i.e. you can
use them against any other database. jOOQ integration tests verify
this by running tests against an HSQLDB database using an
Oracle-generated schema. I'm planning to add some SQL Server / Oracle
combinations to the test suite, as this was also a use case by other
users. Dialect-specific SQL is generated at "render-time", when you
bind a dialect to a Factory, as you have noticed. This means that the
same piece of jOOQ code can be configured to render slightly different
SQL for Oracle, Postgres, SQL Server
2. We map a CHAR(1) to a boolean. Can this be done?
Yes, this has been tested to work. Be sure though, that the generated
classes reference java.lang.Boolean, not String (see question 1). You
also have some control over the data types that are effectively used
in generated source code:
http://www.jooq.org/manual/ADVANCED/CustomTypes/
3. We use a separate table to store sequence numbers for primary keys. Could
we continue this with JOOQ?
If you use triggers to generate sequence numbers from that table on
inserts, then jOOQ will be oblivious of that fact. If you load
sequence numbers programmatically from Java, you will have to ensure
transactional integrity (outside of jOOQ), so again, jOOQ will be
oblivious of that fact. So I don't see a substantial problem.
4. How well does JOOQ perform? i.e are the overheads minimal?
I am infrequently observing jOOQ performance with Yourkit profiler to
rule out single bottlenecks. A more systematic benchmark comparing
JDBC, jOOQ, Hibernate is on the roadmap.
In general, you should expect:
- A little overhead for rendering and binding, as the query model is
no longer flat (String SQL and binding array), but hierarchical. This
should be negligible compared to query execution time.
- Some overhead for fetching data, as the whole JDBC ResultSet is
copied into a list structure. jOOQ uses wrapper types for primitives,
so org.jooq.Result may use up more memory than the ResultSet.
So yes, there is overhead compared to using JDBC directly.
I've generated code but am struggling with telling JOOQ to use the default
schema. ie not prefix the table names with the schema name. How can I do
that with the Factory(connection, dialect, settings) method?
Factory.use() can be used for that, as well as the Settings:
new Factory(connection, dialect,
new Settings().withRenderMapping(
new RenderMapping().withDefaultSchema("MySchema")));
For more details, see
http://www.jooq.org/manual/ADVANCED/SchemaMapping/
Hope this helps,
Cheers
Lukas
2012/4/7<[email protected]>:
First of all, JOOQ looks great. Good job. Hope it works for us.
We develop a Java based app that works with SQL Server, Postgres and Oracle.
We use an in-house ORM solution that generates common code that works across
the databases. We don't use any DB specific features like data types,
syntax, stored procedures, functions, views etc. It is essential that we
have a single code base.
My questions are:
1. Can JOOQ be used to generate classes that will work across databases? I
guess so long as the data mappings are common, it should. Also I see a
generic Factory method that takes in a dialect which can be used instead of
the generated Factory.
2. We map a CHAR(1) to a boolean. Can this be done?
3. We use a separate table to store sequence numbers for primary keys. Could
we continue this with JOOQ?
4. How well does JOOQ perform? i.e are the overheads minimal?
I've generated code but am struggling with telling JOOQ to use the default
schema. ie not prefix the table names with the schema name. How can I do
that with the Factory(connection, dialect, settings) method?
Thanks for your time.
- Ravi.
