Spring JPA在数据库列上出现错误，返回与列名相关的错误。

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

Spring JPA error on DB column returns error related to column name

问题

我在尝试使用JPA查询Postgres数据库时收到了一个错误。

Student.java:

  1. import javax.persistence.Entity;
  2. import javax.persistence.GeneratedValue;
  3. import javax.persistence.Id;
  5. @Entity
  6. public class Student {
  7.     @Id
  8.     @GeneratedValue
  9.     private Long id;
  10.     private String name;
  11.     private String passportNumber;
  13.     public Student() {
  14.         super();
  15.     }
  17.     public Student(Long id, String name, String passportNumber) {
  18.         super();
  19.         this.id = id;
  20.         this.name = name;
  21.         this.passportNumber = passportNumber;
  22.     }
  23.     public Long getId() {
  24.         return id;
  25.     }
  26.     public void setId(Long id) {
  27.         this.id = id;
  28.     }
  29.     public String getName() {
  30.         return name;
  31.     }
  32.     public void setName(String name) {
  33.         this.name = name;
  34.     }
  35.     public String getPassportNumber() {
  36.         return passportNumber;
  37.     }
  38.     public void setPassportNumber(String passportNumber) {
  39.         this.passportNumber = passportNumber;
  40.     }
  42. }

appliction.properties:

  1. spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
  2. spring.jpa.hibernate.ddl-auto=none
  3. spring.jpa.hibernate.show-sql=true
  4. spring.datasource.url=jdbc:postgresql://localhost:5432/shorten-db
  5. spring.datasource.username=my_user
  6. spring.datasource.password=my_password
  7. spring.datasource.initialization-mode=always
  8. spring.datasource.initialize=true
  9. spring.datasource.schema=classpath:/schema.sql
  10. spring.datasource.continue-on-error=true

appliction.yml:

  1. spring:
  2.   datasource:
  3.     url: jdbc:postgresql://localhost:5432/shorten-db
  4.     username: my_user
  5.     password: my_password
  6.     driverClassName: org.postgresql.Driver
  7.   datasource.schema=classpath:/schema.sql

引用此架构：

  1. DROP TABLE student;
  3. CREATE TABLE student
  5. (
  7.  id varchar(100) NOT NULL,
  9.  name varchar(100) DEFAULT NULL,
  11.  passportNumber varchar(100) DEFAULT NULL,
  13.  PRIMARY KEY (id)
  15. );

当我调用服务"/students"时：

  1. @PostMapping("/students")
  2. public ResponseEntity<Object> createStudent(@RequestBody Student student) {
  3.     Student savedStudent = studentRepository.save(student);
  5.     URI location = ServletUriComponentsBuilder.fromCurrentRequest().path("/{id}")
  6.             .buildAndExpand(savedStudent.getId()).toUri();
  8.     return ResponseEntity.created(location).build();
  10. }

我收到以下错误：

  1. postgres-db | 2020-08-28 21:46:28.108 UTC [257] HINT:  Perhaps you meant to reference the column "student0_.passportnumber".
  2. postgres-db | 2020-08-28 21:46:28.108 UTC [257] STATEMENT:  select student0_.id as id1_1_, student0_.name as name2_1_, student0_.passport_number as passport3_1_ from student student0_

如何实现消息中指定的提示 Perhaps you meant to reference the column "student0_.passportnumber"？我没有显式指定SQL，而是尝试仅使用JPA。我是否需要一个自定义查询，还是应该修改Student实体？

英文:

I receive an error when trying to query a Postgres DB using JPA.

Student.java:

  1. import javax.persistence.Entity;
  2. import javax.persistence.GeneratedValue;
  3. import javax.persistence.Id;
  5. @Entity
  6. public class Student {
  7.     @Id
  8.     @GeneratedValue
  9.     private Long id;
  10.     private String name;
  11.     private String passportNumber;
  13.     public Student() {
  14.         super();
  15.     }
  17.     public Student(Long id, String name, String passportNumber) {
  18.         super();
  19.         this.id = id;
  20.         this.name = name;
  21.         this.passportNumber = passportNumber;
  22.     }
  23.     public Long getId() {
  24.         return id;
  25.     }
  26.     public void setId(Long id) {
  27.         this.id = id;
  28.     }
  29.     public String getName() {
  30.         return name;
  31.     }
  32.     public void setName(String name) {
  33.         this.name = name;
  34.     }
  35.     public String getPassportNumber() {
  36.         return passportNumber;
  37.     }
  38.     public void setPassportNumber(String passportNumber) {
  39.         this.passportNumber = passportNumber;
  40.     }
  42. }

