Home > database >  How to map multiple data rows to different fields
How to map multiple data rows to different fields

Time:06-09

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?

CodePudding user response:

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

CodePudding user response:

Looks like I preemptively defining the objects worked. It's working with something like:

@Service
public class QueryService {
    private static final QMyTable OBJ1 = new QMyTable("obj1");
    private static final QMyTable OBJ2 = new QMyTable("obj2");

    public static final ConstructorExpression<DataTransferObject> PROJECTION = Projections.constructor(
        MyTable.projection(OBJ1),
        MyTable.projection(OBJ2)
    );

    private final JPQLQueryFactory queryFactory;

    public DataTransferObject getData(String id1, String id2) {
        return queryFactory.select(PROJECTION)
            .from(QMyTable.myTable)
            .leftJoin(OBJ1)
                    .on(OBJ1.id.eq(id1))
            .leftJoin(OBJ2)
                    .on(OBJ2.id.eq(id2))
            .fetchOne();
    }
}

Left some stuff out, but that's the gist.

  • Related