Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: customerNumber of:

Question

I've following query which is working fine on mysql. The same I wanted to implement through the spring-data-jpa querydsl API.

select c.* from orders o, customers c where o.customerNumber=103 and c.customerNumber=103;

CustomerRepository.java
public interface CustomerRepository extends JpaRepository<Customer, Integer>,
    QueryDslPredicateExecutor<Customer>{

    @Query("select c from Customer c where c.country='USA'")
    List<Customer> findByCountry();

    @Query("select c from Order o, Customer c where o.customerNumber=:customerNumber and c.customerNumber=:customerNumber")
    List<Customer> findByCustomerNumber(@Param("customerNumber") Integer customerNumber);
}

But when I run the code, I see following error comes:

Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: customerNumber of: net.javabeat.springdata.jpa.data.Order [select c from net.javabeat.springdata.jpa.data.Order o, net.javabeat.springdata.jpa.data.Customer c where o.customerNumber=:customerNumber and c.customerNumber=:customerNumber]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1374)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
    at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:294)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
    at com.sun.proxy.$Proxy31.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86)
    ... 53 more
Caused by: org.hibernate.QueryException: could not resolve property: customerNumber of: net.javabeat.springdata.jpa.data.Order [select c from net.javabeat.springdata.jpa.data.Order o, net.javabeat.springdata.jpa.data.Customer c where o.customerNumber=:customerNumber and c.customerNumber=:customerNumber]
    at org.hibernate.persister.entity.AbstractPropertyMapping.propertyException(AbstractPropertyMapping.java:83)
    at org.hibernate.persister.entity.AbstractPropertyMapping.toType(AbstractPropertyMapping.java:77)
    at org.hibernate.persister.entity.AbstractEntityPersister.toType(AbstractEntityPersister.java:1967)
    at org.hibernate.hql.internal.ast.tree.FromElementType.getPropertyType(FromElementType.java:313)
    at org.hibernate.hql.internal.ast.tree.FromElement.getPropertyType(FromElement.java:490)
    at org.hibernate.hql.internal.ast.tree.DotNode.getDataType(DotNode.java:616)
    at org.hibernate.hql.internal.ast.tree.DotNode.prepareLhs(DotNode.java:267)
    at org.hibernate.hql.internal.ast.tree.DotNode.resolve(DotNode.java:214)
    at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:119)
    at org.hibernate.hql.internal.ast.tree.FromReferenceNode.resolve(FromReferenceNode.java:115)
    at org.hibernate.hql.internal.ast.HqlSqlWalker.resolve(HqlSqlWalker.java:882)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.expr(HqlSqlBaseWalker.java:1264)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.exprOrSubquery(HqlSqlBaseWalker.java:4426)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.comparisonExpr(HqlSqlBaseWalker.java:3898)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2076)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.logicalExpr(HqlSqlBaseWalker.java:2001)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.whereClause(HqlSqlBaseWalker.java:794)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.query(HqlSqlBaseWalker.java:595)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.selectStatement(HqlSqlBaseWalker.java:299)
    at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.statement(HqlSqlBaseWalker.java:247)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.analyze(QueryTranslatorImpl.java:250)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:185)
    at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:138)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:105)
    at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:168)
    at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:222)
    at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:200)
    at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1703)
    at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:291)
    ... 60 more

Test case which I developed:

@Test
    public void testCustomerOrder(){
        List<Customer> customers = customerRepository.findByCustomerNumber(103);
        System.out.println("CUSTOMETS : "+customers);
        for (Customer customer : customers) {
            System.out.println("================================");
            System.out.println(customer.getCustomerNumber());
            System.out.println(customer.getCustomerName());
            System.out.println(customer.getContactFirstName());
            System.out.println(customer.getContactLastName());
            System.out.println(customer.getPhone());
            System.out.println(customer.getAddressLine1());
            System.out.println(customer.getAddressLine2());
            System.out.println(customer.getCity());
            System.out.println(customer.getState());
            System.out.println(customer.getPostalCode());
            System.out.println(customer.getCountry());
            System.out.println(customer.getCreditLimit());

            List<Order> orders = customer.getOrders();
            for (Order order : orders) {
                System.out.println("-------------------------");
                System.out.println(order.getOrderNumber());
                System.out.println(order.getOrderDate());
                System.out.println(order.getRequiredDate());
                System.out.println(order.getShippedDate());
                System.out.println(order.getStatus());
                System.out.println();
            }
        }
    }

Order.java

@Entity
@Table(name="orders")
@NamedQuery(name="Order.findAll", query="SELECT o FROM Order o")
public class Order implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private int orderNumber;

    @Lob
    private String comments;

    @Temporal(TemporalType.DATE)
    private Date orderDate;

    @Temporal(TemporalType.DATE)
    private Date requiredDate;

    @Temporal(TemporalType.DATE)
    private Date shippedDate;

    private String status;

    //bi-directional many-to-one association to Orderdetail
    @OneToMany(mappedBy="order")
    private List<Orderdetail> orderdetails;

    //bi-directional many-to-one association to Customer
    @ManyToOne
    @JoinColumn(name="customerNumber")
    private Customer customer;

    // setters and getters
}

