Hi Ben, I'm sorry for the delay in this matter.
It is true that binding the Oracle TIMESTAMP WITH TIME ZONE data type brings a couple of caveats. Unfortunately, I cannot tell you from experience what will happen, perfomance-wise, if you use one solution rather than the other. I can, however, confirm that what you feel is a bit hacky might just be as good as it gets right now. I've reviewed other customers' Binding implementations that attempt to bind the java.sql.SQLXML type, which needs to be free'd explicitly via SQLXML.free() after statement execution (similar to Blob, Clob). Things start getting just as hairy. Usually, with these exotic types, people are happy if they can just serialize / deserialize them to / from the server (e.g. XML). In your case, I agree that there is significant concern regarding runtime performance, but unfortunately, I'm as wise as you are right now. One approach that I've seen in the past is people storing just java.lang.Long values for what corresponds to java.time.Instant. As long as you don't need any date time arithmetic in the database, this might be enough, and is certainly not a performance issue. Another option might be to implement a Binding that completely ignores the serialisation of the TIMESTAMP WITH TIME ZONE type, binds (and reads) java.lang.Long, and converts the number into a timestamp by generating some additional SQL, e.g. as explained here: http://stackoverflow.com/a/15520654/521799 Hope this helps. Let me know if, in the meantime, you have any additional questions. Best Regards, Lukas 2015-12-23 3:32 GMT+01:00 Ben Hood <[email protected]>: > On Tue, Dec 22, 2015 at 9:08 PM, Ben Hood <[email protected]> wrote: > > So I've tried to hack something together, but this is literally a hack: > > > > https://gist.github.com/0x6e6562/51b0caf1ebb6d06d1ee2 > > > > This seems to work, for some value of work. This means that I can > > INSERT and SELECT rows using the custom bindings with generated code. > > > > But I think I'm missing the point on a number of the abstractions - > > this feels like a terrible hack, on many levels. > > > > Is there a better way? > > I've taken a second run up at this problem using OffsetDateTime > instead of Instant. This allows for the offset to be explicit and it > can be down to the application layer to adjust the offset. If the app > doesn't adjust the offset from reason, then the worst that can happen > is that a non-UTC timestamp is stored, but because the offset is also > stored as part of the TIMESTAMP WITH TIME ZONE type, the value is > still explicit and not subject to interpretation. > > Here's the example binding code: > > https://gist.github.com/0x6e6562/325c0020d1355d28bc08 > > Note that this still uses the hacked up code generator to merge in the > TIMESTAMPTZ type information, but this is just a POC right now. > > The main implementation difference between this variant and the > previous is to defer the packing/unpacking to the routines exposed in > https://github.com/marschall/threeten-jpa rather than using the built > in driver packers. This is because the driver packer from Oracle > appears to shell out the server to grab some ref data in order to pack > and unpack the Oracle native format. But marschall/threeten-jpa > appears to have re-implemented the format as a regular routine. Not > sure whether the server based solution is any less performant, but > unless the driver is caching the lookups, this might result in a bunch > of network round trips. I assume without any evidence to back this up > that the reason why the driver takes this approach is to handle the > case when the zone map ref data is updated on the server. > > Note that this approach also links directly to the Oracle library, > which means it's a non-portable and non-restributable solution. > > Having said all of this, am I potentially over thinking the whole problem? > > -- > 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/d/optout. > -- 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/d/optout.
