Jooq Row Mapping

[gantners]

I would like to use jooq for building the sql and  replacing the pure spring jdbc calls with all those ugly ? on update statements and also getting more typesafety.

Currently I use Spring and its RowMapping Functionality. 

class Contact{
int uniqueId;
String name;
Account account;
}

class Account{
int id;
String name;
}

public List<Contact> getContactListByUniqueID(List<String> uniqueids) {
 Map<String, List<String>> namedParameters = Collections.singletonMap("ids", uniqueids);
 return SQLUtils.namedListQuery(namedParameterJdbcTemplate,"Select* from contact left join account on contact.accountid = account.id where UniqueID in (:ids)",namedParameters,RowMapperFactory.getContactRowMapper());
}

}

with:

public static RowMapper<Contact> getContactRowMapper(){

            return new RowMapper<Contact>() {
                   public Contact mapRow(ResultSet rs, int rowNum) throws SQLException, DataAccessException {
                                Contact c = new Contact();
                                c.setFirstname(rs.getString(Contact.Firstname));
                                //fill all
                                c.setAccount(RowMapperFactory.getAccountRowMapper().mapRow(rs,rowNum)); //AccountMapper same style as ContactMapper
                                return c;
                        }
}

For convenience, i build the beans using the cartesian product over joins, for entities which have none to few "one to one" foreign keys.

This approach gets unhandy if the beans consists of many other beans which lead to many joins.

To address this issue, i´d like to normalize this by doing a single query each:

select * from contact where uniqueid in (:ids);
select * from account where id in (select accountid from contact where uniqueid in (:ids));

The questions:

a) i find it unhandy to iterate over the first contact list to map the according account from the second query. Is there a much better approach to set each contacts account?

b) i had a look into modelmapper, but i´m not sure which approach to take for mapping my rows to bean, as modelmapper needs a lot of code for doing this (for reasons i need to specify each mapping property extra, as the table columns are not matching any strategy like camelcase etc. and also i have some legacy strings which are boolean in the bean) 

What i want to achieve is some sort of factory providing me the mapping in a way, that i do not have to think about it in any other place like my current RowMapperFactory.

I imagine something like:

create.selectFrom(BOOK).orderBy(BOOK.ID).fetch().map(new RecordMapper<BookRecord, Integer>() {
         @Override public Integer map(BookRecord book) {
              return book.getId();
         }
 };

But the RecordMapper as Factory like my RowMapperFactory, to have a central place for all mappers which can be used anywhere in all daos like:

public static RecordMapper<ContactRecord, Contact> getContactRowMapper(){ 

return new RecordMapper<ContactRecord, Contact>() { 

@Override public Contact map(ContactRecord record) { 

Contact c = new Contact(); 

c.setFirstname(record.getValue(table.Firstname)); //whereever i would get the table from 

return c; } 

}

}

}

that way i would save a lot of converters, providers which i think would be necessary if i use modelmapper. Maybe i just didn´t see the easy way with it.

So i´m looking forward on your oppion.

Cheers Stefan

[Lukas Eder]

Hi Stefan,

Re-normalising the outcome of joins is a frequent topic on this user group. I'm glad you have also listed the possibility of running two queries, the second one taking the first query in a semi-join using an IN-predicate, which is probably the best way of doing this with SQL, short of using MULTISETs.

Concerning your actual question, there is a possibility to override jOOQ's DefaultRecordMapper centrally for all queries via a RecordMapperProvider that you can register in your Configuration:

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

From how I understand your use-case, you will be able to delegate some mapping to your own reusable RecordMappers once and for all, while delegating unknown mapping targets to jOOQ's DefaultRecordMapper, or perhaps to modelmapper.

Let me know if this is what you were looking for. In my opinion, this should allow for you to implement a solution that's even simpler to use at the call site than Spring's RowMapper.

Best Regards,

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 jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[gantners]

Hi Lukas,

first thanks for the quick responses. 

I tried implementing the provider and recordmappers, but i got a bit stock by now.

