CODEWITHSUNDEEP
javasqljpa
user1119859
user1119859

Reputation: 719

How to use JPA to select native table columns?

I want to select a few column from a table using JPA. In this case I am not interessted in complete Entities, but just in a few columns with certain Ids.

So this is my approach:

EntityManager em = createEntityManager();
List<Object[]> data = em.createNativeQuery( "SELECT ID, FOREIGN_OBJ_1_ID, FOREIGN_OBJ_2_ID FROM TABLE1" ).getResultList();
em.close();

for ( Object[] row : rentaldata )
{
     Long id = ( (BigDecimal) row[0] ).longValue();
     Long fk_1 = ((Long) row[1]);
     Long fk_2 = ((Long) row[2]);

      DataObject do = new DataObject(id, fk_1, fk_2, ...);
...

I tried also with JPA select new .... but I want be be sure no Objects are created. So my question is: Is there any better approach to load several columns and put them into an object that this ?

thanks, Thorsten

Upvotes: 0

Views: 137

Answers (1)

Renato
Renato

Reputation: 2167

You can use SqlResultMapping. Try something like this:

 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 details here and here

Upvotes: 2

Related Questions