JPQLで自己結合

開発の中で、自己結合(Self Join)したいケースがあって、JPQLでどう書くのかやってみました。
今回、あまり調べず試行錯誤してしまったので、あっているかあまり自信ない…(-_-;

とりあえず簡単なサンプルを作って確認しました。

テーブルは以下のような定義で、ロール(役割)階層を表現しています。

CREATE TABLE ROLE_TBL(
    ROLEID VARCHAR(32) PRIMARY KEY,
    ROLENAME VARCHAR(64),
    UPPERROLE VARCHAR(32),
    CONSTRAINT FK_ROLE FOREIGN KEY(UPPERROLE) REFERENCES ROLE_TBL(ROLEID)
)

イメージは単純に以下のような感じで、ロールに親子関係があるだけ。
親1に対して子供は多。

データは

と絵に合わせて積んでみます。

NetBeansでデータベースからエンティティを自動生成すると
(※今回のサンプルではApache Derby使いました)

@Entity
@Table(name = "ROLE_TBL")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "RoleTbl.findAll", query = "SELECT r FROM RoleTbl r"),
    @NamedQuery(name = "RoleTbl.findByRoleid", query = "SELECT r FROM RoleTbl r WHERE r.roleid = :roleid"),
    @NamedQuery(name = "RoleTbl.findByRolename", query = "SELECT r FROM RoleTbl r WHERE r.rolename = :rolename")})
public class RoleTbl implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @NotNull
    @Size(min = 1, max = 32)
    @Column(name = "ROLEID")
    private String roleid;
    @Size(max = 64)
    @Column(name = "ROLENAME")
    private String rolename;
    @OneToMany(mappedBy = "upperrole")
    private Collection<RoleTbl> roleTblCollection;
    @JoinColumn(name = "UPPERROLE", referencedColumnName = "ROLEID")
    @ManyToOne
    private RoleTbl upperrole;
//以下省略

こんな感じで生成されていました。RoleTblのコレクションと単一のRoleTbl持つんだ…。うーむ。

とりあえず、Entityはそのままにして、クエリで、自分＋親、のEntityを一緒に取ることをやってみました。

JPQLクエリは

SELECT r,ru FROM RoleTbl r LEFT OUTER JOIN r.upperrole AS ru

って書いて、NetBeansのJPQLツールで実行すると

取れた(^^;あってんのか。
ちなみに外部結合にしているのは、親のいないCEOも含めて取りたいからです。

最初、WHERE句で「r.upperrole.roleId = ru.roleId」とか色々書いては実行エラーで怒られ続けたけど、rとruは既に関係を持ってるので不要…？と思ったら当たりっぽい。

JPQL、やはり細かい所になってくると難しいです(^^;
JSR317の仕様を調べつつ、トライして、って感じで。

ちょっと自信ないのでググってみよう；