2

I have a situation where I have a single native query which contains data of 2 entity classes, how can I map that data to the entity classes by @SqlResultSetMapping? For Example

Native query:- select customer.name, address.area from customer, address where customer.id=address.custid;

2 Entity Classes like customer and address with name as a variable in customer and area as a variable in the address entity class.

Customer class:
@Entity
public class Customer {
    @Id
    private String name;
    getters and setters
.....
.....
.....
}

Address Class entity:
@Entity
public class Address{
    @Id
    private String area;
    getters and setters
.....
.....
.....
}

To map the native query with the entity class it can be done by giving @SqlResultSetMapping in entity and give the name of the mapping in the nativeQuery

Query query = entityManager.createNativeQuery(sbQuery, "checkInfoMapping");

it will check the mapping name and map to the respective entity class, this can be done for a single entity class.

Can it be done if I have data from the native query of 2 entity classes, if yes how can I map it to the entity class with native query?

Sumanth
  • 159
  • 2
  • 15

2 Answers2

1

You should do something like this:

@SqlResultSetMapping(
   name = "checkInfoMapping",
   classes = {
      @ConstructorResult(targetClass = ResultInfo.class,
         columns = {
            @ColumnResult(name = "cust_name", type = String.class),
            @ColumnResult(name = "adr_area", type = String.class)
         }
      )
   }
)
@Entity
@Table(name = "TST_CUSTOMER")
public class Customer {

    @Id
    @Column(name = "cust_id")
    private Long id;

    @Column(name = "cust_name")
    private String name;

    ...
}


@Entity
@Table(name = "TST_ADDRESS")
public class Address{

    @Column(name = "adr_area")
    private String area;

    @ManyToOne
    @JoinColumn(name = "adr_cust_id")
    private Customer customer;
    ...
}

// this is not entity, just dto class
public class ResultInfo
{
   private String name;
   private String area;

   public ResultInfo(String name, String area)
   {
      this.name = name;
      this.area = area;
   }
   // ...
}

And the query:

List<ResultInfo> resultList = entityManager.createNativeQuery(
   "select cust_name, adr_area from TST_CUSTOMER, TST_ADDRESS where TST_CUSTOMER.cust_id = TST_ADDRESS.adr_cust_id",
   "checkInfoMapping")
   .getResultList();
SternK
  • 11,649
  • 22
  • 32
  • 46
  • 1
    Thank you for your info, what you've said is correct but what I was checking is there any possibility to map multiple entity classes directly with the native query, as we do with single native query mapping because i have really complex query to be mapped in my application, i have done by iterating and setting individual result to the multiple entity class variables,i just curious to know if it can be done through sqlresultmapping. – Sumanth Feb 14 '20 at 10:28
  • I am not sure that understand what you want. In my example the query is completely the same like yours. And I have shown how you can map this query results. You can do the same and for more complex case. – SternK Feb 14 '20 at 10:38
  • 1
    @sumanth Look also at [this](https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#sql-entity-named-queries). – SternK Feb 14 '20 at 10:52
  • 2
    You can see here the example with `@SqlResultSetMapping`, multiple entities and `@FieldResult` (Example 607. Joined-entities NamedNativeQuery). Maybe it will be more suitable for your needs. – SternK Feb 14 '20 at 10:57
  • **Example 607** Perfectly fits my need, Thank you very much! – Sumanth Feb 14 '20 at 13:23
0

The easiest way is to just use JOIN in your native query as shown below .

Native query :- select c.name,a.area from customer c JOIN address a ON c.id = a.custid

And hence you have to just provide result set mapping on customer entity.

Tej Viramgama
  • 135
  • 4