How to get @Query( nativeQuery=true) result into List?

Question

Hello I have a query and I want the result into list of objects, not entity. But the result is actualy a object which I should transfer to my object. Is there a way to map it directly to my custom object?

Answer 1

Maybe this will help you :

public interface ObjRepository extends JpaRepository<MyObject, Long> {

        @Query(value = "FROM MyObject WHERE objname = ?1")
        public List<MyObject> findByName(String name); 
    }

Answer 2

Approach-1: using List of object array.

When using native query, we get list of Object array i.e each row in list is array. Elements in array represent column values. In repo interface:

@Query(value = "select col1, col2, col3 from table1 where col1 = :key", nativeQuery = true)
List<Object[]> findByKey(@Param("key") String key);

In caller

List<Object[]> objectList = new ArrayList<Object[]>();
objectList = repo.findByKey(key);
    List<CustomObject> customObjectList = new ArrayList<>();
    for (Object[] tuple : objectList) {
        String col1 = (String) tuple[0];
        String col2 = (String) tuple[1];
        String col3 = (String) tuple[2];

        CustomObject obj = new CustomObject();
        obj.setCol1(col1);
        obj.setCol2(col2);
        obj.setCol3(col3);
        customObjectList.add(obj);
    }
    return customObjectList;

Approach-2: using custom dto that represents columns in each row.

Refer https://stackoverflow.com/a/42905382/1358551

Answer 3

        final List<MyCustomDTO> statuses = myRepository
            .findStatuses(marketId, campaignId, stationIds).stream()
            .map(o -> new MyCustomDTO(((BigInteger) o[0]), (Boolean) o[1], (Timestamp) o[2]))
            .collect(toList());



public class StationStatusDTO {

    private long id;
    private boolean isSomething;
    private LocalDateTime date;

    public MyCustomDTO(BigInteger id, Boolean isSomething, Timestamp date) {
        this(id.longValue(),
            isSomething,
            (date == null) ? null : LocalDateTime
                .ofInstant(Instant.ofEpochMilli(date.getTime()),
                    TimeZone.getDefault().toZoneId()));
    }