How to select referenced entities with Spring Data JDBC

Question

How to select referenced entities in One-To-May relationship with Spring Data JDBC?

When I tried following code on this blog

class PurchaseOrder {

    @Id
    private Long id;
    private String shippingAddress;
    private Set<OrderItem> items;
}

class OrderItem {

    @Id
    private Long id;
    private int quantity;
    private String product;
}

@Test
void test() {
    PurchaseOrder purchaseOrder = PurchaseOrder.of("Tokyo");
    purchaseOrder.addItem(4, "Captain Future Comet Lego set");
    purchaseOrder.addItem(2, "Cute blue angler fish plush toy");
    orderRepository.save(purchaseOrder);

    Iterable<PurchaseOrder> result = orderRepository.findAll(); //throw MappingException !!
    result.forEach(System.out::println);
}

this throws an exception below:

org.springframework.data.mapping.MappingException: Could not read property @org.springframework.data.annotation.Id()private final java.lang.Long com.example.demo.OrderItem.id from result set!

    at org.springframework.data.jdbc.core.EntityRowMapper.readFrom(EntityRowMapper.java:130)
    at org.springframework.data.jdbc.core.EntityRowMapper.readEntityFrom(EntityRowMapper.java:143)
    at org.springframework.data.jdbc.core.EntityRowMapper.readFrom(EntityRowMapper.java:124)
    at org.springframework.data.jdbc.core.EntityRowMapper.lambda$createInstance$0(EntityRowMapper.java:167)
......
Caused by: org.h2.jdbc.JdbcSQLException: Column "items_id" not found [42122-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
......

It looks that spring data jdbc add the parent entity's name as prefix to columns of child entities when retrieving values from ResultSet. But I couldn't this mapping rule on the document...

Is there any way to custom this mapping rule?

DDL:

create table purchase_order (
  id bigint primary key auto_increment,
  shipping_address varchar NOT NULL
);

create table order_item (
  id bigint primary key auto_increment,
  purchase_order bigint NOT NULL,
  quantity int NOT NULL,
  product varchar NOT NULL
);

[UPDATED]

The version of spring-data-jdbc is 1.0.4.RELEASE.

The SQL statements is here:

2019-01-29 23:49:16.932 DEBUG 80409 --- [           main] o.s.jdbc.core.JdbcTemplate               : Executing prepared SQL statement [SELECT purchase_order.id AS id, purchase_order.shipping_address AS shipping_address FROM purchase_order]

And full stack trace is

org.springframework.data.mapping.MappingException: Could not read property @org.springframework.data.annotation.Id()private java.lang.Long com.example.demo.OrderItem.id from result set!

    at org.springframework.data.jdbc.core.EntityRowMapper.readFrom(EntityRowMapper.java:130)
    at org.springframework.data.jdbc.core.EntityRowMapper.readEntityFrom(EntityRowMapper.java:143)
    at org.springframework.data.jdbc.core.EntityRowMapper.readFrom(EntityRowMapper.java:124)
    at org.springframework.data.jdbc.core.EntityRowMapper.lambda$createInstance$0(EntityRowMapper.java:167)
    at org.springframework.data.relational.core.conversion.BasicRelationalConverter$ConvertingParameterValueProvider.getParameterValue(BasicRelationalConverter.java:251)
    at org.springframework.data.convert.ReflectionEntityInstantiator.createInstance(ReflectionEntityInstantiator.java:75)
    at org.springframework.data.convert.ClassGeneratingEntityInstantiator.createInstance(ClassGeneratingEntityInstantiator.java:84)
    at org.springframework.data.relational.core.conversion.BasicRelationalConverter.createInstance(BasicRelationalConverter.java:141)
    at org.springframework.data.jdbc.core.EntityRowMapper.createInstance(EntityRowMapper.java:160)
    at org.springframework.data.jdbc.core.EntityRowMapper.mapRow(EntityRowMapper.java:71)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:94)
    at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData(RowMapperResultSetExtractor.java:61)
    at org.springframework.jdbc.core.JdbcTemplate$1.doInPreparedStatement(JdbcTemplate.java:679)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:694)
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:748)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:215)
    at org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate.query(NamedParameterJdbcTemplate.java:227)
    at org.springframework.data.jdbc.core.DefaultDataAccessStrategy.findAll(DefaultDataAccessStrategy.java:217)
    at org.springframework.data.jdbc.core.JdbcAggregateTemplate.findAll(JdbcAggregateTemplate.java:158)
    at org.springframework.data.jdbc.repository.support.SimpleJdbcRepository.findAll(SimpleJdbcRepository.java:84)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.springframework.data.repository.core.support.RepositoryComposition$RepositoryFragments.invoke(RepositoryComposition.java:359)
    at org.springframework.data.repository.core.support.RepositoryComposition.invoke(RepositoryComposition.java:200)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$ImplementationMethodExecutionInterceptor.invoke(RepositoryFactorySupport.java:644)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:608)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.lambda$invoke$3(RepositoryFactorySupport.java:595)
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:595)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:59)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:294)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:98)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:139)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:212)
    at com.sun.proxy.$Proxy68.findAll(Unknown Source)
    at com.example.demo.OrderRepositoryTest.test(OrderRepositoryTest.java:61)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at org.junit.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:532)
    at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:115)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$6(TestMethodTestDescriptor.java:171)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:72)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:167)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:114)
    at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:59)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$4(NodeTestTask.java:108)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:72)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:98)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:74)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$4(NodeTestTask.java:112)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:72)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:98)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:74)
    at java.base/java.util.ArrayList.forEach(ArrayList.java:1540)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$4(NodeTestTask.java:112)
    at org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:72)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:98)
    at org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:74)
    at org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
    at org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:220)
    at org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$6(DefaultLauncher.java:188)
    at org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:202)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:181)
    at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:128)
    at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:74)
    at com.intellij.rt.execution.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:47)
    at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:242)
    at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:70)
