我的JPQL查询出了什么问题? [英] What's wrong with my JPQL query?

问题描述

我正在尝试实现连接，但遇到错误.我有产品表和商店表.产品表通过外键引用存储表，如下所示:

I am trying to implement join but I am facing error. I have product table and store table. product table references store table through foreign key as shown below:

Product.java

@Entity
public class Product {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long pId;
    private String model;
    private String brand;
    private byte[] image;
    private Long price;
    private String currency;
    private String transmissionType;
    private String fuelType;

    @ManyToOne
    @JoinColumn(name="storeId")
    private Store store;

    // … getters and setters
}

现在，我显示 Store.java

@Entity
public class Store {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long storeId;
    private String locality;
    private String city;
    private String state;
    private String zipCode;
    private String phone;

    // … getters and setters
}

现在，我显示存储库

public interface ProductRepo extends JpaRepository<Product, Long> {     

    @Query("select p from Product p join p.storeId s where p.storeId = s.storeId and s.city = :city")
    public List<Product> findByCity(@Param("city") String city);

    @Query("select p from Product p join p.storeId s where p.storeId = s.storeId and s.state = :state")
    public List<Product> findByState(@Param("state")  String state);
}

现在，该错误归结于我实现联接的最后两个查询.我想要做的就是获取所有商店，这些商店的商店都在特定的城市或州内，如您在上面看到的那样.

Now, the error comes due to the last two queries where I implement join. What i want to do is get all products whose store is in particular city or state as you can see above.

我遇到的错误是:

启动ApplicationContext时出错.显示自动配置 报告在启用调试"的情况下重新运行您的应用程序. 2016-10-16 09:53:25.203错误16132 --- [main] o.s.boot.SpringApplication:应用程序启动失败

Error starting ApplicationContext. To display the auto-configuration report re-run your application with 'debug' enabled. 2016-10-16 09:53:25.203 ERROR 16132 --- [ main] o.s.boot.SpringApplication : Application startup failed

org.springframework.beans.factory.UnsatisfiedDependencyException: 创建名称为'catalogueServiceController'的bean时出错: 通过字段"productRepo"表示不满意的依赖关系；嵌套的 异常是org.springframework.beans.factory.BeanCreationException: 创建名称为"productRepo"的bean时出错:初始化方法的调用 失败的;嵌套的异常是java.lang.IllegalArgumentException: 验证方法公共抽象java.util.List的查询失败 com.practice.rest.assignment1.repository.ProductRepo.findByCity(java.lang.String)！ 等等....

org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'catalogueServiceController': Unsatisfied dependency expressed through field 'productRepo'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'productRepo': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.practice.rest.assignment1.repository.ProductRepo.findByCity(java.lang.String)! and so on ....

查询中出现什么错误?

推荐答案

查询无效.您引用的p.storeId不存在.我认为这样就足够了:

The query is invalid. You refer to a p.storeId which doesn't exist. I think something like this should be sufficient:

select p from Product where p.store.city = :city

或者:

select p from Product join p.store as store where store.city = :city

上层应该足够，因为您的JPA提供者应该能够为您做正确的事情.如果您想更详细地了解联接类型以优化查询，则最好使用后者.

The upper should be sufficient as your JPA provider should be able to do the right thing for you then. The latter might be preferred if you want to be more specific about the join type to optimize the query.

这篇关于我的JPQL查询出了什么问题?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！