How to insert Date without using java.sql.Date internally?

[Łukasz Stachowiak]

Hi,

I'm using postgres database and I want to insert value into 'Date' type column - which is without timezone.

Problem is that JOOQ internally uses java.sql.Date which extends java.util.Date - date with timezone.

Is there any way to do it?

Regards,

Łukasz

[Lukas Eder]

Hi Łukasz,

I wasn't aware of such a "limitation" of the java.sql.Date type. How would you do it with JDBC, when binding java.sql.Date? Probably, using an additional Calendar object, through:

http://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setDate(int,%20java.sql.Date,%20java.util.Calendar)

I suspect there might currently be a flaw in how jOOQ handles DATE and TIMESTAMP columns with respect to timezones...?

As a workaround, you could inline such date columns using DSL.inline():

http://www.jooq.org/javadoc/3.3.x/org/jooq/impl/DSL.html#inline(T)

That will make jOOQ render something like DATE '2014-05-25'.

Another option is to resort to plain SQL or custom QueryParts:

- http://www.jooq.org/doc/latest/manual/sql-building/plain-sql/

- http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Witold Szczerba]

Hi Lukas,
in plain JDBC one can set date like this "{d '2014-05-25'}" or get date like "2014-05-25" and parse it.

This way there is no need to deal with tragic sql.Date objects.

When declaring a jOOQ converter for date field, we can pick any user object we want, like LocalDate, but we are forced to use sql.Date as database type. Declaring String will not work, the code generator will produce broken definition.

Regards,
Witold Szczerba
---
Sent from my mobile phone.

[Lukas Eder]

Hi Witold,

2014-05-25 11:29 GMT+02:00 Witold Szczerba <pljos...@gmail.com>:

Hi Lukas,
in plain JDBC one can set date like this "{d '2014-05-25'}" or get date like "2014-05-25" and parse it.

Yes, that's about the same thing as forcing jOOQ to inline the bind value. jOOQ doesn't use the JDBC escaping syntax, because that won't work if you copy-paste the generated SQL into pgAdmin III, for instance. But it is the same as writing the SQL standard date literal: DATE '2014-05-25' 

This way there is no need to deal with tragic sql.Date objects.

I know... 

When declaring a jOOQ converter for date field, we can pick any user object we want, like LocalDate, but we are forced to use sql.Date as database type. Declaring String will not work, the code generator will produce broken definition.

Hmm, yes, unfortunately, type-rewrites (from SQL DATE to SQL VARCHAR) currently cannot be combined with Converters. So you're stuck with either using simple String types (by enforcing VARCHAR) or with converting to java.sql.Date.

I wonder whether jOOQ should add new types to support the distinction between with and without timezone SQL temporal types? I don't mean that jOOQ would actually re-implement java.time.* or JodaTime, but we did the same thing to formally support SQL standard INTERVAL data types. I guess the only way to get this right in the long run is to "extend" JDBC in this area by using dedicated types... What's your opinion on this?

Cheers

Lukas

[Witold Szczerba]

I think it would be enough to let the converters to use the 'inline' mechanism, so we could do any conversion we want as far as the JDBC driver us fine with it.
The jOOQ would need to know how it is supposed to treat such a column, though... Maybe by adding third type parameter?

That would also let us deal with db custom types in converters, wouldn't it? Or is it already possible?

Regards,
Witold Szczerba
---
Sent from my mobile phone.

--

[Lukas Eder]

2014-05-25 12:22 GMT+02:00 Witold Szczerba <pljos...@gmail.com>:

I think it would be enough to let the converters to use the 'inline' mechanism, so we could do any conversion we want as far as the JDBC driver us fine with it.
The jOOQ would need to know how it is supposed to treat such a column, though... Maybe by adding third type parameter?

The current Converter types are not supposed to know anything about SQL, bind values, or jOOQ API. Their only responsibility is to convert between <U> (user type) and <T> (JDBC type). If you wanted to adapt this right now, you'd need to patch jOOQ internals.

Another option would be to implement a JDBC proxy, that intercepts all PreparedStatement.setDate() calls, and ResultSet.getDate() calls, to patch the required bind value, or to set it as a different data type, e.g. String.

That would also let us deal with db custom types in converters, wouldn't it? Or is it already possible?

 

I can see where your line of thoughts is going, and I believe that we already have this on the roadmap:

https://github.com/jOOQ/jOOQ/issues/3248

Specifically with PostgreSQL's many vendor-specific data types, it is very hard to get all the type binding right through the JDBC API, if implemented in jOOQ's core code. Many of those PostgreSQL data types are actually not even supported by PostgreSQL's JDBC driver.

So, to help users leverage these things, we want to think about actual type providers that are like Converters, but they also implement all the serialisation and deserialisation logic, i.e.:

- How to render SQL with bind variables

- How to render SQL with inlined values

- How to set bind values onto PreparedStatements

- How to register out parameters from CallableStatements

- How to read values from ResultSet

- How to serialise values to SQLOutput (for Oracle)

- How to deserialise values from SQLInput (for Oracle)

Unlike a Converter<T, U> type, the TypeProvider<U> type would not need any concrete <T> type (JDBC type), only a <U> type (user type) for use with jOOQ API interaction when forming predicates, etc.

Obviously, jOOQ would be providing a lot of default implementations for such type providers. Besides, the existing jOOQ internals will be refactored and pulled out into such providers.

In the case of DATE WITHOUT TIMESTAMP, we can then easily make adaptations to allow for mapping things like LocalDate onto whatever SQL representation seems most appropriate.

As always, your feedback with this matter is very welcome! Getting this design right will be essential for many future data type related features.

Unfortunately, this will not make it into jOOQ 3.4 anymore, but I'm positive that we'll implement it for jOOQ 3.5, some time this summer.

Cheers

Lukas

[Witold Szczerba]

So it seems there is nothing we can do for now with our date columns. At least not until #3248 gets released...

Or, is there any way to guide/amend the codegen somehow (without tweaking the jOOQ), so it would think the column is of character type? Then we could apply a String to LocalDate converter.

Regards,
Witold Szczerba
---
Sent from my mobile phone.

--

[Lukas Eder]

There are always numerous possible hacks :-)

In this case, one idea would be to override or patch the logic from jOOQ-Meta that produces column types, and replace that by VARCHAR. This should be about 5-10 lines of code.

You might also be able to implement this on a SQL level, if it's possible to rewrite the information_schema.columns view for the user that you're using to generate code. This could be implemented either on a SQL level, or on a JDBC level - feeding VARCHAR values back instead of DATE, for INFORMATION_SCHEMA.COLUMNS values.

Another option would be to post-process generated Java files in order to rewrite the column types in Java source code.

Having said so, all of these are (viable IMO) hacks to work around the current limitation, without touching actual jOOQ source code.

Before you continue, though, have you checked if using VARCHAR bind values really works? jOOQ usually casts bind values to coerce them to the appropriate type, e.g. CAST(? as VARCHAR). This might not be correct in some situations, if the expected type is really DATE, not VARCHAR...

Cheers

Lukas