JPA result set mapping to new object

Question

public interface ScheduleRepository extends JpaRepository<Schedule, Long> {

@Query(value = "select * from truck t, " +
           "(select  day, end_time, latitude, longitude, start_time, " +
           "truckid, is_open from schedule) s where s.truckid = t.id and day = :today ;"
           , nativeQuery = true)
List<TruckLocation> getTrucksForToday(@Param("today") int today);
...
}

I have this method that does an join on two tables and returns some data, I have an object that represents this data called TruckLocation (that does not exist in the DB). However it wont map automatically (all the attribute names match) I tried using a @SqlResultSetMapping and a Converter to remap the ResultSet back together but the former doesn't operate on a method and the latter requires there to be a database representation of the resulting object which doesn't seem right for my scenario.

Is there a clean(er) way to make this query?

At least 2 options come to mind: 1)define @SqlResultSetMapping in XML or 2) provide custom implementation for this method and use a ResultTransformer — Lesiak, Apr 02 '20 at 21:06
How would you provide custom implementation if it resides in an interface? Will JPA work properly if I implement this interface and write methods? — SwimMaster, Apr 02 '20 at 21:10
https://stackoverflow.com/questions/11880924/how-to-add-custom-method-to-spring-data-jpa — Lesiak, Apr 02 '20 at 21:18

score 1 · Accepted Answer · answered Apr 19 '20 at 19:07

define an interface with get methods matching the names of the returned attributes. for example

select x, y, x+y as z from b;

the interface would be

interface NonDBEntity{
    getX();
    getY();
    getZ();
}

and the call would be

@Query(value = "select x, y, x+y as z from b; ", nativeQuery = true)
List<NonDBEntity> findAllSomething();

JPA result set mapping to new object

1 Answers1