Spring Data JPA custom query with entity as a parameter doesn't work

Question

When I use custom query with entity as parameter in Spring Data JPA I get the exception:

java.sql.SQLException: No value specified for parameter 1

Here are related entities. I have two entities with @OneToOne relationship, like below:

Car:

@Entity
@Table(name = "car")
public class Car {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "car_id")
private long id;

@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "order_id", unique = true, nullable = true, insertable = true, updatable = true)
private Order order;
...
}

Order:

@Entity
@Table(name = "order")
public class Order {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "order_id")
private long id;

@OneToOne(mappedBy="order")
private Car car;
...
}

I have also Spring Data JPA repository with created custom query to find Order by Car:

public interface OrderRepository extends CrudRepository<Order, Long> {

Optional<Order> findByCar(Car car);

}

However, when I use the method findByCar(carWithNonNullOrder), I get the error:

2016-01-28 12:13:39,494 [main ] WARN SqlExceptionHelper - SQL Error: 0, SQLState: 07001 2016-01-28 12:13:39,494 [main ] ERROR SqlExceptionHelper - No value specified for parameter 1 2016-01-28 12:13:39,503 [main ] INFO TransactionContext - Rolled back transaction for test context [DefaultTestContext@61dde151 testClass = OrderServiceIT, testInstance = com.carsystem.server.OrderServiceIT@b25b095, testMethod = shouldMakeOrder@OrderServiceIT, testException = org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet, mergedContextConfiguration = [MergedContextConfiguration@507b79f7 testClass = OrderServiceIT, locations = '{classpath:context/app-context.xml}', classes = '{}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{}', contextLoader = 'org.springframework.test.context.support.DelegatingSmartContextLoader', parent = [null]]]. 2016-01-28 12:13:39,504 [main ] INFO GenericApplicationContext - Closing org.springframework.context.support.GenericApplicationContext@ff5b51f: startup date [Thu Jan 28 12:13:31 CET 2016]; root of context hierarchy 2016-01-28 12:13:39,506 [main ] INFO tainerEntityManagerFactoryBean - Closing JPA EntityManagerFactory for persistence unit 'default'
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:242) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:225) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:417) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:131) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:208) at com.sun.proxy.$Proxy41.findByCar(Unknown Source) at com.carsystem.server.OrderMapper.map(OrderMapper.java:25) at com.carsystem.server.OrderServiceImpl.listOrders(OrderServiceImpl.java:90) at com.carsystem.server.OrderServiceIT.shouldMakeOrder(OrderServiceIT.java:96) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) 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:84) at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:254) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:89) at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238) at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63) at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236) at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53) at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229) 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:309) at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:193) at org.junit.runner.JUnitCore.run(JUnitCore.java:160) at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:78) at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:212) at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:68) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140) Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:106) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:95) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:79) at org.hibernate.loader.Loader.getResultSet(Loader.java:2116) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1899) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1875) at org.hibernate.loader.Loader.doQuery(Loader.java:919) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:336) at org.hibernate.loader.Loader.doList(Loader.java:2611) at org.hibernate.loader.Loader.doList(Loader.java:2594) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2423) at org.hibernate.loader.Loader.list(Loader.java:2418) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:501) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:371) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:216) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1326) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:87) at org.hibernate.jpa.internal.QueryImpl.list(QueryImpl.java:606) at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:483) at org.hibernate.jpa.criteria.compile.CriteriaQueryTypeQueryAdapter.getResultList(CriteriaQueryTypeQueryAdapter.java:50) at org.springframework.data.jpa.repository.query.JpaQueryExecution$CollectionExecution.doExecute(JpaQueryExecution.java:114) at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:78) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:100) at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:91) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:462) at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:440) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ... 43 more Caused by: java.sql.SQLException: No value specified for parameter 1 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2205) at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2185) at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2115) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1936) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:70) ... 74 more

It seems that Spring Data JPA proxy can't find the Car entity. I have found quite similar question here: Spring Data query not working when query parameter is an entity, but in my case I'm fetching entities eagerly. Is it something wrong with mappings or Spring Data JPA?

EDIT

Hibernate query: select order0_.order_id as order_i1_3_, order0_.client_id as client_i4_3_, order0_.createdDate as createdD2_3_, order0_.returnDate as returnDa3_3_ from order order0_ left outer join car car1_ on order0_.order_id=car1_.order_id where car1_.car_id=?

I have noticed that the parameter Car which I used in custom query is also a class member of Order entity. However, when I call the query, I have a circular dependency between Car and Order: Order had a null Car.

Answer 1

public interface OrderRepository extends JpaRepository<Order, Long> {

Order findByCar(Car car);

}

This should work.

Answer 2

Try this:

Order findOneByCar(Car car);