Spring Boot JPA多表联查返回自定义CartVO的问题解决

问题背景 #

现有CartBean实体，需要通过JPA多表联查执行复杂查询并返回自定义CartVO对象，不想用逐表查询再用Map封装数据的方式，希望掌握JPA原生的复杂查询实现。

初始代码 #

CartDAO #

public interface CartDAO extends JpaRepository<CartBean, Long>{
@Query(value = " SELECT new com.shop.Model.CartVO(c.id AS cart_id, p.image AS image, p.name AS name, p.spec AS spec, p.price AS price, c.quantity AS quantity "
                 + " FROM product AS p JOIN cart AS c "
                 + " WHERE account=?1 AND p.id = c.product_id) ",nativeQuery = false)
    public List<CartVO> findCartVOByAccount(String account);
}

CartVO #

package com.shop.Model;

public class CartVO {

    private Long cart_id;

    private String image, name, spec;

    private int price, cart_Quantity;

    public CartVO(Long cart_id, String name, String image, String spec, int price, int cart_Quantity) {
        this.cart_id = cart_id;
        this.image = image;
        this.name = name;
        this.spec = spec;
        this.price = price;
        this.cart_Quantity = cart_Quantity;

// ... getter、setter省略
    }
}

初始错误 #

启动时出现语法解析错误，核心异常：

antlr.MismatchedTokenException: expecting CLOSE, found 'FROM'
...
org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found 'FROM' near line 1, column 144 [ SELECT new com.shop.Model.CartVO(c.id AS cart_id, p.image AS image, p.name AS name, p.spec AS spec, p.price AS price, c.quantity AS quantity  FROM product AS p JOIN cart AS c  WHERE account=?1 AND p.id = c.product_id) ]

第一次调试（2022/10/31） #

修改内容 #

1. 调整JPQL语句，将构造VO的闭合括号移到FROM之前：

@Query(value = " SELECT new com.shop.Model.CartVO(c.id AS cart_id, p.image AS image, p.name AS name, p.spec AS spec, p.price AS price, c.quantity AS quantity )"
                 + " FROM product AS p JOIN cart AS c "
                 + " WHERE account=?1 AND p.id = c.product_id ",nativeQuery = false)

2. 为实体类添加@Entity(name)注解，指定JPQL中使用的实体别名：

CartBean

@Entity(name = "cart")
@Table(name = "cart")
public class CartBean {
// ... 实体内容省略
}

ProductBean

@Entity(name = "product")
@Table(name = "product")
public class ProductBean {
// ... 实体内容省略
}

新错误 #

此时错误变为SQL语法错误：

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where account='root' and productbea0_.id=cartbean1_.product_id' at line 1

最终解决（2022/11/02） #

修改JPQL的JOIN语法，将关联条件从WHERE移到JOIN后用ON显式声明：

@Query(value = " SELECT new com.shop.Model.CartVO(c.id AS cart_id, p.image AS image, p.name AS name, p.spec AS spec, p.price AS price, c.quantity AS quantity )"
                 + " FROM product AS p JOIN cart AS c ON p.id = c.product_id "
                 + " WHERE account=?1 ",nativeQuery = false)

修改后查询正常执行，问题解决。

关键注意点 #

• JPQL构造VO的语法：new 全类名(参数)的闭合括号必须紧跟参数列表，不能包裹后面的FROM子句
• 实体别名匹配：JPQL中使用的表别名（如product、cart）必须和实体类@Entity(name)指定的名称一致，否则会自动生成别名导致SQL错误
• JOIN关联条件：JPQL中JOIN需要用ON显式指定关联条件，不能像原生SQL一样放到WHERE中（部分场景虽能运行，但不符合JPQL语法规范）
• VO构造方法参数：JPQL中传递给VO构造方法的参数顺序、类型必须和VO构造方法完全匹配，否则会出现参数不匹配的异常

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