Bi directional Many to Many: java.sql.SQLSyntaxErrorException: Unknown column


#1

I have 2 tables

  1. Cluster and
  2. EMRLabel
    both having Bi directional MANY TO MANY relationship with a third table 3) Tag with respective intermediate tables named
  3. Cluster_Tag and
  4. EMR_LABEL_TAG , both with extra columns .

The bi directional mappings from both 1) Cluster and 2) EMRLabel to the Tag table has been done according to https://vladmihalcea.com/the-best-way-to-map-a-many-to-many-association-with-extra-columns-when-using-jpa-and-hibernate/

Initially, i had only the bi-directional mappings between Cluster and Tag tables done and it was working fine when using named queries etc but, after including the second bi directional relationship between EMRLabel and Tag table, I keep getting
java.sql.SQLSyntaxErrorException: Unknown column ‘tags1_.emrLabel_id’ in ‘on clause’

whenever I try to run a JPA query joining EMRLabel and Tag like SELECT c FROM EMRLabel c JOIN c.tags ct where ct.tag.tagName=:val1 order by c.priority DESC

i have checked multiple times the column names ,mappings involved but could not find anything wrong, but i am wondering is it because of the Tag entity now having 2 bi directional mappings 1) one to Cluster table and 2) now another to EMRLabel table, is that the issue ?


#2

You need to add the entity mappings and the database tables description. Also, add the data access code you execute and the stack-trace that you get when the failure occurs. Otherwise, it’s impossible to tell what causes the issue.


#3

sorry about that. I found the issue, I add to add @JoinColumn as below to specify the column names explicitly.

@Entity(name = “EMRLabelTag”)
@Table(name = “emr_label_tag”)
public class EMRLabelTagDto {

@EmbeddedId
private EMRLabelTagId id;

@ManyToOne(fetch = FetchType.LAZY)
@MapsId(“emrLabelId”)
@JoinColumn(name = “emr_label_id”)
private EMRLabelDto emrLabel;

@ManyToOne(fetch = FetchType.LAZY)
@MapsId(“tagId”)
@JoinColumn(name = “tag_id”)
private TagDto tag;

@Column(name=“create_time”, nullable = false, updatable = false)
@CreationTimestamp
private Timestamp createTimestamp;

@Column(name=“last_update”, nullable = false)
@UpdateTimestamp
private Timestamp updateTimestamp;

private EMRLabelTagDto() {}