3-14 : 검색

단 하나의 맥락

: 

 

-

 

Semiflow Bundle

: 검색 대상

 

 

-

 

자각

 

검색 대상

• 질문의 제목
• 질문의 내용
• 질문 작성자
• 답변의 내용
• 답변 작성자

 

-

 

SQL 쿼리

 

• select
  • distinct q.id;
  • q.author_id;
  • q.content;
  • q.create_date;
  • q.modify_date;
  • q.subject
• from question q
• left outer join site_user u1 on q.author_id=u1.id
• left outer join answer a on q.id=a.question_id
• left outer join site_user u2 on a.author_id=u2.id
• where
  • q.subject like '%스프링%'
  • or q.content like '%스프링%'
  • or u1.username like '%스프링%'
  • or a.content like '%스프링%'
  • or u2.username like '%스프링%'

 

-

 

JPA가 제공하는 Specification 쿼리 작성 도구 사용

 

> QuestionService에서

 

// import com.mysite.sbb.answer.Answer;

// import javax.persistence.criteria.CriteriaBuilder;

// import javax.persistence.criteria.CriteriaQuery;

// import javax.persistence.criteria.Join;

// import javax.persistence.criteria.JoinType;

// import javax.persistence.criteria.Predicate;

// import javax.persistence.criteria.Root;

// import org.springframework.data.jpa.domain.Specification;

 

• private Specification<Question> search(String kw) {
  • return new Specification<>() {
    • private static final long serialVersionUID = 1L;
    • @Override
    • public Predicate toPredicate(Root<Question> q, CriteriaQuery<?> query, CriteriaBuilder cb) {
      • query.distinct(true);
      • Join<Question, SiteUser> u1 = q.join("author", JoinType.LEFT);
      • Join<Question, Answer> a = q.join("answerList", JoinType.LEFT);
      • Join<Answer, SiteUser> u2 = a.join("author", JoinType.LEFT);
      • return cb.or(cb.like(q.get("subject"), "%" + kw + "%"),
        • cb.like(q.get("subject"), "%" + kw +"%"),
        • cb.like(u1.get("username"), "%" + kw + "%"),
        • cb.like(a.get("content"), "%" + kw + "%"),
        • cb.like(u2.get("username"), "%" + kw + "%"));
      • } 
    •  };
  • }