使用Hibernate/JPA注解进行多对一连接,涉及多个列,但带有条件OR。

huangapple 未分类评论61阅读模式
标题翻译

HIbernate/JPA Annotation Join ManyToOne with multiple columns but with conditional OR

问题

假设我有以下实体:

公司用户项目推荐

但我想要突出显示的是项目推荐表格。下面是实体示例。

// 项目实体
@Column(name = "id")
private String id;

@Column(name = "user_id")
private String userId;

@Column(name = "company_id", nullable = true)
private String companyId;



// 推荐实体
@Column(name = "id")
private String id;

@Column(name = "user_id")
private String userId;

@Column(name = "company_id", nullable = true)
private String companyId;

@OneToMany(fetch = FetchType.LAZY)
@NotFound(action = NotFoundAction.IGNORE)
@JoinColumns({
            @JoinColumn(name = "id", referencedColumnName = "user_id", nullable = true, insertable = false, updatable = false),
            @JoinColumn(name = "id", referencedColumnName = "company_id", nullable = true, insertable = false, updatable = false)
    })
private List<Project> projects;

上面的表格只是示例,如果没有太多意义,抱歉。
我想在Certification表格中加入Project表格。
它们之间没有直接关联,所以我需要使用这两个列,userId和companyId,来进行连接。

然而,companyId是可空的。并且每当它为空时,我仍然希望仅通过userId连接表格。

我想要实现的大致如下:

Recommendation rec加入Project project
rec.user_id = project.user_id and (rec.company_id = project.company_id OR rec.company_id is null)

是否可能使用Hibernate / JPA注解来实现这一点?
我的项目最近使用的是Hibernate 5。

英文翻译

Let's say I have these Entities

Company,User,Project and Recommendation

But what I want to highlight is the Project and Recommendation table.
Below is the entity sample.

//Project entity
@Column(name = &quot;id&quot;)
private String id;

@Column(name = &quot;user_id&quot;)
private String userId;

@Column(name = &quot;company_id&quot;, nullable = true)
private String companyId;



//Recommendation entity
@Column(name = &quot;id&quot;)
private String id;

@Column(name = &quot;user_id&quot;)
private String userId;

@Column(name = &quot;company_id&quot;, nullable = true)
private String companyId;

@OneToMany(fetch = FetchType.LAZY)
@NotFound(action = NotFoundAction.IGNORE)
@JoinColumns({
            @JoinColumn(name = &quot;id&quot;, referencedColumnName = &quot;user_id&quot;, nullable = true, insertable = false, updatable = false),
            @JoinColumn(name = &quot;id&quot;, referencedColumnName = &quot;company_id&quot;, nullable = true, insertable = false, updatable = false)
    })
private List&lt;Project&gt; projects;

Above tables are just illustration, sorry if that doesn't really make sense.
I want to join Project table inside Certification table.
They both are not directly related, so I need to join using these 2 columns, userId and companyId.

However, companyId is nullable. And whenever it is null, I still want to join the table only by userId.

What I want to achieve is probably along this line:

Select from Recommendation rec Join Project project 
on rec.user_id = project.user_id and (rec.company_id = project.company_id OR rec.company_id is null)

Is it possible to use hibernate / JPA annotations to achieve this?

My project uses Hibernate 5 recently

huangapple
  • 本文由 发表于 2020年3月4日 08:41:03
  • 转载请务必保留本文链接:https://java.coder-hub.com/60517534.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定