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 2014-12-18 11:35 GMT+01:00 gantners <[email protected]>: > > 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 > > > > -- > 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.
