我正在使用Java和MySql构建后端API,尝试在JPA 2.1中使用@SqlResultSetMapping将一对多的本地查询结果映射到POJO类。以下是本地查询:
@NamedNativeQuery(name = "User.getAll”, query = "SELECT DISTINCT t1.ID, t1.RELIGION_ID t1.gender,t1.NAME,t1.CITY_ID , t2.question_id, t2.answer_id FROM user_table t1 inner join user_answer_table t2 on t1.ID = t2.User_ID“,resultSetMapping="userMapping")
这是我的SQL映射结果:
@SqlResultSetMapping(
name = "userMapping",
classes = {
@ConstructorResult(
targetClass = MiniUser.class,
columns = {
@ColumnResult(name = "id"),
@ColumnResult(name = "religion_id"),
@ColumnResult(name = "gender"),
@ColumnResult(name = "answers"),
@ColumnResult(name = "name"),
@ColumnResult(name = "city_id")
}
),
@ConstructorResult(
targetClass = MiniUserAnswer.class,
columns = {
@ColumnResult(name = "question_id"),
@ColumnResult(name = "answer_id")
}
)
})
这里是POJO类的实现:(我只删除了构造函数和getter/setter)
MiniUser类
public class MiniUser {
String id;
String religionId;
Gender gender;
List<MiniUserAnswer> answers;
String name;
String city_id;
}
还有MiniUserAnswer类
public class MiniUserAnswer {
String questionId;
String answerId;
}
我的目标是执行此查询并返回MiniUser
列表,在每个MiniUser中:他的“answers”列表,这是一个MiniUserAnswer
的列表。
运行此代码后,我得到了以下错误:
The column result [answers] was not found in the results of the query.
我知道原因在于查询选择语句中没有“answers”字段。
那么,我怎样才能实现这样的结果,并考虑性能?答案列表可能会有100个之多。
非常感谢您的帮助,提前致谢!