JPA entity without underlying table

Question

I want to create a class that can be mapped to a result extracted from the database using JPA native query. Is there a way to map an entity without an underlying table to the result? I referred to this link which allows it for hibernate. Can this be done using JPA instead?

This is my class for which I want the result to be mapped.

import java.math.BigDecimal;
import javax.persistence.Entity;
@Entity
public class OpUsage {  
    String username;    
    BigDecimal number_of_clicks;    
    String accordion;
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public BigDecimal getNumber_of_clicks() {
        return number_of_clicks;
    }
    public void setNumber_of_clicks(BigDecimal number_of_clicks) {
        this.number_of_clicks = number_of_clicks;
    }

    public String getAccordion() {
        return accordion;
    }

    public void setAccordion(String accordion) {
        this.accordion = accordion;
    }
}

Thank you. I did not mention that this object is to be used in Jasper reports. So i have to use the same name as used in the reports. — Nikhil, Mar 09 '15 at 15:19

score 17 · Accepted Answer · answered Mar 09 '15 at 15:30

JPA 2.1 specification defines the means to return the result from a native query to a non entity class

You should checkout the heading 3.10.16.2 Returning Unmanaged Instances especially the

3.10.16.2.2 Constructor Results

The mapping to constructors is specified using the ConstructorResult annotation element of the SqlResultSetMapping annotation. The targetClass element of the ConstructorResult annotation specifies the class whose constructor corresponds to the specified columns. All columns corresponding to arguments of the intended constructor must be specified using the columns element of the ConstructorResult annotation in the same order as that of the argument list of the constructor. Any entities returned as constructor results will be in either the new or the detached state, depending on whether a primary key is retrieved for the constructed object.

example

Query q = em.createNativeQuery(
        "SELECT c.id, c.name, COUNT(o) as orderCount, AVG(o.price) AS
        avgOrder" +
        "FROM Customer c, Orders o " +
                "WHERE o.cid = c.id " +
                "GROUP BY c.id, c.name",
        "CustomerDetailsResult");

@SqlResultSetMapping(name = "CustomerDetailsResult",
        classes = {
                @ConstructorResult(targetClass = com.acme.CustomerDetails.class,
                        columns = {
                                @ColumnResult(name = "id"),
                                @ColumnResult(name = "name"),
                                @ColumnResult(name = "orderCount"),
                                @ColumnResult(name = "avgOrder", type = Double.class)})
        })

This solution worked like a charm. Only have to make sure to add the SqlResultSetMapping in a class that is annotated with @Entity and not in the non-entity class or Spring will not recognize the mapping. — Nikhil, Mar 10 '15 at 18:43
Can you please provide the detailed example? This does give me what I am looking for but what I am unable to understand is, where do we put the above code ? in repository layer or the bean itself? — Zubair Ahmed, Feb 06 '18 at 01:14
A hint for placement can be found here: https://stackoverflow.com/questions/29636004/where-to-place-sqlresultsetmapping-in-case-of-constructorresult — michaeak, Nov 27 '18 at 11:39

JPA entity without underlying table

1 Answers1

Linked