jpa双表查询+分页实现,请求最后一页数据有时报越界错误
jpa双表查询实现有2种方法
方法一:自定义实体类(包含查询的类容)
方法二:集合方法(List<Map<String, Object>>)
我这里项目的映射表:BasicSentence ,KnowledgePoint
关于DAO层的JPA处理接口类:StudyRepository
@Repository
public interface StudyRepository extends JpaRepository<BasicSentence,Integer>, JpaSpecificationExecutor<BasicSentence> {
}
方法一
1,自定义一个实体类SentenceObject,里面要有2个应射表的类名和构造方法(无参构造方法和有参构造方法),我这里采用了Lombok插件,所以简化了构造方法和getter、setter的代码,代码如下
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SentenceObject {
private BasicSentence basicSentence;
private KnowledgePoint knowledgePoint;
}
2,查询@Query代码
@Query(value = "select new com.pojo.SentenceObject(list,list2) " +
" from tbl_basic_sentence list,tbl_knowledge_point list2 " +
" where list.bid = :b_id and list.kid=list2.kid",nativeQuery = false)
List<SentenceObject> findByBid2(@Param("b_id") Integer b_id);
3,运行结果
方法二
1,查询@Query代码
@Query(value = "select new map(list.bid ,list.sentence ,list.meaning " +
",list.test_word ,list2.knowledge ,list2.description) " +
"from tbl_basic_sentence list,tbl_knowledge_point list2 " +
" where list.bid = :b_id and list.kid=list2.kid ",nativeQuery = false)
List<Map<String, Object>> findByBid2(@Param("b_id") Integer b_id);
2,运行结果
分页实现
把自定义的返回类型改为Page类型,参数增加Pageable pageable就可以了,如
@Query(value = "select new com.pojo.SentenceObject(list,list2) " +
" from tbl_basic_sentence list,tbl_knowledge_point list2 " +
" where list.bid = :b_id and list.kid=list2.kid",nativeQuery = false)
Pageable <SentenceObject> findByBid2(Pageable pageable,@Param("b_id") Integer b_id);
期间遇到的错误
jpa查询代码
@Query(value = "select new com.pojo.SentenceObject(list,list2) " +
" from tbl_basic_sentence list,tbl_knowledge_point list2 " +
" where list.bid = :b_id and list.kid=list2.kid",nativeQuery = true)
List<SentenceObject> findByBid2(@Param("b_id") Integer b_id);
出错原因:nativeQuery 属性值为true时,@Query查询的value值为原生的SQL语句,上面为 select new com.pojo.SentenceObject(list,list2) 所以出错,解决办法:去掉nativeQuery 或者把nativeQuery 属性改为false。
觉得有用的话,请给个赞谢谢~