spring 弹簧数据 JPA。如何仅从 findAll() 方法中获取 ID 列表

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

提问by user6778654

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.

我尝试使用 Spring Data JPA 并准备了一个存储库。

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:

但是当我调用带有对象规范的方法 findAll() 时，会出现性能问题，因为对象非常大。我知道这是因为当我调用这样的方法时：

@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.

问题是我需要调用带有客户规范的 findAll 方法，这就是为什么我不能使用返回对象数组列表的方法。

How to write a method to finding all customers with specifications for Customer entity but which returns only an IDs.

如何编写一种方法来查找具有 Customer 实体规范但仅返回 ID 的所有客户。

like this:

像这样：

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

• I cannot use in this case pagination.

• 在这种情况下我不能使用分页。

Please help.

请帮忙。

采纳答案by user6778654

I solved the problem.

我解决了这个问题。

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

（因此，我们将有一个只有 id 和 name 的稀疏 Customer 对象）

Define their own repository:

定义自己的存储库：

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

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

和一个实现（记住后缀 - Impl 作为默认值）

@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;
    }
}

回答by eav

Why not using the @Queryannotation?

为什么不使用@Query注解？

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

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

我看到的唯一缺点是属性id更改时，但由于这是一个非常常见的名称并且不太可能更改（id = 主键），所以这应该没问题。

回答by Ondrej Bozek

This is now supported by Spring Data using Projections:

Spring Data 现在使用Projections支持这一点：

interface SparseCustomer {  

  String getId(); 

  String getName();  
}

Than in your Customerrepository

比在您的Customer存储库中

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

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

编辑：
正如Radouane ROUFID预测与规格指出目前没有工作怎么一回事，因为的错误。

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

但是您可以使用带有投影的规范库来解决此 Spring Data Jpa 缺陷。

回答by Radouane ROUFID

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

不幸的是，投影不适用于规范。 JpaSpecificationExecutor仅返回使用存储库管理的聚合根键入的列表 ( 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 Projectionsis a functional interface for root projection.

哪里Projections是根投影的功能接口。

@FunctionalInterface
public interface Projections<D> {

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

}

SingleTupleMapperand TupleMapperare used to map the TupleQueryresult to the Object you want to return.

SingleTupleMapper并TupleMapper用于将TupleQuery结果映射到要返回的对象。

@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.

我希望它有帮助。