How to make a query with 3 ManyToOne components?

Question

I got a database with a table Result, it looks like this:

Table Result:

resultId, resultValue, patientId (its a Foreign key of the table Patient), scriptId(Foreign key of ScriptMW), userId(Foreign key of User)

and i forgot the table user has also a foreign key in it, from patientId so 1 user has more patients....its OneToMany annotation there all of them are integers

thats what it looks like, and in my java code i got this:

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Integer resultId;

@ManyToOne
private User user;

@ManyToOne
private ScriptMW scriptMW;

@ManyToOne
private Patient patient;

@Lob
@Column(length=65536)
private String resultValue;

So u can see its 3 times the ManyToOne annotation

Now i need to make a query to get all results of one patient, that belongs to the user, who has used the specific script...

I got the userId, the ScriptId and the patientId as path params, and that works....but now i need to get all the values...and i have no idea how to do it...look at the code below

i cant even select anything from result

@Override
public List<Result> getResults(Integer userId, Integer scriptId, Integer patientId) {

    Query q=em.createQuery("Select r From Result r");
    return q.getResultList();

}

Once i do the previous code i get this error:

Caused by: com.fasterxml.jackson.databind.JsonMappingException: failed to lazily initialize a collection of role: entities.User.patients, could not initialize proxy - no Session (through reference chain: java.util.ArrayList[0]->entities.Result["user"]->entities.User["patients"])

any ideas?

Answer 1

I found the answer, the problem was with the user

@OneToMany(cascade={CascadeType.ALL})
private Set<Patient> patients;

He was LAZY (default), but had to be EAGER

@OneToMany(fetch = FetchType.EAGER, cascade={CascadeType.ALL})
private Set<Patient> patients;

Once i added it, i used the following query and it worked all...

Query q=em.createQuery("Select r From Result r where r.user.userId=:userId and r.scriptMW.scriptId=:scriptId and r.patient.patientId=:patientId")
            .setParameter("userId", userId).setParameter("scriptId", scriptId).setParameter("patientId", patientId);

It is an answer to my question, but still i dont like it that it has to be EAGER, has anyone a better way?