2013/3/23 Jon Inloes <[email protected]>:
>
> On Saturday, March 23, 2013 3:25:34 AM UTC-7, Lukas Eder wrote:
>>
>> Unfortunately, this is currently not possible with jOOQ's code
>> generator. The only possible way to introduce converters is by using
>> matching table / column names, which you don't want to do...
>>
>> I agree though, that it should be possible to configure this. I could
>> think of an enhancement to the code generation configuration's
>> <ForcedType/> definition. Something along these lines:
>>
>> <complexType name="ForcedType">
>> <all>
>> <!-- The name of the type to be forced upon various artefacts -->
>> <element name="name" type="string" minOccurs="1" maxOccurs="1" />
>>
>> <!--
>> A Java regular expression matching columns, parameters,
>> attributes,
>> etc to be forced to have this type
>> -->
>> <element name="expressions" type="string" minOccurs="0"
>> maxOccurs="1" />
>>
>> <!--
>> A Java regular expression matching data types to be forced to
>> have this type
>> -->
>> <element name="types" type="string" minOccurs="0" maxOccurs="1" />
>> </all>
>> </complexType>
>>
>> The added <types/> element would allow to supply a regular expression
>> such as VARCHAR(\(\d+\))? or in your case, DATETIMEOFFSET. As with
>> today's forced type feature, this would then refer to a converter
>> referenced from <name/>
>>
>> Would that suit your needs?
>>
>> I have registered #2352 to track this feature request:
>> https://github.com/jOOQ/jOOQ/issues/2352
>
>
> I have been thinking about ways to implement the this custom conversion.
> Maybe you want to add a conversion strategy config to the factory?
> For example, lets look at SQLServerDataType DateTimeOffset. You define that
> "datetimeoffset" should be converted to a java.sql.Timestamp. What if
> instead of doing the text search you do a class search to find a converter
> in a map. By default, the map will be populated with the default conversions
> defined in those DataType classes but if you want to override one you might
> edit the config with something like
> .withConverter("microsoft.sql.DateTimeOffset", new Converter())
>
> So when retrieving and object from the database, you can do the following to
> retrieve the converter:
> converterMap.get(resultSet.getObject().getClass().getCannonicalClassName())
Unfortunately, jOOQ's runtime won't know that the original data type
was a DATETIMEOFFSET. It'll only know of TIMESTAMP data types (the SQL
/ JDBC standard)
>> java.sql.Timestamp should be able to maintain timezone information.
>> Could this be a JDBC driver issue? Are you using jconn3 or jtds? How
>> would you properly deserialise DateTimeOffset through JDBC directly?
>> Using ResultSet.getString()?
>>
>> Note, even if you'd apply the above converter, jOOQ would first use
>> ResultSet.getTimestamp() to deserialise DATETIMEOFFSET types. I'll
>> have to think about this when implementing #2352. It should be
>> possible to derive the "best" JDBC type from the converter,
>> directly...
>
>
> Well, each database vendor is different for a ResultSet.getTimestamp() call.
> From what I noticed with Oracle TIMESTAMPTZ, you might get a string back
> 2013-1-1 13:30:1234567 +7:00 and if you call getTimestamp you get 2013-1-1
> 13:30:1234567 back as a timestamp losing the timezone information. You can
> preserve the timezone offset by parsing it out of the byte array returned to
> you in their oracle TIMESTAMPTZ object you get in a call to
> ResultSet.getObject().
>
> So, to construct a joda DateTime object with and oracle TIMESTAMPTZ object
> you do something like:
> Timestamp ts = rs.getTimestamp();
> bytes[] objBytes = rs.getBytes();
> // Parse bytes and compute offset in hours and minutes
> DateTimeZone dtz = DateTimeZone.withHoursAndMinutesOffset(hours, minutes)
> DateTime dt = new DateTime(ts.getTime(), dtz)
Nice to know, I wasn't aware of that.
> with DateTimeOffset it's a little simpler
> DateTimeOffset dateTimeOffset = (DateTimeOffset) resultSet.getObject();
> int minutesOffset = dateTimeOffset.getMinutesOffset()
> // Compute hours offset
> DateTimeZone dtz = DateTimeZone.withHoursAndMinutesOffset(hours, minutes)
> DateTimeZone dtz =
> DateTimeZone.withHoursAndMinutesOffset(dateTimeOffset.getTimestamp(), dtz)
>
> Parsing the string won't necessarily work for all cases because sometimes
> people change their formats and sometimes databases return broken formats
> for the date formatters you can use in java.
>
> One major reason for wanting to preserve the timezone offset if you
> reserialization. If you just take the timestamp and lets assume it's based
> off of UTC offset. Writing it as a string from a JodaTime Object might
> produce a string showing the incorrect timezone. ("2013-1-1T12:30:123Z"
> instead of something like "2013-1-1T5:30:123 -7:00" when you preserve the
> timezone). They might be internally equal times but they don't display the
> timezones properly.
>
> Yea, I think it would be nice to allow someone to create a deserialization
> strategy kind of like what Jackson provides you for deserializing and
> serializing objects for json.
OK. I hope I can come up with a generic solution for jOOQ 3.1. I think
that #2352 should provide enough flexibility for the task at hand. You
should then be able to define custom converters for any combination of
[column / parameter / attribute name pattern] and [data type name
pattern]. Converters define two Class<T> and Class<U> types between
which they are responsible for conversion. In your case, T=Object (or
DateTimeOffset) and U=DateTimeZone.
Clearly, I'll have to think through a couple of corner cases.
Note, I'm currently not sure if a generic ConverterFactory is a good
idea, for two reasons:
1. I'm never a big fan of custom factories registered in the Executor.
They're hard to implement, document, maintain, and understand. Often,
they are not well thought through. If there's a better solution, I'd
like to avoid the extra configuration.
2. A generic ConverterFactory will provide two-way conversion tools
between any U <-> T types. This conversion is unlikely to match the
actual types that should be returned by a Record.getValue() call, as
you're passing it a Field<T> argument. In other words, a generic
ConverterFactory might break typesafety.
Cheers
Lukas
--
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 [email protected].
For more options, visit https://groups.google.com/groups/opt_out.
