我有 CareBean，但我需要加入其他表以進行復雜的 SQL 查詢，然后回傳到 CartVO。
我查看了很多類似問題的解決方法，但仍然無法解決我的問題。
我知道我可以一個接一個地查找每個表并使用一個 Map 來放置我需要的值，但我寧愿學習如何使用 JPA 來處理復雜的查詢
購物車DAO
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);
}
購物車VO
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'
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) ]
.....
更新 2022/10/31
我將查詢更改為（更改之前的括號結束位置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)
我發現 JPQL 使用物體名稱而不是表名稱，所以把它@Entity(name="my table name")放到我的模型中
購物車豆
@Entity(name = "cart")
@Table(name = "cart")
public class CartBean(
.....
產品豆
@Entity(name = "product")
@Table(name = "product")
public class ProductBean {
.....
并且錯誤訊息變為
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
我修改了查詢，它的作業
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 "
uj5u.com熱心網友回復：
期待關閉，發現'來自'
從此錯誤訊息中可以清楚地看出您有一個未閉合的括號。
之后關閉支架c.quantity AS quantity，它就可以作業了。
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/525689.html