開発の中で、自己結合(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の仕様を調べつつ、トライして、って感じで。
ちょっと自信ないのでググってみよう；
