CODEWITHSUNDEEP
javaspring-bootspring-data-jdbc
Sudhir Kumar
Sudhir Kumar

Reputation: 153

JdbcSQLSyntaxErrorException column not found while using Spring data JDBC

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

Upvotes: 3

Views: 5397

Answers (1)

Andreas
Andreas

Reputation: 159086

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 same1, 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.

Upvotes: 4

Related Questions