I can't seem to find any example of how to use Existing pojos.....

[Ryan Cornia]

I have an application with existing POJO's (and many hand coded SQL statements) I would like to try JOOQ on. 

I can't find any examples of how to do this? Do I need to configure JOOQ programmatically so it knows, for instance, I have a Document table and Annotation table, and how they are related? Where would I find an example of that?

Or is code generation needed to get full feature type checking queries? Right now the data is mostly read only, so I am looking for the most maintainable way to do queries. 

Thanks,

Ryan

[Lukas Eder]

Hi Ryan,

Unlike most JPA-based ORMs, jOOQ cleanly separates querying from mapping. There is no implicit knowledge about relations between your document and annotation tables, unless you explicitly join the two tables in your SQL statement. Once the result is fetched, you have lots of means of mapping the flat result set onto your custom domain model (i.e. your existing POJOs). jOOQ supports a default mapping algorithm that is described here:

http://www.jooq.org/javadoc/latest/org/jooq/impl/DefaultRecordMapper.html

Since jOOQ 3.1, you can globally override the DefaultRecordMapper and inject your own mapping strategies, using tools like

- http://modelmapper.org

- https://code.google.com/p/orika

- Your own implementation of jOOQ's RecordMapper

Some documentation:

http://www.jooq.org/doc/3.1/manual/sql-execution/fetching/recordmapper

http://www.jooq.org/doc/3.1/manual/sql-execution/fetching/pojos

http://www.jooq.org/doc/3.1/manual/sql-execution/fetching/pojos-with-recordmapper-provider

Hope this helps

Lukas

2013/7/19 Ryan Cornia <ryanc...@gmail.com>

--
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/groups/opt_out.
 
 

[Rob Sargent]

Are there any actual examples of RecordMapper working with fetchMap() on results of joins?

I can map a direct select to a string (name field), but not for a join. I've wondered through the code for DefaultRecordMapper, Record and RecordType but still haven't managed to get a usable mapper the will fetchMap() my select/join results to a string (the name field of the record)

[Samir Faci]

fetchMap:

Map<Long, String> userMapping =  complexQuery.fetchMap(schema.table.fieldname, schema.table.fieldname2); 

I've used the exact same pattern for fetchMap irrelevant of the complexity of the SQL and it works fine for me.  I do find repeating the 

field names a bit repetitive but it does work. 

If you're on Java 8, you have access to the .fetch().stream() pattern and you can simply use the standard Java 8 pattern to conver it to a map if you'd like.

Actually, I take it back.. if you're on Jooq 3.1 I don't think you have that feature.  I can't remember what version I started with but I don't think I was ever on 3.1 might be a legacy issue? 

--

Samir Faci

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Thank you
Samir Faci

https://keybase.io/csgeek

[Rob Sargent]

Map<String,PersonRecord> is a different matter I think. 

For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

Hmm, I thought there was a fetchMap(Field, Table) method but there isn't. In that case, you could do:

DSL.using(configuration)

   .select(...)

   .from(...)

   .fetchMap(MY_TABLE.KEY_FIELD, r -> r.into(PERSON));

This will be using the Record.into(Table) method:

https://www.jooq.org/javadoc/latest/org/jooq/Record.html#into-org.jooq.Table-

Hope this helps,

Lukas

[Rob Sargent]

Thank you for the suggestion, which I'll try shortly.  I have had success with this

        fullMap.putAll(ctx.selectFrom(PERSON)
                .whereExists(ctx.selectOne()
                        .from(ctx.select().from(ALIAS)
                                .where(ALIAS.AKA.in(nameSet).and(ALIAS.PERSON_ID.equal(PERSON.ID)))))
                .fetchMap(PERSON.NAME));

but I was certain there was a flavour of fetchMap(String, Record) that would do the trick.

For more options, visit https://groups.google.com/d/optout.

[Rob Sargent]

Thank you again, all of these work:

        Map<String, PersonRecord> fullMap = new HashMap<>();
        fullMap.putAll(ctx.select(PERSON.fields())
                       .from(PERSON.join(ALIAS)
                             .on(PERSON.ID.equal(ALIAS.PERSON_ID)))
                       .where(ALIAS.AKA.in(nameSet))
                       .fetchMap(PERSON.NAME, r -> r.into(PERSON)));

        fullMap.putAll(ctx.select(PERSON.fields())
                       .from(PERSON.join(ALIAS)
                             .on(PERSON.ID.equal(ALIAS.PERSON_ID)
                                 .and(ALIAS.AKA.in(nameSet))))
                       .fetchMap(PERSON.NAME, r -> r.into(PERSON)));

        fullMap.putAll(ctx.select(PERSON.fields())
                       .from(PERSON.join(ALIAS)
                             .on(PERSON.ID.equal(ALIAS.PERSON_ID))
                             .and(ALIAS.AKA.in(nameSet)))
                       .fetchMap(PERSON.NAME, r -> r.into(PERSON)));

Is any more proper than the other?

I had gotten side tracked thinking I needed to use a RecordMapper but could never set that up properly.

On 03/28/2017 10:15 AM, Lukas Eder wrote:

For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

