JpaSpecificationExecutorWithProjection methods create 'select *' type of queries

[eugenekup]

Hi @pramoth,

Thank you for your work.

This is more of a question rather then issue potentially.

Using projection interfaces as part e.g. CrudRepository adds an important benefit of Spring/Hibernate creating 'select' queries fetching only those columns that are required by the projection.

However, when using JpaSpecificationExecutorWithProjection methods the 'select all' type of queries are created resulting in unnecessary data being fetched from the DB.

Is this by design or have I missed anything in my configuration?

Thank you in advance, Eugene

[pramoth]

Hi @eugenekup That is limitation of this library. It do projection on a beans in memory rather than in sql query. If you want to optimize by select some column you need to use JpaEntityGraph to limit set of columns/entity as I show on readme page.

[elFarto]

I also came across this issue, but I found an alternate way of resolving it. This method actually lets me change the return object. It is however, quite the hack:

public class SelectionInterceptor
 {
    private final Class<?> clz;
    private final String fields[];
    private CriteriaBuilder cb;
    private CriteriaQuery cq;
    private Root root;

    public SelectionInterceptor(Class<?> clz, String ... fields)
    {
        this.clz = clz;
        this.fields = fields;
    }

    public void intercept()
    {
        Expression[] exps = new Expression[fields.length];
        for (int i = 0; i < fields.length; i++)
        {
            exps[i] = root.get(fields[i]);
        }
        cq.select(cb.construct(clz, exps));
    }

    public Pageable getWrappedPageable(Pageable pageable)
    {
        return PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), getWrappedSort(pageable.getSort()));
    }

    public Sort getWrappedSort(final Sort sort)
    {
        return new Sort(sort.stream().collect(Collectors.toList()))
        {
            @Override
            public boolean isSorted()
            {
                intercept();
                return super.isSorted();
            }
        };
    }

    public <S> Specification<S> getWrappedSpec(final Specification<S> spec)
    {
        return (root, cq, cb) ->
        {
            this.cb = cb;
            this.cq = cq;
            this.root = root;
            return spec.toPredicate(root, cq, cb);
        };
    }
}

It can then be call via the JpaSpecificationExecutor.findAll(Specification, Pageable) method like:

SelectionInterceptor si = new SelectionInterceptor(InboxEntry.class, "id", "received");
Page p = repo.findAll(si.getWrappedSpec(user(user).and(searchSpec)), si.getWrappedPageable(pageable));

[pramoth]

@elFarto Thank you for the Great Idea! I will try to implement it soon.

[pramoth]

I just complete this issue by borrow code from spring data jpa project.

434d16b619fbdde56ecb952a656badd1ea0b1614

please try and let me know for any problem.

[zhengdai]

I just complete this issue by borrow code from spring data jpa project.

434d16b619fbdde56ecb952a656badd1ea0b1614

please try and let me know for any problem.

when used in one to many，it may produce multi record with the same left table record