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.
