JdbcSQLSyntaxErrorException在使用Spring数据JDBC时找不到列。

huangapple go评论81阅读模式
英文:

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(&quot;customer_id&quot;)
    private  Long id;
    private  String name;
    private  int age;

    @MappedCollection(idColumn = &quot;customer_id&quot;)
    private Set&lt;Address&gt; addresses = new HashSet&lt;&gt;();

    public Customer() {
    }

    public Customer(Long id, String name, int age, Set&lt;Address&gt; 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 &quot;customer_id&quot; not found; SQL statement:
INSERT INTO &quot;ADDRESS&quot; (&quot;CITY&quot;, &quot;customer_id&quot;) 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&quot;customer_id&quot; 不是相同的名称。

这是因为 H2 通过将未引用的名称大写来处理大小写不敏感,因此 customer_idCustomer_IdCUSTOMER_ID&quot;CUSTOMER_ID&quot; 都是相同的<sup>1</sup>,但 &quot;customer_id&quot;&quot;Customer_Id&quot; 是两个完全不同的名称。

修复插入以不引用名称,或者将名称拼写为大写。不引用是推荐的方式。

<sup>1) 就 H2 而言是如此。其他数据库处理方式不同,例如 PostgreSQL 将名称转为小写。</sup>

英文:

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

That is because H2 handles case-insensitivity by uppercasing unquoted names, so customer_id, Customer_Id, CUSTOMER_ID, and &quot;CUSTOMER_ID&quot; are all the same<sup>1</sup>, but &quot;customer_id&quot; and &quot;Customer_Id&quot; 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>

huangapple
  • 本文由 发表于 2020年8月10日 01:55:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/63329611.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定