What's wrong with my JPQL query?

Question

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
}

Now, I show the 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
}

Now , I show the repository

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.

The error I encounter is :

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: 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 ....

What is the error in my query ?

Answer 1

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

Or:

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

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.

The same applies to the other query. For future reference: everything you cut off the exception stack trace would've been the interesting part . If persistence providers reject JPQL, they're usually very specific about the error they encounter. So you should be able to find something around p.storeId being an invalid reference somewhere deeper down the stack trace actually.