JdbcSQLSyntaxErrorException column not found while using Spring data JDBC

Question

I was reading the Spring data JDBC docs and doing experiments. While doing that I was trying to make simple one to many relationships using @MappedCollection annotation in then parent class and getting the error saying column not found. However I see column is alredy created in the database.

Here is My entity classes:

public class Customer {
    @Id
    @Column("customer_id")
    private  Long id;
    private  String name;
    private  int age;

    @MappedCollection(idColumn = "customer_id")
    private Set<Address> addresses = new HashSet<>();

    public Customer() {
    }

    public Customer(Long id, String name, int age, Set<Address> addresses) {
        this.id = id;
        this.name = name;
        this.age = age;
        this.addresses = addresses;
    }

    public Customer withId(Long id){
        return new Customer(id, this.name, this.age, this.addresses);
    }
   // Getters, equals and hashcodes methods below

}

My another class:

public class Address {
    private  String city;

    public Address() {
    }

    public Address( String city) {
        this.city = city;
    }
// Getters equals and hashcode methods
}

Sql script files:

CREATE TABLE IF NOT EXISTS Customer (
    customer_id INTEGER IDENTITY PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER not null
    );


CREATE TABLE  IF NOT EXISTS Address (
    customer_id INTEGER,
    city VARCHAR(100)
);

ALTER TABLE Address ADD CONSTRAINT FK_ADDRESS_CUSTOMER 
FOREIGN KEY (customer_id) REFERENCES Customer (customer_id)

Now when I try to save the Customer with set of addresses. I am getting below error

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "customer_id" not found; SQL statement:
INSERT INTO "ADDRESS" ("CITY", "customer_id") VALUES (?, ?) [42122-200]

error says customer_id not found in the table but I see it's already there. Can someone explain to me why? I am using h2 database.

The source code can be found in my github. Error can be reproduced by running the test CustomerRepositoryTest -->aggregationTest

Are you using any quoting confguration for Hibernate ? Like `hibernate.globally_quoted_identifiers` — Eklavya, Aug 09 '20 at 18:27
I am not using hibernate or spring data jpa to be specific. Its spring data jdbc. Anyway the issue was related to the way I was specifying the column name inside mappeCollection. Column name should be in upper case at lest for H2 — Sudhir Kumar, Aug 09 '20 at 20:12

score 4 · Answer 1 · answered Aug 09 '20 at 18:13

When you quote the names, they become case-sensitive, e.g. customer_id and "customer_id" are not the same name.

That is because H2 handles case-insensitivity by uppercasing unquoted names, so customer_id, Customer_Id, CUSTOMER_ID, and "CUSTOMER_ID" are all the same¹, but "customer_id" and "Customer_Id" are two entirely separate names.

Fix the insert to not quote the names, or spell the names in uppercase. Not quoting is the recommended way.

^{1) As far as H2 is concerned. Other databases handle it differently, e.g. PostgreSQL lowercases the names.}

Thanks!! Replaced @MappedCollection(idColumn="customer_id") with @MappedCollection(idColumn="CUSTOMER_ID") worked. — Sudhir Kumar, Aug 09 '20 at 18:24

JdbcSQLSyntaxErrorException column not found while using Spring data JDBC

1 Answers1