CODEWITHSUNDEEP
javasqlhibernatejpa
dummy
dummy

Reputation: 67

How do we map complex NativeQuery results to a DTO objects using java

Query query = em.createNativeQuery(STUDENT_QUERY_WITH_PARAMS);
        query.setParameter("studentId", stduentId);
        query.setParameter("startDate", valueOf(startDate), TemporalType.DATE);
        query.setParameter("endDate", valueOf(endDate), TemporalType.DATE);
        List<Object[]> resultList = query.getResultList();

        List<StudentDepartmentCatDTO> result = new ArrayList<>(resultList.size());
        for (Object[] row : resultList) {
            result.add(new StudentDepartmentCatDTO((String)row[0], (String)row[1], (String)row[2], null,(UUID)row[4], (Integer)row[5], (Integer)row[6], (Integer)row[7], (Integer)row[8], null, null, null, null ));
        }

`I have SQL query to pull the certain data from various tables by joining multiple tables(in my case 13 tables) and I am able to get all the results back successfully but when I try to map these results back to DTO I am getting cast exceptions. How do I overcome these exceptions?

B cannot be cast to class java.util.UUID and class java.math.BigDecimal cannot be cast to class java.lang.Integer

Since All the results are coming from various tables, I didnt find a way to do this using SqlResultSetMapping as none of the managed entities can be directly mapped to the results. Is there a better way to do this? and I how to fix these cast exceptions? Can I create the unmanaged entity to map all the results to it? How do I extract the sql results to my DTO.

Upvotes: 0

Views: 4902

Answers (1)

Thorben Janssen
Thorben Janssen

Reputation: 3275

You can use an @SqlResultSetMapping to convert each record in your result set to a DTO object. But that would require an additional constructor so that you don't need to set any parameters to null.

Here is an example of such a mapping. As you can see, you need to reference the elements in your result set by their name, but you didn't share your query. So, I can't adapt the example to your query.

@SqlResultSetMapping(
        name = "BookValueMapping",
        classes = @ConstructorResult(
                targetClass = BookValue.class,
                columns = {
                    @ColumnResult(name = "id", type = Long.class),
                    @ColumnResult(name = "title"),
                    @ColumnResult(name = "version", type = Long.class),
                    @ColumnResult(name = "authorName")}))

I use this mapping with a query that selects the id, title, version and authorName column from the database. The @ConstructorResult annotation describes a constructor call of the BookValue class. The @ColumnResult annotations define the constructor parameters and their order. You need to make sure that the instantiated class provides a constructor that matches these parameters.

@SqlResultSetMapping is a powerful and flexible feature in JPA. I explain it in great details here: https://thoughts-on-java.org/result-set-mapping-constructor-result-mappings/

OK, let's now take a look at the class cast issue ...

The cast fails because the two objects are of the wrong class. row[4] seems to be a byte[] and one of row[6/7/8] (or all of them) seems to be a BigInteger.

This code snippet should help you to convert the byte[] to a UUID:

ByteBuffer bb = ByteBuffer.wrap(bytes);
long high = bb.getLong();
long low = bb.getLong();
UUID uuid = new UUID(high, low);

The conversion of a BigDecimal to an Integer is much easier. You just need to call the intValueExact() method on your BigDecimal, e.g. row[6].intValueExact();.

Upvotes: 9

Related Questions