Spring Data JPA. How to get only a list of IDs from findAll() method

Question

I have a very complicated model. Entity has a lot relationship and so on.

I try to use Spring Data JPA and I prepared a repository.

but when I invoke a method findAll() with specification for the object a have a performance issue because objects are very big. I know that because when I invoke a method like this:

@Query(value = "select id, name from Customer ")
List<Object[]> myFindCustomerIds();

I didn't have any problems with performance.

But when I invoke

List<Customer> findAll(); 

I had a big problem with performance.

The problem is that I need to invoke findAll method with Specifications for Customer that is why I cannot use method which returns a list of arrays of objects.

How do I write a method to find all customers with specifications for the Customer entity but which returns only IDs.

like this:

List<Long> findAll(Specification<Customer> spec);

• I cannot use in this case pagination.

Please help.

Uncomfortable

This sounds like exactly what FetchType.LAZY was intended to solve. – Ence

It is better but still there is 10 - 15 second. From find all with query I have result in 1-2 second. It is possible to solve this problem using Spring Data. It means get value only from particular column instead of all object? – Uncomfortable

I can't imagine how retrieving a whole row would be significantly slower than a single column. Your database schema terrifies me! But fixing it is probably out of the question I assume. Hopefully someone else will answer. Spring JPA is incredibly flexible and I'd think you could do this easily with a custom @Query. But I've never done it personally – Ence

Look, this is not a problem with database. Can you imagine that have you for instance some proxy between your application and your database. Do you see the diffrent when you want transfer a sparse object for example with two fields and when you want to transfer a object with many relationship and you cannot use a lazy fetching? This is the problem. – Uncomfortable

Answer 1

I solved the problem.

(As a result we will have a sparse Customer object only with id and name)

Define their own repository:

public interface SparseCustomerRepository {
    List<Customer> findAllWithNameOnly(Specification<Customer> spec);
}

And an implementation (remember about suffix - Impl as default)

@Service
public class SparseCustomerRepositoryImpl implements SparseCustomerRepository {
    private final EntityManager entityManager;

    @Autowired
    public SparseCustomerRepositoryImpl(EntityManager entityManager) {
        this.entityManager = entityManager;
    }

    @Override
    public List<Customer> findAllWithNameOnly(Specification<Customer> spec) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> tupleQuery = criteriaBuilder.createTupleQuery();
        Root<Customer> root = tupleQuery.from(Customer.class);
        tupleQuery.multiselect(getSelection(root, Customer_.id),
                getSelection(root, Customer_.name));
        if (spec != null) {
            tupleQuery.where(spec.toPredicate(root, tupleQuery, criteriaBuilder));
        }

        List<Tuple> CustomerNames = entityManager.createQuery(tupleQuery).getResultList();
        return createEntitiesFromTuples(CustomerNames);
    }

    private Selection<?> getSelection(Root<Customer> root,
            SingularAttribute<Customer, ?> attribute) {
        return root.get(attribute).alias(attribute.getName());
    }

    private List<Customer> createEntitiesFromTuples(List<Tuple> CustomerNames) {
        List<Customer> customers = new ArrayList<>();
        for (Tuple customer : CustomerNames) {
            Customer c = new Customer();
            c.setId(customer.get(Customer_.id.getName(), Long.class));
            c.setName(customer.get(Customer_.name.getName(), String.class));
            c.add(customer);
        }
        return customers;
    }
}

Uncomfortable

Answer 2

Why not using the @Query annotation?

@Query("select p.id from #{#entityName} p")
List<Long> getAllIds();

The only disadvantage I see is when the attribute id changes, but since this is a very common name and unlikely to change (id = primary key), this should be ok.

Ovipositor

or just @Query("select id from #{#entityName}") – Firstnighter

does it work with specification? – Nablus

@Emil Terman No. Everything in the @Query annotation is the SQL query that will be executed, therefore whatever SQL your specification is producing, it will be ignored. – Alliaceous

Answer 3

This is now supported by Spring Data using Projections:

interface SparseCustomer {  

  String getId(); 

  String getName();  
}

Than in your Customer repository

List<SparseCustomer> findAll(Specification<Customer> spec);

EDIT:
As noted by Radouane ROUFID Projections with Specifications currently doesn't work beacuse of bug.

But you can use specification-with-projection library which workarounds this Spring Data Jpa deficiency.

Clunk

