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?
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;
Try changing your column names to lower case in your db and entity class.
I had a situation like that, and I solved it by changing the field's position on the query. Looks like it's a MySQL bug, like (or the same as) the one mentioned on this post:
https://bugs.mysql.com/bug.php?id=1689
The description of this MySQL bug mentioned a similar workaround solution: "I found that by swapping that field's position in the table with another field that it worked OK."