jpa手写sql_工作日记：jpa中使用原生sql与手写分页

2020.9.30

一、业务场景

先从播放时长表(play_time_table)中根据用户号(user_id)查出已观看视频总长度(按照任务号task_code、课程号course_code分组并使用sum得出)，然后左连课程详情表(course_detail_table)，获得每个课程的详细信息；

然后按照类似的条件，左连课程节数表(course_num_table)，获得课程总节数(使用group by和count获得)；

然后将这两个结果左连起来。

二、原生SQL代码

由于不熟悉jpa，当多表联查的返回结果大于JavaBean时，就不会写了；

(jpa中，一个JavaBean对应一个数据库的Table及其中的列)

(jpa还有自己的sql语法)

因此在XXXRepository.java中，直接写原生sql：

//XXXRepository是自己起名的respository接口

//XXXJavaBean是自己写的JavaBean，需要对应数据库中的【一个表】及表中的列。

public inferface XXXRepository extends JpaRepository, JpaSecificationExecutor{

//这个Modifying注解，如果涉及到增删改方法，则需要加；如果只是查，不能加，否则会报错

//@Modifying

@Query(

nativeQuery = true,

value = "select * from " +

"( " +

"select a.task_code, a.play_time, b.* from " +

"( " +

"select task_code, course_code, sum(play_time) as play_time from play_time_table where user_id = " +

":userId " +

"GROUP BY task_code, course_code " +

") a " +

"left join course_detail_table b on a.course_code = b.course_code " +

") tb1 " +

"LEFT JOIN " +

"( " +

"select d.course_code as course_code2, IFNULL(count(*),0) as course_num " +

"from " +

"( " +

"select course_code from play_time_table where user_id = " +

":userId "+

"GROUP BY course_code " +

") d "+

"LEFT JOIN course_num_table c ON d.course_code = c.course_code " +

"GROUP BY d.course_code " +

") tb2 "+

"on tb1.course_code = tb2.course_code2 " +

"ORDER BY tb1.id DESC " +

"LIMIT :beginNo, :pageSize"

)

//多表联查得到的数据大于XXXJavaBean，因此不能写List

List> findBeanList(@Param("userId")String userId, @Param("beginNo")Integer beginNo, @Param("pageSize")Integer pageSize);

}

三、手写分页的代码

以下是上方分页参数beginNo与pageSize的获取方法：

@GetMapping("/myurl")

@Timed

//BeanModel是自定义的javabean，规范了一下返回信息参数

public ResponseEntity findBeanList(Pageable pageable){

BeanModel beanModel = new BeanModel();

//获取页码号

int pageNum = pageable.getPageNumber();

//获取每页大小

int pageSize = pageable.getPageSize();

//将页码号转为sql中的起始No(数据库第一条是从0开始的)

int beginNo;

if(pageNum <=0){

beginNo = 0;

}else{

beginNo = (pageNum - 1) * pageSize;

}

//测试用假id

String userId = "abc";

//调用sql返回信息，Map没有封装成Bean(jpa中一个Bean对应一张表，但是返回的信息是多表联查的，大于一张表的列，所以不会弄了...)

//是手写的分页，正常应该是直接将Pageable对象传过去让框架实现的(传入Pageable总报错，因此手写分页)

List> content = XXXService.findBeanList(userId, beginNo, pageSize);

//beanModel中有一个自己写的类型为Object的变量

beanModel.setData(content);

return ResponseEntity.ok().body(beanModel);

}

四、后记

遗留的问题：

现在这句sql感觉还可以优化，然而暂时不会；

这句sql应该用jpa语法写、集成Pageable分页对象才对；由于对jpa不太熟悉，为了先实现需求，就用原生sql、手写分页实现了；如果可以的话还需要完善。