I have a table that contains descriptions of various types. To differentiate the types we have a column with the "description group" name.
So the table can have user defined groups for Invoice Statuses, Countries, Regions or whatever they think of next.
This is not a design choice I would have made, but I'm integrating into a 3rd party system.
I can view and assign values successfully, but it generates the wrong SQL if I use that column in a JPQL query.
To add a field to an Entity that references a record from this table I use non standard joins as explained in the OpenJPA docs:
Chapter 6: Non-Standard Joins. ( I can't include the URL directly here)
For OpenJPA my mapping looks like this:
@JoinColumns({@JoinColumn(name = "fk_status", referencedColumnName = "description_id")
, @JoinColumn(name = "DBName\".\"dbo\".\"TheDescriptionTable.description_group", referencedColumnName = "'InvoiceStatus'")
})
@ManyToOne
private LookupDescription invoiceStatus;
The quotes above look strange but that's because OpenJPA quotes the column names, making it necessary for me to quote the fully qualified name as I did.
This syntax doesn't work as is in EclipseLink and I have to switch the second join columns around:
@JoinColumns({@JoinColumn(name = "fk_status", referencedColumnName = "description_id")
, @JoinColumn(name = "'InvoiceStatus'", referencedColumnName = "DBName.dbo.TheDescriptionTable.description_group")
})
@ManyToOne
private LookupDescription invoiceStatus;
So, if we had a status column for an Invoice then we only need to store the numeric value of the related table in our Invoice table and the "InvoiceStatus" discriminator is assumed.
This works well and I'm able to view existing data correctly as well as assign values.
Let's assume we want to find Invoices in a specific status:
SELECT DISTINCT o.id
FROM Invoices AS o
WHERE o.invoiceStatus.descriptionId = ?1
The generated SQL that throws the error is:
SELECT DISTINCT t0.ID, t0.INVOICE_NUMBER
FROM INVOICE t0, DBName.dbo.TheDescriptionTable t1
WHERE (t1.description_group = t0.'InvoiceStatus') AND (t1.description_table_id = t0.fk_status)
The correct where clause would contain: t1.description_group = 'InvoiceStatus'
So the problem is that the table prefix should not be used with the fixed value.
It seems like it could be a fairly simple fix in EclipseLink but I don't know where to look...
It would be great if EclipseLink would have the same functionality as OpenJPA, which has it's own quirks that I'd rather not deal with.
I know this is not a very common use case but with some guidance I'd be willing to implement and test any suggestions.
Thanks
] 
Search
Help
Register
Login
Home
