springboot 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();
}
}