Announcement Announcement Module
Collapse
No announcement yet.
Translate complex HQL query / Spring JPA Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Translate complex HQL query / Spring JPA

    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:

    Code:
    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!

  • #2
    I rewrite the query using CriteriaBuilder.

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

    Code:
    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;
        }
    }

    Comment

    Working...
    X