通过native sql来获取多个表的杂交类

先讲需求，比如出一张报表，从多个数据库表中取数据，合成一个表，这里就需要一个表对应的实体类，然后用native sql来查询，同时，对类元素和SQL列进行映射
-----------------------------------------------------
方法就是自定义Repository
比如一个PersonReport(人的报表)
先写一个PersonReportDao接口,这个接口完全普通


public interface PersonReportDao {


List<PersonReport> getThePerson(String lastName);
}
接着完成它的实现类PersonReportRepositoryImpl，再定义
public interface PersonReportRepository extends JpaRepository<PersonReport, Integer>, PersonReportDao{ 
}


最后再Test
public class PersonReportTest {
private ApplicationContext ctx = null;
private PersonReportRepository personReportRepository = null; 
private PersonReport aaPersonReport; 
private PersonRepsotory personRepsotory; 
{
ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
personReportRepository =  ctx.getBean(PersonReportRepository.class); 
personRepsotory = ctx.getBean(PersonRepsotory.class); 
} 
  @Test
public void testReport()
{ 
System.out.println((PersonReport)(personReportRepository.getThePerson("bbb").get(1)));
} 
}
-------------------------------------------------------------
接着完成它的实现类，但是类的名称要按规范来，PersonReportRepositoryImpl，这里是这个


这里有特别要注意的事情：
1 @PersistenceContext注解不能直接在外面用，现在还不知道为什么直接在外面用用不了。在刚开始测试的时候，直接写在@Test中，根本拿不到注入的对象
2 createNativeQuery 和 createNamedQuery 两个函数是有区别的
createNativeQuery的参数里面，是有native sql的，createNativeQuery也可以直接使用@SqlResultSetMapping来映射字段，也可以自动映射字段
createNamedQuery的参数里面，不是native sql,而是用 @NamedNativeQueries来定义的名称，名称代表了一个真实的SQL，这些东西，是要在实体类里面去定义的，还有包括SQL列与类元素的映射关系，后面会有实体代码
3 在定义实体代码时，需要有一个@ID注解，如果没有会报错的。这里这样处理，随便找一个元素加上ID注解，但是，不要更新也不要插入字段就好，这个ID在查询中，出现重复也是没有关系的


下面是DAO接口的实现：PersonReportRepositoryImpl
/**
 * file peter
2017年1月25日
 */
package com.atguigu.springdata;


import java.util.List;


import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;


/**
 *file peter
2017年1月25日
 */
public class PersonReportRepositoryImpl implements PersonReportDao {


@PersistenceContext
private EntityManager entityManager;

@Override
public List<PersonReport> getThePerson(String lastName) {
// TODO Auto-generated method stub
/* Query q = entityManager.createNativeQuery("SELECT t1.email ,t1.last_name, t2.province, "
+ "t2.city, t1.birth   "
    + " FROM jpa_persons t1, jpa_addresses t2 where t2.id = t1.address_id and t1.last_name =?1"
, "PersonReportRst"); */
//Query q = entityManager.createNativeQuery("PersonReportRst", "PersonReportRst");
Query q = entityManager.createNamedQuery("PersonReportRst");//("PersonReportRst", "PersonReportRst");

 q.setParameter(1, lastName);
System.out.println("entityManager = " + entityManager); 
System.out.println("********************* q = " + q); 


List<PersonReport> orderList = q.getResultList();
System.out.println("orderList = " + orderList);
//这里是设定在sql中所需的参数
// q.setParameter(1, 1);
//得到结果集
/* List<PersonReport> orderList = (List<PersonReport>)q.getResultList();
System.out.println(orderList); */

return orderList;
} 
}


-------------------------------------------------------------
最后是实体类PersonReport，特别说明@NamedNativeQueries和@SqlResultSetMappings的定义，是可以在impl里面直接引用的，虽然它们不在一个文件里面




import java.util.Date;
import javax.persistence.*;
import javax.persistence.ColumnResult;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.NamedNativeQueries;
import javax.persistence.NamedNativeQuery;
import javax.persistence.SqlResultSetMapping;
import javax.persistence.SqlResultSetMappings;
 
@Entity
 @NamedNativeQueries
(
    {
       @NamedNativeQuery(
           name="PersonReportRst",
           query="SELECT t1.last_name, t2.province, t2.city, t1.email, t1.birth "
            + " FROM jpa_persons t1, jpa_addresses t2 where t2.id = t1.address_id and t1.last_name =?1",
            resultClass = PersonReport.class,
            resultSetMapping="PersonReportRst"),
}
)  


@SqlResultSetMappings(
{
    @SqlResultSetMapping
    (
       name="PersonReportRst",
       entities={
      @EntityResult
               ( 
                  entityClass=PersonReport.class, 
                  fields= 
                  { 
         @FieldResult(name = "lastName", column = "t1.last_name"),          
         @FieldResult(name = "province", column = "t2.province"),          
         @FieldResult(name = "city", column = "t2.city"),          
         @FieldResult(name = "email", column = "t1.email") , 
         @FieldResult(name = "birth", column = "t1.birth") 
                  } 
               )       
       } 
    )
}) 
 
public class PersonReport {
@Id
private String lastName;
private String province;
private String city;
private String email;
private Date birth;
 
@Id
//private Integer id; 

public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getProvince() {
return province;
}
public void setProvince(String province) {
this.province = province;
}
public String getCity() {
return city;
}
public void setCity(String city) {
this.city = city;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public Date getBirth() {
return birth;
}
public void setBirth(Date birth) {
this.birth = birth;
} 
}