JPQL Constructor Expression - org.hibernate.hql.as

Question

My original problem was https://stackoverflow.com/questions/12172614/hql-join-without-foreign-key-reference but couldn't find any solution for this, hence moved forward with native query using JPA. createNativeQuery of entityManager returns Query object which in turn returns List<Object[]>. I don't want to deal with indexes while iterating the list because it's error prone in nature.Therefore i looked at some other solution for it and found JPQL's Constructor expressions as one of the solution.

Table structure is

Schema1 -TableA

 - NameColumn
 - PhoneColumn

Corresponding Java class is

    public class PersonSearch implements Serializable {

    public PersonSearch (String NameColumn, String PhoneColumn) {
        this.name = NameColumn;
        this.phone = PhoneColumn;
    }

    private String name;

    private String phone;

    public String getName() {
    return name;
    }

    public void setName(String name) {
    this.name = name;
    }

    public String getPhone() {
    return phone;
    }

    public void setPhone(String phone) {
    this.phone = phone;
    }
    }

Query is

 Select NEW com.xyz.PersonSearch(ms.NameColumn, ms.PhoneColumn) From Schema1.TableA ms Where ms.PhoneColumn='9134409930'

while running this query using entityManager API

entityManager.createQuery(queryString, PersonSearch.class);

getting below error.

Caused by: org.hibernate.hql.ast.QuerySyntaxException: Schema1.TableA is not mapped   [Select NEW com.xyz.PersonSearch(ms.NameColumn, ms.PhoneColumn) From Schema1.TableA ms Where ms.PHONE='9134409930']

What's wrong with my code? Any idea ?

Answer 1

according to the book "Pro EJB 3 Java Persistence API"

Constructor Expressions

A more powerful form of SELECT clause involving multiple expressions is the constructor expression, which specifies that the results of the query are to be stored using a user-specified object type. Consider the following query:

SELECT NEW example.EmployeeDetails(e.name, e.salary, e.department.name)
FROM Employee e

The result type of this query is the type example.EmployeeDetails. As the query processor iterates over the results of the query, it instantiates new instances of EmployeeDetails using the constructor that matches the expression types listed in the query. In this case the expression types are String, Double, and String, so the query engine will search for a constructor with those class types for arguments. Each row in the resulting query collection is therefore an instance of EmployeeDetails containing the employee name, salary, and department name.

The result object type must be referred to using the fully qualified name of the object. The class does not have to be mapped to the database in any way, however. Any class with a constructor compatible with the expressions listed in the SELECT clause can be used in a constructor expression.

Constructor expressions are powerful tools for constructing coarse-grained data transfer objects or view objects for use in other application tiers. Instead of manually constructing these objects, a single query can be used to gather together view objects ready for presentation on a web page.

The example code is as follows

List result = em.createQuery("SELECT NEW example.EmpMenu(e.name, e.department.name) " +
         "FROM Project p JOIN p.employees e " +
         "WHERE p.name = ?1 " +
        "ORDER BY e.name").setParameter(1, projectName).getResultList();

The EmpMenu class is a simple pojo, no annotations but has the correct constructor to match the constructor expression. The result is a List of EmpMenu objects for each row returned.

I believe the part of your SQL ".... From Schema1.TableA ms .." should refer to an entity that is mapped. So you should have an entity mapped to TableA, and then the jpql should be something more along the lines of ".... From MyTableAEntity ms ..." where MyTableAEntity has all the proper jpa annotations mapping it to DB table TableA. As the book snippet states, the target of "SELECT NEW ..." does not have to be mapped, but the entity referred to in the FROM clause does.