工作日记：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<XXXJavaBean, Long>, JpaSecificationExecutor<XXXJavaBean>{

  //这个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<XXXJavaBean>
  List<Map<String,Object>> findBeanList(@Param("userId")String userId, @Param("beginNo")Integer beginNo, @Param("pageSize")Integer pageSize); 
  
}

三、手写分页的代码

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

@GetMapping("/myurl")
@Timed
//BeanModel是自定义的javabean，规范了一下返回信息参数
public ResponseEntity<BeanModel> 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<Map<String,Object>> content = XXXService.findBeanList(userId, beginNo, pageSize);
  //beanModel中有一个自己写的类型为Object的变量
  beanModel.setData(content);
  return ResponseEntity.ok().body(beanModel);
}

四、后记

遗留的问题：

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

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