Issue
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 "
Solution
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.
Answered By - Alien
Answer Checked By - Gilberto Lyons (JavaFixing Admin)