appliction.properties:

  1. spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQLDialect
  3. spring.jpa.hibernate.ddl-auto=none
  5. spring.jpa.hibernate.show-sql=true
  7. spring.datasource.url=jdbc:postgresql://localhost:5432/shorten-db
  9. spring.datasource.username=my_user
  11. spring.datasource.password=my_password
  13. spring.datasource.initialization-mode=always
  15. spring.datasource.initialize=true
  17. spring.datasource.schema=classpath:/schema.sql
  19. spring.datasource.continue-on-error=true

appliction.yml:

  1. spring:
  2.   datasource:
  3.     url: jdbc:postgresql://localhost:5432/shorten-db
  4.     username: my_user
  5.     password: my_password
  6.     driverClassName: org.postgresql.Driver
  8.  spring.datasource.schema=classpath:/schema.sql

refers to this schema:

  1. DROP TABLE student;
  3. CREATE TABLE student
  5. (
  7.  id varchar(100) NOT NULL,
  9.  name varchar(100) DEFAULT NULL,
  11.  passportNumber varchar(100) DEFAULT NULL,
  13.  PRIMARY KEY (id)
  15. );

When I invoke the service "/students" :

  1. @PostMapping(&quot;/students&quot;)
  2. public ResponseEntity&lt;Object&gt; createStudent(@RequestBody Student student) {
  3.     Student savedStudent = studentRepository.save(student);
  5.     URI location = ServletUriComponentsBuilder.fromCurrentRequest().path(&quot;/{id}&quot;)
  6.             .buildAndExpand(savedStudent.getId()).toUri();
  8.     return ResponseEntity.created(location).build();
  10. }

I receive the following error:

  1. postgres-db    | 2020-08-28 21:46:28.108 UTC [257] HINT:  Perhaps you meant to reference the column &quot;student0_.passportnumber&quot;.
  2. postgres-db    | 2020-08-28 21:46:28.108 UTC [257] STATEMENT:  select student0_.id as id1_1_, student0_.name as name2_1_, student0_.passport_number as passport3_1_ from student student0_

How to implement the hint specified in the message Perhaps you meant to reference the column &quot;student0_.passportnumber&quot; I'm not explicitly specifying the SQL and trying to use JPA only. Do I need a custom query or should I modify the student entity ?

答案1

得分: 4

JPA期望列名采用小写形式，每个单词之间用下划线分隔。这会转换为驼峰命名法的Java变量名。在您的情况下，您的Java变量名为passportNumber，对应于passport_number。有三种方法可以解决这个问题：

  1. 将Java变量重命名为passportnumber，这意味着它是一个单词。不幸的是，这看起来不太好，因为它违反了Java的变量命名约定。

  2. 将表列重命名为passport_number，以便正确连接JPA。

  3. 使用 @Column(name="passportNumber") 注解您的Java变量。

英文:

JPA expects column names in the form of lower case and each word separated by an underscore. This translates to a camel cased java variable names. In your case, you have java variable name as passportNumber which translates to passport_number. There are three ways this can be solved:

  1. Rename java variable to passportnumber, meaning its a single word. Unfortunately, it doesn't look so good since it breaks the variable naming convention of java.

  2. Rename table column to passport_number, hence making correct JPA connection.

  3. Annotate your java variable with @Column(name="passportNumber").

huangapple
  • 本文由 发表于 2020年8月29日 05:55:02
  • 转载请务必保留本文链接：https://go.coder-hub.com/63641307.html
  • java
  • jpa
  • postgresql
  • spring
  • spring-boot
匿名

发表评论

匿名网友

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

确定