JPA Entity Mapping which is related based on two other entity mappings

Question

Not sure if this is possible but trying to map WorkflowInstancePlayer player which is related based on two other entity mappings, WorkActionClass and WorkflowInstance in the entity below.

public class Action implements Serializable {
@Id
private Long action_id;

@ManyToOne
@JoinColumn(name = "work_action_class_id", referencedColumnName = "work_action_class_id")
private WorkActionClass workActionClass;

@ManyToOne
@JoinColumn(name = "workflow_instance_id", referencedColumnName = "workflow_instance_id")
private WorkflowInstance workflowInstance;

UPDATE: How can I map to a WorkflowInstancePlayer player?????
 @ManyToOne
 @JoinColumns( {
        @JoinColumn(name = "workflow_instance_id", referencedColumnName = "workflow_instance_id", insertable = false, updatable = false),
        @JoinColumn(name = "workActionClass.role_class_id", referencedColumnName = "role_class_id", insertable = false, updatable = false)
})
 private WorkflowInstancePlayer player;

The workflowInstancePlayer is derived based on workflow_instance_id and role_class_id but role_class_id is really an attibute of workActionClass mapped above (workActionClass.role_class_id)

public class WorkflowInstancePlayer implements Serializable {
@Id
private WorkflowInstance workflowInstance;

@Id
private RoleClass roleClass;

@ManyToOne
@JoinColumn(name = "badge", referencedColumnName = "badge")
private Employee employee;

public class WorkActionClass implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "trx_seq")
private Long work_action_class_id;

@ManyToOne
@JoinColumn(name = "role_class_id")
private RoleClass roleClass;

Example data would be:

Action
------
Id = 10
work_action_class_id = 7
workflow_instance_id = 2


WorkActionClass
---------------
Id = 7
role_name = reviewer
role_class_id = 3


WorkflowInstancePlayer
----------------------
workflow_instance_id = 2
role_class_id = 3
badge = 111222

So in the Action Entity, I'll know the Workflow Instance Player is Employee with Id 111222 without actually storing the badge in the Action table.

UPDATE

Based on Vlad's post I tweaked it to be

@ManyToOne(fetch=FetchType.LAZY)
   @JoinColumnsOrFormulas({
   @JoinColumnOrFormula(formula=@JoinFormula(value="(SELECT a.role_class_id FROM (Use Table Name not Entity Name here) a WHERE a.work_action_class_id = work_action_class_id)", referencedColumnName="role_class_id")),
   @JoinColumnOrFormula(column = @JoinColumn(name="workflow_instance_id", referencedColumnName="workflow_instance_id"))
})

badge = 111222 is some function of workflow_instance_id( = 2) and role_class_id ( = 3) - is that correct?.... if not how do you derive badge = 111222? — NickJI, Jul 14 '15 at 14:15
not really. This is a workflow application. Employee 111222 was assigned as the Reviewer with in the workflow. The reviewer will have many actions depending on the workflow type. The Oracle sequence number of 2 was next inline for the workflow instance id and a Reviewer Role has a role_class_id of 3. So those three values get inserted into WorkflowInstancePlayer — jeff, Jul 14 '15 at 14:32
@NickJI I reread your comment. if you mean function as in "a relationship" then yes. At first I read it as "a formula" as in instance_id times role_class_id divided by.... etc. The relationship is defined in the WorkflowInstancePlayer table — jeff, Jul 14 '15 at 14:57
to get to Employee you need to get to WorkflowInstancePlayerId but coming from either RoleClass or WorkflowInstance, so you'll need to traverse a one-to-many - with nothing to tell you which is the correct WorkflowInstancePlayerId - so I don't think that particualr approach would work. — NickJI, Jul 14 '15 at 15:07
what is a work around? I was thinking @Transient and then manually mapping?? — jeff, Jul 14 '15 at 15:44
You can have somethin like this.... (see next comment also) @Transient private Employee actionOwnerUser; Public Employee getActionOwnerUser() return actionOwnerUser; Public void setActionOwnerUser() //your manual mapping code actionOwnerUser = //your manual mapping code — NickJI, Jul 14 '15 at 15:52
From what I understand you will have the Employee info when the employee is actually assigned to a workflow, but without the action_owner_badge join you will have no way to get to this on future calls. So although setting up the @Transient variable is possible when the workflow is introduced to the system, you are not persisting the link between Action and Employee so will not be able to populate actionOwnerUser at a future date... depending on your business requriements that might not be a problem. — NickJI, Jul 14 '15 at 16:02

Vlad Mihalcea · Accepted Answer · 2015-07-17T08:22:53.220

6

Try with this mapping:

@ManyToOne
@JoinColumn(
    name = "workflow_instance_id", 
    referencedColumnName = "workflow_instance_id",
    insertable = false, 
    updatable = false
)
private WorkflowInstance workflowInstance;

@ManyToOne
@JoinColumnOrFormula(
    formula = @JoinFormula(
            value="(SELECT a.work_action_class_id FROM WorkActionClass a WHERE a.role_class_id = role_class_id)", 
        referencedColumnName = "work_action_class_id"
    )
)
private WorkActionClass workActionClass;

@ManyToOne
@JoinColumns( {
    @JoinColumn(
        name = "workflow_instance_id", 
        referencedColumnName = "workflow_instance_id"),
    @JoinColumn(
        name = "role_class_id", 
        referencedColumnName = "role_class_id")
})
private WorkflowInstancePlayer player;

edited Jul 17 '15 at 08:22

answered Jul 17 '15 at 08:11

Vlad Mihalcea

142,745
71
566
911

your @JoinColumnOrFormula has helped out. See my post, I believe the formula should be on the WorkflowInstancePlayer mapping. Things compile and my tables look to be created as expected but now I get ORA-01799: a column may not be outer-joined to a subquery when retrieving. – jeff Jul 17 '15 at 15:27
Great. For that error, make the association LAZY and make sure you don't JOIN FETCH it, to allow Hibernate to retrieve it with a secondary query. – Vlad Mihalcea Jul 17 '15 at 15:30
hmm I did click and upvoted, then added my comment. The comment submit in IE hung so I copied to clipboard, refreshed page and pasted it again. The upvote must have timedout too. Let me try LAZY and see if we can close this Q out – jeff Jul 17 '15 at 15:36
Thanks. I hope it can help. – Vlad Mihalcea Jul 17 '15 at 15:37
Nice. on WorkflowInstancePlayer I added @ManyToOne(fetch=FetchType.LAZY). I had to modify the joinformula to use the actual table name instead of the Entity "SELECT a.role_class_id FROM WF_WORK_ACTION_CLASS" vs "SELECT a.role_class_id FROM WorkActionClass" and it works. Thanks! – jeff Jul 17 '15 at 16:31

JPA Entity Mapping which is related based on two other entity mappings

UPDATE

1 Answers1