如何用JPA将数据填充到非实体类列表？含单州查询升级需求

环境信息 #

• java 8
• spring Boot 2.2.11.RELEASE
• spring 5.2.10.RELEASE
• hibernate 5.4.22.FINAL
• mysql-connector-java 8.0.22

详情 #

实体类 #

@Entity
@Table( name="phone_number")
public class PhoneNumber implements java.io.Serializable {

    private static final long serialVersionUID = -2620178064909933422L;

    @Id
    @Column( name="full_number", nullable=false, length=10)
    private String phoneNumber;    
    
    @Column( name="area_code", nullable=false, length=3)
    private String areaCode;
    
    @Column( name="exchange", nullable=false, length=3)
    private String exchange;
    
    @Column( name="postfix", nullable=false, length=4)
    private String postfix;
    
    @Column( name="state_name", nullable=false)
    private String state;
}

非持久化POJO #

public class PhoneNumberByState {
    
    private String state;
    private String areaCode;
    private BigInteger total;
   
}

ServiceImpl方法问题 #

现有方法执行报错：

public List<PhoneNumberByState> groupPhoneNumbersByStateAndAreaCode() {
    String query = "SELECT state, areaCode, COUNT(*) AS 'total' FROM PhoneNumber GROUP BY state, areaCode ORDER BY state, areaCode";

    // 需要JPA实现逻辑
    List<PhoneNumberByState> lisst = (List<PhoneNumberByState>) em.createQuery( query, PhoneNumber.class);
    // 抛出错误：Cannot create TypedQuery for query with more than one return using requested result type
    
    return lisst;
}

错误原因：查询返回state、areaCode、count(*)三个字段的组合，而非完整PhoneNumber实体，无法用PhoneNumber.class作为结果类型，也不能直接强转为PhoneNumberByState列表。

整体目标：将查询结果列表用于填充jsp页面。

附加需求：支持按州/省名称查询 #

现有代码无法编译，需完善JPA实现逻辑：

public List<PhoneNumberByState> groupPhoneNumbersByStateAndAreaCodeForOneState( String nameOfState) {
    String query = "SELECT state, areaCode, COUNT(*) AS 'total' FROM PhoneNumber WHERE state = ?1 GROUP BY state, areaCode ORDER BY state, areaCode";

    // 需要JPA实现逻辑
    List<PhoneNumberByState> tq = ????
    tq.setParameter( 1,  nameOfState);
    // 无法编译
    
    return tq.getResultList();
}

解决方案 #

方法一：构造函数表达式映射（推荐） #

先给PhoneNumberByState添加匹配查询结果的构造函数：

public class PhoneNumberByState {
    
    private String state;
    private String areaCode;
    private BigInteger total;
   
    // 新增构造函数
    public PhoneNumberByState(String state, String areaCode, BigInteger total) {
        this.state = state;
        this.areaCode = areaCode;
        this.total = total;
    }

    // 可选：添加无参构造
    public PhoneNumberByState() {}

    // 补充getter/setter方法
    public String getState() { return state; }
    public void setState(String state) { this.state = state; }
    public String getAreaCode() { return areaCode; }
    public void setAreaCode(String areaCode) { this.areaCode = areaCode; }
    public BigInteger getTotal() { return total; }
    public void setTotal(BigInteger total) { this.total = total; }
}

1. 全局分组查询方法

public List<PhoneNumberByState> groupPhoneNumbersByStateAndAreaCode() {
    // 注意替换为PhoneNumberByState实际包路径
    String query = "SELECT NEW com.yourpackage.PhoneNumberByState(p.state, p.areaCode, COUNT(*)) " +
                   "FROM PhoneNumber p GROUP BY p.state, p.areaCode ORDER BY p.state, p.areaCode";

    TypedQuery<PhoneNumberByState> typedQuery = em.createQuery(query, PhoneNumberByState.class);
    return typedQuery.getResultList();
}

2. 按州/省名称查询方法

public List<PhoneNumberByState> groupPhoneNumbersByStateAndAreaCodeForOneState(String nameOfState) {
    String query = "SELECT NEW com.yourpackage.PhoneNumberByState(p.state, p.areaCode, COUNT(*)) " +
                   "FROM PhoneNumber p WHERE p.state = :stateName " +
                   "GROUP BY p.state, p.areaCode ORDER BY p.state, p.areaCode";

    TypedQuery<PhoneNumberByState> typedQuery = em.createQuery(query, PhoneNumberByState.class);
    typedQuery.setParameter("stateName", nameOfState);
    return typedQuery.getResultList();
}

方法二：Tuple接收后手动映射 #

若不想修改POJO构造函数，可通过Tuple接收结果再手动转换：

全局分组查询方法

public List<PhoneNumberByState> groupPhoneNumbersByStateAndAreaCode() {
    String query = "SELECT p.state, p.areaCode, COUNT(*) AS total FROM PhoneNumber p GROUP BY p.state, p.areaCode ORDER BY p.state, p.areaCode";

    List<Tuple> resultTuples = em.createQuery(query, Tuple.class).getResultList();
    List<PhoneNumberByState> resultList = new ArrayList<>();

    for (Tuple tuple : resultTuples) {
        PhoneNumberByState dto = new PhoneNumberByState();
        dto.setState(tuple.get("state", String.class));
        dto.setAreaCode(tuple.get("areaCode", String.class));
        dto.setTotal((BigInteger) tuple.get("total"));
        resultList.add(dto);
    }

    return resultList;
}

按州/省名称查询方法

public List<PhoneNumberByState> groupPhoneNumbersByStateAndAreaCodeForOneState(String nameOfState) {
    String query = "SELECT p.state, p.areaCode, COUNT(*) AS total FROM PhoneNumber p WHERE p.state = :stateName GROUP BY p.state, p.areaCode ORDER BY p.state, p.areaCode";

    TypedQuery<Tuple> typedQuery = em.createQuery(query, Tuple.class);
    typedQuery.setParameter("stateName", nameOfState);
    List<Tuple> resultTuples = typedQuery.getResultList();

    List<PhoneNumberByState> resultList = new ArrayList<>();
    for (Tuple tuple : resultTuples) {
        PhoneNumberByState dto = new PhoneNumberByState();
        dto.setState(tuple.get("state", String.class));
        dto.setAreaCode(tuple.get("areaCode", String.class));
        dto.setTotal((BigInteger) tuple.get("total"));
        resultList.add(dto);
    }

    return resultList;
}

方法三：SqlResultSetMapping映射（复杂场景适用） #

在PhoneNumber实体上添加映射配置：

@Entity
@Table(name="phone_number")
@SqlResultSetMapping(
    name = "PhoneNumberByStateMapping",
    classes = @ConstructorResult(
        targetClass = PhoneNumberByState.class,
        columns = {
            @ColumnResult(name = "state", type = String.class),
            @ColumnResult(name = "areaCode", type = String.class),
            @ColumnResult(name = "total", type = BigInteger.class)
        }
    )
)
public class PhoneNumber implements java.io.Serializable {
    // 原有代码不变
}

修改查询方法：

public List<PhoneNumberByState> groupPhoneNumbersByStateAndAreaCode() {
    String query = "SELECT state, areaCode, COUNT(*) AS total FROM PhoneNumber GROUP BY state, areaCode ORDER BY state, areaCode";

    Query nativeQuery = em.createNativeQuery(query, "PhoneNumberByStateMapping");
    return nativeQuery.getResultList();
}

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