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;
  4. @Entity
  5. public class Student {
  6. @Id
  7. @GeneratedValue
  8. private Long id;
  9. private String name;
  10. private String passportNumber;
  11. public Student() {
  12. super();
  13. }
  14. public Student(Long id, String name, String passportNumber) {
  15. super();
  16. this.id = id;
  17. this.name = name;
  18. this.passportNumber = passportNumber;
  19. }
  20. public Long getId() {
  21. return id;
  22. }
  23. public void setId(Long id) {
  24. this.id = id;
  25. }
  26. public String getName() {
  27. return name;
  28. }
  29. public void setName(String name) {
  30. this.name = name;
  31. }
  32. public String getPassportNumber() {
  33. return passportNumber;
  34. }
  35. public void setPassportNumber(String passportNumber) {
  36. this.passportNumber = passportNumber;
  37. }
  38. }

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;
  2. CREATE TABLE student
  3. (
  4. id varchar(100) NOT NULL,
  5. name varchar(100) DEFAULT NULL,
  6. passportNumber varchar(100) DEFAULT NULL,
  7. PRIMARY KEY (id)
  8. );

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

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

我收到以下错误:

  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;
  4. @Entity
  5. public class Student {
  6. @Id
  7. @GeneratedValue
  8. private Long id;
  9. private String name;
  10. private String passportNumber;
  11. public Student() {
  12. super();
  13. }
  14. public Student(Long id, String name, String passportNumber) {
  15. super();
  16. this.id = id;
  17. this.name = name;
  18. this.passportNumber = passportNumber;
  19. }
  20. public Long getId() {
  21. return id;
  22. }
  23. public void setId(Long id) {
  24. this.id = id;
  25. }
  26. public String getName() {
  27. return name;
  28. }
  29. public void setName(String name) {
  30. this.name = name;
  31. }
  32. public String getPassportNumber() {
  33. return passportNumber;
  34. }
  35. public void setPassportNumber(String passportNumber) {
  36. this.passportNumber = passportNumber;
  37. }
  38. }

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. spring.datasource.schema=classpath:/schema.sql

refers to this schema:

  1. DROP TABLE student;
  2. CREATE TABLE student
  3. (
  4. id varchar(100) NOT NULL,
  5. name varchar(100) DEFAULT NULL,
  6. passportNumber varchar(100) DEFAULT NULL,
  7. PRIMARY KEY (id)
  8. );

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);
  4. URI location = ServletUriComponentsBuilder.fromCurrentRequest().path(&quot;/{id}&quot;)
  5. .buildAndExpand(savedStudent.getId()).toUri();
  6. return ResponseEntity.created(location).build();
  7. }

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
匿名

发表评论

匿名网友

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

确定