Caused by: org.h2.jdbc.JdbcSQLException: Column "items_id" not found [42122-197]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:357)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.jdbc.JdbcResultSet.getColumnIndex(JdbcResultSet.java:3174)
    at org.h2.jdbc.JdbcResultSet.get(JdbcResultSet.java:3247)
    at org.h2.jdbc.JdbcResultSet.getObject(JdbcResultSet.java:529)
    at org.springframework.data.jdbc.core.EntityRowMapper.readFrom(EntityRowMapper.java:127)
    ... 86 more

first, your tables don't have an items_id column and the exception seems to be triggered because of that. second, your code does not reflect any mapping information. — Lalit Mehra, Jan 29 '19 at 09:17
Could you please post the full stack trace, the version you are using and could you please activate logging for the SQL statements by setting the log level of `org.springframework.jdbc.core` to `trace`? If you could make your project available on Github, that would make things probably rather easy. — Jens Schauder, Jan 29 '19 at 13:06
@LalitMehra I tried but caught similar exception: `org.h2.jdbc.JdbcSQLException: Column "items_items_id" not found ` — Ryo Shindo, Jan 29 '19 at 14:47
@JensSchauder added the sql statement and full stack trace. Could you take a look at them? — Ryo Shindo, Jan 29 '19 at 14:55
Git repo is here https://github.com/rshindo/spring-data-jdbc-demo (Sorry, not exactly same as I described above) — Ryo Shindo, Jan 29 '19 at 15:04

Supun Dharmarathne · Answer 1 · 2019-01-29T10:20:28.020

0

Following copied from official docs.

To wrap it up: Spring Data JDBC does not support many-to-one or many-to-many relationships. In order to model these, use IDs. This encourages a clean modularization of the domain model. It also removes a whole type of questions that one would have to solve and learn to reason about if such a mapping were possible.

 class PurchaseOrder {

   @Id
   private Long id;
   private String shippingAddress;
  private Set<OrderItem> items;

     private OrderItemRef createOrderItem(OrderItem orderItem ) {

   OrderItemRef orderItemRef = new OrderItemRef ();
   orderItemRef.orderItem = orderItemRef.id;
   return orderItemRef ;
 }
 }

  @Table("Purchase_OrderItem ")
class OrderItemRef {
    Long orderItem;
 }


 class OrderItem {

   @Id
  private Long id;
  private int quantity;
  private String product;
 }

edited Jan 29 '19 at 10:20

answered Jan 29 '19 at 09:44

Supun Dharmarathne

1,138
1
10
19

Can Spring Data JDBC generate DDLs from Entity classes? I know Spring Data JPA (or, Hibernate) can do it.. – Ryo Shindo Jan 29 '19 at 09:58
Please check my modified answer. I have refferred this https://spring.io/blog/2018/09/24/spring-data-jdbc-references-and-aggregates – Supun Dharmarathne Jan 29 '19 at 10:22
@RyoShindo No at the moment Spring Data JDBC does not support schema generation. – Jens Schauder Jan 29 '19 at 12:47
@SupunDharmarathne but what the OP poster tries to do is not a Many-to-X relationship it seems. They are just missing a column. – Jens Schauder Jan 29 '19 at 12:50
1

It seems you are hitting a variant of https://jira.spring.io/browse/DATAJDBC-127 Your entity looks quite a bit different from what you posted. Especially it does expect a `Set` in the constructor. Could you remove the `Set` from the constructor? – Jens Schauder Jan 29 '19 at 15:29
@JensSchauder Thanks for the insight, btw just found that you are a top contributor in this project ;) – Supun Dharmarathne Jan 29 '19 at 16:59

How to select referenced entities with Spring Data JDBC

1 Answers1