Link on Non-primary key?

Support
#1

Is it possible to link two entities on an alternate key? I have a legacy database, Orders and Products. The Products have a numeric ID and a Product Code. Unfortunately the Order row has the Product Code and not the Product ID in it.

Can I link on the Product Code declaratively?

#2

I think I solved it. Does this look correct?


@Lookup(type = LookupType.DROPDOWN, actions = {"lookup", "clear", "open"})
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "PRODUCT", referencedColumnName = "PRODCODE")
private Product product;

I added the referencedColumnName, which causes a lookup on a different column in the referenced table.

#3

Did you manage to solve the problem?

#4

Yes, please look at the comment with the Best Answer checkmark. That is the solution.

#5

Which database was used, postgreSQL or hsql? Can You transfer the source code?

#6

I actually use Oracle, but this code is based on the EclipseLink ORM that CUBA platform uses. It should work with any supported database. After you declare your Entity (use CUBA Studio!), you find the field that you want to link to in the Java code. In my case, it is a Many to One link from Orders Lines to Products. My problem was the the Products table is a legacy table and I had to adapt to the column name used as the referencing key (PRODCODE) - which was NOT the primary key. The column in my Order Line Entity table is called PRODUCT. So, I had to tell CUBA Platform to link Order_Line.PRODUCT to Product.PRODCODE. Here is the complete declaration:

@Lookup(type = LookupType.DROPDOWN, actions = {"lookup", "clear", "open"})
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "PRODUCT", referencedColumnName = "PRODCODE")
private Product product;

The EclipseLink parts are the @ManyToOne, which is automatically generated for you by CUBA Studio, and the @JoinColumn, which I had to add. @JoinColumn [url=http://www.eclipse.org/eclipselink/api/2.5/javax/persistence/JoinColumn.html]http://www.eclipse.org/eclipselink/api/2.5/javax/persistence/JoinColumn.html[/url] says that the column named “PRODUCT” in my current table links to the column named “PRODCODE” in the other entity.
The @Lookup is a CUBA Platform attribute describing how I want the field displayed - as a DropDown box with Lookup, Clear, and Open buttons. [url=https://doc.cuba-platform.com/manual-6.5/entity_attr_annotations.html]https://doc.cuba-platform.com/manual-6.5/entity_attr_annotations.html[/url]
Hopefully this helps.

#7

Thank you!
Unfortunately I did not succeed! If you do it on hsqldb it works, but does not work for postgresql.

#8

I guess this is now a job for the developers…