带有命名列的 Spring 数据 JPA @Query 映射 [英] Spring data JPA @Query mapping with named columns
问题描述
我在 MySQL 中使用 Spring Boot 1.5 和 Spring Data JPA.我试图在单个表上运行一个简单的计数查询,但找不到比这更好的方法来映射查询结果.:
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.:
存储库:
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();
}
服务:
@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());
}
我使用 org.immutables,所以 DTO.:
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();
}
}
这里的问题是映射,我需要转换结果或手动检查所有内容.即使 JdbcTemplate
具有更好的映射能力,我也不敢相信没有更好的方法可以做到这一点.
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.
我也试过这个:https://stackoverflow.com/a/36329166/840315,但你需要将类路径硬编码到查询中以使其工作,而且我仍然需要将对象映射到不可变对象.
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.
使用 JdbcTemplate,您可以使用 RowMapper
(src) :
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;
}
}
Spring 数据 JPA @Query
是否有类似的东西?
Is there something similar for spring data JPA @Query
?
推荐答案
如何使用 投影如下?
static interface VehicleStats {
public String getSourceModule();
public Long getVehicleCount();
}
你的存储库方法是
@Query("select v.sourceModule as sourceModule, count(v) as vehicleCount from Vehicle v group by v.sourceModule")
List<VehicleStats> sourceModuleStats();
在您的 Service 类中,您可以使用如下接口方法.
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());
这篇关于带有命名列的 Spring 数据 JPA @Query 映射的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!