Deciding if JOOQ is an option for us.
rwa...@gmail.com
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.
Lukas Eder
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 <rwa...@gmail.com>:
Ravindra Wankar
Thank you very much for that informative reply. Yes, I'm using Postgres
to generate the code and I'm using Java code to generate ID's. Have a
few more questions.
1. The example for <forcedType> parameter uses column names to do the
mapping. Is there a way to indicate that a data type (e.g CHAR(1))
should be mapped to a Boolean?
2. Can I specify custom data mapping using the ANT task or can I invoke
the ANT task with a jooq-config.xml file?
3. Is it possible to not specify the schema name at all? I tried the
following with Postgres and it worked. Haven't tried it with SQL
Server/Oracle yet.
new Factory(connection, dialect,
new Settings().withRenderMapping(
new RenderMapping().withDefaultSchema("")));
4. How do I set up JOOQ logging? e.g to see the SQL statements it is
generating? We use log4j.
- Ravi
Ravindra Wankar
Thank you very much for that informative reply. Yes, I'm using Postgres
to generate the code and I'm using Java code to generate ID's. Have a
few more questions.
1. The example for <forcedType> parameter uses column names to do the
mapping. Is there a way to indicate that a data type (e.g CHAR(1))
should be mapped to a Boolean?
2. Can I specify custom data mapping using the ANT task or can I invoke
the ANT task with a jooq-config.xml file?
3. Is it possible to not specify the schema name at all? I tried the
following with Postgres and it worked. Haven't tried it with SQL
Server/Oracle yet.
new Factory(connection, dialect,
new Settings().withRenderMapping(
new RenderMapping().withDefaultSchema("")));
4. How do I set up JOOQ logging? e.g to see the SQL statements it is
generating? We use log4j.
- Ravi
On Saturday 07 April 2012 02:06 PM, Lukas Eder wrote:
Lukas Eder
> 1. The example for <forcedType> parameter uses column names to do the
> mapping. Is there a way to indicate that a data type (e.g CHAR(1)) should be
> mapped to a Boolean?
This is possible with some experimental code generation functionality.
It is not yet documented, however. You can use <forcedType/> to force
a SQL data type upon a set of columns. The following example is from
the jOOQ integration tests::
<forcedType>
<!-- Use a valid Postgres SQL type here, when generating from Postgres -->
<name>BOOLEAN</name>
<!-- All objects matching this regular expression will be forced
to type BOOLEAN -->
<expressions>(?i:(.*?.)?T_BOOLEANS.(VC|C|N)_BOOLEAN)</expressions>
</forcedType>
> 2. Can I specify custom data mapping using the ANT task or can I invoke the
> ANT task with a jooq-config.xml file?
The org.jooq.util.GenerationTask ant task is not up to date with the
latest developments of jooq-codegen. I recommend running the
GenerationTool using ant's <java/> task, as documented here:
http://www.jooq.org/manual/META/Configuration/
> 3. Is it possible to not specify the schema name at all? I tried the
> following with Postgres and it worked. Haven't tried it with SQL
> Server/Oracle yet.
>
> new Factory(connection, dialect,
> new Settings().withRenderMapping(
> new RenderMapping().withDefaultSchema("")));
This would have no effect on generated SQL. What are you trying to do?
> 4. How do I set up JOOQ logging? e.g to see the SQL statements it is
> generating? We use log4j.
The default for Settings.isExecuteLogging() is true, so jOOQ already
logs all rendered SQL to log4j's debug log output. This is done using
the org.jooq.tools.LoggerListener, which supports log4j, slf4j, and
java.util.logging. If you need more information, you can set up your
own ExecuteListener, as documented here:
http://www.jooq.org/manual/ADVANCED/ExecuteListener/
A sample log4j configuration can be found here:
https://github.com/lukaseder/jOOQ/blob/master/jOOQ-test/src/log4j.xml
Hope this helps
Cheers
Lukas
Ravindra Wankar
1. Can you explain what
<expressions>(?i:(.*?.)?T_BOOLEANS.(VC|C|N)_BOOLEAN)</expressions> is
trying to do?
2. I'm a bit confused about schema names. Presently we connect to the
database with the db owner and use Apache Torque which generates SQL
WITHOUT prefixing the table names with schema names. So we don't have to
know the schema names like 'Public' in PG and 'dbo' (I guess) in SQL Server.
a) I've been able to run the GenerateTask with the following config.
<generate-classes
jdbcdriver="org.postgresql.Driver"
jdbcurl="jdbc:postgresql://localhost:5432/mydb?protocolVersion=2"
jdbcuser="dbuser"
jdbcpassword=""
jdbcschema="public"
generatordatabase="org.jooq.util.postgres.PostgresDatabase"
generatordatabaseinputschema=""
generatordatabaseoutputschema=""
generatortargetpackage="com.mycompany.jooq"
generatortargetdirectory="${src.java.dir}"/>
Seems like here the InputSchema is empty. But I'm specifying the
jdbcschema. Can you explain the difference? The generated code works.
b) When I call
new Factory(connection, dialect,
new Settings().withRenderMapping(
new RenderMapping().withDefaultSchema("")));
I'm hoping that JOOQ will not prefix the table names with any schema
name and that the default schema for the user connected would be used.
Again, the purpose is to not concern ourselves with the schema name at all.
3) Regarding logging, log4j.properties is on the classpath and I've
added log4j.logger.org.jooq=DEBUG. Our log statements are coming through
but not JOOQ's. What am I missing?
Thanks again for your help,
- Ravi
Lukas Eder
> 1. Can you explain what
> <expressions>(?i:(.*?.)?T_BOOLEANS.(VC|C|N)_BOOLEAN)</expressions> is trying
> to do?
This is an example for a regular expression matching all objects that
will be forced to a given type. In this case, columns VC_BOOLEAN,
C_BOOLEAN, N_BOOLEAN from the table T_BOOLEANS in any schema will be
matched.
> 2. I'm a bit confused about schema names. Presently we connect to the
> database with the db owner and use Apache Torque which generates SQL WITHOUT
> prefixing the table names with schema names. So we don't have to know the
> schema names like 'Public' in PG and 'dbo' (I guess) in SQL Server.
As I mentioned in the first mail, your best choice is to use the
RenderMapping to dynamically specify the default schema
new Factory(connection, dialect,
new Settings().withRenderMapping(
new RenderMapping().withDefaultSchema("public")));
jOOQ has been designed to work with multi-schema environments, that is
why the schema names are always rendered by default. If you think you
will only ever have one schema, and you want to prevent jOOQ's
rendering of schema names, set that schema as your default schema to
the render mapping. The rationale behind this functionality is
explained here:
http://www.jooq.org/manual/ADVANCED/SchemaMapping/
Another option is to map the Postgres "public" schema to "dbo" on SQL
Server, and to "your-oracle-schema" on Oracle, or alternatively, to
create the same schema name on every one of the three databases.
> a) I've been able to run the GenerateTask with the following config.
> [...]
>
> Seems like here the InputSchema is empty. But I'm specifying the jdbcschema.
> Can you explain the difference? The generated code works.
You'll find a warning in the generation logs indicating that
"jdbc.Schema" is a deprecated property. You should only use
inputSchema.
> b) When I call
>
> new Factory(connection, dialect,
> new Settings().withRenderMapping(
> new RenderMapping().withDefaultSchema("")));
> I'm hoping that JOOQ will not prefix the table names with any schema name
> and that the default schema for the user connected would be used. Again, the
> purpose is to not concern ourselves with the schema name at all.
Then, please use the Setting's default schema, and set it to "public",
"dbo", "your-oracle-schema-name", depending on the database against
which you're running your query. I have added a feature request to add
a flag to the RenderMapping, that allows for avoiding schema names
entirely in rendered SQL:
https://sourceforge.net/apps/trac/jooq/ticket/1286
> 3) Regarding logging, log4j.properties is on the classpath and I've added
> log4j.logger.org.jooq=DEBUG. Our log statements are coming through but not
> JOOQ's. What am I missing?
I'm sorry, I don't have much experience configuring log4j. I've had no
issues with this configuration file:
https://github.com/lukaseder/jOOQ/blob/master/jOOQ-test/src/log4j.xml
Maybe you'll have better luck asking this question on Stack Overflow?
Cheers
Lukas
Ravindra Wankar
Currently our boolean columns are prefixed with is_. So the pattern
matching works fine. However, our date columns are named created,
approved_on, start_date, plan_finish, last_reminded_at etc. I'm having
to specify a pattern like
(?i:.*\.(CREATED|DELETED|.*_DATE|.*_AT|.*_ON)). I think it will be
better if we can simply specify a database type to be specified.
Thanks,
- Ravi
Lukas Eder
> Currently our boolean columns are prefixed with is_. So the pattern matching
> works fine. However, our date columns are named created, approved_on,
> start_date, plan_finish, last_reminded_at etc. I'm having to specify a
> pattern like (?i:.*\.(CREATED|DELETED|.*_DATE|.*_AT|.*_ON)). I think it will
> be better if we can simply specify a database type to be specified.
Yes, that was in the original idea, to be able to force all
occurrences of a SQL data type to be mapped to another type. But I
haven't had the time yet to make this work correctly on all databases.
I'm afraid that some substantial changes to jooq-meta / jooq-codegen
are needed for that.
Anyway, you could query your Oracle (or Postgres, SQL Server)
dictionary views to discover all date / time columns like this:
select owner, table_name, column_name, data_type
from all_tab_cols
where owner = '[your schema name]'
and (data_type = 'DATE'
or data_type like 'TIMESTAMP%'
or data_type like 'INTERVAL%');