How to map multiple data rows to different fields

Question

I'm trying to use querydsl and projection to map the results of a query onto an object.

I have something like the following:

public record DataTransferObject(MyTable obj1, MyTable obj2) {
    public static final ConstructorExpression<DataTransferObject> PROJECTION = 
        Projections.constructor(obj1, obj2);
}

@Service
public class QueryService {
    private final JPQLQueryFactory queryFactory;

    public DataTransferObject getData(String id1, String id2) {
        return queryFactory.select(DataTransferObject.PROJECTION)
            .from(QMyTable.myTable)
            .where(QMyTable.myTable.id.eq(id1)
                .or(QMyTable.myTable.id.eq(id2))
            .fetchOne();
    }
}

But this doesn't work as I run into com.querydsl.core.NonUniqueResultException.

Using joins like in the following results in obj1 and obj2 being the same object (even though the 2 ids map to 2 unique rows):

.leftJoin(QMyTable.myTable)
    .on(QMyTable.myTable.id.eq(id1))
.leftJoin(QMyTable.myTable)
    .on(QMyTable.myTable.id.eq(id2))

I want to match DB row corresponding to String id1 to the MyTable obj1 field in the DataTransferObject object. Similarly, I want to match DB row corresponding to String id2 to the MyTable obj2 field in the DataTransferObject object.

What's a preferable/best way to accomplish this?

@bananas I ask because when I do something like .leftJoin(QMyTable.myTable) .on(QMyTable.myTable.id.eq(id1)) .leftJoin(QMyTable.myTable) .on(QMyTable.myTable.id.eq(id2)) both obj1 and obj2 end up being the same object. I'll update the post. — Azianese

黄志祥黄志祥 · Accepted Answer · 2022-06-08T07:18:00

0

votes

Try changing fetchOne() to fetchFirst() or fetchAll()

How to map multiple data rows to different fields

1 Answers