1

I use Spring Boot 1.5 and spring data JPA with MySQL. I tried to run a simple counting query on a single table, but could not find a better way to map the Query results than this.:

Repository:

public interface VehicleRepository extends JpaRepository<Vehicle, String> {
    @Query("select v.sourceModule as sourceModule, count(v) as vehicleCount from Vehicle v group by v.sourceModule")
    List<Object[]> sourceModuleStats();
}

Service:

@Override
public List<SourceModuleStatDTO> getSourceModuleStats() {
    List<Object[]> objects = vehicleRepository.sourceModuleStats();

    return objects.stream()
            .map(o->SourceModuleStatDTO.from((String)o[0], (Long)o[1]))
            .collect(Collectors.toList());
}

I use org.immutables, so the DTO.:

@Value.Immutable
@JsonSerialize(as = ImmutableSourceModuleStatDTO.class)
@JsonDeserialize(as = ImmutableSourceModuleStatDTO.class)
public abstract class SourceModuleStatDTO {
    public abstract String sourceModule();
    public abstract long vehicleCount();

    public static SourceModuleStatDTO from(String sm, long c) {
        return ImmutableSourceModuleStatDTO.builder()
                .sourceModule(sm)
                .vehicleCount(c)
                .build();
    }
}

The problem here is the mapping, I need to cast the results or manually check everything. Even JdbcTemplate has better mapping capabilities, I can't believe there is no better way to do this.

I tried this too: https://stackoverflow.com/a/36329166/840315 , but you need to hard code classpaths into the Query to get it work and also I would still need to map the objects to Immutables.

Using JdbcTemplate, you can use the RowMapper (src) :

private static final class EmployeeMapper implements RowMapper<Employee> {
    @Override
    public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
        Employee employee = new Employee();
        employee.setCountry(rs.getString("country"));
        employee.setEmployeeName(rs.getString("employee"));
        return employee;
    }
}

Is there something similar for spring data JPA @Query?

szab.kel
  • 2,356
  • 5
  • 40
  • 74
  • "_simple counting query_" - Are you trying to get _only_ count or count along with sourceModule as well? – Ram Mar 03 '18 at 08:57
  • @jusermar10 Count along the `sourceModule`. I know I could use a simple `long` return value if I only wanted the count. – szab.kel Mar 04 '18 at 10:20

1 Answers1

8

How about using Projections as below?

static interface VehicleStats { 
    public String getSourceModule();
    public Long getVehicleCount();
}

And your repository method would be

@Query("select v.sourceModule as sourceModule, count(v) as vehicleCount from Vehicle v group by v.sourceModule")
List<VehicleStats> sourceModuleStats();

In your Service class, you can use the interface methods as below.

List<VehicleStats> objects = vehicleRepository.sourceModuleStats();
return objects.stream()
        .map(o->SourceModuleStatDTO.from(getSourceModule(),getVehicleCount() )
        .collect(Collectors.toList());
Ram
  • 1,743
  • 2
  • 18
  • 40