在JPA框架下使用group_concat函数

前景:JPQL语句中没有group_concat关键字,因此需要使用原生代码去写sql.
业务场景:对group by分组后的同类字段进行合并以,进行分割

需要以上这种效果
开始实现

@Query(nativeQuery = true,
            value = "select c.id as clazzId,c.name as clazzName,GROUP_CONCAT(cr.employee_name) as headTeacherName,c.month_age_start as monthAgeStart,c.month_age_end as monthAgeEnd,c.clazz_number as clazzNumber,c.remark  \n" +
            "FROM cl_clazz c left join cl_clazz_role cr on c.id = cr.clazz_id \n" +
            "where (c.organization_id = :organizationId) and (cr.type = :teacherType) \n" +
            "and (:clazzName is null or c.name like %:clazzName%) \n" +
            "and (:headTeacherName is null or cr.employee_name like %:headTeacherName%) \n" +
            "group by c.id order by c.create_time desc",
            countQuery = "select count(c.id) \n" +
                    "FROM cl_clazz c left join cl_clazz_role cr on c.id = cr.clazz_id \n" +
                    "where (c.organization_id = :organizationId) and (cr.type = :teacherType) \n" +
                    "and (:clazzName is null or c.name like %:clazzName%) \n" +
                    "and (:headTeacherName is null or cr.employee_name like %:headTeacherName%) \n" +
                    "group by c.id order by c.create_time desc")
    Page<ClazzPageVO> findByNameLikeAndHeadTeacherNameOrderByCreateTimeDesc(String clazzName, String headTeacherName, Integer organizationId, Pageable pageable, Integer teacherType);

1.首先原生代码nativeQuery 中,严格按照数据库字段进行查询,不能用jpql
2. 其次查询出来的药与实体中的字段顺序一一对应,并且使用别名与实体中的字段对应(必须要别名,不然封装不进去)
3. 需要分页的还要指定一个countQuery 语句
4. 关键
查出来的数据用接口去接!!
查出来的数据用接口去接!!
查出来的数据用接口去接!!

Page<ClazzPageVO> findByNameLikeAndHeadTeacherNameOrderByCreateTimeDesc(String clazzName, String headTeacherName, Integer organizationId, Pageable pageable, Integer teacherType);

这个返回值的ClazzPageVO需要是一个接口!!

前缀加get以驼峰方式命名,sql那需要用别名指定,才能正确映射到!!

最后
不是我膨胀,这可能是网络上第一篇关于在jpa框架下用group_concat函数的了,花了一天时间在网上找都找不到有关资料,最后还是请教组长才写出来的.