what i have currently:

public class MyRecordMapperProvider implements RecordMapperProvider {

@Override

public <R extends Record, E> RecordMapper<R, E> provide(RecordType<R> recordType, Class<? extends E> type) {

if (type == Account.class) {

return (RecordMapper<R, E>) getAccountRecordMapper();

}

return new DefaultRecordMapper(recordType, type);

}

public static RecordMapper<TAccountRecord, Account> getAccountRecordMapper() {

return new RecordMapper<TAccountRecord, Account>() {

@Override

public Account map(TAccountRecord record) {

Account c = new Account();

c.setId(record.getId());

c.setName(record.getName());

c.setCreateTime(record.getCreatetime());

return c;

}

};

}
}

@Test
public void myTest(){

        TAccount a = new TAccount();

List<Account> accountList = create.select(a.fields()).from(a).fetch().map(CockpitRecordMapperProvider.getAccountRecordMapper());

}

where the query part doesn´t let me use the recordmapper like this. I guess i have to use intoGroups() instead, but even that does not let me use the RecordMapper. Maybe because of generics.

Eclipse is whining:

The method map(RecordMapper<? super Record,E>) in the type Result<Record> is not applicable for the arguments (RecordMapper<TAccountRecord,Account>)

so i´m curios what would be the correct query for the account class!

also, as i configured jooq within my spring.xml with:

<!--  jooq -->

<!-- Configure jOOQ's ConnectionProvider to use Spring's TransactionAwareDataSourceProxy,

         which can dynamically discover the transaction context -->

    <bean id="transactionAwareDataSource"

        class="org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy">

        <constructor-arg ref="dataSource_mysql" />

    </bean>

    <bean class="org.jooq.impl.DataSourceConnectionProvider" name="connectionProvider">

        <constructor-arg ref="transactionAwareDataSource" />

    </bean>

    

    <bean id="exceptionTranslator" class="com.westhouse.jooq.exception.ExceptionTranslator" />

    

    <!-- Invoking an internal, package-private constructor for the example

         Implement your own Configuration for more reliable behaviour -->

    <bean class="org.jooq.impl.DefaultConfiguration" name="config">

        <constructor-arg index="0" ref="connectionProvider" />

        <constructor-arg index="1"><null /></constructor-arg>

        <constructor-arg index="2"><null /></constructor-arg>

        <constructor-arg index="3">

            <list>

                <bean class="org.jooq.impl.DefaultExecuteListenerProvider">

                    <constructor-arg index="0" ref="exceptionTranslator"/>

                </bean>

            </list>

        </constructor-arg>

        <constructor-arg index="4"><null /></constructor-arg>

        <constructor-arg index="5"><value type="org.jooq.SQLDialect">MARIADB</value></constructor-arg>

        <constructor-arg index="6"><null /></constructor-arg>

        <constructor-arg index="7"><null /></constructor-arg>

    </bean>

    

    <!-- Configure the DSL object, optionally overriding jOOQ Exceptions with Spring Exceptions -->

    <bean id="dsl" class="org.jooq.impl.DefaultDSLContext">

        <constructor-arg ref="config" />

    </bean>

    <!-- joo end -->

in order to provide the general MyRecordRowMapperProvider i dont like to reconfig everything using:

