Saturday, October 24, 2015

Complex Native SQL to JPA entity

Assuming there is a ContactDetail entity.
This could be a database table or table view.


@Entity public class ContactDetail {     private String name;     private String address;     private String phoneNo;     private String mobileNo;     private String email;     // getter and setter
}

the direct way to get this entity is to execute the following sql.

select name, address, phone_no, mobile_no, email from contact_detail;

another way is to select those column from different tables. 
eg. 

select p.name as name, a.address as address, b.phoneNo as phoneNo, b.mobileNo as mobileNo, c.email as email from person p join address a on a.p_id = p.p_id join phone b on b.p_id = p.p_id join email c on c.p_id = p.p_id;

where the entity columns are actually come from different table. 
which could be a view. 
or SQL to return a table entity based on business requirement.

after gotten the correct SQL, we can proceed to query the JPA with the native SQL.

List<ContactDetail > customers = (List<ContactDetail>)em.createNativeQuery(YOUR_NATIVE_SQL, ContactDetail.class).getResultList();


Done!!

LinkWithin

Related Posts Plugin for WordPress, Blogger...