Does this actually work for this use case? It seems java can't distinguish between this and the original method in JpaSpecificationExecutor<OriginalEntity> extended by the repository and I don't know which name to give the method instead. – Hamsun

Projections does not work using specifications. JpaSpecificationExecutor only return a List typed with the aggregated root managed by the repository ( List<T> findAll(Specification<T> var1); ) – Carbide

Specification with Projection now is supported -> in Spring Data 3.0.0 github.com/spring-projects/spring-data-jpa/issues/… – Billie

Answer 4

Unfortunately Projections does not work with specifications. JpaSpecificationExecutor return only a List typed with the aggregated root managed by the repository ( List<T> findAll(Specification<T> var1); )

An actual workaround is to use Tuple. Example :

    @Override
    public <D> D findOne(Projections<DOMAIN> projections, Specification<DOMAIN> specification, SingleTupleMapper<D> tupleMapper) {
        Tuple tuple = this.getTupleQuery(projections, specification).getSingleResult();
        return tupleMapper.map(tuple);
    }

    @Override
    public <D extends Dto<ID>> List<D> findAll(Projections<DOMAIN> projections, Specification<DOMAIN> specification, TupleMapper<D> tupleMapper) {
        List<Tuple> tupleList = this.getTupleQuery(projections, specification).getResultList();
        return tupleMapper.map(tupleList);
    }

    private TypedQuery<Tuple> getTupleQuery(Projections<DOMAIN> projections, Specification<DOMAIN> specification) {

        CriteriaBuilder cb = entityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> query = cb.createTupleQuery();

        Root<DOMAIN> root = query.from((Class<DOMAIN>) domainClass);

        query.multiselect(projections.project(root));
        query.where(specification.toPredicate(root, query, cb));

        return entityManager.createQuery(query);
    }

where Projections is a functional interface for root projection.

@FunctionalInterface
public interface Projections<D> {

    List<Selection<?>> project(Root<D> root);

}

SingleTupleMapper and TupleMapper are used to map the TupleQuery result to the Object you want to return.

@FunctionalInterface
public interface SingleTupleMapper<D> {

    D map(Tuple tuple);
}

@FunctionalInterface
public interface TupleMapper<D> {

    List<D> map(List<Tuple> tuples);

}

Example of use :

        Projections<User> userProjections = (root) -> Arrays.asList(
                root.get(User_.uid).alias(User_.uid.getName()),
                root.get(User_.active).alias(User_.active.getName()),
                root.get(User_.userProvider).alias(User_.userProvider.getName()),
                root.join(User_.profile).get(Profile_.firstName).alias(Profile_.firstName.getName()),
                root.join(User_.profile).get(Profile_.lastName).alias(Profile_.lastName.getName()),
                root.join(User_.profile).get(Profile_.picture).alias(Profile_.picture.getName()),
                root.join(User_.profile).get(Profile_.gender).alias(Profile_.gender.getName())
        );

        Specification<User> userSpecification = UserSpecifications.withUid(userUid);

        SingleTupleMapper<BasicUserDto> singleMapper = tuple -> {

            BasicUserDto basicUserDto = new BasicUserDto();

            basicUserDto.setUid(tuple.get(User_.uid.getName(), String.class));
            basicUserDto.setActive(tuple.get(User_.active.getName(), Boolean.class));
            basicUserDto.setUserProvider(tuple.get(User_.userProvider.getName(), UserProvider.class));
            basicUserDto.setFirstName(tuple.get(Profile_.firstName.getName(), String.class));
            basicUserDto.setLastName(tuple.get(Profile_.lastName.getName(), String.class));
            basicUserDto.setPicture(tuple.get(Profile_.picture.getName(), String.class));
            basicUserDto.setGender(tuple.get(Profile_.gender.getName(), Gender.class));

            return basicUserDto;
        };

        BasicUserDto basicUser = findOne(userProjections, userSpecification, singleMapper);

I hope it helps.

Carbide

Answer 5

@Autowired private EntityManager        entityManager;

protected List<Long> getIDs() {

    final CriteriaBuilder criteriaBuilder    = entityManager.getCriteriaBuilder();
    final CriteriaQuery<Long> criteriaQuery  = criteriaBuilder.createQuery(Long.class);
    final Root<Customer> root                = criteriaQuery.from(Customer.class);
    criteriaQuery.where(...);
    final CriteriaQuery<Long> select         = criteriaQuery.select(root.get("id"));

    return entityManager.createQuery(select).getResultList();
}

Immensurable