jOOQ:在单个对象中返回具有join，groupby和count的列表 [英] jOOQ: returning list with join,groupby and count in single object

问题描述

核心问题:如何正确地将查询中的信息提取到对象中?

Core question: how do you properly fetch information from a query into objects?

想法
我正在DAO中创建函数，具体取决于以下查询:

Idea
I am creating functions in my DAO, which comes down to the following query:

select A.*, count(*)
from A
left join B on B.aId = A.aId
group by A.*

我正在寻找一种创建jOOQ表达式的方法，该表达式只给我一个包含对象A(pojo)和整数的列表(或我可以循环访问的内容).

Im looking for a way to create a jOOQ expression that just gives me a list (or something I can loop over) with objects A (pojo) and Integer.

混凝土箱
在我的代码中:A =志愿者，B = VolunteerMatch，我为每个志愿者存储了几场比赛. B具有(volunteerId，志愿者MatchId)作为主要对象 钥匙.因此，此查询会同时获得来自志愿者的信息以及匹配数.显然，可以在两个单独的查询中完成此操作，但我想将它作为一个查询来完成！

Concrete case
In my code case: A = Volunteer and B = VolunteerMatch where I store several matches for each volunteer. B has (volunteerId, volunteerMatchId) as primary key. Thus this query results in both the information from the Volunteer, as well as the number of matches. Clearly this can be done in two seperate queries, but I want to do it as one!

问题
我在函数中找不到要返回的单个对象.我正在尝试获取类似List< VolunteerPojo，Integer>的东西.让我使用示例更好地解释这一点，以及为什么它们不适合我.

Problem
I cannot find a single object to return in my function. I am trying to get something like List<VolunteerPojo, Integer>. Let me explain this better using examples and why they dont fit for me.

我尝试了1

SelectHavingStep<Record> query = using(configuration())
        .select(Volunteer.VOLUNTEER.fields())
        .select(Volunteermatch.VOLUNTEERMATCH.VOLUNTEERID.count())
        .from(Volunteer.VOLUNTEER)
        .leftJoin(Volunteermatch.VOLUNTEERMATCH).on(Volunteermatch.VOLUNTEERMATCH.VOLUNTEERID.eq(Volunteer.VOLUNTEER.VOLUNTEERID))
        .groupBy(Volunteer.VOLUNTEER.fields());

Map<VolunteerPojo, List<Integer>> map = query.fetchGroups(
                r -> r.into(Volunteer.VOLUNTEER).into(VolunteerPojo.class),
                r -> r.into(Volunteermatch.VOLUNTEERMATCH.VOLUNTEERID.count()).into(Integer.class)
        );

这个问题是，我从整数创建了一个列表.但这不是我想要的，我想要一个整数(计数将始终返回一行).注意:我不希望解决方案仅创建没有列表的自己的地图"，因为我的直觉说jOOQ内有一个解决方案.我在这里学习！

The problem with this, is that I create a List from the integers. But that is not what I want, I want a single integer (the count will always return one row). Note: I don't want the solution "just create your own map without list", since my gut says there is a solution inside jOOQ. Im here to learn!

我尝试过的2

SelectHavingStep<Record> query = using(configuration())
        .select(Volunteer.VOLUNTEER.fields())
        .select(Volunteermatch.VOLUNTEERMATCH.VOLUNTEERID.count())
        .from(Volunteer.VOLUNTEER)
        .leftJoin(Volunteermatch.VOLUNTEERMATCH).on(Volunteermatch.VOLUNTEERMATCH.VOLUNTEERID.eq(Volunteer.VOLUNTEER.VOLUNTEERID))
        .groupBy(Volunteer.VOLUNTEER.fields());

Result<Record> result = query.fetch();
for (Record r : result) {
    VolunteerPojo volunteerPojo = r.into(Volunteer.VOLUNTEER).into(VolunteerPojo.class);
    Integer count = r.into(Volunteermatch.VOLUNTEERMATCH.VOLUNTEERID.count()).into(Integer.class);
}

但是，我不想在我的代码中返回结果对象.在每个我调用此函数的地方，我都在调用r.into(...).into(...).在编译期间，如果它返回整数或实数pojo，则不会产生错误.我不想这样来防止将来出现错误.但至少在我想的列表中没有给出它.

However, I do not want to return the result object in my code. On each place I call this function, I am calling the r.into(...).into(...). During compile time, this won't give an error if it returns an integer or a real pojo. I don't want this to prevent future errors. But at least it doesn't give it in a List I suppose.

理由
每种选择都可能不错，但是我觉得我在文档中错过了一些更好的东西.也许我可以改写(1)而不获取整数列表.也许我可以将Result< Record>更改为Result< VolunteerPojo，Integer>之类的内容，以指示实际返回了哪些对象.每个问题的解决方案将是不错的，因为我越来越多地使用jOOQ，这将是一个很好的学习经历！

Reasoning
Either option is probably fine, but I have the feeling there is something better that I missed in the documentation. Maybe I can adapt (1) to not get a list of integers. Maybe I can change Result<Record> into something like Result<VolunteerPojo, Integer> to indicate what objects really are returned. A solution for each problem would be nice, since I am using jOOQ more and more and this would be a good learning experience!

推荐答案

如此接近！不要使用 ResultQuery.fetchMap() 代替:

So close! Don't use ResultQuery.fetchGroups(). Use ResultQuery.fetchMap() instead:

Map<VolunteerPojo, Integer> map =
using(configuration())
    .select(VOLUNTEER.fields())
    .select(VOLUNTEERMATCH.VOLUNTEERID.count())
    .from(VOLUNTEER)
    .leftJoin(VOLUNTEERMATCH)
    .on(VOLUNTEERMATCH.VOLUNTEERID.eq(VOLUNTEER.VOLUNTEERID))
    .groupBy(VOLUNTEER.fields())
    .fetchMap(
         r -> r.into(VOLUNTEER).into(VolunteerPojo.class),
         r -> r.get(VOLUNTEERMATCH.VOLUNTEERID.count())
    );

这篇关于jOOQ:在单个对象中返回具有join，groupby和count的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！