英文:
JdbcSQLSyntaxErrorException column not found while using Spring data JDBC
问题
以下是您提供的文本的翻译部分:
我正在阅读Spring Data JDBC文档并进行实验。在这个过程中,我试图使用@MappedCollection注解在父类中创建简单的一对多关系,但出现了列未找到的错误。然而,我看到数据库中已经创建了该列。
这是我的实体类:
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
}
我的另一个类:
public class Address {
private String city;
public Address() {
}
public Address(String city) {
this.city = city;
}
// Getters equals and hashcode methods
}
SQL脚本文件:
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)
现在,当我尝试保存带有地址集的Customer时,我遇到以下错误:
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "customer_id" not found; SQL statement:
INSERT INTO "ADDRESS" ("CITY", "customer_id") VALUES (?, ?) [42122-200]
错误说在表中找不到customer_id列,但我看到它已经存在。有人能解释一下吗?我正在使用H2数据库。
源代码可以在我的 GitHub 上找到。可以通过运行测试 CustomerRepositoryTest --> aggregationTest 来重现错误。
英文:
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
答案1
得分: 4
当引用名称时,它们变成区分大小写,例如 customer_id
和 "customer_id"
不是相同的名称。
这是因为 H2 通过将未引用的名称大写来处理大小写不敏感,因此 customer_id
、Customer_Id
、CUSTOMER_ID
和 "CUSTOMER_ID"
都是相同的<sup>1</sup>,但 "customer_id"
和 "Customer_Id"
是两个完全不同的名称。
修复插入以不引用名称,或者将名称拼写为大写。不引用是推荐的方式。
<sup>1) 就 H2 而言是如此。其他数据库处理方式不同,例如 PostgreSQL 将名称转为小写。</sup>
英文:
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<sup>1</sup>, 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.
<sup>1) As far as H2 is concerned. Other databases handle it differently, e.g. PostgreSQL lowercases the names.</sup>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论