DSL.using(new DefaultConfiguration()
   .set(connection)
   .set(SQLDialect.ORACLE)

. Is there a way to specify the provider in the spring.xml configuration of the constructor? perhaps as the third constructor argument as a reference to the MyRecordRowMapperProvider bean?

The provider goes into the right direction of centralizing my mappers, but its not yet the best solution. Is there an abstract class of the RecordMapperProvider interface also available? I´m asking as i like it more to specify the parameter class
directly to get a specific AccountRecordMapperProvider instead of a generic method where i have to conditionally ask for each class type and eventually return the defaultrecordmapper instead of throwing an exception,
when the appriopriate class mapper cannot be found, which IMO leads to bad behaviour.

if (type == Account.class) {

return (RecordMapper<R, E>) getAccountRecordMapper();

}

return new DefaultRecordMapper(recordType, type);

 I dont think the default mapper could not map anything right in my cases, the fallback therefor is for me more pain than gain.

maybe this thoughts are a leftover excessive spring row mapper using, but it just somehow feels bad.

share your thoughts!

best regards

stefan

[Lukas Eder]

On a sidenote: You probably shouldn't assume in your account record mapper that you will be getting a TAccountRecord. In your particular query, that might be the case but as soon as you join tables or reduce the number of fields, you might get something else...

In particular, when you use select(Field[]).from(some table), then there is no way that jOOQ can still know the record type that you have in mind (TAccountRecord). In fact, it could be any record type, you're passing a Field[] argument to the SELECT clause, which is why you will get the much more general Record type from your fetch() method - with no type information.

Note also that the idea of the RecordMapperProvider is for you not to need to explicitly reference these mappers any longer. The idea would be to do simply this:

@Test
public void myTest(){

        TAccount a = new TAccount();

List<Account> accountList = create.select(a.fields()).from(a).fetch().into(Account.class);

}

The Account.class is passed on to your RecordMapperProvider, where you already decided that it would be a RecordMapper obtained from getAccountRecordMapper(). Does that make sense?

Yes, it's the third argument. We'll change the example configuration in our documentation soon and use the set() methods, which will be more appropriate.

 

The provider goes into the right direction of centralizing my mappers, but its not yet the best solution. Is there an abstract class of the RecordMapperProvider interface also available? I´m asking as i like it more to specify the parameter class
directly to get a specific AccountRecordMapperProvider instead of a generic method where i have to conditionally ask for each class type and eventually return the defaultrecordmapper instead of throwing an exception,
when the appriopriate class mapper cannot be found, which IMO leads to bad behaviour.

if (type == Account.class) {

return (RecordMapper<R, E>) getAccountRecordMapper();

}

return new DefaultRecordMapper(recordType, type);

Hmm, I'm not so sure what the value of such an abstract class would be, as it would not contain much implementation. The idea of this provider is to allow for *any* sort of implementation and / or routing of record mapping calls.

 

 I dont think the default mapper could not map anything right in my cases, the fallback therefor is for me more pain than gain.

maybe this thoughts are a leftover excessive spring row mapper using, but it just somehow feels bad.

Unfortunately, I'm not quite sure right now what your bad feeling is, or how it could be better...

I agree that RecordMapperProvider is a *very* generic SPI. At some point, jOOQ needs something extremely generic. How to make that work more specifically towards the use-case you have in mind is not easy to say. It's also not easy to say if your use-case (that you used to implement with Spring) is a general approach.

In any case, we're always looking for improvements, so if you have concrete ideas, let us know.

But beware that there is ultimately always the same wall to bang one's head into:

The impossibility of generally re-normalising de-normalised relationships. Once the joins have been expressed, the relationship info is *lost* and re-normalisation has to be performed manually on a per-query basis...

In jOOQ 3.6, we're going to look into supporting SQL standard MULTISETs (and hopefully emulating trivial use-cases via multiple queries). With those in place, nested tables will be possible, conceptually, and mapping should be easier, even if we're staying on a pure SQL level.

[gantners]

On a sidenote: You probably shouldn't assume in your account record mapper that you will be getting a TAccountRecord. In your particular query, that might be the case but as soon as you join tables or reduce the number of fields, you might get something else...

In particular, when you use select(Field[]).from(some table), then there is no way that jOOQ can still know the record type that you have in mind (TAccountRecord). In fact, it could be any record type, you're passing a Field[] argument to the SELECT clause, which is why you will get the much more general Record type from your fetch() method - with no type information.

OK got it!

In the RecordMapper i could subselect other predefinded Record mappers, but where do i get the table instances from? As in your examples you always directly use String column References to identify the field columns:

public <R extends Record, E> RecordMapper<R, E> provide(RecordType<R> recordType, Class<? extends E> type) {

    // UUID mappers will always try to find the ID column

    if (type == UUID.class) {

        return new RecordMapper<R, E>() {

            @Override

            public E map(R record) {

                return (E) record.getValue("ID");

            }

        }

    }

    

    // Books might be joined with their authors, create a 1:1 mapping

    if (type == Book.class) {

        return new BookMapper();

    }

    // Fall back to jOOQ's DefaultRecordMapper, which maps records onto

    // POJOs using reflection.

    return new DefaultRecordMapper(recordType, type);

}

but isnt that the opposite of jooqs targets? How do i get the referenced tables from the origin select:

TAccount a = new TAccount();

List<Account> accountList = create.select(a.fields()).from(a).fetch().into(Account.class);

like TAccount a, where i maybe also defined an alias, inside the RecordMapper? If i would create an new instance of TAccount inside the RecordMapper i could address the field columns statically but i would loose the 
alias, right? so how do i get the references for all joined tables from inside the RecordMapper where i only have access to the class type and the generic Record?

Or is the RecordMapper approach not designed for may particular use case as described? If so, the record mapper is probably only useful directly inline or for specific purpose, is that correct?

Best regards and happy holidays

Stefan 

[Lukas Eder]

Hi Stefan,

2014-12-30 15:08 GMT+01:00 gantners <stefan...@live.de>:

On a sidenote: You probably shouldn't assume in your account record mapper that you will be getting a TAccountRecord. In your particular query, that might be the case but as soon as you join tables or reduce the number of fields, you might get something else...

In particular, when you use select(Field[]).from(some table), then there is no way that jOOQ can still know the record type that you have in mind (TAccountRecord). In fact, it could be any record type, you're passing a Field[] argument to the SELECT clause, which is why you will get the much more general Record type from your fetch() method - with no type information.

OK got it!

In the RecordMapper i could subselect other predefinded Record mappers, but where do i get the table instances from? As in your examples you always directly use String column References to identify the field columns:

I guess we should design those examples more carefully... :-)

