mysql - Java JPA Repository One To One Join Table Wrong Join Variable and Unknown Column Error - Stack Overflow

admin管理员组

文章数量:1026630

I am struggling to get a one to one join relationship JPA repository call to work.

I have two tables product and display_name so I made the follow entities

 @Entity
 @Table(name = "product")
 @NoArgsConstructor
 @ToString(callSuper = true)
 @EqualsAndHashCode(callSuper = true, of = "id")
 @Getter
 @Setter
 public class Product {
 
   @Builder
   public Product(
       UUID id,
       ...
       String name) {
     this.id = id
     ...
     this.name = name;
   }
 
   @Id
   @Column(name = "id", nullable = false, updatable = false, unique = true)
   protected UUID id;
 
   ...
 
   @Column(name = "name", nullable = false)
   private String name;
 }

 @Entity
 @Table(name = "display_name")
 @NoArgsConstructor
 @ToString()
 @Getter
 @Setter
 public class DisplayName {
 
   @Builder
   public DisplayName(
       String systemName,
       String displayName) {
     this.systemName = systemName;
     this.displayName = displayName;
   }
 
   @Id
   @Column(name = "system_name", nullable = false)
   private String systemName;
 
   @Column(name = "display_name", nullable = false)
   private String displayName;

And now I want to use my JPA Repository for Product to do something similar to this query that fetches both tables data:

 SELECT * FROM product
 LEFT JOIN display_name
 ON name = system_name;

This a one to one relationship meaning that each name in Product maps to one system_name and the other way too so I added some foreign keys (not sure if they are needed)

ALTER TABLE display_name
 ADD CONSTRAINT fk_system_name
 FOREIGN KEY (system_name) REFERENCES product (`name`) ON DELETE CASCADE;
 
 ALTER TABLE product
 ADD CONSTRAINT fk_name
 FOREIGN KEY (`name`) REFERENCES display_name (system_name) ON DELETE CASCADE;

then I a added one to one field to Prdouct for DisplayName

   @OneToOne()
   @JoinTable(
       name = "display_name",
       joinColumns = {@JoinColumn(name = "system_name")},
       inverseJoinColumns = {@JoinColumn(name = "name")})
   @Exclude
   private DisplayName displayName;

So now I have this jpa repostiory that I want to get all the Product values from

 public interface ProductRepository
     extends JpaRepository<Product, UUID>, JpaSpecificationExecutor<Product> {
         Page<Product> findAllByName(String name);
     }

but when I call findAllByName in my rest controller I get following error

2024-11-16 10:38:07 2024-11-16 15:38:07.706 ERROR 1 --- [io-10005-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: .springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [
 select p1_0.id,p1_0.name,p1_2.name from product p1_0 left join display_name p1_2 on p1_0.id=p1_2.system_name [(conn=76324) Unknown column 'p1_2.name' in 'field list'] [n/a]; SQL [n/a]] with root cause [] 
 2024-11-16 10:38:07 
 2024-11-16 10:38:07 java.sql.SQLSyntaxErrorException: (conn=76324) Unknown column 'p1_2.name' in 'field list'

which I am not sure why it's joining product.id on display_name.system_name and looking for display_name.name when that does not exit? I probably setup the @OneToOne field wrong but I am not quite sure what the correct syntax should be. Thanks for any help in advance

I am struggling to get a one to one join relationship JPA repository call to work.

I have two tables product and display_name so I made the follow entities

 @Entity
 @Table(name = "product")
 @NoArgsConstructor
 @ToString(callSuper = true)
 @EqualsAndHashCode(callSuper = true, of = "id")
 @Getter
 @Setter
 public class Product {
 
   @Builder
   public Product(
       UUID id,
       ...
       String name) {
     this.id = id
     ...
     this.name = name;
   }
 
   @Id
   @Column(name = "id", nullable = false, updatable = false, unique = true)
   protected UUID id;
 
   ...
 
   @Column(name = "name", nullable = false)
   private String name;
 }

 @Entity
 @Table(name = "display_name")
 @NoArgsConstructor
 @ToString()
 @Getter
 @Setter
 public class DisplayName {
 
   @Builder
   public DisplayName(
       String systemName,
       String displayName) {
     this.systemName = systemName;
     this.displayName = displayName;
   }
 
   @Id
   @Column(name = "system_name", nullable = false)
   private String systemName;
 
   @Column(name = "display_name", nullable = false)
   private String displayName;

And now I want to use my JPA Repository for Product to do something similar to this query that fetches both tables data:

 SELECT * FROM product
 LEFT JOIN display_name
 ON name = system_name;

This a one to one relationship meaning that each name in Product maps to one system_name and the other way too so I added some foreign keys (not sure if they are needed)

ALTER TABLE display_name
 ADD CONSTRAINT fk_system_name
 FOREIGN KEY (system_name) REFERENCES product (`name`) ON DELETE CASCADE;
 
 ALTER TABLE product
 ADD CONSTRAINT fk_name
 FOREIGN KEY (`name`) REFERENCES display_name (system_name) ON DELETE CASCADE;

then I a added one to one field to Prdouct for DisplayName

   @OneToOne()
   @JoinTable(
       name = "display_name",
       joinColumns = {@JoinColumn(name = "system_name")},
       inverseJoinColumns = {@JoinColumn(name = "name")})
   @Exclude
   private DisplayName displayName;

So now I have this jpa repostiory that I want to get all the Product values from

 public interface ProductRepository
     extends JpaRepository<Product, UUID>, JpaSpecificationExecutor<Product> {
         Page<Product> findAllByName(String name);
     }

but when I call findAllByName in my rest controller I get following error

2024-11-16 10:38:07 2024-11-16 15:38:07.706 ERROR 1 --- [io-10005-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: .springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [
 select p1_0.id,p1_0.name,p1_2.name from product p1_0 left join display_name p1_2 on p1_0.id=p1_2.system_name [(conn=76324) Unknown column 'p1_2.name' in 'field list'] [n/a]; SQL [n/a]] with root cause [] 
 2024-11-16 10:38:07 
 2024-11-16 10:38:07 java.sql.SQLSyntaxErrorException: (conn=76324) Unknown column 'p1_2.name' in 'field list'

which I am not sure why it's joining product.id on display_name.system_name and looking for display_name.name when that does not exit? I probably setup the @OneToOne field wrong but I am not quite sure what the correct syntax should be. Thanks for any help in advance

• java
• mysql
• spring-boot
• hibernate
• jpa

Share Improve this question Follow asked Nov 16, 2024 at 17:14 LucyElyLucyEly 7322 silver badges1313 bronze badges 1

• JPA doesn't support foreign keys going to non-ID fields. If the Product's name property/column unique, why not use it as the entities ID? you can then easily setup a FK relation from DisplayName -> Product using just OneToOne with a JoinColumn(name = "display_name", referencedColumnName = "name"). While I believe Hibernate and other providers do allow FKs to unique keys in the reference object, it is sometimes more difficult - and you'll have to remember that JPA caches the entities on the ID which might make relationship traversal more expensive. – Chris Commented Nov 18, 2024 at 15:02

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Modify your code like this:

// Product entity
    @Column(name = "name", unique = true)
    private String name;

    @OneToOne(mappedBy = "product", cascade = CascadeType.ALL)
    @JsonManagedReference
    private DisplayName displayName;

// DisplayName entity
    @OneToOne
    @JsonBackReference
    @ToString.Exclude
    @JoinColumn(referencedColumnName = "name")
    private Product product;

public interface ProductRepository extends JpaRepository<Product, UUID> {
    Page<Product> findAllByName(String name);
}

In the database define a constraint similar to this:

alter table display_name add constraint display_name_const_1
foreign key (product_name) references product (name)

I am struggling to get a one to one join relationship JPA repository call to work.

I have two tables product and display_name so I made the follow entities

 @Entity
 @Table(name = "product")
 @NoArgsConstructor
 @ToString(callSuper = true)
 @EqualsAndHashCode(callSuper = true, of = "id")
 @Getter
 @Setter
 public class Product {
 
   @Builder
   public Product(
       UUID id,
       ...
       String name) {
     this.id = id
     ...
     this.name = name;
   }
 
   @Id
   @Column(name = "id", nullable = false, updatable = false, unique = true)
   protected UUID id;
 
   ...
 
   @Column(name = "name", nullable = false)
   private String name;
 }

 @Entity
 @Table(name = "display_name")
 @NoArgsConstructor
 @ToString()
 @Getter
 @Setter
 public class DisplayName {
 
   @Builder
   public DisplayName(
       String systemName,
       String displayName) {
     this.systemName = systemName;
     this.displayName = displayName;
   }
 
   @Id
   @Column(name = "system_name", nullable = false)
   private String systemName;
 
   @Column(name = "display_name", nullable = false)
   private String displayName;

And now I want to use my JPA Repository for Product to do something similar to this query that fetches both tables data:

 SELECT * FROM product
 LEFT JOIN display_name
 ON name = system_name;

This a one to one relationship meaning that each name in Product maps to one system_name and the other way too so I added some foreign keys (not sure if they are needed)

ALTER TABLE display_name
 ADD CONSTRAINT fk_system_name
 FOREIGN KEY (system_name) REFERENCES product (`name`) ON DELETE CASCADE;
 
 ALTER TABLE product
 ADD CONSTRAINT fk_name
 FOREIGN KEY (`name`) REFERENCES display_name (system_name) ON DELETE CASCADE;

then I a added one to one field to Prdouct for DisplayName

   @OneToOne()
   @JoinTable(
       name = "display_name",
       joinColumns = {@JoinColumn(name = "system_name")},
       inverseJoinColumns = {@JoinColumn(name = "name")})
   @Exclude
   private DisplayName displayName;

So now I have this jpa repostiory that I want to get all the Product values from

 public interface ProductRepository
     extends JpaRepository<Product, UUID>, JpaSpecificationExecutor<Product> {
         Page<Product> findAllByName(String name);
     }

but when I call findAllByName in my rest controller I get following error

2024-11-16 10:38:07 2024-11-16 15:38:07.706 ERROR 1 --- [io-10005-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: .springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [
 select p1_0.id,p1_0.name,p1_2.name from product p1_0 left join display_name p1_2 on p1_0.id=p1_2.system_name [(conn=76324) Unknown column 'p1_2.name' in 'field list'] [n/a]; SQL [n/a]] with root cause [] 
 2024-11-16 10:38:07 
 2024-11-16 10:38:07 java.sql.SQLSyntaxErrorException: (conn=76324) Unknown column 'p1_2.name' in 'field list'

which I am not sure why it's joining product.id on display_name.system_name and looking for display_name.name when that does not exit? I probably setup the @OneToOne field wrong but I am not quite sure what the correct syntax should be. Thanks for any help in advance

I am struggling to get a one to one join relationship JPA repository call to work.

I have two tables product and display_name so I made the follow entities

 @Entity
 @Table(name = "product")
 @NoArgsConstructor
 @ToString(callSuper = true)
 @EqualsAndHashCode(callSuper = true, of = "id")
 @Getter
 @Setter
 public class Product {
 
   @Builder
   public Product(
       UUID id,
       ...
       String name) {
     this.id = id
     ...
     this.name = name;
   }
 
   @Id
   @Column(name = "id", nullable = false, updatable = false, unique = true)
   protected UUID id;
 
   ...
 
   @Column(name = "name", nullable = false)
   private String name;
 }

 @Entity
 @Table(name = "display_name")
 @NoArgsConstructor
 @ToString()
 @Getter
 @Setter
 public class DisplayName {
 
   @Builder
   public DisplayName(
       String systemName,
       String displayName) {
     this.systemName = systemName;
     this.displayName = displayName;
   }
 
   @Id
   @Column(name = "system_name", nullable = false)
   private String systemName;
 
   @Column(name = "display_name", nullable = false)
   private String displayName;

And now I want to use my JPA Repository for Product to do something similar to this query that fetches both tables data:

 SELECT * FROM product
 LEFT JOIN display_name
 ON name = system_name;

This a one to one relationship meaning that each name in Product maps to one system_name and the other way too so I added some foreign keys (not sure if they are needed)

ALTER TABLE display_name
 ADD CONSTRAINT fk_system_name
 FOREIGN KEY (system_name) REFERENCES product (`name`) ON DELETE CASCADE;
 
 ALTER TABLE product
 ADD CONSTRAINT fk_name
 FOREIGN KEY (`name`) REFERENCES display_name (system_name) ON DELETE CASCADE;

then I a added one to one field to Prdouct for DisplayName

   @OneToOne()
   @JoinTable(
       name = "display_name",
       joinColumns = {@JoinColumn(name = "system_name")},
       inverseJoinColumns = {@JoinColumn(name = "name")})
   @Exclude
   private DisplayName displayName;

So now I have this jpa repostiory that I want to get all the Product values from

 public interface ProductRepository
     extends JpaRepository<Product, UUID>, JpaSpecificationExecutor<Product> {
         Page<Product> findAllByName(String name);
     }

but when I call findAllByName in my rest controller I get following error

2024-11-16 10:38:07 2024-11-16 15:38:07.706 ERROR 1 --- [io-10005-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: .springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [
 select p1_0.id,p1_0.name,p1_2.name from product p1_0 left join display_name p1_2 on p1_0.id=p1_2.system_name [(conn=76324) Unknown column 'p1_2.name' in 'field list'] [n/a]; SQL [n/a]] with root cause [] 
 2024-11-16 10:38:07 
 2024-11-16 10:38:07 java.sql.SQLSyntaxErrorException: (conn=76324) Unknown column 'p1_2.name' in 'field list'

which I am not sure why it's joining product.id on display_name.system_name and looking for display_name.name when that does not exit? I probably setup the @OneToOne field wrong but I am not quite sure what the correct syntax should be. Thanks for any help in advance

• java
• mysql
• spring-boot
• hibernate
• jpa

Share Improve this question Follow asked Nov 16, 2024 at 17:14 LucyElyLucyEly 7322 silver badges1313 bronze badges 1

• JPA doesn't support foreign keys going to non-ID fields. If the Product's name property/column unique, why not use it as the entities ID? you can then easily setup a FK relation from DisplayName -> Product using just OneToOne with a JoinColumn(name = "display_name", referencedColumnName = "name"). While I believe Hibernate and other providers do allow FKs to unique keys in the reference object, it is sometimes more difficult - and you'll have to remember that JPA caches the entities on the ID which might make relationship traversal more expensive. – Chris Commented Nov 18, 2024 at 15:02

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

Modify your code like this:

// Product entity
    @Column(name = "name", unique = true)
    private String name;

    @OneToOne(mappedBy = "product", cascade = CascadeType.ALL)
    @JsonManagedReference
    private DisplayName displayName;

// DisplayName entity
    @OneToOne
    @JsonBackReference
    @ToString.Exclude
    @JoinColumn(referencedColumnName = "name")
    private Product product;

public interface ProductRepository extends JpaRepository<Product, UUID> {
    Page<Product> findAllByName(String name);
}

In the database define a constraint similar to this:

alter table display_name add constraint display_name_const_1
foreign key (product_name) references product (name)

本文标签： mysqlJava JPA Repository One To One Join Table Wrong Join Variable and Unknown Column ErrorStack Overflow