Spring Boot中如何通过单条SQL查询统计各类型客户数量

Hey there! Let's break down why your original query isn't working and get you the correct results you need.

What's Wrong with the Original Query? #

Your current @Query is trying to count properties like p.gold and p.silver, but your Customer entity only has a type field—those gold/silver/bronze/usual properties don't exist! That's why you're not getting the right numbers.

Solution 1: Grouped Query (Flexible & Simple) #

The most straightforward way to count each customer type is to group by the type field and count the entries per group. This returns a map where keys are the type names and values are their counts:

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long> {
    @Query("SELECT p.type, COUNT(p) FROM Customer p GROUP BY p.type")
    Map<String, Long> countCustomerTypes();
}

When you call this method, you'll get exactly what you need:

• Gold → 3
• Silver → 2
• Bronze → 1
• Usual → 1

Bonus: This automatically handles any new customer types you add later—no need to update the query!

Solution 2: Custom DTO with Conditional Sum (Matches Your Original Approach) #

If you specifically want a single MyModel object with each type's count as a field (like your original selectTotals() method), use a CASE WHEN clause with SUM() to calculate each type's count:

First, make sure your MyModel has a constructor that matches the order of values in the query:

public class MyModel {
    private Long gold;
    private Long silver;
    private Long bronze;
    private Long usual;

    // Constructor matching the query's result order
    public MyModel(Long gold, Long silver, Long bronze, Long usual) {
        this.gold = gold;
        this.silver = silver;
        this.bronze = bronze;
        this.usual = usual;
    }

    // Add getters and setters as needed
}

Then update your repository query to use JPQL's constructor syntax:

@Repository
public interface CustomerRepository extends JpaRepository<Customer, Long> {
    @Query("SELECT new com.yourpackage.MyModel(" +
           "SUM(CASE WHEN p.type = 'Gold' THEN 1 ELSE 0 END), " +
           "SUM(CASE WHEN p.type = 'Silver' THEN 1 ELSE 0 END), " +
           "SUM(CASE WHEN p.type = 'Bronze' THEN 1 ELSE 0 END), " +
           "SUM(CASE WHEN p.type = 'Usual' THEN 1 ELSE 0 END)) " +
           "FROM Customer p")
    MyModel selectTotals();
}

This query checks each customer's type field, adds 1 to the corresponding sum if it matches, and returns all totals in one MyModel instance.

Why This Works #

Instead of trying to count non-existent properties, we're using conditional logic to tally up each type value. The grouped query is ideal for flexibility, while the DTO approach gives you the structured result you initially expected.

内容的提问来源于stack exchange，提问作者ali