Select Query with Dynamic Columns in Spring Data JPA

Question

I have a requirement where the I receive the values from the Table A. Here Table A stores column details of TableB.

select columnList from TableA => Id1, Id2, Id3, Id4, Id5..

Using the result I need to query from Table B.

Following are the cases

Select Id1, Id2, Id3 from TableB.
Select Id4, Id5 from TableB.
Select Id6, Id7, Id8 from TableB.

Like this case will grow. I would like to know how to achieve this one in Spring Data JPA. It is something like dynamic projections in Spring Data JPA.

Note: Column Names stored in TableA is used in the TableB.

Reason: If I could able to achieve this, it would be easy to pull the data which only what I need.

Need to know, whether we can acheive this in Spring Data JPA or Not? If possible, then how we can.

Can we use CriteriaQuery and CriteriaBuilder.??? Based on the number of column passed, the select query will construct with those values passed in the method call. I can pass that as list and fetch the results from this method call. entityManager.createQuery(criteriaQuery).getResultList();

I need a better solution than this.

public void getTableBValues(final List<String> columnList) {

    final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();        
    final CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
    final Root<TableB> root = criteriaQuery.from(TableB.class);

    // Construct the Selection List to get required columns from TableB
    final List<Selection<?>> pathList = columnList.stream().map(p -> {
        final Selection<Object> path = root.get(p);
        return path;
    }).collect(Collectors.toList());

    criteriaQuery.multiselect(pathList);
    // result will be stored in the result list. 
    final List<Tuple> resultList = entityManager.createQuery(criteriaQuery).getResultList();
    entityManager.close();

}

This looks like a very bad idea! JPA is an Object-Relational-Mapping for Java. Objects in Java don't have dynamic properties. To what kind of object would you want to map these? — GreyFairer
Using JPA Specification your problem can be solve. See my ans here stackoverflow.com/questions/61062078/… — Eklavya
@AbinashGhosh: Is your code generates the where condition based on the given values. — vasanthraj devaraju
@GreyFairer: Yes I agree, to achieve few functionality we are deviating from the core. — vasanthraj devaraju
My Intention is to achieve the dynamic projection for the Table. The main resaon for this is to reduced the number of column to be selected during the select query. Because I don't need to pull un wanted column data from the table B. — vasanthraj devaraju

GreyFairer GreyFairer · Accepted Answer · 2020-04-21T11:58:20

If you only have a limited number of different usecases, so it's not completely dynamic, you can use projections.

There are a lot of ways to do this, see .e.g. https://www.bytestree.com/spring/spring-data-jpa-projections-5-ways-return-custom-object/

Perhaps the easiest way with Spring JPA is something like this:

@Repository
public interface ClassBRepository extends JpaRepository<ClassB, Long> {

     @Query("select b.id1 as id1, b.id2 as id2, b.id3 as id3 from ClassB b")
     List<Map<String, Object>> findForUseCase123();

     @Query("select b.id4 as id4, b.id5 as id5 from ClassB b")
     List<Map<String, Object>> findForUseCase45();

     ...     
}

This is not completely dynamic, but again, if you need completely dynamic mapping, Object-Relational-Mapping is not fit for that.

Select Query with Dynamic Columns in Spring Data JPA

1 Answers