@OneToMany association joining on the wrong field

Question

I have 2 tables, devices which contains a list of devices and dev_tags, which contains a list of asset tags for these devices. The tables join on dev_serial_num, which is the primary key of neither table. The devices are unique on their ip_address field and they have a primary key identified by dev_id. The devices "age out" after 2 weeks. Therefore, the same piece of hardware can show up more than once in devices.

I mention that to explain why there is a OneToMany relationship between dev_tags and devices where it seems that this should be a OneToOne relationship.

So I have my 2 entities

@Entity
@Table(name = "dev_tags")
public class DevTags implements Serializable {

private Integer tagId;
private String devTagId;
private String devSerialNum;
private List<Devices> devices;

@Id
@GeneratedValue
@Column(name = "tag_id")
public Integer getTagId() {
 return tagId;
}

public void setTagId(Integer tagId) {
 this.tagId = tagId;
}

@Column(name="dev_tag_id")
public String getDevTagId() {
 return devTagId;
}

public void setDevTagId(String devTagId) {
 this.devTagId = devTagId;
}

@Column(name="dev_serial_num")
public String getDevSerialNum() {
 return devSerialNum;
}

public void setDevSerialNum(String devSerialNum) {
 this.devSerialNum = devSerialNum;
}


@OneToMany(mappedBy="devSerialNum")
public List<Devices> getDevices() {
 return devices;
}

public void setDevices(List<Devices> devices) {
 this.devices = devices;
}


}

and this one

public class Devices implements java.io.Serializable {

private Integer devId;
private Integer officeId;
private String devSerialNum;
private String devPlatform;
private String devName;
private OfficeView officeView;
private DevTags devTag;

public Devices() {
}

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "dev_id", unique = true, nullable = false)
public Integer getDevId() {
 return this.devId;
}

public void setDevId(Integer devId) {
 this.devId = devId;
}

@Column(name = "office_id", nullable = false, insertable=false, updatable=false)
public Integer getOfficeId() {
 return this.officeId;
}

public void setOfficeId(Integer officeId) {
 this.officeId = officeId;
}

@Column(name = "dev_serial_num", nullable = false, length = 64, insertable=false, updatable=false)
@NotNull
@Length(max = 64)
public String getDevSerialNum() {
 return this.devSerialNum;
}

public void setDevSerialNum(String devSerialNum) {
 this.devSerialNum = devSerialNum;
}

@Column(name = "dev_platform", nullable = false, length = 64)
@NotNull
@Length(max = 64)
public String getDevPlatform() {
 return this.devPlatform;
}

public void setDevPlatform(String devPlatform) {
 this.devPlatform = devPlatform;
}

@Column(name = "dev_name")
public String getDevName() {
 return devName;
}

public void setDevName(String devName) {
 this.devName = devName;
}

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "office_id")
public OfficeView getOfficeView() {
 return officeView;
}

public void setOfficeView(OfficeView officeView) {
 this.officeView = officeView;
}

@ManyToOne()
@JoinColumn(name="dev_serial_num")
public DevTags getDevTag() {
 return devTag;
}

public void setDevTag(DevTags devTag) {
 this.devTag = devTag;
}

}

I messed around a lot with @JoinColumn(name=) and the mappedBy attribute of @OneToMany and I just cannot get this right. I finally got the darn thing to compile, but the query is still trying to join devices.dev_serial_num to dev_tags.tag_id, the @Id for this entity. Here is the transcript from the console:

13:12:16,970 INFO  [STDOUT] Hibernate: 
select
    devices0_.office_id as office5_2_,
    devices0_.dev_id as dev1_2_,
    devices0_.dev_id as dev1_156_1_,
    devices0_.dev_name as dev2_156_1_,
    devices0_.dev_platform as dev3_156_1_,
    devices0_.dev_serial_num as dev4_156_1_,
    devices0_.office_id as office5_156_1_,
    devtags1_.tag_id as tag1_157_0_,
    devtags1_.comment as comment157_0_,
    devtags1_.dev_serial_num as dev3_157_0_,
    devtags1_.dev_tag_id as dev4_157_0_ 
from
    ond.devices devices0_ 
left outer join
    ond.dev_tags devtags1_ 
        on devices0_.dev_serial_num=devtags1_.tag_id 
where
    devices0_.office_id=?
13:12:16,970 INFO  [IntegerType] could not read column value from result set: dev4_156_1_; Invalid value for getInt() - 'FDO1129Y2U4'
13:12:16,970 WARN  [JDBCExceptionReporter] SQL Error: 0, SQLState: S1009
13:12:16,970 ERROR [JDBCExceptionReporter] Invalid value for getInt() - 'FDO1129Y2U4'

That value for getInt() 'FD01129Y2U4' is a serial number, definitely not an Int! What am I missing/misunderstanding here? Can I join 2 tables on any fields I want or does at least one have to be a primary key?

Answer 1

The short answer is "no, you can't join 2 tables on any fields"; association will always refer to primary key on one side.

"mappedBy" attribute for @OneToMany is used for bi-directional assocations and specifies the name of the property on collection element that maps back to owner entity as @ManyToOne. In your case,

@OneToMany(mappedBy="devSerialNum")

declaration is invalid; it should be changed to

@OneToMany(mappedBy="devTag")

instead if you want to maintain a bi-directional relationship. @JoinColumn can be used with @ManyToOne to specify the name of the (foreign key) column pointing to the other table. In your case,

@ManyToOne()
@JoinColumn(name="dev_serial_num")
public DevTags getDevTag() {

declaration says that you have a column called dev_serial_num in your devices table that will be a foreign key pointing to dev_tags.tag_id which is also wrong.

I'm not quite clear on what you meant by "devices age out", but it seems to me that you're trying to merge two separate concepts into a single table which is where all these issues stem from. Consider instead separating your "devices" table (and entity) into two:

1. "Core" device (for the lack of better name) should contain truly unique attributes, like serial number. Your DevTags entity will be linked as one-to-many to this one.
2. Device "version" would contain attributes applicable to individual "version". There will be multiple "versions" for each "core" device; "version" is what will be updated every 2 weeks.