Paging and Research - Springdata with Sqlserver2008

Question

Hello, I have a problem in my application and I wanted to see if anyone here can assist me.

I’m developing a system with Springboot (Springdata, etc. ) on top of a base Sqlserver2008. Due to force majeure, it was necessary to implement a datatable allowing paging, and filter in multiple columns (see annex).

What happens is that when I’m on a page that’s not the first one, and I search the column for a value the data is inconsistent, let me explain.

What happens is that due to the way the filter is being applied in the database query, the values that come filtered in a "page" other than the current one (ex: 2) are not shown. Drawing..:

If you have a dataset just like:

• john
• Mário

If I’m paying mine datatable with size 1, and goes to the second page, where the result will be shown mário and filter by john, I cannot get the result, because the query "suppose" that the result should be on the second page of dataset.

I hope it was clear enough.. D

Of implementation, I am using the Pageable and Specification of Springdata. Relevant codes:

public class UsuarioRestController {
    private final UsuarioService usuarioService;
    public Page<UsuarioListModel> listarUsuarios(@PageableDefault Pageable pageable, @Validated UsuarioSearchCriteria usuarioSearchCriteria) {
        return usuarioService.buscaPaginado(usuarioSearchCriteria, pageable)
                .map(UsuarioListModel::new);
    }
}

public class UsuarioService {
    private final UsuarioRepository repository;
    public Page<Usuario> buscaPaginado(UsuarioSearchCriteria criteria, Pageable pageable) {
        return repository.findAll(criteria.getSpecification(), pageable);
    }
}

public class UsuarioSearchCriteria {
    private Long codigo;
    private String nome;
    private Boolean habilitado;
    public Specification<Usuario> getSpecification() {
        return Specification
                .where(UsuarioSpecifications.codigo(codigo))
                .and(UsuarioSpecifications.nomeLike(nome))
                .and(UsuarioSpecifications.habilitado(habilitado));
    }
}

class UsuarioSpecifications {
    static Specification<Usuario> codigo(Long codigo) {
        if (codigo == null) return null;
        return (root, query, cb) -> cb.equal(root.get("codigo"), codigo);
    }

    static Specification<Usuario> nomeLike(String nome) {
        if (nome == null) return null;
        return (root, query, cb) -> cb.like(root.get("nome"), "%" + nome + "%");
    }

    static Specification<Usuario> habilitado(Boolean habilitado) {
        if (habilitado == null) return null;
        return (root, query, cb) -> cb.equal(root.get("habilitado"), habilitado);
    }
}

public interface UsuarioRepository extends JpaRepository<Usuario, Long>, JpaSpecificationExecutor<Usuario> {
    Optional<Usuario> findByCodigo(Long codigo);
    Boolean existsByCodigo(Long codigo);
}

Important points:

• I’ve tested so much with Hibernate as to Eclipselink, the problem persists.
• I know that the SQLServer2008 does not have a good pagination support (eg, offset), and this is quite possibly my problem.
• I don’t have the possibility to change SGBD unfortunately.

Anyway, if anyone has any ideas, or knows a solution, please! : help

Answer 1

I found a solution to the problem (I don’t know if it’s the best, but it’s working).

Thinking about the issue of the page, I saw that if I had a way to ignore the page when there was a query by some of the filters I could return the correct data. Since the filter is a global query, the page is of little importance actually.

To implement a correction that was valid for all cases of the application, I ended up using AOP of Spring, to have an aspect that intercepted all the requests to the methods that are responsible for the filtered and paged query and changed their parameters as needed.

Follows implementation:

@Aspect
@Configuration
public class PaginationWithSpecificationsAspect {

    @Around("execution(* br.com.company.wms.app..filterAndPage(*,*))")
    public Object paginationAspectBefore(ProceedingJoinPoint joinPoint) throws Throwable {
        final Object[] args = joinPoint.getArgs();

        SearchCriteria criteria = (SearchCriteria) args[0];
        Pageable pageable = (Pageable) args[1];

        if (!criteria.isEmpty()) {
            // Caso existe consulta nos filtros, continua como se estivesse na primeira página (o que ignora a 'paginação' na consulta)
            return joinPoint.proceed(new Object[]{criteria, pageable.first()});
        }

        return joinPoint.proceed();
    }

}

I also made them @Services that exposes pagination have the method filterAndPage(SearchCriteria, Pageable).

I don’t think it is necessary to put the rest of the related code because there is nothing out of the ordinary or very different from what was stated in the question except for some small modifications to the operation of logic.