如何将JPA createNativeQuery返回的List转换为Map？

方法1：手动转换查询结果为目标Map #

首先修正你代码里的参数绑定顺序错误（SQL中第一个占位符对应startDate，第二个是endDate，第三个是customerId，你当前代码的参数顺序完全搞反了），然后遍历查询返回的List<Object[]>，将每行数据组装成Map：

public Map<String, Long> expenseReport(long customerId, LocalDate startDate, LocalDate endDate) {
    List<Object[]> resultList = em.createNativeQuery("select c.category_name, sum(amount)\n" +
                    "from account as a\n" + 
                    "left join transaction as t on account_from_id = account_id\n" +
                    "left join transaction_to_category ttc on t.transaction_id = ttc.transaction_id\n" +
                    "left join category c on ttc.category_id = c.category_id\n" +
                    "WHERE (t.data_created BETWEEN ? AND ?) AND a.customer_id = ? AND category_name is not null\n" + 
                    "group by c.category_name;")
            .setParameter(1, startDate)
            .setParameter(2, endDate)
            .setParameter(3, customerId)
            .getResultList();

    Map<String, Long> reportMap = new HashMap<>();
    for (Object[] row : resultList) {
        String categoryName = (String) row[0];
        // sum(amount)可能返回BigDecimal，统一转成Long类型
        Long totalAmount = ((Number) row[1]).longValue();
        reportMap.put(categoryName, totalAmount);
    }
    return reportMap;
}

方法2：用JPA Tuple提升可读性（JPA 2.1+支持） #

给SQL字段加上别名，通过Tuple类型直接按别名取数，代码更清晰：

public Map<String, Long> expenseReport(long customerId, LocalDate startDate, LocalDate endDate) {
    List<Tuple> resultList = em.createNativeQuery("select c.category_name as categoryName, sum(amount) as totalAmount\n" +
                    "from account as a\n" + 
                    "left join transaction as t on account_from_id = account_id\n" +
                    "left join transaction_to_category ttc on t.transaction_id = ttc.transaction_id\n" +
                    "left join category c on ttc.category_id = c.category_id\n" +
                    "WHERE (t.data_created BETWEEN ? AND ?) AND a.customer_id = ? AND category_name is not null\n" + 
                    "group by c.category_name;", Tuple.class)
            .setParameter(1, startDate)
            .setParameter(2, endDate)
            .setParameter(3, customerId)
            .getResultList();

    Map<String, Long> reportMap = new HashMap<>();
    for (Tuple tuple : resultList) {
        String categoryName = tuple.get("categoryName", String.class);
        Long totalAmount = tuple.get("totalAmount", Long.class);
        reportMap.put(categoryName, totalAmount);
    }
    return reportMap;
}

关键注意点 #

• 修正SQL语法：把category_name notnull改成标准的category_name is not null
• 严格对应参数占位符顺序，否则会导致查询逻辑错误
• 处理sum(amount)的类型：数据库返回的聚合结果可能是BigDecimal，用((Number) row[1]).longValue()可以避免类型转换异常

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