CODEWITHSUNDEEP
javaspring-data-jpaaggregate-functionshibernate-criteria
user3145373 ツ
user3145373 ツ

Reputation: 8146

Spring JPA CriteriaQuery child entity columns

I am trying to make some dynamic mechanism to fetch data from db using criteria query based on given class, specification, select columns, aggregated columns and group by columns.

Here's the code which is working fine:

    public <T> List<T> findDataByConfiguration(Specification<T> specification, 
        Class clazz, List<String> selectColumns, 
        List<String> aggregationColumns, List<String> groupByColumns) {
        CriteriaBuilder criteriaBuilder = this.entityManager.getCriteriaBuilder();
        CriteriaQuery<Tuple> query = criteriaBuilder.createTupleQuery();
        Root<T> root = query.from(clazz);

        List<Selection<?>> multiSelectColumns = new ArrayList<>();
        for (String s : selectColumns) {
            multiSelectColumns.add(root.get(s).alias(s));
        }

        for (String s : aggregationColumns) {
            multiSelectColumns.add(criteriaBuilder.sum(root.get(s)).alias(s));
        }

        List<Expression<?>> groupByColumnsList = new ArrayList<>();
        for (String s : groupByColumns) {
            groupByColumnsList.add(root.get(s));
        }

        query.groupBy(groupByColumnsList);
        query.multiselect(multiSelectColumns);
        query.where(specification.toPredicate(root, query, criteriaBuilder));

        List<Tuple> resultList = this.entityManager.createQuery(query).getResultList();

        // resultList processing
    }

This code is working fine when when I want to fetch data of flat entity class. But when I am trying to add child entiti'es field in select/aggregate/group by then it's throwing an error:

java.lang.IllegalArgumentException: Unable to locate Attribute with the the given name [child.id] on this ManagedType [com.sup.as.data.entity.co.Annual]

// annotations
public class Annual {
    private Long id;
    private String fp;
    private ID iDetails;
    // getter-setter
}

public class ID {
    private Long id;
    private BigDecimal amount;
    // getter-setter

    public String giveMeData() {
        if(id != null && amount != null) {
            return "FILED";
        } else {
            return "NOT FILED";
        }
    }
}

now suppose I want field Annual.id, Annual.fp, SUM(Annual.iDetails.amount), Annual.iDetails.giveMeData

So is it possible to add child entities columns in select/aggregate/group by ? If anyone have done this type of solution then please help me to resolve this.

Upvotes: 1

Views: 2491

Answers (1)

Oleksii Valuiskyi
Oleksii Valuiskyi

Reputation: 2841

If some of column names are dot-separated (for instance, child.id) you have to use root.get("child").get("id") instead of root.get("child.id")

You need method like this

private Path<Object> getPath(Root<?> root, String columnPath) {

    if(!columnPath.contains(".")) {
       return root.get(columnPath);
    }

    String[] levels = columnPath.split(".");  
    Path<T> path = root;
    for(String level : levels) {
        path = path.get(level);
    }

    return path;
}

Then use it for selectColumns, aggregationColumns and groupByColumns this way

for (String s : selectColumns) {
   multiSelectColumns.add(getPath(root, s).alias(s));
}

Upvotes: 2

Related Questions