How to map ONE-TO-MANY native query result into a POJO class using @SqlResultSetMapping

Question

Im working in a backend API using Java and MySql, and I'm trying to use @SqlResultSetMapping in JPA 2.1 for mapping a ONE-TO-MANY native query result into a POJO class, this is the native query:

@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")

And, here is my result SQL mapping:

@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")

                                    }
                              )

            })

And, here is the implementation of the POJO classes: (I just removed the constructor and the getters/setter)

MiniUser class

public class MiniUser {

    String id;
    String religionId;
    Gender gender;
    List<MiniUserAnswer> answers;
    String name;
    String city_id;
}

and the MiniUserAnswer class

public class MiniUserAnswer {

    String questionId;
    String answerId;
}

My goal is to execute this Query and return a list of MiniUser, and in each MiniUser: a list of his “answers", which is a list of MiniUserAnswer.

after running this code, I got this error:

The column result [answers] was not found in the results of the query.

I know why, it's because there is no “answers" field in the query select statement.
So, how can I accomplish something like this, considering the performance? This answers list may reach 100.

I really appreciate your help, Thanks in advance!

Eidolon

any answer plz? is what I'm thinking to do is doable? – Eidolon

Answer 1

The query "SELECT DISTINCT t1.ID, t1.RELIGION_ID t1.gender, t1.NAME, t1.CITY_ID, t2.question_id, t2.answer_id" does not return a parameter called answers. To obtain the result you are looking for I would use:

Option 1 (Criteria Builder)

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<UserTableEntity> cq = cb.createQuery(UserTableEntity.class);
Root<UserTableEntity> rootUserTable = cq.from(UserTableEntity.class);
Join<UserTableEntity,UserAnswerTableEntity> joinAnswerTable = rootUserTable.join(rootUserTable_.id) // if the relationship is defined as lazy, use "fetch" instead of "join"
//cq.where() NO WHERE CLAUSE
cq.select(rootUserTable)

entityManager.createQuery(cq).getResultList();

Option 2 (Named query, not native)

@NamedQuery(name = "User.getAll”, query = "SELECT t1 FROM UserTableEntityt1 join fetch t1.answers)

Option 3 (Entity subgraph, new in JPA 2.1)

In User Entity class:

@NamedEntityGraphs({
    @NamedEntityGraph(name = "graph.User.Answers", attributeNodes = @NamedAttributeNode("answers"))
})

In DAO set hints in the entity manager:

EntityGraph graph = this.em.getEntityGraph("graph.User.Answers");

Map hints = new HashMap();
hints.put("javax.persistence.fetchgraph", graph);

Shammer