まったり技術ブログ

Technology is power.

Specificationインタフェースを利用した副問合せ

Specificationインタフェースを利用した副問合せに関して、あまり情報が見当たらなかったので、メモ程度にφ(・ω・ )。

サンプルテーブル

よく見かける 1対多 の関係で説明していきます。
f:id:motikan2010:20171013000832p:plain:w400

ユーザ テーブル

サンプルコード

下記のサンプルコードでは、「同じ内容のツイートを3回以上しているユーザ」を選択という少し面倒くさいSQL文を発行してみます。
f:id:motikan2010:20171014134003j:plain

ファイル構成

 ── dbapp
    ├── DbappApplication.java
    ├── model
    │   ├── Tweet.java
    │   ├── Tweet_.java
    │   ├── User.java
    │   └── User_.java
    ├── repository
    │   ├── TweetRepository.java
    │   └── UserRepository.java
    └── spec
        └── BadUserSpec.java

Specification

今回の肝となる部分です。

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で型安全な検索を追求しよう!【基本編】 - 技術ブログ | 株式会社クラウディア
ちなみにエンティティと同じパケッケージに所属させないといけないらしい。
別のパッケージに配置し、ヌルポと格闘したのはいい思い出・・・。

stackoverflow.com

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;
}

リポジトリ

今回は空っぽ。

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
)

いい感じに副問い合わせが行えている。
countの部分をsumやavgに変えるなどして様々なパターンに応用できそう。