I'm developing a filing system where I have 3 tables. PROJECTS table consists of projectid, project name and other details (see below). This is an existing class and populated schema and I do not want to modify this part of the application if possible.
Folders table (Called ProjectClassification) consists of folderid and foldername and is the owning side of a unidirectional onetomany relationship.
Project_Folders is a join table. I'm using JPA 2.0 (EclipseLink) and JSF 2.0 as my web framework.
My basic problem is I can't add duplicate records to the join table using a merge operation. MERGE is good for adding records until the owning key already exists, after which point it will only update the join table. I know this is the way it's supposed to work but I need to add new records even if there's a duplicate of the owning key. This will allow me to store different projects in the same folder.
The folder entity class:
The Project entity class is a pre existing code and I do not want to modify at all if possible:
I then use two html select lists to select values for projectid and proclassid which call the following methoid using a JSF managed bean:
My questions are:
1) Is MERGE the correct operation used to add records into a join table?
2) Is there a way to add records that contain duplicate keys (foreign keys represented as new records in the join table) using MERGE?
3) Should PERSIST be used to achieve question 2?
4) Would it be better to create an entity for the join table itself and simply use a PERSIST method to insert the records