Customer.java

@Entity
@Table(name="customers")
@NamedQuery(name="Customer.findAll", query="SELECT c FROM Customer c")
public class Customer implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO)
    private int customerNumber;

    private String addressLine1;

    private String addressLine2;

    private String city;

    private String contactFirstName;

    private String contactLastName;

    private String country;

    private double creditLimit;

    private String customerName;

    private String phone;

    private String postalCode;

    private String state;

    //bi-directional many-to-one association to Employee
    @ManyToOne
    @JoinColumn(name="salesRepEmployeeNumber")
    private Employee employee;

    //bi-directional many-to-one association to Order
    @OneToMany(mappedBy="customer")
    private List<Order> orders;

    //bi-directional many-to-one association to Payment
    @OneToMany(mappedBy="customer")
    private List<Payment> payments;
    // setters and getters
}

Any pointers from experts asap?

EDIT-1: I tried below, but still errors are coming:

@Query("select c from Customer c where c.orders.customerNumber=:customerNumber and c.customerNumber=:customerNumber")
    List<Customer> findByCustomerNumber(@Param("customerNumber") Integer customerNumber);

Error comes for above configuration:

Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List net.javabeat.springdata.repository.CustomerRepository.findByCustomerNumber(java.lang.Integer)!
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:92)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.<init>(SimpleJpaQuery.java:62)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromMethodWithQueryString(JpaQueryFactory.java:72)
    at org.springframework.data.jpa.repository.query.JpaQueryFactory.fromQueryAnnotation(JpaQueryFactory.java:53)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$DeclaredQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:136)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$CreateIfNotFoundQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:204)
    at org.springframework.data.jpa.repository.query.JpaQueryLookupStrategy$AbstractQueryLookupStrategy.resolveQuery(JpaQueryLookupStrategy.java:73)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.<init>(RepositoryFactorySupport.java:416)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport.getRepository(RepositoryFactorySupport.java:206)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.initAndReturn(RepositoryFactoryBeanSupport.java:251)
    at org.springframework.data.repository.core.support.RepositoryFactoryBeanSupport.afterPropertiesSet(RepositoryFactoryBeanSupport.java:237)
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactoryBean.afterPropertiesSet(JpaRepositoryFactoryBean.java:92)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.invokeInitMethods(AbstractAutowireCapableBeanFactory.java:1631)
    at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.initializeBean(AbstractAutowireCapableBeanFactory.java:1568)
    ... 40 more
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: could not resolve property: customerNumber of: net.javabeat.springdata.jpa.data.Order [select c from net.javabeat.springdata.jpa.data.Customer c where c.orders.customerNumber=:customerNumber and c.customerNumber=:customerNumber]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1374)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
    at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:294)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:344)
    at com.sun.proxy.$Proxy31.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:86)
    ... 53 more
Caused by: org.hibernate.QueryException: could not resolve property: customerNumber of: net.javabeat.springdata.jpa.data.Order [select c from net.javabeat.springdata.jpa.data.Customer c where c.orders.customerNumber=:customerNumber and c.customerNumber=:customerNumber]

ER Diagram:

Edit-2: Per suggestion from @Andy, I can access data but seeing following error too

org.hibernate.LazyInitializationException: failed to lazily initialize a collection of role: net.javabeat.springdata.jpa.data.Customer.orders, could not initialize proxy - no Session
    at org.hibernate.collection.internal.AbstractPersistentCollection.throwLazyInitializationException(AbstractPersistentCollection.java:566)
    at org.hibernate.collection.internal.AbstractPersistentCollection.withTemporarySessionIfNeeded(AbstractPersistentCollection.java:186)
    at org.hibernate.collection.internal.AbstractPersistentCollection.initialize(AbstractPersistentCollection.java:545)
    at org.hibernate.collection.internal.AbstractPersistentCollection.read(AbstractPersistentCollection.java:124)
    at org.hibernate.collection.internal.PersistentBag.iterator(PersistentBag.java:266)
    at net.javabeat.springdata.CustomerTest.testCustomerOrder(CustomerTest.java:57)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
    at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
    at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
    at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
    at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
    at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
    at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:70)
    at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:224)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83)
    at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
    at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
    at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
    at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
    at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
    at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
    at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
    at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
    at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)

Answer 1

@Query("select c from Customer c where c.orders.customerNumber=:customerNumber and c.customerNumber=:customerNumber")

You try to access the customerNumerattributein Order, but I do not see an attribute called customeNumber. You only have a orderNumber.

@Query("select c from Customer c, Order o where o.customer.customerNumber=:customerNumber and c.customerNumber=:customerNumber")

But I am not that sure what you actually want to achieve. Do you want all orders by a customer?