The example is really a bit random and might not fit your particular use-case.

 

public <R extends Record, E> RecordMapper<R, E> provide(RecordType<R> recordType, Class<? extends E> type) {

    // UUID mappers will always try to find the ID column

    if (type == UUID.class) {

        return new RecordMapper<R, E>() {

            @Override

            public E map(R record) {

                return (E) record.getValue("ID");

            }

        }

    }

    

    // Books might be joined with their authors, create a 1:1 mapping

    if (type == Book.class) {

        return new BookMapper();

    }

    // Fall back to jOOQ's DefaultRecordMapper, which maps records onto

    // POJOs using reflection.

    return new DefaultRecordMapper(recordType, type);

}

but isnt that the opposite of jooqs targets?

What do you mean by "target"?

 

How do i get the referenced tables from the origin select:

You do not have access to the table type(s) from within the RecordMapperProvider. In fact, there is no such notion as an "original" table types list in SQL. Think about SQL this way:

- FROM and WHERE are executed first. It creates a denormalised table from a variety of "table sources", such as actual tables on the disk, derived tables, joined tables, unnested tables, table-valued functions, etc.

- GROUP BY and HAVING are executed next. In the presence of a GROUP BY clause, your whole idea of "original" table stops making sense as only the columns referenced from the GROUP BY clause (and in some DBs the functionally dependent columns) remain in the "table source". All other columns "disappear" and may only be referenced from within aggregate functions.

- SELECT is executed next. Now, if GROUP BY already removes any notion of "original table source", SELECT makes that information even harder to reach. With SELECT you can transform every row into an entirely different type. This is also known as projection.

- Now UNION makes things even worse, because you can concatenate data from different table sources, and by now you probably get my point :-)

When I say "executed", this doesn't mean that the SQL engine actually executes the clauses in this order. But the logical order is the one that's interesting. Some more details in this article:

