Translate complex HQL query / Spring JPA

[Alessandro Cocco]

Hi guys,
I'm adding Spring JPA on a pre-existing Hibernate project. I moved many of the queries into XXXRepository with success but now I have a problem with this HQL query:

String HQL_QUERY_TOTALI = "select inst.id.settoriale, execution.id.status, count(*) as counter ";

// livello ("level") is a input parameter

if ( livello >= 1 )
{
    HQL_QUERY_TOTALI += ", inst.id.piano";
}
if ( livello >= 2 )
{
    HQL_QUERY_TOTALI += ", inst.id.applicazione";
}
if ( livello >= 3 )
{
    HQL_QUERY_TOTALI += ", inst.id.jobName, inst.id.jobInstanceId ";
}

HQL_QUERY_TOTALI += " from BatchJobInstanceV as inst, BatchJobExecutionFullV as execution "
                    + " where inst.id.jobInstanceId = execution.id.jobInstanceId and inst.id.jobName "
                    + " not in ( select param.id.chain from BatchParameters param ) ";

if ( livello == 1 )
{
    HQL_QUERY_TOTALI += "and inst.id.settoriale = ? ";
}
if ( livello == 2 )
{
    HQL_QUERY_TOTALI += "and inst.id.settoriale = ? AND inst.id.piano = ? ";
}
if ( livello >= 3 )
{
    HQL_QUERY_TOTALI += "and inst.id.settoriale = ? AND inst.id.piano = ? AND inst.id.applicazione = ? ";
}

if ( null != startTime )
{
    HQL_QUERY_TOTALI += " and execution.id.startTime >= ? ";
}

if ( null != endTime )
{
    HQL_QUERY_TOTALI += " and execution.id.endTime <= ? ";
}

HQL_QUERY_TOTALI += " group by inst.id.settoriale, execution.id.status ";

if ( livello >= 1 )
{
    HQL_QUERY_TOTALI += ", inst.id.piano ";
}
if ( livello >= 2 )
{
    HQL_QUERY_TOTALI += ", inst.id.applicazione ";
}
if ( livello >= 3 )
{
    HQL_QUERY_TOTALI += ", inst.id.jobName, inst.id.jobInstanceId ";
}

HQL_QUERY_TOTALI += " order by inst.id.settoriale asc ";

if ( livello >= 1 )
{
    HQL_QUERY_TOTALI += ", inst.id.piano asc ";
}
if ( livello >= 2 )
{
    HQL_QUERY_TOTALI += ", inst.id.applicazione asc ";
}
if ( livello >= 3 )
{
    HQL_QUERY_TOTALI += ", inst.id.jobName asc ";
}

HQL_QUERY_TOTALI += ", execution.id.status asc ";

// return a List<Object[]> with results

How can I bring this query to Spring JPA?
I tried with Specification without success, the documentation seems to be limited to very simple cases

Can you give me some advice? Thanks!

[Alessandro Cocco]

I rewrite the query using CriteriaBuilder.

Is there a better way to do the same thing? Thanks

public class JpaQueryBuilder
{
    @Autowired
    private EntityManagerFactory entityManagerFactory;

    public void setEntityManagerFactory(EntityManagerFactory entityManagerFactory)
    {
        this.entityManagerFactory = entityManagerFactory;
    }

    public TypedQuery<Object[]> buildRecuperaTotaliQuery(Date startTime,
                                                         Date endTime,
                                                         TreeNodeSummary parent,
                                                         int livello)
    {
        String settoriale = null;
        String piano = null;
        String applicazione = null;

        if ( livello == 1 )
        {
            settoriale = parent.getNode();
        }
        else if ( livello == 2 )
        {
            settoriale = parent.getParent().getNode();
            piano = parent.getNode();
        }
        else if ( livello >= 3 )
        {
            settoriale = parent.getParent().getParent().getNode();
            piano = parent.getParent().getNode();
            applicazione = parent.getNode();
        }

        CriteriaBuilder builder = entityManagerFactory.getCriteriaBuilder();
        CriteriaQuery<Object[]> query = builder.createQuery(Object[].class);

        Root<BatchJobInstanceV> batchJobInstanceVRoot = query.from(BatchJobInstanceV.class);
        Root<BatchJobExecutionFullV> batchJobExecutionFullVBatch = query.from(BatchJobExecutionFullV.class);

        Path<Object> idJobInstance = batchJobInstanceVRoot.get("id");
        Path<Object> idJobExecutionFull = batchJobExecutionFullVBatch.get("id");

        List<Selection<?>> selections = selectPreparation(livello, builder, idJobInstance, idJobExecutionFull);
        List<Expression<?>> groupingList = groupByPreparation(livello, idJobInstance, idJobExecutionFull);
        List<Order> orderList = orderByPreparation(livello, builder, idJobInstance, idJobExecutionFull);
        List<Predicate> predicates = wherePreparation(query, startTime, endTime, livello, builder, idJobInstance, idJobExecutionFull);

        CriteriaQuery<Object[]> criteriaQuery = query.multiselect(selections)
                                                     .where(predicates.toArray(new Predicate[] {}))
                                                     .groupBy(groupingList)
                                                     .orderBy(orderList);
        TypedQuery<Object[]> typedQuery = entityManagerFactory.createEntityManager().createQuery(criteriaQuery);
        setUpParameters(startTime, endTime, livello, settoriale, piano, applicazione, typedQuery);

        return typedQuery;
    }

