@SqlResultSetMapping columns : entities with sub-entities

Question

I have Test entity :

public class Test {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;
@Column(name = "duration", nullable = false)
private int duration;
@Column(name = "test_name", nullable = false, unique = true)
private String testName;
@Column(name = "archived", nullable = false)
private boolean archived;
@OneToMany(mappedBy = "test", fetch = FetchType.EAGER)
private Set<Question> questions;
@ManyToMany(mappedBy = "tests")
private Set<User> users;

Question Entity:

public class Question {
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.AUTO)
private long id;
@Column(name = "is_multichoice", nullable = false)
private boolean isMultichoice;
@Column(name = "is_open", nullable = false)
private boolean isOpen;
@Column(name = "picture")
private String picture;
@Column(name = "question")
private String question;
@ManyToOne
@JoinColumn(name = "test_id", nullable = false)
private Test test;
@Column(name = "archived", nullable = false)
private boolean isArchived;
@OneToMany(mappedBy = "question", fetch = FetchType.EAGER)
private Set<Answer> answers;

This Test entity has Set of questions, in such way Question Entity has Set of answers.

I wrote SQL query to get test (The reason why it is not HQL you could find by link Hibernate HQL : no entity found for query) :

@NamedNativeQuery(name = "getCurrentTestById",
            query = "SELECT t.id as tId, t.test_name, t.duration, q.id as qId, " +
                    "q.question as question, q.is_multichoice as is_multichoice, " +
                    "q.is_open as is_open, a.id as aId, a.answer_text as answer_text  FROM result r " +
                    "JOIN test t ON r.test_id = t.id " +
                    "JOIN user u ON r.user_id = u.id " +
                    "JOIN question q ON t.id = q.test_id JOIN answer a ON q.id = a.question_id " +
                    "WHERE t.id = :testId AND u.id = :userId AND r.permission = :permissionId " +
                    "AND q.archived = false AND a.archived = false")

Now i need to map it to my entity Test by using @SqlResultSetMapping annotation:

@SqlResultSetMappings({
    @SqlResultSetMapping(name="toTest",
    entities = {
            @EntityResult(entityClass = com.bionic.entities.Test.class, fields = {
                    @FieldResult(name = "id", column = "tId"),
                    @FieldResult(name = "test_name", column = "test_name"),
                    @FieldResult(name = "duration", column = "duration"),
                    @FieldResult(name = "questions.question", column = "question"),
                    @FieldResult(name = "questions.id", column = "qId"),
                    @FieldResult(name = "questions.isMultichoice", column = "is_multichoice"),
                    @FieldResult(name = "questions.isOpen", column = "is_open"),
                    @FieldResult(name = "questions.answers.id", column = "aId"),
                    @FieldResult(name = "questions.answers.answer_text", column = "answer_text"),
            })
    })
})

I am getting exception :

Caused by: org.hibernate.MappingException: dotted notation reference neither a component nor a many/one to one

Add

did you find solution? – Wolters

@meetjoeblack no, i just get all results and then iterate over them to filter. I know that this is not good solution, however no ideas – Add

Answer 1

This is why frameworks are generally bad news. Instead of using hibernate, you should follow the interface segregation principle. Your application should not know or care about How to select the data you need and what the table names are etc. Simply create a stored procedure that takes this responsibility and call it as opposed to having all the junk code in your app. Then if you want an easy way to map just have your stored proc return json by calling For JSON at the end. Mapping object fields to the JSON object becomes a piece of cake. You will find that with frameworks you spend more time troubleshooting the framework than actually programming.

Whiskey

You will find that with frameworks you spend more time troubleshooting the framework than actually programming - that's what has happened to me these days – Devan