http://tech.pro/tutorial/1555/10-easy-steps-to-a-complete-understanding-of-sql

Unfortunately, many people have gotten used to the mind set of Hibernate and JPA, an API that doesn't go far beyond mapping the relational representation of your actual tables to the object-oriented representation of your entities. You can hardly use any SQL features if your API is so strictly tied to the notion of physical entities... But CRUD is easier, of course.

 

TAccount a = new TAccount();

List<Account> accountList = create.select(a.fields()).from(a).fetch().into(Account.class);

like TAccount a, where i maybe also defined an alias, inside the RecordMapper? If i would create an new instance of TAccount inside the RecordMapper i could address the field columns statically but i would loose the 

alias, right? 

I'm not sure what you mean by this.

 

so how do i get the references for all joined tables from inside the RecordMapper where i only have access to the class type and the generic Record?

One thing you could do is type-check all of the Field references from the RecordType instance that is passed to your RecordMapperProvider to see if they're of type TableField. In that case, those fields will contain a reference to the original Table.

That is of course far from robust, but if you know the type of queries that you're running, it might be sufficient.

 

Or is the RecordMapper approach not designed for may particular use case as described? If so, the record mapper is probably only useful directly inline or for specific purpose, is that correct?

Not sure. We might have overlooked a particular use-case and in that case, might not have designed RecordMapper(Provider) for that use case yet. You tell me :)

I'm not 100% sure what your use-case is, though. But I'm afraid that it might be trying to re-normalise flattened result sets, that have been denormalised by random joins. In that case, I don't think there will be a reliable solution.

Best regards and happy holidays

Same to you!

Cheers

Lukas

[gantners]

Hi Lukas,

jooq will not only be suitable for creating typesafe sql queries but also for code validation. in the past i randomly ran into sql exceptions caused by non existing columns, tables which have been just forgotten to change. jooq provides a good way,

to instantly see validation errors if anything on the table schema changed. that way i could improve the code to become more stable, even in the darkest corners. 

a plus will also be to reduce the amount of code necessary to query and build the entities for my model. on half will be the query which i already tested successfully and works for me very well, the other half is to put the result of a query into any
kind of pojo, no matter what type it will be, single or joined table row representations. This is the part which gives me headaches. 

i understood now that jooq is purely from the sql point of view, which does not really care about my problematic second half. 

maybe my ultimate goal gets more clear with this example, though i mean its the latter you mentioned on your last reply:

But I'm afraid that it might be trying to re-normalise flattened result sets, that have been denormalised by random joins. In that case, I don't think there will be a reliable solution.

 

lets pretend i have two tables Account and Contact, which will be joined and some values of both tables should be extracted into an object, which i guess i a very common and basic use case. 

i currently now from jooq api, there are a few ways to get those queried values, one of those is:

TAccount a = new TAccount("a,");

TContact c = new TContact("c");

List<AccountContact> list = create.select(a.NAME,c.FIRSTNAME, c.LASTNAME).from(a.leftOuterJoin(c).on(a.ID.eq(c.ACCOUNTID))).fetchInto(AccountContact.class);

the others are fetch into an array or map (afaik, yet).

The recordmapper, which is called in the provider, would look as follows:

public static RecordMapper<Record, AccountContact> getAccountRecordMapper() {

return new RecordMapper< Record, AccountContact>() {

@Override

public AccountContact map(Record record) {

AccountContact ac = new AccountContact();

ac.setName(record.getValue("NAME"));

ac.setFistname(record.getValue("FIRSTNAME"));

ac.setLastname(record.getValue("LASTNAME"));

return ac;

}

};

}

but this way i need to know the column reference string like "NAME", "FIRSTNAME","LASTNAME", which is not suitable as i would loose the validation feature for column names. it should look more like:

public static RecordMapper<Record, AccountContact> getAccountRecordMapper() {

return new RecordMapper< Record, AccountContact>() {

@Override

public AccountContact map(Record record) {

AccountContact ac = new AccountContact();

ac.setName(record.getValue(a.NAME));

ac.setFistname(record.getValue(c.FIRSTNAME));

ac.setLastname(record.getValue(c.LASTNAME));

return ac;

}

};

}