    private void setUpParameters(Date startTime,
                                 Date endTime,
                                 int livello,
                                 String settoriale,
                                 String piano,
                                 String applicazione,
                                 TypedQuery<Object[]> typedQuery)
    {
        if ( livello >= 1 )
        {
            typedQuery.setParameter("settoriale", settoriale);
        }

        if ( livello >= 2 )
        {
            typedQuery.setParameter("piano", piano);
        }

        if ( livello >= 3 )
        {
            typedQuery.setParameter("applicazione", applicazione);
        }

        if ( startTime != null )
        {
            typedQuery.setParameter("startTime", new Timestamp(startTime.getTime()));
        }

        if ( endTime != null )
        {
            typedQuery.setParameter("endTime", new Timestamp(endTime.getTime()));
        }
    }

    private List<Predicate> wherePreparation(CriteriaQuery<Object[]> query,
                                             Date startTime,
                                             Date endTime,
                                             int livello,
                                             CriteriaBuilder builder,
                                             Path<Object> idJobInstance,
                                             Path<Object> idJobExecutionFull)
    {
        List<Predicate> predicates = new ArrayList<Predicate>();

        Predicate sameJobInstanceId = builder.equal(idJobInstance.<String> get("jobInstanceId"),
                                                    idJobExecutionFull.<String> get("jobInstanceId"));

        Subquery<String> subquery = query.subquery(String.class);

        Root<BatchParameters> batchParametersRoot = subquery.from(BatchParameters.class);
        Path<Object> idParameters = batchParametersRoot.get("id");

        Predicate notIn = builder.not(builder.in(idJobInstance.<String> get("jobName"))
                                             .value(subquery.select(idParameters.<String> get("chain"))
                                                            .distinct(true)));
        predicates.add(builder.and(sameJobInstanceId, notIn));

        ParameterExpression<String> settorialeParam = builder.parameter(String.class, "settoriale");
        ParameterExpression<String> pianoParam = builder.parameter(String.class, "piano");
        ParameterExpression<String> applicazioneParam = builder.parameter(String.class, "applicazione");

        Predicate equalsToSettoriale = builder.equal(idJobInstance.<String> get("settoriale"), settorialeParam);
        Predicate equalsToPiano = builder.equal(idJobInstance.<String> get("piano"), pianoParam);
        Predicate equalsToApplicazione = builder.equal(idJobInstance.<String> get("applicazione"), applicazioneParam);

        ParameterExpression<Date> starTimeParam = builder.parameter(Date.class, "startTime");
        Predicate startTimeGreaterThanOrEqualsTo = builder.greaterThanOrEqualTo(idJobExecutionFull.<Date> get("startTime"), starTimeParam);

        ParameterExpression<Date> endTimeParam = builder.parameter(Date.class, "endTime");
        Predicate startTimeLessThanOrEqualsTo = builder.lessThanOrEqualTo(idJobExecutionFull.<Date> get("startTime"), endTimeParam);

        if ( livello >= 1 )
        {
            predicates.add(equalsToSettoriale);
        }

        if ( livello >= 2 )
        {
            predicates.add(equalsToPiano);
        }

        if ( livello >= 3 )
        {
            predicates.add(equalsToApplicazione);
        }

        if ( startTime != null )
        {
            predicates.add(startTimeGreaterThanOrEqualsTo);
        }

        if ( endTime != null )
        {
            predicates.add(startTimeLessThanOrEqualsTo);
        }

        return predicates;
    }

    private List<Order> orderByPreparation(int livello,
                                           CriteriaBuilder builder,
                                           Path<Object> idJobInstance,
                                           Path<Object> idJobExecutionFull)
    {
        List<Order> orderList = new ArrayList<Order>();
        orderList.add(builder.asc(idJobInstance.<String> get("settoriale")));

        if ( livello >= 1 )
        {
            orderList.add(builder.asc(idJobInstance.<String> get("piano")));
        }

        if ( livello >= 2 )
        {
            orderList.add(builder.asc(idJobInstance.<String> get("applicazione")));
        }

        if ( livello >= 3 )
        {
            orderList.add(builder.asc(idJobInstance.<String> get("jobName")));
        }

        orderList.add(builder.asc(idJobExecutionFull.<String> get("status")));

        return orderList;
    }

    private List<Expression<?>> groupByPreparation(int livello,
                                                   Path<Object> idJobInstance,
                                                   Path<Object> idJobExecutionFull)
    {
        List<Expression<?>> groupingList = new ArrayList<Expression<?>>();
        groupingList.add(idJobInstance.<String> get("settoriale"));
        groupingList.add(idJobExecutionFull.<String> get("status"));

        if ( livello >= 1 )
        {
            groupingList.add(idJobInstance.<String> get("piano"));
        }

        if ( livello >= 2 )
        {
            groupingList.add(idJobInstance.<String> get("applicazione"));
        }

        if ( livello >= 3 )
        {
            groupingList.add(idJobInstance.<String> get("jobName"));
            groupingList.add(idJobInstance.<String> get("jobInstanceId"));
        }

        return groupingList;
    }

    private List<Selection<?>> selectPreparation(int livello,
                                                 CriteriaBuilder builder,
                                                 Path<Object> idJobInstance,
                                                 Path<Object> idJobExecutionFull)
    {
        List<Selection<?>> selections = new ArrayList<Selection<?>>();
        selections.add(idJobInstance.<String> get("settoriale"));
        selections.add(idJobExecutionFull.<String> get("status"));
        selections.add(builder.count(idJobInstance.<String> get("jobInstanceId")));

        if ( livello >= 1 )
        {
            selections.add(idJobInstance.<String> get("piano"));
        }

        if ( livello >= 2 )
        {
            selections.add(idJobInstance.<String> get("applicazione"));
        }

        if ( livello >= 3 )
        {
            selections.add(idJobInstance.<String> get("jobName"));
            selections.add(idJobInstance.<String> get("jobInstanceId"));
        }

        return selections;
    }
}