Java Persistence Criteria fetch collection using multiselect

Question

I have a class consists of id and a list of vitals with other attributes

public class Patient
{
    private Integer Id;
    private String FirstName;
    private String LastName;
    private List<Vital> Vitals;

    public Patient(Integer id, List<Vital> vitals)
    {
        this.Id = id;
        this.setVitals(vitals);
    }

    main()
    {
        Session session = Hibernate.openSession();
        CriteriaBuilder cb = session.getCriteriaBuilder();
        CriteriaQuery<Patient> cq = cb.createQuery(Patient.class);
        Root<Patient> root = cq.from(Patient.class);
        cq.multiselect(root.get("Id"), root.get("Vitals"));
        List<Patient> patients = session.createQuery(cq).getResultList();

        session.close();
    }

}

But the mentioned code produces an exception of undefined proper constructor

Exception in thread "main" java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: Unable to locate appropriate constructor on class [com.bk.Patient]. Expected arguments are: int, java.util.Collection [select new com.bk.Patient(generatedAlias0.Id, generatedAlias0.Vitals) from com.bk.Patient as generatedAlias0] at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:138)

If I change my constructor to

    public PatientEncounter(Integer id, java.util.Collection vitals)
    {
        this.Id = id;
//      this.setVitals(vitals);
    }

The exception converts to

Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'as'.

Following these links Question1 and Question2

    CriteriaQuery<Tuple> cq = cb.createTupleQuery();
        
    Root<Patient> root = cq.from(Patient.class);
        
    Path<String> namePath = root.get("Vitals");
    Path<Integer> idPath = root.get("Id");
        
    cq.multiselect(idPath , namePath);
        
    session.createQuery(cq).getResultList().stream();

Also produces the same exception

Exception in thread "main" javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'as'.

What am I doing wrong and how can I fix it?

P.S: If I fetch only attributes, the code works fine. Only fetching collection results in exceptions

Is there any need to use criteria? is there any dynamic query issues? if not you can use join fetch attributes — Mazhar Ibna Zahur, Aug 19 '22 at 16:41

score 0 · Answer 1 · answered Aug 22 '22 at 08:37

It's not possible to select a collection. You can only select elements of it. What you probably want there is to create a fetch for the collection i.e.

    Session session = Hibernate.openSession();
    CriteriaBuilder cb = session.getCriteriaBuilder();
    CriteriaQuery<Patient> cq = cb.createQuery(Patient.class);
    Root<Patient> root = cq.from(Patient.class);
    root.fetch("Vitals");
    List<Patient> patients = session.createQuery(cq).getResultList();

Java Persistence Criteria fetch collection using multiselect

1 Answers1