i wrote a DAO-Abstraction that all my DAOs extend from. This DAO-Abstraction provides typical remote-pagination with a common query-param model.
It fetches joined-data from multiple tables. This joined-data is then given to the concrete DAO in form of a List<Record> records parameter, and now the developer must convert those Record instances to the DTO instances (where a DTO-instance may need to be constructed from multiple Record-instances). Here is an example:
public class ProductViewDAO extends AbstractViewDAO<ProductRecord, ProductDTO, Long> {
...
@Override
protected TableOnConditionStep<Record> getViewJoins() {
return Product.PRODUCT
.leftJoin(ProductLang.PRODUCT_LANG)
.on(ProductLang.PRODUCT_LANG.PRODUCTID
.eq(Product.PRODUCT.PRODUCTID));
}
@Override
protected List<ProductDTO> recordsToView(List<Record> records) {
List<ProductDTO> products = new ArrayList<>();
Map<Long, ProductDTO> productMap = new HashMap<>();
for (Record record : records) {
Long productId = record.get(Product.PRODUCT.PRODUCTID);
ProductDTO productDTO = productMap.get(productId);
if (productDTO == null) {
ProductRecord productRecord = record.into(new ProductRecord());
productDTO = productRecord.into(new ProductDTO());
List<ProductLangDTO> xLangs = new ArrayList<>();
productDTO.setLangs(xLangs);
products.add(productDTO);
productMap.put(productId, productDTO);
}
ProductLangRecord xLangRecord = record.into(new ProductLangRecord());
ProductLangDTO xLang = xLangRecord.into(new ProductLangDTO());
productDTO.getLangs().add(xLang);
if (xLang.getLangId().equals(requestContext().getLangId())) {
productDTO.setLang(xLang);
}
}
return products;
}
...
}
...
public abstract class AbstractViewDAO<R extends UpdatableRecord<R>, P extends AbstractDTO, T> extends AbstractBaseDAO<R, T> {
...
public List<P> query(final QueryParameters queryParameters) throws DataAccessException {
...
var result = getViewQuery().where(equal(pk, ids)).fetch();
return recordsToView(result);
}
}
how can this be done easier?
Does Java or jOOQ provide Helper-Classes that can help automate such a mapping of Record to DTO ?
This can be solved in the most convinient way, by using MULTISET (as suggested by @LukasEder).
But to use MULTISET here, we need to change/refactor the initial concept or remote-paginated filtering with given query-params.
I found two way to do this...
TableOnConditionStep viewJoins = Product.PRODUCT
.leftJoin(ProductLang.PRODUCT_LANG)
.on(ProductLang.PRODUCT_LANG.PRODUCTID
.eq(Product.PRODUCT.PRODUCTID));
List<Long> productIds = dsl
.select(Product.PRODUCT.PRODUCTID)
.from(viewJoins)
.where(DSL.and(getFilters(queryParameters)))
.groupBy(Product.PRODUCT.PRODUCTID)
.orderBy(getSorters(queryParameters))
.offset(queryParameters.getPage())
.limit(queryParameters.getPageSize())
.fetch(Product.PRODUCT.PRODUCTID);
this first query will do all the filtering and sorting as given by the frontend, and will also remote-paginate. It will only select the grouped ids of the main-table (here productIds) of the paginate-window. This will be a very small number of productIds, only as large as the paginate-window (5, 25, 50)
MULTISET:// use multiset to let the database and jooq do all the work of joining the tables and mapping to dto.
List<ProductDTO> products = dsl.select(
asterisk(),
multiset(
selectDistinct(asterisk())
.from(ProductLang.PRODUCT_LANG)
.where(ProductLang.PRODUCT_LANG.PRODUCTID.eq(Product.PRODUCT.PRODUCTID))
).as("langs")
).from(Product.PRODUCT)
.where(Product.PRODUCT.PRODUCTID.in(productIds))
.fetchInto(ProductDTO.class);
// append and change some data that is still missing after our fetch.
for (ProductDTO product : products) {
for (ProductLangDTO productLang : product.getLangs()) {
if (productLang.getLangId().equals(request.getLangId())) {
product.setLang(productLang);
}
}
}
return products;
By splitting this taks into two parts, we also have a helpful function that we can always use to get a complete resolved model for a small number of productIds. This will be also helpful when doing Streaming, because when we will Stream on the database, we also will stream only a small number of items (250, 500) per block, and can reuse this function.
the biggest win here, is (in my opinion) that the database will at no point return a Kartesisches Produkt for the Table-Relationships, when returning the results.
A very small loss is, that we need to send two separate queries to the database
At the same time, this is a win, because it allows us to reuse this logic for other purposes (streaming).
Instead of using two separate queries (like shown in the above example), everything can also be done in one combined query:
In this case it is not necessary to fetch intermediate Ids, but the Models will be fetched directly (please note: the queryJooqMapper is a helper class that contains the Filters/Sort/Paginate Settings, but you can just as well pass them directly).
List<ProductDTO> products = dsl.select(
asterisk(),
multiset(
selectDistinct(
asterisk(),
jsonObject(asJsonEntries(Lang.LANG.fields())).as("lang")
)
.from(ProductLang.PRODUCT_LANG)
.join(Lang.LANG).on(Lang.LANG.LANGID.eq(ProductLang.PRODUCT_LANG.LANGID))
.where(ProductLang.PRODUCT_LANG.PRODUCTID.eq(Product.PRODUCT.PRODUCTID))
).as("langs"))
.from(Product.PRODUCT
.leftJoin(ProductLang.PRODUCT_LANG)
.on(ProductLang.PRODUCT_LANG.PRODUCTID
.eq(Product.PRODUCT.PRODUCTID))
.leftJoin(Lang.LANG)
.on(Lang.LANG.LANGID
.eq(ProductLang.PRODUCT_LANG.LANGID)))
.where(queryJooqMapper.getFilters())
.and(Product.PRODUCT.CLIENTID.eq(request().getClientId()))
.groupBy(Product.PRODUCT.PRODUCTID)
.orderBy(queryJooqMapper.getSorter())
.offset(queryJooqMapper.getOffset())
.limit(queryJooqMapper.getLimit());
The important part here is, that we need to construct the Joins on all the Tables that we need to Sort/Filter (Deep Joins). The database (here: Mariadb) will first resolve those Sort/Filters on the Joins, afterwards the result is Grouped on the ID of the Main-Table (here: Product). That way we will flatten the "kartesisches Produkt" after! we have filtered/sorted, so we are fulfilling the Limit/Offset criteria to only return the specific numbers of rows of the main-table (but they are filtered/sorted by the joined tables). And finally (with help of MULTISET) we will resolve all the Deep Data from the Relevant Tables (this mostly will be the same tables as the ones we have joined for filtering/sorting), and this Deep Data is then mapped by Jooq automatically to the List result.