Hi Christopher,
> About dates, I don't know if this is the best strategy, but this is what we
> do:
>
> with formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS")
> [...]
Thanks for those hints. I'll consider them when implementing these
integration tests:
https://sourceforge.net/apps/trac/jooq/ticket/1147
> We seem to have a different simpler conversion when the date is only
> used as a day and not date/time. I wonder how it should behave
> depending on the actual type of the column on Oracle (Timestamp vs
> Date)
That's a difficult story as Oracle doesn't really have a true DATE
type... With Oracle-only JDBC, I prefer to only use java.sql.Timestamp
and handle date truncation myself, especially because of JDBC's weird
behaviour related to timezones when using java.sql.Date. Feel free to
answer this question, if you know it :-)
http://stackoverflow.com/questions/9202857/timezones-in-sql-date-vs-java-sql-date
On the other hand, beware of this subtlety here:
http://stackoverflow.com/questions/6612679/non-negligible-execution-plan-difference-with-oracle-when-using-jdbc-timestamp-o
I'm not sure what would be the best general option to optimise
date/time queries for both SQL Server AND Oracle at the same time... I
guess that would have to be answered on Stack Overflow.
> I also wonder what would happen if I generate the model for SQLServer,
> then use it against Oracle which for the DATE_TIME uses TIMESTAMP.
> Would the generated script be compatible (the factory would know that
> the dialect is Oracle but the declared type of the field would be the
> SQLServer type)?
jooq-codegen generates only org.jooq.SQLDataType, not
org.jooq.util.[dialect].[Dialect]DataType, so the generated source
code is compatible.
> I gave a thought about this, and I am not sure prepared statements get
> reused if a new instance of the prepared statement is created for
> every execution. Do you have some knowledge on the subject? And in
> jOOQ, are there ways to get the actual instance of the prepared
> statement for external caching and APIs to reuse that instance for a
> bind/fetch?
It is certainly the case for Oracle. There are three levels of caching:
1) Caching the cursor in Java by keeping PreparedStatements open and
referenced, avoiding calls to Statement.close() or letting the GC
clean up statement references. In Oracle, I think this prevents the
database from cleaning up this particular cursor, even if the cursor
cache is full.
2) Caching the cursor in the database by closing PreparedStatements
and tuning the database accordingly. When "soft-parsing" the same
statement again, Oracle will recognise that it can reuse a previous
cursor
3) No caching by closing the PreparedStatement and purging the
database's cursor cache. This will result in "hard-parses" at every
execution.
A single query executes fastest in 1), and slowest in 3) when the
query is repeatedly executed.
I tend to let the database do all the work (i.e. 2)), as our DBAs are
usually quite experts in this kind of fine-tuning. In jOOQ, there is
currently no way of extracting the PreparedStatement, or to prevent
closing it. There is an old feature request asking for precisely that,
though:
https://sourceforge.net/apps/trac/jooq/ticket/385
> jTDS has some sort of internal caching for automatic reuse of
> PreparedStatements, but it is unclear for me what are its criteria to
> decide the reuse of a cached statement. Here is the place I had that
> hint:
> http://jtds.sourceforge.net/faq.html#preparedStatmentMemoryLeak
I'd be curious to hear about your findings, when you find out.
> Having it not
> thread safe is not a problem, I just wanted to clarify the situation.
Sure, no problem
Cheers
Lukas
