我有CareBean,但我需要JOIN其他表以进行复杂的SQL查询,然后返回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);
}
卡片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")
放到我的模型中
手推车Bean
@Entity(name = "cart")
@Table(name = "cart")
public class CartBean(
.....
产品Bean
@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月2日
我修改了查询,它的工作
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 "
1条答案
按热度按时间ny6fqffe1#
应为CLOSE,却找到“FROM”
从这个错误消息中可以清楚地看出,您有一个未闭合的括号。
c.quantity AS quantity
后关闭支架,它就能工作了。