Skip to content

Instantly share code, notes, and snippets.

@tcollins

tcollins/-Spring-JPA-Dynamic-Query-With-Limit

Last active September 5, 2024 13:26
Show Gist options
  • Save tcollins/0ebd1dfa78028ecdef0b to your computer and use it in GitHub Desktop.
Save tcollins/0ebd1dfa78028ecdef0b to your computer and use it in GitHub Desktop.
Download ZIP
Spring Data JPA - Limit results when using Specifications without an unnecessary count query being executed
Raw
-Spring-JPA-Dynamic-Query-With-Limit
If you use the findAll(Specification, Pageable) method, a count query is first executed and then the
data query is executed if the count returns a value greater than the offset.
For what I was doing I did not need pageable, but simply wanted to limit my results. This is easy
to do with static named queries and methodNameMagicGoodness queries, but from my research (googling
for a few hours) I couldn't find a way to do it with dynamic criteria queries using Specifications.
During my search I found two things that helped me to figure out how to just do it myself.
1.) A stackoverflow question.
How to disable count when Specification and Pageable are used together?
http://stackoverflow.com/questions/26738199/how-to-disable-count-when-specification-and-pageable-are-used-together
(where I will add a link to this gist)
2.) Spring documentation - Adding custom behavior to all repositories
http://docs.spring.io/spring-data/data-jpa/docs/current/reference/html/#repositories.custom-behaviour-for-all-repositories
I followed the Spring documentation pretty closely and got this all working pretty quickly without
any real problems.
Raw
BaseRepository.java
@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {
List<T> findAll(Specification<T> spec, int offset, int maxResults, Sort sort);
List<T> findAll(Specification<T> spec, int offset, int maxResults);
}
Raw
BaseRepositoryFactoryBean.java
public class BaseRepositoryFactoryBean<R extends JpaRepository<T, I>, T, I extends Serializable> extends JpaRepositoryFactoryBean<R, T, I> {
@SuppressWarnings("rawtypes")
@Override
protected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager) {
return new BaseRepositoryFactory(entityManager);
}
private static class BaseRepositoryFactory<T, I extends Serializable> extends JpaRepositoryFactory {
private final EntityManager em;
public BaseRepositoryFactory(EntityManager em) {
super(em);
this.em = em;
}
@SuppressWarnings({ "unchecked", "rawtypes", "hiding" })
protected <T, ID extends Serializable> SimpleJpaRepository<?, ?> getTargetRepository(RepositoryMetadata metadata, EntityManager entityManager) {
SimpleJpaRepository<?, ?> repo = new BaseRepositoryImpl(metadata.getDomainType(), entityManager);
return repo;
}
protected Class<?> getRepositoryBaseClass(RepositoryMetadata metadata) {
return BaseRepositoryImpl.class;
}
}
}
Raw
BaseRepositoryImpl.java
public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {
private final EntityManager entityManager;
public BaseRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
super(domainClass, entityManager);
this.entityManager = entityManager;
}
public List<T> findAll(Specification<T> spec, int offset, int maxResults) {
return findAll(spec, offset, maxResults, null);
}
public List<T> findAll(Specification<T> spec, int offset, int maxResults, Sort sort) {
TypedQuery<T> query = getQuery(spec, sort);
if (offset < 0) {
throw new IllegalArgumentException("Offset must not be less than zero!");
}
if (maxResults < 1) {
throw new IllegalArgumentException("Max results must not be less than one!");
}
query.setFirstResult(offset);
query.setMaxResults(maxResults);
return query.getResultList();
}
}
Raw
MySpringBootApplication.java
@SpringBootApplication
@EnableJpaRepositories(repositoryFactoryBeanClass = BaseRepositoryFactoryBean.class)
public class MySpringBootApplication {
public static void main(String[] args) {
SpringApplication app = new SpringApplication(MySpringBootApplication.class);
app.run(args);
}
}
Raw
UserRepository.java
// This is just to show an example of a repo
public interface UserRepository extends BaseRepository<User, Long>, JpaSpecificationExecutor<User> {
}
@Graf54
Copy link

Graf54 commented Feb 11, 2021 •
edited
Loading

Thanks for solution.
In my case enough
Work in spring boot 2.4.2

  1. @EnableJpaRepositories(repositoryBaseClass = BaseRepositoryImpl.class)
    and no BaseRepositoryFactoryBean.java
  2. And yes, need add code how write deathghost

Finally

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import java.io.Serializable;
import java.util.List;

public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {

    private final EntityManager entityManager;

    public BaseRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
        super(domainClass, entityManager);
        this.entityManager = entityManager;
    }

    public BaseRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityManager = entityManager;
    }

    public List<T> findAll(Specification<T> spec, long offset, int maxResults) {
        return findAll(spec, offset, maxResults, Sort.unsorted());
    }

    public List<T> findAll(Specification<T> spec, long offset, int maxResults, Sort sort) {
        TypedQuery<T> query = getQuery(spec, sort);

        if (offset < 0) {
            throw new IllegalArgumentException("Offset must not be less than zero!");
        }
        if (maxResults < 1) {
            throw new IllegalArgumentException("Max results must not be less than one!");
        }

        query.setFirstResult((int) offset);
        query.setMaxResults(maxResults);
        return query.getResultList();
    }

}


import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.NoRepositoryBean;

import java.io.Serializable;
import java.util.List;

@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {
    List<T> findAll(Specification<T> spec, long offset, int maxResults, Sort sort);

    List<T> findAll(Specification<T> spec, long offset, int maxResults);
}


@Repository
public interface GeoAttributeTypeRepository extends BaseRepository<GeoAttributeType, Long> {
}

@SpringBootApplication
@EnableJpaRepositories(repositoryBaseClass = BaseRepositoryImpl.class)
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

