select join using @NamedNativeQueries on non entity class

Question

I would like to retrieve data from complex join select only for reports propose. I decided to use @NamedNativeQueries in class without @Entity annotation as follows

@NamedNativeQueries({
        @NamedNativeQuery(name = "detailInformationForInvoicePurpose",
                query = "SELECT DISTINCT b.ShipName, \n" +
                        "    b.ShipAddress, \n" +
                        "    b.ShipCity, \n" +
                        "    b.ShipRegion, \n" +
                        "    b.ShipPostalCode, \n" +
                        "    b.ShipCountry, \n" +
                        "    b.CustomerID, \n" +
                        "    c.CompanyName, \n" +
                        "    c.Address, \n" +
                        "    c.City, \n" +
                        "    c.Region, \n" +
                        "    c.PostalCode, \n" +
                        "    c.Country, \n" +
                        "    concat(d.FirstName,  ' ', d.LastName) AS Salesperson, \n" +
                        "    b.OrderID, \n" +
                        "    b.OrderDate, \n" +
                        "    b.RequiredDate, \n" +
                        "    b.ShippedDate, \n" +
                        "    a.CompanyName, \n" +
                        "    e.ProductID, \n" +
                        "    f.ProductName, \n" +
                        "    e.UnitPrice, \n" +
                        "    e.Quantity, \n" +
                        "    e.Discount,\n" +
                        "    e.UnitPrice * e.Quantity * (1 - e.Discount) AS ExtendedPrice,\n" +
                        "    b.Freight\n" +
                        "FROM Shippers a \n" +
                        "INNER JOIN Orders b ON a.ShipperID = b.ShipVia \n" +
                        "INNER JOIN Customers c ON c.CustomerID = b.CustomerID\n" +
                        "INNER JOIN Employees d ON d.EmployeeID = b.EmployeeID\n" +
                        "INNER JOIN Order_Details e ON b.OrderID = e.OrderID\n" +
                        "INNER JOIN Products f ON f.ProductID = e.ProductID\n" +
                        "ORDER BY b.ShipName",
                resultClass = JpaReport.class)
})
public class JpaReport {
    private String shipname;
    private String shipaddress;
    private String shipcity;
    private String shipregion;
    private String shippostalcode;
    private String shipcountry;
    private String customerid;
    private String companyname;
    private String address;
    private String city;
    private String region;
    private String postalcode;
    private String country;
    private String lastname;
    private String firstname;
    private Long orderid;
    private java.sql.Timestamp orderdate;
    private java.sql.Timestamp requireddate;
    private java.sql.Timestamp shippeddate;

    public JpaReport(String shipname, String shipaddress, String shipcity, String shipregion, String shippostalcode, String shipcountry, String customerid, String companyname, String address, String city, String region, String postalcode, String country, String lastname, String firstname, Long orderid, Timestamp orderdate, Timestamp requireddate, Timestamp shippeddate) {
        this.shipname = shipname;
        this.shipaddress = shipaddress;
        this.shipcity = shipcity;
        this.shipregion = shipregion;
        this.shippostalcode = shippostalcode;
        this.shipcountry = shipcountry;
        this.customerid = customerid;
        this.companyname = companyname;
        this.address = address;
        this.city = city;
        this.region = region;
        this.postalcode = postalcode;
        this.country = country;
        this.lastname = lastname;
        this.firstname = firstname;
        this.orderid = orderid;
        this.orderdate = orderdate;
        this.requireddate = requireddate;
        this.shippeddate = shippeddate;
    }

When I try to retrieve data as list from JpaReport class I have information please look at screenshot

@Repository
public class JpaReportDaoImpl {

    @PersistenceContext
    private EntityManager em;

    public List<JpaReport> detailInformationForInvoicePurpose(){
        List<JpaReport> jpaReport = em.createNamedQuery("detailInformationForInvoicePurpose", JpaReport.class).getResultList();
        return jpaReport;
    }

}

I have a question:

• What am I doing wrong?
• This is a good way to retrieve data only for the report?

I try also to extract data as follows

 public List<JpaReport> detailInformationForInvoicePurpose(){
        List<JpaReport> jpaReport = em.createNativeQuery("SELECT DISTINCT b.ShipName, \n" +
                "    b.ShipAddress, \n" +
                        "    b.ShipCity, \n" +
                        "    b.ShipRegion, \n" +
                        "    b.ShipPostalCode, \n" +
                        "    b.ShipCountry, \n" +
                        "    b.CustomerID, \n" +
                        "    c.CompanyName as customersCompasnyName, \n" +
                        "    c.Address, \n" +
                        "    c.City, \n" +
                        "    c.Region, \n" +
                        "    c.PostalCode, \n" +
                        "    c.Country, \n" +
                        "    concat(d.FirstName,  ' ', d.LastName) AS Salesperson, \n" +
                        "    b.OrderID, \n" +
                        "    b.OrderDate, \n" +
                        "    b.RequiredDate, \n" +
                        "    b.ShippedDate, \n" +
                        "    a.CompanyName as shipersCompanyName, \n" +
                        "    e.ProductID, \n" +
                        "    f.ProductName, \n" +
                        "    e.UnitPrice, \n" +
                        "    e.Quantity, \n" +
                        "    e.Discount,\n" +
                        "    e.UnitPrice * e.Quantity * (1 - e.Discount) AS ExtendedPrice,\n" +
                        "    b.Freight\n" +
                        "FROM Shippers a \n" +
                        "INNER JOIN Orders b ON a.ShipperID = b.ShipVia \n" +
                        "INNER JOIN Customers c ON c.CustomerID = b.CustomerID\n" +
                        "INNER JOIN Employees d ON d.EmployeeID = b.EmployeeID\n" +
                        "INNER JOIN Order_Details e ON b.OrderID = e.OrderID\n" +
                        "INNER JOIN Products f ON f.ProductID = e.ProductID\n" +
                        "ORDER BY b.ShipName").getResultList();
        return jpaReport;
    }

but when I want to iterate by List<JpaReport> as follows I get an error

List<JpaReport> jpaReports = jpaReportDaoImpl.detailInformationForInvoicePurpose();
        for (JpaReport jpaReport : jpaReports) {
            System.out.println(jpaReport);
        }

Caused by: java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to ###################.model.JpaReport

Answer 1

Well as it is not managed entity class, query annotation are not processed at all, resulting in missing query. Move query annotations to a managed (@Entity) class.