2017-03-28 18:29 GMT+02:00 Rob Sargent <robjs...@gmail.com>:

but I was certain there was a flavour of fetchMap(String, Record) that would do the trick.

There's one version that takes a Class as the second argument: fetchMap(String, Class), but that can lead to ambiguities if your JOIN produces duplicate column names.

[Lukas Eder]

Comment inline...

2017-03-28 20:36 GMT+02:00 Rob Sargent <robjs...@gmail.com>:

Thank you again, all of these work:

        Map<String, PersonRecord> fullMap = new HashMap<>();
        fullMap.putAll(ctx.select(PERSON.fields())
                       .from(PERSON.join(ALIAS)
                             .on(PERSON.ID.equal(ALIAS.PERSON_ID)))
                       .where(ALIAS.AKA.in(nameSet))
                       .fetchMap(PERSON.NAME, r -> r.into(PERSON)));

        fullMap.putAll(ctx.select(PERSON.fields())
                       .from(PERSON.join(ALIAS)
                             .on(PERSON.ID.equal(ALIAS.PERSON_ID)
                                 .and(ALIAS.AKA.in(nameSet))))
                       .fetchMap(PERSON.NAME, r -> r.into(PERSON)));

        fullMap.putAll(ctx.select(PERSON.fields())
                       .from(PERSON.join(ALIAS)
                             .on(PERSON.ID.equal(ALIAS.PERSON_ID))
                             .and(ALIAS.AKA.in(nameSet)))
                       .fetchMap(PERSON.NAME, r -> r.into(PERSON)));

Is any more proper than the other?

The first one is what I'd choose here, because the WHERE predicate is semantically sligthly different from the ON predicate. Your ALIAS.AKA predicate is not stricly part of the JOIN specification, so I wouldn't put it there.

For inner join, this is mostly just a stylistic debate (at least in most databases). For outer join, 1 would be a very different query from 2/3 (which is another reason why stylistically, you should put the predicates at the semantically right place).

2 and 3 are exactly the same thing. 2 is creating a Condition inside of ON, whereas 3 is using TableOnConditionStep convenience API (which internally does the same thing as 2 but leads to a bit less confusing parentheses). 

Note, you could also write this, and it would still be the same as 2 and 3, just using convenience API:

        fullMap.putAll(ctx.select(PERSON.fields())
                       .from(PERSON)
                       .join(ALIAS)

                       .on(PERSON.ID.equal(ALIAS.PERSON_ID))
                       .and(ALIAS.AKA.in(nameSet))
                       .fetchMap(PERSON.NAME, r -> r.into(PERSON)));

I had gotten side tracked thinking I needed to use a RecordMapper but could never set that up properly.

Well, your lambda *is* a RecordMapper :) Just much more convenient syntax than an anonymous or "normal" class.

Hope this helps,
Lukas

[Rob Sargent]

Thank you for the guidance and confirmation.  The first option was from my sql instinct, but I wanted to see where jOOQ might take me. (The parenthesis mess is nicely managed for me by emacs :) )

[Lukas Eder]

2017-03-30 19:48 GMT+02:00 Rob Sargent <robjs...@gmail.com>:

Thank you for the guidance and confirmation.  The first option was from my sql instinct, but I wanted to see where jOOQ might take me.

jOOQ takes you where ever you want to go. Also, jOOQ won't judge you if where you want to go is not a good idea to go to - you might know better than jOOQ ;)

(The parenthesis mess is nicely managed for me by emacs :) )

Oh, interesting - is that some nice emacs feature? Or is it just the fact that you're not impress with anything less than 20 levels of nested lisp-style parentheses?

[Rob Sargent]

LOL.  It's just "some nice feature" which has done sensible indentation in code for more years than I care to remember.

[Lukas Eder]

I see, I wonder if that exists for Java / jOOQ code :)

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

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 jooq-user+unsubscribe@googlegroups.com.

[Rob Sargent]

You would be hard pressed to name a language for which it doesn’t exist.

> On Apr 2, 2017, at 8:21 AM, Lukas Eder <lukas...@gmail.com> wrote:
>
> I see, I wonder if that exists for Java / jOOQ code :)
>
> 2017-03-30 23:35 GMT+02:00 Rob Sargent <robjs...@gmail.com>:
> LOL. It's just "some nice feature" which has done sensible indentation in code for more years than I care to remember.
>
> On 03/30/2017 03:24 PM, Lukas Eder wrote:
>>
>>
>> 2017-03-30 19:48 GMT+02:00 Rob Sargent <robjs...@gmail.com>:
>> Thank you for the guidance and confirmation. The first option was from my sql instinct, but I wanted to see where jOOQ might take me.
>>
>>
>> jOOQ takes you where ever you want to go. Also, jOOQ won't judge you if where you want to go is not a good idea to go to - you might know better than jOOQ ;)
>>
>> (The parenthesis mess is nicely managed for me by emacs :) )
>>
>> Oh, interesting - is that some nice emacs feature? Or is it just the fact that you're not impress with anything less than 20 levels of nested lisp-style parentheses?
>> --
>> 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.
>
>
> --
> 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.
>
>
> --
> 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.