0

For more context, please see my other question.

I want to join my Employee entity class to Code entity class but the Code PK is composite (DOMAIN, CODE) because it lists many different code domains, yet the codes that go in the Employee class/table are all from within a single domain, eliminating the need to have a domain field in the Employee class/table (because it would always be the same).

Can I join Employee to Code by using the CODE field in the Employee table and a hardcoded value (e.g. EMP_TYPE) instead of a redundant column?

If my Employee class/table did indeed have that redundant column, I would join it like this:

@JoinColumns({
        @JoinColumn(name = "EMP_TYPE_CODE", referencedColumnName = "CODE"),
        @JoinColumn(name = "DOMAIN", referencedColumnName = "DOMAIN)})
    @ManyToOne(optional = false)
    private Code sharedStatute;

but I REALLY don't want to have that extra column in the DB and in the class because it will always be the same.

What I am trying to accomplish would be equivalent to the following in SQL:

SELECT e.emp_id, e.first_name, e.last_name, c.description as emp_type
FROM Employee e JOIN Code c
ON e.emp_type_code = c.code
WHERE c.domain = 'EMP_TYPE'

as opposed to adding a field domain in the Employee table and populating EVERY SINGLE RECORD with the same value ('EMP_TYPE') and then doing:

SELECT e.emp_id, e.first_name, e.last_name, c.description as emp_type
FROM Employee e JOIN Code c
ON e.emp_type_code = c.code
AND e.domain = c.domain

The former is more efficient because it saves me from having to have a redundant field. So what I am trying to do is the same thing but in JPA.

Some of you may say something to the effect of "why not have a separate lookup table for each code" but I think that's a terrible idea and causes cluttering of DB tables and corresponding application entities. It is much better to have a single code lookup table partitioned by code type (or domain).

Community
  • 1
  • 1
amphibient
  • 29,770
  • 54
  • 146
  • 240
  • See this question instead https://stackoverflow.com/questions/8924928/hibernate-how-to-use-a-constant-as-part-of-composite-foreign-reference – f.carlsen Sep 11 '17 at 17:42

2 Answers2

6

This should work?

@JoinColumn(name = "EMP_TYPE_CODE", referencedColumnName = "CODE")
@Where(clause = "domain = 'EMP_TYPE'")
@ManyToOne(optional = false)
private Code sharedStatute;
Affe
  • 47,174
  • 11
  • 83
  • 83
  • where do you get the `@Where` annotation from ? Doesn't seem to be under `javax.persistence` – amphibient Jan 08 '15 at 01:27
  • It's a hibernate feature, I thought it had made it into JPA2, but apparently not. – Affe Jan 08 '15 at 01:33
  • unfortunately not... is there a different way to accomplish the same thing in JPA ? – amphibient Jan 08 '15 at 01:34
  • 2
    The only way I can think of would be make an 'EmployeeDomainCode' a subclass of 'Code' and use the Domain column as the discriminator value. – Affe Jan 08 '15 at 01:37
  • e.g. can I create a 'pseudo' column in the entity whose value is hard coded to 'EMP_TYPE' and then join on that as opposed to having to have an actual table column? – amphibient Jan 08 '15 at 01:38
  • This works with a caveat: When I try to do this twice in the same entity but with different where clauses I get 'able with that name [MyJoinTable] already associated with entity' There is a @WhereJoinTable annotation I can try out – Christian Bongiorno May 04 '16 at 18:50
0

Note: Apparantly this solution is OpenJPA only.

There is a way to do this with javax.persistence annotations only:

@JoinColumns({
    @JoinColumn(name = "EMP_TYPE_CODE", referencedColumnName = "CODE"),
    @JoinColumn(name = "CODE_TABLE_NAME.DOMAIN", referencedColumnName = "'EMP_TYPE'")})
@ManyToOne(optional = false)
private Code sharedStatute;

Note the single quotes in the referencedColumnName, this is the String value you are looking for.

There is a downside however, when you have multiple Code objects in your entity, the join on DOMAIN will be done only once in jpa, giving bad results. At the moment I circumvent this by making those field load lazily, but that's not ideal.

More information here.

Hans Wouters
  • 588
  • 5
  • 16
  • I can explain the downvote: Single quoted strings do not exist in Java. Single quotes are used for single characters only. Maybe you meant something like: `referenceColumnName = "'EMP_TYPE'"`? – LordOfThePigs Jan 23 '18 at 13:14
  • Thanks, I guess I messed up when I made my working code look more like what the asker was using. Wish someone told me about the typo ':-) – Hans Wouters Jan 24 '18 at 13:56
  • 3
    Also, that seems to be specific to OpenJPA (according to the link you gave). I've just tried to do just that with Hibernate 5, and I get `org.hibernate.MappingException: Unable to find column with logical name: '1' in OBJECT_PARAM` – LordOfThePigs Jan 24 '18 at 14:33
  • Yes this method doesn't work with Hibernate "Unable to find column with logical name:'EMP_TYPE' – Tarun Gupta Apr 08 '19 at 10:35