Advice wanted - SPRING JPA JoinColumns

Question

Just started exploring SPRING and JPA. So bear with me, please.

Lets assume I have two tables

EMPLOYEE TABLE                  ATTACHMENT TABLE
---------------------------     -------------------------------------------
ID | FIRSTNAME | LASTNAME |     ID  |  REF_TYPE  | REF_ID | FILE
---------------------------     -------------------------------------------
1  | EMP1      | EMPL1    |     1   | "EMPLOYEE" | 1      | EMPL1_FIle1.jpg
---------------------------     -------------------------------------------
2  | EMP2      | EMPL2    |     2   | "EMPLOYEE" | 1      | EMPL1_FIle2.jpg
                                -------------------------------------------
                                3   | "EMPLOYEE" | 2      | EMPL2_FIle1.jpg

** To keep attachments flexible (so I can reuse the same table/class for other places where I need attachments), I use a combination of REF_TYPE and REF_ID to locate the referencing Object.

What would be the best way to do this?
- Should be even doing this? Is there a better approach to the DB/Class design?

- Should I use @JoinColumns? (But I am not sure how that would work...)

- Or should the logic to load attachments go into a Service/DAO class with a findByRefTypeAndRefId(String refType, long refId) function falling back to a JPARepository @Autowired interface?

Answer 1

You will not be able to map such relation in JPA.

OneToMany or ManyToOne mappings (from Employee to Attachment or Attachment to Employee) are @ID based. Your Employe has simple one column ID, but in your attachment join depends on value of two columns.

Answer 2

Assuming your attachment table would have records from other table including Employee, e.g. company, etc.

First, You would have to write a JPA query, @JoinColumns would not work, since you should not map one column mapping to two tables as foreign-primary relationship.

Logic to load any db queries should always go to DAO class. and you should follow, service and DAO patterns to make simplication of code.

Solution1: If you want to go with current approach, use JPA query + your column type REF_TYPE as CHAR(1), e.g. E,C, etc.

Solution2: Make separate tables for attachment (users, company,etc), remove REF_TYPE column. create a foreign primary relation between the id column and use join. This will make your code also look neat.

Hope this helps.