Map Query result to dto

Question

I have these 3 independent table i.e Student, Teacher and Subject. Independent here refers that there is no relation in these tables.

I want the count of all these tables . SQL query looks like -

SELECT
  (SELECT COUNT(*) FROM Student as ST, 
  (SELECT COUNT(*) FROM Teacher as TE,
  (SELECT COUNT(*) FROM Subject as SU

Now I want to map this result into dto . The DTO looks like

public class CountDto{
        Integer student;
        Integer teacher;
        Integer subject;
        }

The repository call looks like -

@Query(value = "SELECT\r\n"
        + "  (SELECT COUNT(*) FROM Student) as ST, \r\n"
        + "  (SELECT COUNT(*) FROM Teacher) as TE,\r\n"
        + "  (SELECT COUNT(*) FROM Subject) as SU", nativeQuery = true)
public CountDto getCount();

While calling this function I get following error stating

"message": "Failed to convert from type [java.lang.Object[]] to type [com.rbl.mdm.dto.CountDto ] for value '{16, 16 , 34}'; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type [java.lang.Integer] to type [com.rbl.mdm.dto.CountDto]"

How should I convert my response to desired DTO ?

Maybe [this](https://stackoverflow.com/a/64776376/6277104) will be helpful. — SternK, May 27 '21 at 21:13

Yuriy Tsarkov · Answer 1 · 2021-05-26T07:04:19.730

You can declare CountDto as a public interface and it should work. It's called a Projection in terms of Spring. Or you can use SqlResultSetMapping or ConstructorResult along with your class.

you don't have to implement it by any entity class, just create it like an independent interface even within the repository file:

public interface StudentRepository extends CrudRepository<Student, Long> {

    @Query(value = "SELECT\r\n"
            + "  (SELECT COUNT(*) FROM Student) as ST, \r\n"
            + "  (SELECT COUNT(*) FROM Teacher) as TE,\r\n"
            + "  (SELECT COUNT(*) FROM Subject) as SU", nativeQuery = true)
    Counts getCount();
    
    public static interface Counts {
        Integer getST();        
        Integer getTE();
        Integer getSU();
    }

}

As the repo call returns data from 3 independent tables, which type of Projection shall I make. As per my understanding to the Projections, they reflects methods of a entity and here we have 3 different entities ! — Bhumika, May 26 '21 at 06:32

score -1 · Answer 2 · answered May 26 '21 at 06:30

So here is the answer to do - The DTO will look like

public CountDto{
    private Integer studentTotal;
    private Integer teacherTota;
    private Integer subjectTotal;
}

The repository call -

@Query(value = "SELECT\r\n"
        + "  (SELECT COUNT(*) FROM Student) as ST, \r\n"
        + "  (SELECT COUNT(*) FROM Teacher) as TE,\r\n"
        + "  (SELECT COUNT(*) FROM Subject) as SU", nativeQuery = true)
public Map<String,Integer> getCount();

Finally the serviceImpl--

public CountDto getCount{
        CountDto CountValue = new CountDto();
        Map<String,Integer> map =  repository.getCount();
        for (Map.Entry<String,Integer> entry : map.entrySet())   {
            if(entry.getKey().equals("ST"))
                CountValue.setStudentTotal( entry.getValue());
            if(entry.getKey().equals("TE"))
                CountValue.setTeacherTotal( entry.getValue());
            if(entry.getKey().equals("SU"))
                CountValue.setSubjectTotal( entry.getValue());
        }
        return CountValue ;
}

But the solution seems quite complex to me. Any simpler approach required.

score -1 · Answer 3 · answered May 26 '21 at 13:52

-1

In pure Hibernate/JPA usage this is a simple dynamic-instantiation query (what JPA calls a "constructor result"):

select new CountDTO( 
   (SELECT COUNT(*) FROM Student) as ST,
   ...
)

No idea here about Spring, though a word of warning.. in trying to be useful, it often "gets in the way". Not sure that is the case here... Have you tried straight Hibernate/JPA?

answered May 26 '21 at 13:52

Steve Ebersole

9,339
2
48
46

If you are simply wanting to use the Spring APIs, you should remove the 'hibernate` tag. If just using Spring there is nothing Hibernate related here. – Steve Ebersole May 26 '21 at 13:53
This approach only works for non-native (JPQL) queries while OP uses native. – Nikolai Shevchenko May 26 '21 at 14:47
No idea what `@Query` is. Its a Spring annotation apparently :shrug: For native queries, even easier... `@NamedNativeQuery` with a result mapping using a `@ConstructorResult` result – Steve Ebersole May 26 '21 at 17:01
And if the user is wanting Spring specific features only, then `hibernate` should be removed as a tag – Steve Ebersole May 26 '21 at 17:04

Map Query result to dto

3 Answers3