JPQL Constructor Expression - org.hibernate.hql.ast.QuerySyntaxException:Table is not mapped

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.

Answer 2

To achieve this, you can use the JPQL (Java Persistence Query Language) constructor expression in your JPA query. The constructor expression is used to create instances of custom result classes.

1. Create a Constructor in Custom Class:

First, create a custom class to hold the result information. In your case, this class might be named BookAuthorInfo and have a constructor that accepts the necessary values (author's name and book's title).

public class BookAuthorInfo {
private String name;
private String title;

public BookAuthorInfo(String name, String title) {
    this.name = name;
    this.title = title;
}

}

2. Write the Constructor-Based Query:

Next, use the constructor expression in your JPQL query to select the required attributes from the entities and construct instances of your custom class.

TypedQuery<BookAuthorInfo> query = entityManager.createQuery(
"SELECT NEW com.example.BookAuthorInfo(a.name, b.title) " +
"FROM Book b JOIN b.author a " +
"WHERE b.publicationYear = :year", BookAuthorInfo.class);

query.setParameter("year", desiredYear); List results = query.getResultList();

• NEW com.example.BookAuthorInfo(a.name, b.title) creates instances of the BookAuthorInfo class using the provided constructor and passing the selected attributes from the Author and Book entities.
• JOIN b.author a specifies the relationship between Book and Author entities.
• WHERE b.publicationYear = :year filters the books based on the desired publication year.

This approach allows you to fetch specific attributes from multiple entities and construct custom result objects using constructor-based JPA queries.