Thanks for the reply Lukas. I understand where you're coming from. I was looking through the code a bit and figured I'd ask :)
I think having some way of registering a converter for DateTImes with timezone would be a great feature since Java's internal Date implementation is so broken. On Sunday, March 24, 2013 2:17:40 AM UTC-7, Lukas Eder wrote: > > 2013/3/23 Jon Inloes <[email protected] <javascript:>>: > > > > 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.
