Re: support of joda-time LocalDate, LocalDateTime and LocalTime

[Lukas Eder]

Hello Eric,

> However, the Cart pojo could have joda-time type properties like LocalDate,
> LocalDateTime and LocalTime (so no TimeZone).
> And i noticed that jooq Convert class can only handle simple Date sql types
> as Timestamp, Date or Time.

You can always implement your own Converters in jOOQ, in order to
transform JDBC date/time types into your own custom ones. Some
examples are given here:
http://www.jooq.org/doc/2.6/manual/sql-execution/fetching/data-type-conversion/

In principle, this works for JodaTime as well. However, it doesn't
work automatically for record to POJO mapping. I think you've
discovered an important missing feature here. I've registered #2029
for this:
https://github.com/jOOQ/jOOQ/issues/2029

I currently don't see an easy workaround, short of manual mapping from
Record to Cart. Maybe someone else on this list has run into a similar
problem, before?

> Would it be possible to handle these joda-time types ? Or possible to use
> custom converters, like inside Routines ?

Unfortunately, jOOQ Routines (e.g. generated ones) currently don't
support jOOQ Converters.

> Or maybe there is another possibility with jooq to obtain a Pojo result from
> a Sproc ? (I use Sql Server)

This is a pending feature request:
https://github.com/jOOQ/jOOQ/issues/710

Currently, the only way to fetch records (and thus, pojos) from SQL
Server stored procedures is through plain SQL, the way you showed in
your example.

[Lukas Eder]

Hi Eric,

Thanks for clarifying

> [...]
> where Cart is a POJO generated by Jooq, which contains joda types thanks to
> Jooq Converters.
>
> I did a test, and it seems to work well.

Yes, that's more or less how I understood it.

> Great ! I will try to send you a pull request for this if needed.

Thanks for the offer. Sure, why not give it a shot? The best place to
provide a pull request would be on github master (for jOOQ 3.0). As
you're probably using jOOQ 2.6, though, you can also try to implement
something on the version-2.6.0-branch. If it works well, I can do the
merging.

What is currently not clear yet is how this should be implemented
API-wise. There are lots of options:

------------------------------------------------
1. Provide converters to an overloaded version of the
Record.into(Class<E>) method:
<E> E into(Class<? extends E> type, Converter<?, ?>... converters);

In this case, a matching algorithm would have to be implemented to see
if for any Record-Field to POJO-member mapping, Converter.fromType()
(Record type) and Converter.toType() (POJO type) are applicable. While
this isn't a bad solution implementation-wise, I'll have to think
again if such an API is maintainable in the long run

------------------------------------------------
2. Provide a more explicit converter mapping to an overloaded version
of the Record.into(Class<E>) method:
<E> E into(Class<? extends E> type, Map<Field<?>, Converter<?, ?>>
converters); // or
<E> E into(Class<? extends E> type, ConverterMapping converters);

This is pretty much the same as 1. except that the user has more work
to specify the mapping, but also more control. I'm not such a big fan
of this though. Introducing new types or maps should be carefully
considered, as this would be quite new to the overall jOOQ API

------------------------------------------------
3. Register converters globally
Converters are currently registered globally through internal
org.jooq.impl.DataTypes.registerConverter(). This could be improved
and made publicly available

------------------------------------------------

There are probably more options. I personally favour a combination of
3. and 1. It should generally be possible to statically register a
Converter for a pair of types U (user type) and T (database type).
Note, currently, Converters are registered for U only. And then, it
should be possible to specify converters on an ad-hoc scope for a
single method call, locally overriding registered Converters.

What do you think about such a solution?

> I would also like to thank you for the great work done on this framework, as
> it's becoming
> less and less boring and hard to work with Stored procedures thanks to it.

Thank you very much. Feel free to indicate any trouble / feature
requests encountered with stored procedures. So far, I have not much
feedback about how they are used in SQL Server environments.

Cheers
Lukas