英文:
How to get paginated result in Spring JPA repository using Oracle 10g
问题
AI_DpEntriesRepository.java
@Repository
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
@Service
public class AIDpEntryServiceImpl implements AIDpEntryService{
@Autowired
private 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{
@Autowired
private 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-与提问中的查询相比，查询需要进行轻微更改）
@Repository
public 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)
@Repository
public 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);
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论