springboot --jpa 之多表操作

---

jpa可使用nativeQuery实现多表联查，多表聚合

 

示例：school student

school：id，name

student：id、name、age、school_id

 

多表联查：id，name，age，schoolName，自定义类StudentInfo接收查询结果

聚合：查询每个学校的学生人数，自定义类StudentCount接收返回结果

 

@Repository
public class StudentCustomRepository {

    @Autowired
    private EntityManager entityManager;

    @SuppressWarnings("unchecked")
    public List<StudentInfo> get(){
                  //多表联查

        String sql="select a.id,a.name,a.age,b.name as schoolName from student a,school b where a.school_id=b.id";
        Query query=entityManager.createNativeQuery(sql).setFirstResult(0).setMaxResults(10);

        return query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(StudentInfo.class)).getResultList();
    }

    @SuppressWarnings("unchecked")
    public List<StudentCount> count(){
                   //多表聚合

        String sql="select b.name as schoolName,count(*) as count from student a,school b where a.school_id=b.id group by b.name order by 2 desc,b.name asc ";
        Query query=entityManager.createNativeQuery(sql);

        return query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(StudentCount.class)).getResultList();
    }
}