はじめに
Specificationインタフェースを利用した副問合せに関して、あまり情報が見当たらなかったので、メモ程度に紹介します。
実装
サンプルテーブル
本記事では以下のサンプルテーブルを例に説明していきます。
「1対多」の関係を例に説明していきます。 ユーザが1、ツイートが多となっています。
コード
本記事での目的は「同じ内容のツイートを3回以上しているユーザ」を選択するという少し面倒なSQL文を発行するというものです。
ネイティブクエリを利用すれば簡単ですが、Specificationで副問合せのSQLを発行するやり方です。
ファイル構成
── dbapp ├── DbappApplication.java ├── model │ ├── Tweet.java │ ├── Tweet_.java │ ├── User.java │ └── User_.java ├── repository │ ├── TweetRepository.java │ └── UserRepository.java └── spec └── BadUserSpec.java
Specification
発行クエリを生成する部分です。ここで副問合せとなるSQLを生成しますので、本記事で一番重要な部分です。
BadUserSpec.java
package com.motikan2010.dbapp.spec; import com.motikan2010.dbapp.model.Tweet; import com.motikan2010.dbapp.model.Tweet_; import com.motikan2010.dbapp.model.User; import com.motikan2010.dbapp.model.User_; import org.springframework.data.jpa.domain.Specification; import javax.persistence.criteria.*; import java.util.ArrayList; import java.util.List; public class BadUserSpec implements Specification<User> { @Override public Predicate toPredicate(Root<User> root, CriteriaQuery<?> query, CriteriaBuilder cb) { final List<Predicate> predicates = new ArrayList<>(); // サブクエリ Subquery<Tweet> subquery = query.subquery(Tweet.class); Root<Tweet> subRoot = subquery.from(Tweet.class); subquery.select(subRoot.get(Tweet_.user.getName())); subquery.where(cb.equal(root.get(User_.id.getName()), subRoot.get(Tweet_.user.getName()))); // ツイート内容でグループ化 subquery.groupBy(subRoot.get(Tweet_.body.getName())); // 条件 subquery.having(cb.and( cb.greaterThanOrEqualTo(cb.count(subRoot), 3L) )); predicates.add(cb.exists(subquery)); return cb.and(predicates.toArray(new Predicate[predicates.size()])); } }
エンティティ(モデル)
User.java
package com.motikan2010.dbapp.model; import lombok.Data; import javax.persistence.*; import java.util.List; @Entity @Data @Table(name = "user") public class User { @Id @GeneratedValue @Column(name = "id") private int id; @Column(name = "nickname") private String nickname; @OneToMany(mappedBy = "user", fetch = FetchType.LAZY) private List<Tweet> tweetList; }
Tweet.java
package com.motikan2010.dbapp.model; import com.sun.istack.internal.NotNull; import lombok.Data; import javax.persistence.*; @Entity @Data @Table(name = "tweet") public class Tweet { @Id @GeneratedValue @Column(name = "id") private int id; @Column(name = "body") private String body; @NotNull @Column(name = "user_id") private int userId; @ManyToOne(targetEntity=User.class) @JoinColumn(name = "user_id", referencedColumnName = "id", insertable=false, updatable=false) private User user; }
メタモデル
メタモデルを作成していきます。(恥ずかしながら最近メタモデルの存在を知りました)
JPAを深掘りする〜Criteria APIで型安全な検索を追求しよう!【基本編】 - 技術ブログ | 株式会社クラウディア
ちなみにエンティティと同じパッケージに所属させないといけないらしい。
別のパッケージに配置し、ヌルポと格闘したのはいい思い出・・・。
User_.java
package com.motikan2010.dbapp.model; import javax.persistence.metamodel.ListAttribute; import javax.persistence.metamodel.SingularAttribute; import javax.persistence.metamodel.StaticMetamodel; @StaticMetamodel(User.class) public class User_ { public static volatile SingularAttribute<User, Integer> id; public static volatile SingularAttribute<User, String> nickname; public static volatile ListAttribute<User, Tweet> tweetList; }
Tweet_.java
package com.motikan2010.dbapp.model; import javax.persistence.metamodel.SingularAttribute; import javax.persistence.metamodel.StaticMetamodel; @StaticMetamodel(Tweet.class) public class Tweet_ { public static volatile SingularAttribute<Tweet, Integer> id; public static volatile SingularAttribute<Tweet, String> body; public static volatile SingularAttribute<Tweet, User> user; }
リポジトリ
UserRepository.java
中身は空っぽで問題ないです。
package com.motikan2010.dbapp.repository; import com.motikan2010.dbapp.model.User; import org.springframework.data.jpa.repository.JpaRepository; import org.springframework.data.jpa.repository.JpaSpecificationExecutor; public interface UserRepository extends JpaRepository<User, Integer>, JpaSpecificationExecutor<User>{ }
呼び出し側
DbappApplication.java
package com.motikan2010.dbapp; import com.motikan2010.dbapp.model.User; import com.motikan2010.dbapp.repository.UserRepository; import com.motikan2010.dbapp.spec.BadUserSpec; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.CommandLineRunner; import org.springframework.boot.SpringApplication; import org.springframework.boot.autoconfigure.SpringBootApplication; import java.util.List; @SpringBootApplication public class DbappApplication implements CommandLineRunner { @Autowired UserRepository userRepo; public static void main(String[] args) { SpringApplication.run(DbappApplication.class, args); } @Override public void run(String... args) throws Exception { BadUserSpec spec = new BadUserSpec(); List<User> userList = userRepo.findAll(spec); for(User user : userList){ System.out.println(user.getId() + " : " + user.getNickname()); } } }
発行SQL
select user0_.id as id1_1_, user0_.nickname as nickname2_1_ from user user0_ where exists (select tweet1_.user_id from tweet tweet1_, user user2_ where tweet1_.user_id=user2_.id and user0_.id=tweet1_.user_id group by tweet1_.body having count(tweet1_.id)>=3)
分かりやすく & 整形すると以下のようになります。
select id , nickname from user user1 where exists ( select user_id from tweet, user user2 where tweet.user_id = user2.id and user1.id = tweet.user_id group by body having count(tweet.id)>=3 )
想定通りに Specificationを利用した副問合せができています。
countの部分をsumやavgに変えるなどして様々なパターンに応用できそうです。
更新履歴
- 2017年10月13日 新規作成