优化获取前一个和下一个记录的方式

Question

优化获取前一条和下一条记录的方式

我做了一个实现，我得到前一个记录和下一个记录，但不是很有效。当我们进入日志细节屏幕时，在本例中是一个玩家，整个搜索所有记录以检查下一个和上一个记录完成。

在这种情况下，搜索仅针对设备，但可以通过带有过滤器的搜索引擎获得详细的页面结果。我如何改进前一个和下一个记录的执行?我想要的不是检索所有的记录，只有你需要的记录。这是我的实现:

我的控制器:

@Controller
public class PlayerController {
    @Autowired
    private PlayerService pService;
    @RequestMapping(value="/players/{id}", method = RequestMethod.GET)
    public final ModelAndView printPlayerDetail(@PathVariable(value = "id") Integer id, Locale locale) throws ControllerException 
    {
        ModelAndView view = new ModelAndView("players/detail");
        Player p = null;
        NavigatorDTO navigator = new NavigatorDTO();
        try {
            p = pService.getById(id);
            navigator.setId(p.getId());
            navigator.setRecords(pService.getNavigator(p.getTeam().getId()));
        } catch (ServiceException e) {
            throw new ControllerException(this.messages.getMessage(ERROR_QUERY, null, locale), e);
        }
        PlayerDTO dto = new PlayerDTO();        
        dto.setId(p.getId());
        dto.setName(p.getName());
        if (p.getTeam() != null) {
            dto.setTeam(p.getTeam().getId());
        }           
        view.addObject("navigator", navigator);
        view.addObject("player", dto);
        return view;
    }   
}

我的服务:

@Service
public class PlayerServiceImpl implements PlayerService {
    @Autowired
    private PlayerDao pDao; 
    @Override
    @Transactional
    public List<Integer> getNavigator(Integer teamId) throws ServiceException {
        List<Integer> result = new ArrayList<Integer>();
        try {
            List<Player> players = pDao.findByTeanm(teamId);
            for (Player p : players) {
                result.add(p.getId());
            }
        } catch (FacadeException e) {
            throw new ServiceException(e);
        }
        return result;
    }
}

My Navigation Class:

public final class NavigatorDTO implements Serializable {
    private static final long serialVersionUID = 1L;
    private Integer id;
    private List<Integer> records = new ArrayList<Integer>();
    public Integer getId() {
        return id;
    }
    public void setId(final Integer id) {
        this.id = id;
    }
    public List<Integer> getRecords() {
        return records;
    }
    public void setRecords(final List<Integer> records) {
        this.records = records;
    }   
    /**
     * Get next id
     * @return
     */
    public Integer getNext() {
        if ( id!=null ) {
            Integer actualPosition = records.indexOf(id);
            try {
                return records.get(actualPosition + 1);
            } catch (Exception e) {
                return null;
            }   
        } else { 
            return null;
        }
    }
    /**
     * Get previous id
     * @return
     */
    public Integer getPrevious() {
        if (id != null){
            Integer actualPosition = records.indexOf(id);
            try {
                return records.get(actualPosition - 1);
            } catch (Exception e) {
                return null;
            }
        } else {
            return null;
        }
    }
    /**
     * Get first id
     * @return
     */
    public Integer getFirst(){
        if (id != null) {
            try {
                return records.get(0);
            } catch (Exception e) {
                return null;
            }
        } else {
            return null;
        }
    }
    /**
     * Get last id
     * @return
     */
    public Integer getLast(){
        if (id != null) {
            try{
                return records.get(records.size() - 1);
            } catch (Exception e){
                return null;
            }
        } else {
            return null;
        }
    }
    /**
     * Get total records
     * @return Total
     */
    public int getTotalrecords(){
        return (records == null) ? 1 : records.size();
    }
    /**
     * Get actual position
     * @return
     */
    public int getActualPosition(){
        return (records == null) ? 1 : records.indexOf(id) + 1;
    }
}

Answer 1

OFFSET分页，这可能是您所做的固有的慢。

见http://use-the-index-luke.com/sql/partial-results/fetch-next-page

遗憾的是，JPA并不真正支持键集分页(我认为)。因此，您必须使用原始SQL。