Spring Data JPA 将原生查询结果映射到 Non-Entity POJO
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29082749/
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
Spring Data JPA map the native query result to Non-Entity POJO
提问by alexanoid
I have a Spring Data repository method with a native query
我有一个带有本机查询的 Spring Data 存储库方法
@Query(value = "SELECT g.*, gm.* FROM group g LEFT JOIN group_members gm ON g.group_id = gm.group_id and gm.user_id = :userId WHERE g.group_id = :groupId", nativeQuery = true)
GroupDetails getGroupDetails(@Param("userId") Integer userId, @Param("groupId") Integer groupId);
and I'd like to map the result to Non-Entity POJO GroupDetails.
我想将结果映射到 Non-Entity POJO GroupDetails。
Is it possible and if so, could you please provide an example ?
有没有可能，如果有，请举例说明？
采纳答案by Daimon
Assuming GroupDetails as in orid's answer have you tried JPA 2.1 @ConstructorResult?
假设 GroupDetails 在 orid 的回答中，您是否尝试过 JPA 2.1 @ConstructorResult？
@SqlResultSetMapping(
name="groupDetailsMapping",
classes={
@ConstructorResult(
targetClass=GroupDetails.class,
columns={
@ColumnResult(name="GROUP_ID"),
@ColumnResult(name="USER_ID")
}
)
}
)
@NamedNativeQuery(name="getGroupDetails", query="SELECT g.*, gm.* FROM group g LEFT JOIN group_members gm ON g.group_id = gm.group_id and gm.user_id = :userId WHERE g.group_id = :groupId", resultSetMapping="groupDetailsMapping")
and use following in repository interface:
并在存储库界面中使用以下内容：
GroupDetails getGroupDetails(@Param("userId") Integer userId, @Param("groupId") Integer groupId);
According to Spring Data JPA documentation, spring will first try to find named query matching your method name - so by using @NamedNativeQuery, @SqlResultSetMappingand @ConstructorResultyou should be able to achieve that behaviour
根据春天JPA的数据文件，春季将首先尝试找到一个名为查询匹配你的方法名-因此使用@NamedNativeQuery，@SqlResultSetMapping并且@ConstructorResult你应该能够实现这一行为
回答by Micha? Stochmal
I think the easiest way to do that is to use so called projection. It can map query results to interfaces. Using SqlResultSetMappingis inconvienient and makes your code ugly :).
我认为最简单的方法是使用所谓的投影。它可以将查询结果映射到接口。使用SqlResultSetMapping很不方便，并使您的代码变得丑陋:)。
An example right from spring data JPA source code:
Spring Data JPA 源代码中的一个示例：
public interface UserRepository extends JpaRepository<User, Integer> {
@Query(value = "SELECT firstname, lastname FROM SD_User WHERE id = ?1", nativeQuery = true)
NameOnly findByNativeQuery(Integer id);
public static interface NameOnly {
String getFirstname();
String getLastname();
}
}
You can also use this method to get a list of projections.
您还可以使用此方法获取投影列表。
Check out this spring data JPA docs entry for more info about projections.
查看此 spring 数据 JPA 文档条目，了解有关投影的更多信息。
Note 1:
注 1：
Remember to have your Userentity defined as normal - the fields from projected interface must match fields in this entity. Otherwise field mapping might be broken (getFirstname()might return value of last name et cetera).
请记住将您的User实体定义为正常 - 来自投影界面的字段必须与该实体中的字段匹配。否则字段映射可能会被破坏（getFirstname()可能会返回姓氏等的值）。
Note 2:
笔记2：
If you use SELECT table.column ...notation always define aliases matching names from entity. For example this code won't work properly (projection will return nulls for each getter):
如果您使用SELECT table.column ...表示法，请始终定义与实体名称匹配的别名。例如，此代码将无法正常工作（投影将为每个 getter 返回空值）：
@Query(value = "SELECT user.firstname, user.lastname FROM SD_User user WHERE id = ?1", nativeQuery = true)
NameOnly findByNativeQuery(Integer id);
But this works fine:
但这工作正常：
@Query(value = "SELECT user.firstname AS firstname, user.lastname AS lastname FROM SD_User user WHERE id = ?1", nativeQuery = true)
NameOnly findByNativeQuery(Integer id);
In case of more complex queries I'd rather use JdbcTemplatewith custom repository instead.
如果有更复杂的查询，我宁愿使用JdbcTemplate自定义存储库。
回答by Ashish
I think Michal's approach is better. But, there is one more way to get the result out of the native query.
我认为 Michal 的方法更好。但是，还有另一种方法可以从本机查询中获取结果。
@Query(value = "SELECT g.*, gm.* FROM group g LEFT JOIN group_members gm ON g.group_id = gm.group_id and gm.user_id = :userId WHERE g.group_id = :groupId", nativeQuery = true)
String[][] getGroupDetails(@Param("userId") Integer userId, @Param("groupId") Integer groupId);
Now, you can convert this 2D string array into your desired entity.
现在，您可以将此二维字符串数组转换为所需的实体。
回答by Waqas Memon
You can write your native or non-native query the way you want, and you can wrap JPQL query results with instances of custom result classes. Create a DTO with the same names of columns returned in query and create an all argument constructor with same sequence and names as returned by the query. Then use following way to query the database.
您可以按照自己的方式编写本机或非本机查询，并且可以使用自定义结果类的实例包装 JPQL 查询结果。创建一个与查询中返回的列名称相同的 DTO，并创建一个具有与查询返回的相同序列和名称的全参数构造函数。然后使用以下方式查询数据库。
@Query("SELECT NEW example.CountryAndCapital(c.name, c.capital.name) FROM Country AS c")
Create DTO:
创建 DTO：
package example;
public class CountryAndCapital {
public String countryName;
public String capitalName;
public CountryAndCapital(String countryName, String capitalName) {
this.countryName = countryName;
this.capitalName = capitalName;
}
}
回答by Chandan Gawri
You can do something like
你可以做类似的事情
@NamedQuery(name="IssueDescriptor.findByIssueDescriptorId" ,
query=" select new com.test.live.dto.IssuesDto (idc.id, dep.department, iss.issueName,
cat.issueCategory, idc.issueDescriptor, idc.description)
from Department dep
inner join dep.issues iss
inner join iss.category cat
inner join cat.issueDescriptor idc
where idc.id in(?1)")
And there must be Constructor like
并且必须有类似的构造函数
public IssuesDto(long id, String department, String issueName, String issueCategory, String issueDescriptor,
String description) {
super();
this.id = id;
this.department = department;
this.issueName = issueName;
this.issueCategory = issueCategory;
this.issueDescriptor = issueDescriptor;
this.description = description;
}
回答by jiangke
In my computer, I get this code works.It's a little different from Daimon's answer.
在我的电脑上，我得到了这段代码。这与 Daimon 的回答有点不同。
@SqlResultSetMapping(
name="groupDetailsMapping",
classes={
@ConstructorResult(
targetClass=GroupDetails.class,
columns={
@ColumnResult(name="GROUP_ID",type=Integer.class),
@ColumnResult(name="USER_ID",type=Integer.class)
}
)
}
)
@NamedNativeQuery(name="User.getGroupDetails", query="SELECT g.*, gm.* FROM group g LEFT JOIN group_members gm ON g.group_id = gm.group_id and gm.user_id = :userId WHERE g.group_id = :groupId", resultSetMapping="groupDetailsMapping")