I am trying out a simple use case for many-to-one association with Spring Data JPA but it is resulting in an SQLException. Model is of typical Order and OrderItem classes. Order OrderItem Unit test Unit test passes. But if I uncomment the line to find all items from OrderItemRepository it fails with following exception: What could be missing here ?

Querying with ManyToOne association results in SQLException

I am trying out a simple use case for many-to-one association with Spring Data JPA but it is resulting in an SQLException.

Model is of typical Order and OrderItem classes.

Order

@Entity
public class Order {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

private String name;

public Long getId() {
    return id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}
}

OrderItem

@Entity
public class OrderItem {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

private String name;

@ManyToOne
@JoinColumn(name = "fk_order")
private Order order;

public Long getId() {
    return id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public Order getOrder() {
    return order;
}

public void setOrder(Order order) {
    this.order = order;
}
}

Unit test

@SpringBootTest
public class MappingTests {

    @Autowired
    private OrderRepository orderRepository;

    @Autowired
    private OrderItemRepository orderItemRepository;

    @Test
    @Transactional
    @Disabled
    public void testManyToOneUnidirectional() {
        Order order = new Order();
        order.setName("order-01");
        orderRepository.save(order);

        OrderItem orderItem = new OrderItem();
        orderItem.setName("item-01");
        orderItem.setOrder(order);
        orderItemRepository.save(orderItem);

//        List<OrderItem> all = orderItemRepository.findAll();
    }

Unit test passes. But if I uncomment the line to find all items from OrderItemRepository it fails with following exception:

2020-11-17 11:37:07.671  WARN 35823 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 42001, SQLState: 42001
2020-11-17 11:37:07.671 ERROR 35823 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : Syntax error in SQL statement "INSERT INTO ORDER[*] (NAME, ID) VALUES (?, ?)"; expected "identifier"; SQL statement:
insert into order (name, id) values (?, ?) [42001-200]
2020-11-17 11:37:07.685  INFO 35823 --- [           main] o.s.t.c.transaction.TransactionContext   : Rolled back transaction for test: [DefaultTestContext@6c61a903 testClass = MappingTests, testInstance = com.example.jpademo.MappingTests@5408d4b3, testMethod = testManyToOneUnidirectional@MappingTests, testException = org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [insert into order (name, id) values (?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement, mergedContextConfiguration = [WebMergedContextConfiguration@658c5a19 testClass = MappingTests, locations = '{}', classes = '{class com.example.jpademo.JpaDemoApplication}', contextInitializerClasses = '[]', activeProfiles = '{}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@66d18979, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@17f7cd29, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.web.client.TestRestTemplateContextCustomizer@7ee8290b, org.springframework.boot.test.web.reactive.server.WebTestClientContextCustomizer@2e377400, org.springframework.boot.test.autoconfigure.actuate.metrics.MetricsExportContextCustomizerFactory$DisableMetricExportContextCustomizer@e4487af, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@683dbc2c, org.springframework.boot.test.context.SpringBootTestArgs@1, org.springframework.boot.test.context.SpringBootTestWebEnvironment@233c0b17], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]], attributes = map['org.springframework.test.context.web.ServletTestExecutionListener.activateListener' -> true, 'org.springframework.test.context.web.ServletTestExecutionListener.populatedRequestContextHolder' -> true, 'org.springframework.test.context.web.ServletTestExecutionListener.resetRequestContextHolder' -> true]]


org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [insert into order (name, id) values (?, ?)]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement

    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:259)

What could be missing here ? Thanks.

Answer

While schema is created by hibernate, it seems to fail with following error if comes across table named ‘order’. I renamed the table to some other name and it worked as expected. I think hibernate identifies order as keyword rather than entity name.

2020-11-17 16:14:51.097  WARN 126490 --- [         task-1] o.h.t.s.i.ExceptionHandlerLoggedImpl     : GenerationTarget encountered exception accepting command : Error executing DDL "drop table if exists order CASCADE " via JDBC Statement

org.hibernate.tool.schema.spi.CommandAcceptanceException: Error executing DDL "drop table if exists order CASCADE " via JDBC Statement

Advertisement

Answer