英文:
How to get paginated result in Spring JPA repository using Oracle 10g
问题
AI_DpEntriesRepository.java
@Repositorypublic interface AI_DpEntriesRepository extends PagingAndSortingRepository<AI_DpEntries, Long>{@Query(value = "select a.*,m.description,m.name from AI_DPENTRIES a,"+ "MEDICALHIERARCHY m where PAGEID in (select pageid from PAGES where caseid=8960)"+ " and a.HID=m.ID", nativeQuery = true)Page<AI_DpEntries> getLabAIDpEntries(Pageable pageable);}
AIDpEntryServiceImpl.java
@Servicepublic class AIDpEntryServiceImpl implements AIDpEntryService{@Autowiredprivate AI_DpEntriesRepository aiDpEntryRepository;@Override@Cacheable("labdpentries")public Page<AI_DpEntries> getAIDpEntries(int page, int size) {Pageable pageRequest = PageRequest.of(page, 5);Page<AI_DpEntries> pageResult = aiDpEntryRepository.getLabAIDpEntries(pageRequest);List<AI_DpEntries> dpEntries = pageResult.getContent().stream().collect(Collectors.toList());return new PageImpl<>(dpEntries, pageRequest, pageResult.getTotalElements());//return aiDpEntryRepository.getLabAIDpEntries();}}
出自存储库调用本身的java.sql.SQLSyntaxErrorException: ORA-00904: "A"：无效标识符。我做错了什么？如果我将size参数值传递为Integer.MAX_VALUE，它会一次性返回所有记录。但我需要每页获取5条记录。
英文:
AI_DpEntriesRepository.java
public interface AI_DpEntriesRepository extends PagingAndSortingRepository<AI_DpEntries, Long>{@Query(value = "select a.*,m.description,m.name from AI_DPENTRIES a,"+ "MEDICALHIERARCHY m where PAGEID in (select pageid from PAGES where caseid=8960)"+ " and a.HID=m.ID",nativeQuery = true)Page<AI_DpEntries> getLabAIDpEntries(Pageable pageable);}
AIDpEntryServiceImpl.java
public class AIDpEntryServiceImpl implements AIDpEntryService{@Autowiredprivate AI_DpEntriesRepository aiDpEntryRepository;@Override@Cacheable("labdpentries")public Page<AI_DpEntries> getAIDpEntries(int page,int size) {Pageable pageRequest = PageRequest.of(page, 5);Page<AI_DpEntries> pageResult = aiDpEntryRepository.getLabAIDpEntries(pageRequest);List<AI_DpEntries> dpEntries = pageResult.getContent().stream().collect(Collectors.toList());return new PageImpl<>(dpEntries, pageRequest, pageResult.getTotalElements());//return aiDpEntryRepository.getLabAIDpEntries();}}
Getting java.sql.SQLSyntaxErrorException: ORA-00904: "A": invalid identifier
From repository call itself. What am i doing wrong? If i pass size parameter value as Integer.MAX_VALUE it returns all records at single time currently. But my requirement is to get 5 records per page
答案1
得分: 1
解决方案如下 - 需要在本地查询的基础上进行计数查询，计数查询应返回与本地查询返回的行数相同（PS-与提问中的查询相比，查询需要进行轻微更改）
@Repositorypublic interface AI_DpEntriesRepository extends JpaRepository<AI_DpEntries, Long>{@Query(value = "select a.*,m.description,m.name from AI_DPENTRIES a,MEDICALHIERARCHY m where PAGEID in (select pageid from PAGES where caseid=8960) and a.HID=m.ID And (a.REVIEW_IND != 'D' OR a.REVIEW_IND IS NULL)",countQuery = "select * from AI_DPENTRIES where PAGEID in (select pageid from PAGES where caseid=8960) and (REVIEW_IND != 'D' OR REVIEW_IND IS NULL)", nativeQuery = true)public Page<AI_DpEntries> getLabAIDpEntries(Pageable pageable);}
英文:
Solution here - There was a need for count query along with native query which would be returning same no. of rows to as returned by native query(PS-Minor change in query from question asked)
@Repositorypublic interface AI_DpEntriesRepository extends JpaRepository<AI_DpEntries, Long>{@Query(value = "select a.*,m.description,m.name from AI_DPENTRIES a,MEDICALHIERARCHY m where PAGEID in (select pageid from PAGES where caseid=8960) and a.HID=m.ID And (a.REVIEW_IND != 'D' OR a.REVIEW_IND IS NULL),countQuery = "select * from AI_DPENTRIES where PAGEID in (select pageid from PAGES where caseid=8960) and (REVIEW_IND != 'D' OR REVIEW_IND IS NULL)",nativeQuery = true)public Page<AI_DpEntries> getLabAIDpEntries(Pageable pageable);}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论