(SOLVE) Springboot JPA complex SQL query with JOIN and return custom object-CodePudding

I have CareBean, but I need to JOIN other tables for complex SQL queries and then return to CartVO.

I looked at a lot of workarounds for similar problems, but still can't solve my problem.

I know that I can look up each table one after another and use a Map to put the values that I need, but I'd rather learn how to do it with complex queries using 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....
    }

ERROR message

antlr.MismatchedTokenException: expecting CLOSE, found 'FROM'
    at antlr.Parser.match(Parser.java:211) ~[antlr-2.7.7.jar:na]
.....

antlr.MismatchedTokenException: expecting EOF, found ')'
    at antlr.Parser.match(Parser.java:211) ~[antlr-2.7.7.jar:na]
.....

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'cartController': Unsatisfied dependency expressed through field 'cartService'; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'cartService': Unsatisfied dependency expressed through field 'cartDAO'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'cartDAO' defined in com.shop.DAO.CartDAO defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.shop.DAO.CartDAO.findCartVOByAccount(java.lang.String)!
.....

Caused by: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'cartService': Unsatisfied dependency expressed through field 'cartDAO'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'cartDAO' defined in com.shop.DAO.CartDAO defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.shop.DAO.CartDAO.findCartVOByAccount(java.lang.String)!
.....

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'cartDAO' defined in com.shop.DAO.CartDAO defined in @EnableJpaRepositories declared on JpaRepositoriesRegistrar.EnableJpaRepositoriesConfiguration: Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.shop.DAO.CartDAO.findCartVOByAccount(java.lang.String)!
.....

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.shop.DAO.CartDAO.findCartVOByAccount(java.lang.String)!
.....

Caused by: java.lang.IllegalArgumentException: 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) ]
.....

Caused by: 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) ]
.....

Update 2022/10/31

I change the query to (change the bracket end position before 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)

And I found that JPQL uses the entity name not table name so put the @Entity(name="my table name") to my model

CartBean

@Entity(name = "cart")
@Table(name = "cart")
public class CartBean(
.....

ProductBean

@Entity(name = "product")
@Table(name = "product")
public class ProductBean {
.....

And the ERROR message become

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

Update 2022/11/02

I modify the query and it's work

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 "

CodePudding user response：

expecting CLOSE, found 'FROM'

From this error message it's clear that you have a unclosed bracket.

Close the bracket after c.quantity AS quantity and it works.