Java 使用本机查询从 Spring Data 返回自定义对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42893870/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Return custom object from Spring Data with Native Query
提问by Sidney de Moraes
My question is based on another post. How can I achieve the same with a native query? Native queries do not allow JPQL thus do not allow new instances either.
我的问题基于另一篇文章。如何使用本机查询实现相同的功能？本机查询不允许 JPQL 因此也不允许新实例。
My POJO.
我的POJO。
class Coordinates {
private final BigDecimal latitude
private final BigDecimal longitude
...
}
My database table contains coordinates for cities perimeter, so there are three columns: city_name, latitude, longitude. Each city contains lots (really, LOTS) of perimeter coordinates that will be used to build a shadow area in Google Maps.
我的数据库表包含城市周长的坐标，因此有三列：city_name、纬度、经度。每个城市都包含很多（实际上是很多）周边坐标，这些坐标将用于在 Google 地图中构建阴影区域。
I intend to build a simple native query on that table that should return a list of coordinates.
我打算在该表上构建一个简单的本机查询，该查询应返回坐标列表。
采纳答案by Sidney de Moraes
Found the answer on another post. Basically I used SqlResultSetMappingalong with ConstructorResult(no other way worked out) with a special attention to a comment on the accepted answer of the mentioned post: you need to add the @NamedNativeQueryannotation to the entity of the used interfaceANDprepend the entity's name with a .otherwise it won't work.
在另一个帖子上找到了答案。基本上，我使用的SqlResultSetMapping一起ConstructorResult（没有其他办法解决）有特别注意上提后的接受的答案评论：您需要将添加@NamedNativeQuery注释所使用的实体interface和与前面加上实体的名称.，否则它赢得不行。
Example:
例子：
@Entity
@Table(name = "grupo_setorial")
@SqlResultSetMapping(
name = "mapeamentoDeQuadrantes",
classes = {
@ConstructorResult(
targetClass = Coordenada.class,
columns = {
@ColumnResult(name = "latitude"),
@ColumnResult(name = "longitude")
}
)
}
)
@NamedNativeQuery(
name = "GrupoCensitario.obterPerimetroDosSetores",
query = "SELECT latitude as latitude, longitude as longitude FROM coordenadas where id_setor IN (:setores)",
resultSetMapping = "mapeamentoDeQuadrantes"
)
public class GrupoCensitario {
回答by johncena
You will have to use sql result set mappingwhich is part of JPA.
您将不得不使用sql 结果集映射，它是 JPA 的一部分。
回答by ltsallas
This is https://jira.spring.io/browse/DATAJPA-980and Hereis a project that demonstrates the issue.
这是https://jira.spring.io/browse/DATAJPA-980， 这是一个演示该问题的项目。
@Query(value = "SELECT name AS name, age AS age FROM Person", nativeQuery = true)
List<PersonSummary> findAllProjectedNativeQuery();
It is fixed in the Spring Data JPA 2.0 GA (Kay) release which comes with Hibernate 5.2.11.
它在 Hibernate 5.2.11 附带的 Spring Data JPA 2.0 GA (Kay) 版本中得到修复。
The issue is also fixed for Spring Data 1.10.12 (Ingalls) and 1.11.8 (Hopper) but will need to be run on Hibernate 5.2.11 to work.
Spring Data 1.10.12 (Ingalls) 和 1.11.8 (Hopper) 也修复了这个问题，但需要在 Hibernate 5.2.11 上运行才能工作。
回答by morecore
If you are using a recent version of spring-dataand also making use of the Repositories, I personally think that the answer from Itsallasleads to the right solution.
如果您使用的是 的最新版本spring-data并且也在使用Repositories，我个人认为来自Itsallas的答案会带来正确的解决方案。
I actually did't now about (Spring Data) Projectionsyet and needed a moment to understand what he was showing in his example.
实际上，我现在Projections还没有了解（Spring Data），需要一点时间来理解他在示例中展示的内容。
Therefore I just want to add a link to the Spring Data JPA - Reference Documentation, have a look at the Projections chapter.
因此我只想添加一个链接Spring Data JPA - Reference Documentation，看看投影一章。
Spring Data query methods usually return one or multiple instances of the aggregate root managed by the repository. However, it might sometimes be desirable to create projections based on certain attributes of those types. Spring Data allows modeling dedicated return types, to more selectively retrieve partial views of the managed aggregates.
Spring Data 查询方法通常返回存储库管理的聚合根的一个或多个实例。但是，有时可能需要根据这些类型的某些属性创建投影。Spring Data 允许对专用返回类型进行建模，以更有选择地检索托管聚合的部分视图。
回答by Reneta
The answer I found:
我找到的答案：
public interface UserEventRepository extends JpaRepository<UserEvent, Long> {
List<UserEvent> findAllByUserId(Long userId);
@Query(value = "SELECT user_id FROM user_event ue " +
"WHERE ue.user_id = :userId", nativeQuery = true)
List<Long> findUserIdByEventId(@Param("userId") Long userId);
}
That way we return List of Long - list of ids. The key here is that we are setting the nativeQuery property to true. The value itself is the query we want to be executed.
这样我们就返回了长列表 - id 列表。这里的关键是我们将 nativeQuery 属性设置为 true。值本身就是我们想要执行的查询。
I hope that helps. It seems a clear solution.
我希望这有帮助。这似乎是一个明确的解决方案。
