JPA Unknown Column in Field List

Question

I have a relation between Accommodation and Booking classes, and also I set a foreign key in booking table.

ALTER TABLE `project`.`booking` 
ADD INDEX `fk_accId_fk_idx` (`accommodation` ASC);
ALTER TABLE `project`.`booking` 
ADD CONSTRAINT `fk_accId_fk`
  FOREIGN KEY (`accommodation`)
  REFERENCES `project`.`accommodation` (`id`)
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

Accommodation class:

@Entity
  ....
public class Accommodation implements Serializable {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "id", unique = true, nullable = false)
  private BigInteger id;

  @OneToMany(mappedBy = "accommodation", fetch = FetchType.EAGER, cascade = CascadeType.REMOVE)
  @JsonManagedReference
  private List < Booking > bookings;
  ......getters setters
}

Booking class:

@Entity
public class Booking implements Serializable {
  ......
  @ManyToOne(fetch = FetchType.EAGER)
  @JoinColumn(name = "bookings", nullable = true)
  @JsonBackReference
  private Accommodation accommodation;
  ....getters setters
}

When I execute a query for listing accommodations, I get unknown column in field list error.

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'bookings7_.bookings' in 'field list'

Even I set the relation and define the foreign key in table, what is the reason that I get this error?

Answer 1

Try to define your join-table mapping manually in JPA. Drop your schema and let JPA create your tables:

Accommodation class

@OneToMany(mappedBy = "accommodation", fetch = FetchType.EAGER, cascade = CascadeType.REMOVE)
@JsonManagedReference
private List < Booking > bookings;

Booking class

@ManyToOne(fetch = FetchType.EAGER)
@JoinTable(name = "accommodation_booking_join_table", 
           joinColumns = {@JoinColumn(name="booking_id")},
           inverseJoinColumns = @JoinColumn(name = "accommodation_id"))
@JsonBackReference
private Accommodation accommodation;