how to select multiple objects in spring data jpa using specifications

Question

I have following @Query, that is perfectly working fine. But now I have a scenario where there screen needs a filter, that will add some where clauses to the query.

@Query

("
        SELECT 
            ef, ed, ea
        FROM EntityA ea
            JOIN EntityB eb
            JOIN EntityC ec
            JOIN EntityD ed
            JOIN EntityE ee
            JOIN EntityF ef
        WHERE
            TRUNC(ee.date) = TRUNC(:date)

        -- conditions based on screen filter parameters
        AND ef.amount = :amount
        AND LOWER(ec.name) LIKE LOWER('%' || :name || '%')
        AND ec.projectId = :projectId
        AND ed.divisionId = :divisionId

")

Found that there is a good Specifications support to dynamically create the queries as per requirement.

But not sure how do I select multiple objects ef, ed & ea in one go using Specifications, otherwise I have to write 4 more queries to return result based on filter criteria.

N.B. Not using eager loading for performance reasons as entities are used by multiple services.

Answer 1

I was able to achieve this by implementing Custom Repositories, auto-wiring the EntityManager in that implementation class and then building the final JPQL based on parameters passed. A good example is in Eugen's blog.

Previously I had following structure

public interface EntityARepository extends JpaRepository<EntityA, Long> {
    @Query(...)
    List<EntityA> findAllBy(Date filterDate, Long amount, String name, Long projectId, Long divisionId);
}

public interface EntityAService {
    List<Object[]> findAllBy(Date filterDate, Long amount, String name, Long projectId, Long divisionId);
}

@Service
public class EntityAServiceImpl implements EntityAService {

    @Autowired
    EntityARepository entityARepository;

    @Override
    public List<Object[]> findAllBy(Date filterDate, Long amount, String name, Long projectId, Long divisionId) {
        ...
        ...
        ...
    }
}

And by using Custom Repositories all becomes like

public interface EntityACustomRepository {
    List<Object[]> findAllBy(Date filterDate, Long amount, String name, Long projectId, Long divisionId);
}

public interface EntityARepository extends JpaRepository<EntityA, Long> {
    //@Query(...)
    //List<EntityA> findAllBy(Date filterDate, Long amount, String name, Long projectId, Long divisionId);
}

@Repository
public class EntityACustomRepositoryImpl implements EntityACustomRepository {

    // autowiring entityManager helped to create and execute dynamic jpql
    @Autowired
    EntityManager entityManager;

    public List<Object[]> findAllBy(Date filterDate, Long amount, String name, Long projectId, Long divisionId) {
        String jpql = "SELECT " +
            " ef, ed, ea " +
        " FROM EntityA ea " +
            " JOIN EntityB eb " +
            " JOIN EntityC ec " +
            " JOIN EntityD ed " +
            " JOIN EntityE ee " +
            " JOIN EntityF ef " +
        " WHERE " +
            " TRUNC(ee.date) = TRUNC(:date) "
        ;

        //conditions based on screen filter parameters
        if(amount!=null && amount>0L) {
            jpql += " AND ef.amount = :amount ";
        }
        if(name!=null && name.trim().length()>0) {
            jpql += " AND LOWER(ec.name) LIKE LOWER('%' || :name || '%') ";
        }
        if(projectId!=null && projectId>0L) {
            jpql += " AND ec.projectId = :projectId ";
        }
        if(divisionId!=null && divisionId>0L) {
            jpql += " AND ed.divisionId = :divisionId ";
        }

        Query query = entityManager.createQuery(jpql);
        query.setParameter("date", filterDate);

        if(amount!=null && amount>0L) {
            query.setParameter("amount", amount);
        }
        if(name!=null && name.trim().length()>0) {
            query.setParameter("name", name);
        }
        if(projectId!=null && projectId>0L) {
            query.setParameter("projectId", projectId);
        }
        if(divisionId!=null && divisionId>0L) {
            query.setParameter("divisionId", divisionId);
        }

        return query.getResultList();
    }

}

public interface EntityAService {
    List<Object[]> findAllBy(Date filterDate, Long amount, String name, Long projectId, Long divisionId);
}

@Service
public class EntityAServiceImpl implements EntityAService {

    @Autowired
    EntityARepository entityARepository;

    @Override
    public List<Object[]> findAllBy(Date filterDate, Long amount, String name, Long projectId, Long divisionId) {
        return entityARepository.findAllBy(filterDate, amount, name, projectId, divisionId);
    }

}

Answer 2

It is possible to query on child entities using the Specification API if you map them with JPA, e.g. with @OneToMany.

@Entity
@Table(...)
public class EntityA {
    // Omitting fields

    @OneToMany(...)
    private List<EntityB> bList = new ArrayList<>();

}

public class EntityASpecification implements Specification<EntityA> {

    private SearchCriteriaValueClass criteria;

    public EntityASpecification(SearchCriteriaValueClass criteria) {
        this.criteria = criteria;
    }

    @Override
    public Predicate toPredicate(Root<EntityA> root, CriteriaQuery<?> query, CriteriaBuilder builder) {

        ListJoin<EntityA, EntityB> pathToEntityB = root.join(EntityA_.bList, JoinType.INNER);
        Predicate amountInBIsEqual = builder.equal(pathToEntityB.get(EntityB_.amount), criteria.getAmount);

        Path<SomeEntityAField> pathToAField = root.get(EntityA_.someAField);
        Predicate someValueInA = pathToAField.in(criteria.getCollectionForAFieldToBeIn());

        query.distinct(true);
        return builder.and(amountInBIsEqual, someValueInA);
    }
}

Obviously a Specification is defined for one Entity and can only return instances of that. I don't see any (safe and effective) way of returning instances of F, D and A in one method call other than them being connected in a has-a relationship.

Answer 3

Specifications are only used for dynamically creating a where clause.

If you also need to control the select clause I recommend using the JPA Criteria API inside a custom method of your repository.