@tcollins
Copy link
Author

I suspect the problems that @deathghost listed are probably due to the initial solution being done on an older version of Spring. Since I did this almost 3 years ago, I have no idea what version of Spring I did this for originally.

Anyway, I'm glad this helped you and you were able to get it working.

@vora-bei
Copy link

vora-bei commented Mar 26, 2021 •
edited
Loading

Thanks for solution. I implement Slice semantic based on your solution.

@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {
    Slice<T> findAllSliced(@Nullable Specification<T> var1, Pageable var2);
}

public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {

    private final EntityManager entityManager;

    public BaseRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
        super(domainClass, entityManager);
        this.entityManager = entityManager;
    }

    public BaseRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityManager = entityManager;
    }

    @Override
    public Slice<T> findAllSliced(Specification<T> spec, Pageable pageable) {
        TypedQuery<T> query = getQuery(spec, pageable.getSort());

        query.setFirstResult((int) pageable.getOffset());
        int extraSize = pageable.getPageSize() + 1;
        query.setMaxResults(extraSize);
        boolean hasNext = query.getResultList().size() == extraSize;

        List<T> result = query.getResultList();
        if(hasNext){
            result.remove(extraSize - 1);
        }
        return new SliceImpl<>(result, pageable, hasNext);
    }

}

@Stexxen
Copy link

Stexxen commented Jul 24, 2021 •
edited
Loading

@vora-bei FYI you are calling query.getResultList() twice

boolean hasNext = query.getResultList().size() == extraSize;
List<T> result = query.getResultList();

so you will be executing the query twice. Can I suggest you change it to

List<T> result = query.getResultList();
boolean hasNext = result.size() == extraSize;

@yekota-copart
Copy link

Thanks for solution. In my case enough Work in spring boot 2.4.2

  1. @EnableJpaRepositories(repositoryBaseClass = BaseRepositoryImpl.class)
    and no BaseRepositoryFactoryBean.java
  2. And yes, need add code how write deathghost

Finally

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import java.io.Serializable;
import java.util.List;

public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {

    private final EntityManager entityManager;

    public BaseRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
        super(domainClass, entityManager);
        this.entityManager = entityManager;
    }

    public BaseRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityManager = entityManager;
    }

    public List<T> findAll(Specification<T> spec, long offset, int maxResults) {
        return findAll(spec, offset, maxResults, Sort.unsorted());
    }

    public List<T> findAll(Specification<T> spec, long offset, int maxResults, Sort sort) {
        TypedQuery<T> query = getQuery(spec, sort);

        if (offset < 0) {
            throw new IllegalArgumentException("Offset must not be less than zero!");
        }
        if (maxResults < 1) {
            throw new IllegalArgumentException("Max results must not be less than one!");
        }

        query.setFirstResult((int) offset);
        query.setMaxResults(maxResults);
        return query.getResultList();
    }

}

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.NoRepositoryBean;

import java.io.Serializable;
import java.util.List;

@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {
    List<T> findAll(Specification<T> spec, long offset, int maxResults, Sort sort);

    List<T> findAll(Specification<T> spec, long offset, int maxResults);
}

@Repository
public interface GeoAttributeTypeRepository extends BaseRepository<GeoAttributeType, Long> {
}

@SpringBootApplication
@EnableJpaRepositories(repositoryBaseClass = BaseRepositoryImpl.class)
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

Thanks buddy

@ATI-687
Copy link

ATI-687 commented Feb 13, 2024

Muchas gracias 🤝

@josergdev
Copy link

josergdev commented Sep 4, 2024 •
edited
Loading

Slice is simply a specialization of Window, the same goal can be achieved using the
<S extends T, R> R findBy(Specification<T> spec, Function<FluentQuery.FetchableFluentQuery<S>, R> queryFunction);
method of JpaSpecificationExecutor

https://gist.github.com/josergdev/06c82891a719eca4834410339885ad23

package dev.joserg.jpa;

import static org.springframework.data.domain.ScrollPosition.offset;

import java.util.function.Function;

import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.ScrollPosition;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.SliceImpl;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Window;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.query.FluentQuery.FetchableFluentQuery;

public interface SliceSpecificationExecutor<T> extends JpaSpecificationExecutor<T> {

  default Window<T> findAllWindowed(Specification<T> spec, Sort sort, int limit, ScrollPosition scrollPosition) {
    return this.findBy(spec, toWindow(sort, limit, scrollPosition));
  }

  default Window<T> findAllWindowed(Specification<T> spec, Sort sort, ScrollPosition scrollPosition) {
    return this.findBy(spec, toWindow(sort, scrollPosition));
  }

  default Window<T> findAllWindowed(Specification<T> spec, ScrollPosition scrollPosition) {
    return this.findAllWindowed(spec, Sort.unsorted(), scrollPosition);
  }

  default Slice<T> findAllSliced(Specification<T> spec, Pageable pageable) {
    final var window = pageable.isUnpaged()
        ? this.findAllWindowed(spec, pageable.getSort(), offset())
        : this.findAllWindowed(spec, pageable.getSort(), pageable.getPageSize(), offset(pageable.getOffset()));
    return new SliceImpl<>(window.getContent(), pageable, window.hasNext());
  }

  private static <T> Function<FetchableFluentQuery<T>, Window<T>> toWindow(Sort sort, int limit, ScrollPosition scrollPosition) {
    return fetchableFluentQuery -> fetchableFluentQuery.sortBy(sort).limit(limit).scroll(scrollPosition);
  }

  private static <T> Function<FetchableFluentQuery<T>, Window<T>> toWindow(Sort sort, ScrollPosition scrollPosition) {
    return fetchableFluentQuery -> fetchableFluentQuery.sortBy(sort).scroll(scrollPosition);
  }

}

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment