CODEWITHSUNDEEP
javajpa
Giovane
Giovane

Reputation: 793

JPA native query to return the entity with fields from multiple tables

I have a query in JPA NativeSql, where I do "unions" of tables and joins. I made an entity with all the query fields which are from multiple tables. So I can not do "@Column" "@ table" as usual with JPA.

How could I set the given values ​​of the query to my entity?

Upvotes: 9

Views: 33786

Answers (5)

Pieter
Pieter

Reputation: 2783

You can map the columns returned by your native SQL query to your entity by using @SqlResultSetMapping.

Example:

Query q = em.createNativeQuery(
    "SELECT o.id AS order_id, " +
        "o.quantity AS order_quantity, " +
        "o.item AS order_item, " +
        "i.name AS item_name, " +
    "FROM Order o, Item i " +
    "WHERE (order_quantity > 25) AND (order_item = i.id)",
    "OrderResults");

@SqlResultSetMapping(name="OrderResults", 
    entities={ 
        @EntityResult(entityClass=com.acme.Order.class, fields={
            @FieldResult(name="id", column="order_id"),
            @FieldResult(name="quantity", column="order_quantity"), 
            @FieldResult(name="item", column="order_item")
        })
    },
    columns={
        @ColumnResult(name="item_name")}
)

More examples can be found here.

Upvotes: 10

Kevin Bowersox
Kevin Bowersox

Reputation: 94489

Why not put the NATIVE SQL into a view? Then just create an entity mapped to the view like any normal entity would be mapped to table. The only difference being you cannot insert, update or delete entities based off of the view.

Upvotes: 2

Masudul
Masudul

Reputation: 21981

JPA native SQL is same as generic SQL. You can do union operation same as SQL query do. But, if you want to do it with JPQL, than you need to use EclipseLink, becasue JPQL of JPA does not support of Union operation.

Upvotes: 0

Rahul
Rahul

Reputation: 45080

You can use the overloaded EntityManager#createNativeQuery(sql,resultClass) method for this.

Upvotes: 2

VWeber
VWeber

Reputation: 1261

IMHO using DAOs with JPA isn't a good idea. But have a look at the Criteria API. You can construct such queries like the one you described with the CriteriaBuilder.

Upvotes: 0

Related Questions