could i just create a new jooq table to reference the columns like:

public static RecordMapper<Record, AccountContact> getAccountRecordMapper() {

return new RecordMapper< Record, AccountContact>() {

@Override

public AccountContact map(Record record) {

                                TAccount a = new TAccount();

                                TContact c = new TContact();

AccountContact ac = new AccountContact();

ac.setName(record.getValue(a.NAME));

ac.setFistname(record.getValue(c.FIRSTNAME));

ac.setLastname(record.getValue(c.LASTNAME));

return ac;

}

};

}

would this work or how its done? What if my tables on the queries had aliases?

is this only possible on inline use? Like:

                final TAccount a = new TAccount("a,");

final TContact c = new TContact("c");

List<AccountContact> list = create.select(a.NAME,c.FIRSTNAME, c.LASTNAME).from(a.leftOuterJoin(c).on(a.ID.eq(c.ACCOUNTID))).fetch(new RecordMapper< Record, AccountContact>() {

@Override

public AccountContactmap(Record record) {

AccountContactac = new AccountContact();

ac.setName(record.getValue(a.NAME));

ac.setFirstname(record.getValue(c.FIRSTNAME));

ac.setLastname(record.getValue(c.LASTNAME));

return ac;

}

});

how could i externalize this inline RecordMapper?

Thanks for your efforts on this!

Stefan

[Lukas Eder]

Hi Stefan,

Thanks for your E-Mail. I guess I've misunderstood you somewhere on the way... Please find additional comments inline:

2015-01-02 14:23 GMT+01:00 gantners <stefan...@live.de>:

Hi Lukas,

jooq will not only be suitable for creating typesafe sql queries but also for code validation. in the past i randomly ran into sql exceptions caused by non existing columns, tables which have been just forgotten to change. jooq provides a good way,

to instantly see validation errors if anything on the table schema changed. that way i could improve the code to become more stable, even in the darkest corners. 

Yes, this is one of the main value propositions of jOOQ. To help you validate your darkest corners :-) (I will need to cite that at some time)

 

a plus will also be to reduce the amount of code necessary to query and build the entities for my model. on half will be the query which i already tested successfully and works for me very well, the other half is to put the result of a query into any
kind of pojo, no matter what type it will be, single or joined table row representations. This is the part which gives me headaches. 

i understood now that jooq is purely from the sql point of view, which does not really care about my problematic second half. 

maybe my ultimate goal gets more clear with this example, though i mean its the latter you mentioned on your last reply:

But I'm afraid that it might be trying to re-normalise flattened result sets, that have been denormalised by random joins. In that case, I don't think there will be a reliable solution.

As I said, I misunderstood you. What I meant by this is the fact that there is no way to formally distinguish results from inner joins / left outer joins / full outer joins / partitioned outer joins / lateral joins / derived tables / unions, etc in an automatic fashion.

But that's not what you meant, so let's focus on waht you meant.

I think we should definitely clarify the Record.getValue() behaviour in the Javadoc (https://github.com/jOOQ/jOOQ/issues/3894).

You can use Field<?> references to dereference a value from a Record, even if the value does not originate from the exact table as the field you're passing to getValue(). In other words, this will work:

ac.setName(record.getValue(a.NAME));

ac.setFistname(record.getValue(c.FIRSTNAME));

ac.setLastname(record.getValue(c.LASTNAME));

It will work no matter what you called your table aliases. The reason for this is what I explained in the previous E-Mail. There is no notion of an "original" table that produced the columns a.NAME, c.FIRSTNAME, c.LASTNAME. There is only this notion of a derived table with a row type of (NAME, FIRSTNAME, LASTNAME)

Now there are some caveats when you have ambiguous column names from joins, such as ID, for example, which is possible in SQL only in top-level selects...

I hope this helps.

Lukas