In autumn, there is no sun in Beijing as always. It is not clear whether the sky is cloud or fog or what strange things are. Buildings are black and white to prove their own outlines. I can't see the edge in a foggy day, just like I need to use Spring Data Jpa to implement a very small SQL. I can't see the effect I want when I write it.

The SQL you want to implement is very simple, which is a simple statistic:

SELECT
    activity_code,
    activity_name,
    sum(contact_user_nums),
    conversion_step_code,
    conversion_step
FROM
    epm_estimate_main
WHERE
    activity_code ='1'
GROUP BY
    conversion_step_code
ORDER BY
    contact_user_nums DESC;

Because of some rules and regulations, I finally chose to use the implementation of jpa. I thought that I could go home and play the game in five minutes after writing this thing, so I came up and wrote:

However, the results of the query are not very good-looking: first, I did not get the desired sum; second, the result set of the query includes all fields;
Fortunately, grouping and sorting are implemented as expected.

First, go to a search engine B to find it. There are four ways to find it:
The first one: JPA to XXX, mybatis real fragrance;
Second, JPQL is not available;
Third, create CriteriaBuilder, CriteriaQuery and Root by yourself;
Fourth: don't know how to write, wait for thigh.

After searching in China, I went to a certain engine in G and flipped it over. It seemed that foreigners asked me more specific questions, but they didn't find the answer I wanted (the fourth level in bulk was still laborious);

Looking at some online questions and answers, it seems that there is a rule. It seems that the articles that use findAll method to implement the Specification interface all have this problem, but the little partners who use EntityManager to implement it themselves don't pay much attention to it.

Naked discrimination, determined to use this findAll to achieve one.

The sky outside the window is getting darker and darker, but I think such a simple query has wasted my time in opening the dark, so I began to doubt myself. No matter how I operate on CriteriaQuery.multiselect, the result set queried contains all fields, so I began to wonder why multiselect didn't work?

What is the status of the interface of jpaspecification executor?

Find the API for this interface: there are two implementation classes (where QuerydslJpaRepository shows expiration)

The rest of the implementation classes SimpleJpaRepository Find the corresponding findAll method in. It calls other methods in this class. Find it all the way down. At the end of the method call, find the felicity:

Finally, all the fields defined by me are overwritten by query.select(root)!

It's late and the lights are beginning to shine. I wanted to rewrite this method at last, but looking down at the bus station, the background of the billboard seems to be a bit of new year.

That code eventually becomes like this (Spring Boot automatically registers the EntityManager):

        CriteriaBuilder builder = entityManager.getCriteriaBuilder();
        CriteriaQuery<EstimateDO> query = builder.createQuery(EstimateDO.class);
        Root<EstimateDO> root = query.from(EstimateDO.class);

        query
                .multiselect(
                        root.get("activityCode").as(String.class).alias("activityCode"),
                        root.get("activityName").as(String.class).alias("activityName"),
                        builder.sum(root.get("contactUserNums").as(Long.class)).alias("contactUserNums"),
                        root.get("conversionStepCode").as(String.class).alias("conversionStepCode"),
                        root.get("conversionStep").as(String.class).alias("conversionStep")
                );

        List<Predicate> predicates = new ArrayList();
        predicates.add(
                builder.equal(root.get("activityCode").as(String.class), estimateDO.getActivityCode())
        );

        if (!StringUtils.isEmpty(estimateDO.getContactCode())) {
            predicates.add(
                    builder.equal(root.get("contactCode").as(String.class), estimateDO.getContactCode())
            );
        }

        query
                .where(predicates.toArray(new Predicate[predicates.size()]))
                .groupBy(root.get("conversionStepCode"))
                .orderBy(builder.desc(root.get("contactUserNums")));

        List<EstimateDO> list = entityManager.createQuery(query).getResultList();

My determination is worthless.