itemsPage = articleService.search(pageable); model.addAttribute("itemsPage", itemsPage); model.addAttribute("kwTypesMap", kwTypesMap); return "article/list"; } } ``` - list.html 수정 ```html ``` # 6강 - [25 1 9, p 13947, 6강, 검색기능 구현 - 제목, 내용, 제목 or 내용](https://youtu.be/sNXtnWgXaGA) - [GIT](https://github.com/sik2/chat-app-mession/commit/2f63a46) - ArticleController.java 수정 ```java @GetMapping("/list") public String list() { // (...) Page

itemsPage = articleService.search(kwTypes, kw, pageable); // (...) } ``` - ArticleService.java 수정 ```java public Page

search(List kwTypes, String kw, Pageable pageable) { if (kwTypes.contains("title") && kwTypes.contains("content")) { return articleRepository.findByTitleContainingOrContentContaining(kw, kw, pageable); } else if (kwTypes.contains("title")) { return articleRepository.findByTitleContaining(kw, pageable); } else if (kwTypes.contains("content")) { return articleRepository.findByContentContaining(kw, pageable); } return articleRepository.findAll(pageable); } ``` - ArticleRepository.java 수정 ```java public interface ArticleRepository extends JpaRepository { List

findByOrderByIdDesc(); Page

findByTitleContainingOrContentContaining(String kw, String kw_, Pageable pageable); Page

findByTitleContaining(String kw, Pageable pageable); Page

findByContentContaining(String kw, Pageable pageable); } ``` # 7강 - [25 1 9, p 13947, 7강, 검색기능 구현 - 작성자명 or 제목 or 내용](https://youtu.be/6Ei3FZzU_Go) - [GIT](https://github.com/sik2/chat-app-mession/commit/12fad67) - ArticleRepository.java 수정 ```java Page

findByAuthor_usernameContainingOrTitleContainingOrContentContaining(String kw, String kw_, String kw__, Pageable pageable); Page

findByAuthor_usernameContaining(String kw, Pageable pageable); ``` - ArticleService.java 수정 ```java public Page

search(List kwTypes, String kw, Pageable pageable) { if (kwTypes.contains("authorUsername") && kwTypes.contains("title") && kwTypes.contains("content")) { return articleRepository.findByAuthor_usernameContainingOrTitleContainingOrContentContaining(kw, kw, kw, pageable); } else if (kwTypes.contains("title") && kwTypes.contains("content")) { return articleRepository.findByTitleContainingOrContentContaining(kw, kw, pageable); } else if (kwTypes.contains("title")) { return articleRepository.findByTitleContaining(kw, pageable); } else if (kwTypes.contains("content")) { return articleRepository.findByContentContaining(kw, pageable); } else if (kwTypes.contains("authorUsername")) { return articleRepository.findByAuthor_usernameContaining(kw, pageable); } return articleRepository.findAll(pageable); } ``` # [8강] - [25 1 9, p 13947, 8강, 검색기능 구현 - 전체 조건 체크](https://youtu.be/8Mol71gIJ6E) - [GIT](https://github.com/sik2/chat-app-mession/commit/7433b2c) - ArticleRepository.java 수정 ```java Page

findByAuthor_usernameContainingOrTitleContainingOrContentContainingOrTags_contentOrComments_author_usernameContainingOrComments_bodyContaining(String kw, String kw_, String kw__, String kw___, String kw____, String kw_____, Pageable pageable); ``` - ArticleService.java 수정 ```java public Page

search(List kwTypes, String kw, Pageable pageable) { if ( kwTypes.contains("authorUsername") && kwTypes.contains("title") && kwTypes.contains("content") && kwTypes.contains("tagContent") && kwTypes.contains("commentAuthorUsername") && kwTypes.contains("commentBody") ) { return articleRepository.findByAuthor_usernameContainingOrTitleContainingOrContentContainingOrTags_contentOrComments_author_usernameContainingOrComments_bodyContaining(kw, kw, kw, kw, kw, kw, pageable); } else if (kwTypes.contains("authorUsername") && kwTypes.contains("title") && kwTypes.contains("content")) { return articleRepository.findByAuthor_usernameContainingOrTitleContainingOrContentContaining(kw, kw, kw, pageable); } else if (kwTypes.contains("title") && kwTypes.contains("content")) { return articleRepository.findByTitleContainingOrContentContaining(kw, kw, pageable); } else if (kwTypes.contains("title")) { return articleRepository.findByTitleContaining(kw, pageable); } else if (kwTypes.contains("content")) { return articleRepository.findByContentContaining(kw, pageable); } else if (kwTypes.contains("authorUsername")) { return articleRepository.findByAuthor_usernameContaining(kw, pageable); } return articleRepository.findAll(pageable); } ``` ## QueryDSL 사용 # 9강 - [25 1 9, p 13947, 9강, QueryDSL 도입](https://youtu.be/pOzVbWJqbGg) - [GIT](https://github.com/sik2/chat-app-mession/commit/b48cbcb) - 의존성 추가 ```java // JPA QueryDSL implementation 'com.querydsl:querydsl-jpa:5.0.0:jakarta' annotationProcessor "com.querydsl:querydsl-apt:${dependencyManagement.importedProperties['querydsl.version']}:jakarta" annotationProcessor "jakarta.annotation:jakarta.annotation-api" annotationProcessor "jakarta.persistence:jakarta.persistence-api" ``` - src/main/java/com/ll/chatApp/domain/article/article/repository/ArticleRepositoryCustom.java ```java public interface ArticleRepositoryCustom { } ``` - src/main/java/com/ll/chatApp/domain/article/article/repository/ArticleRepositoryImpl.java ```java public class ArticleRepositoryImpl implements ArticleRepositoryCustom { } ``` - src/main/java/com/ll/chatApp/global/jpa/JpaConfig.java ```java @Configuration public class JpaConfig { @PersistenceContext private EntityManager entityManager; @Bean public JPAQueryFactory jpaQueryFactory() { return new JPAQueryFactory(entityManager); } } ``` - src/main/java/com/ll/chatApp/domain/article/article/repository/ArticleRepository.java ```java public interface ArticleRepository extends JpaRepository, ArticleRepositoryCustom { List

findByOrderByIdDesc(); } ``` - .gitignore ``` /src/main/generated/ ``` # 10강 - [25 1 9, p 13947, 10강, QueryDSL 사용한 검색 - 작성자명 or 제목 or 내용](https://youtu.be/FoIrkJy2dfs) - [GIT](https://github.com/sik2/chat-app-mession/commit/8b03f7f) - ArticleRepositoryCustom.java ```java import org.springframework.data.domain.Page; import org.springframework.data.domain.Pageable; import java.util.List; public interface ArticleRepositoryCustom { Page

search(List kwTypes, String kw, Pageable pageable); } ``` - ArticleRepositoryImpl.java ```java import com.querydsl.core.BooleanBuilder; import com.querydsl.core.types.Order; import com.querydsl.core.types.OrderSpecifier; import com.querydsl.core.types.dsl.PathBuilder; import com.querydsl.jpa.impl.JPAQuery; import com.querydsl.jpa.impl.JPAQueryFactory; import lombok.RequiredArgsConstructor; import org.springframework.data.domain.Page; import org.springframework.data.domain.Pageable; import org.springframework.data.domain.Sort; import org.springframework.data.support.PageableExecutionUtils; import java.util.List; @RequiredArgsConstructor public class ArticleRepositoryImpl implements ArticleRepositoryCustom { private final JPAQueryFactory jpaQueryFactory; @Override public Page

search(List kwTypes, String kw, Pageable pageable) { BooleanBuilder builder = new BooleanBuilder(); if (kwTypes.contains("authorUsername") && kwTypes.contains("title") && kwTypes.contains("content")) { builder.and( article.title.containsIgnoreCase(kw) .or(article.content.containsIgnoreCase(kw)) .or(article.author.username.containsIgnoreCase(kw)) ); } JPAQuery

articlesQuery = jpaQueryFactory .select(article) .from(article) .where(builder); for (Sort.Order o : pageable.getSort()) { PathBuilder pathBuilder = new PathBuilder(article.getType(), article.getMetadata()); articlesQuery.orderBy(new OrderSpecifier(o.isAscending() ? Order.ASC : Order.DESC, pathBuilder.get(o.getProperty()))); } articlesQuery.offset(pageable.getOffset()).limit(pageable.getPageSize()); JPAQuery totalQuery = jpaQueryFactory .select(article.count()) .from(article) .where(builder); return PageableExecutionUtils.getPage(articlesQuery.fetch(), pageable, totalQuery::fetchOne); } } ``` - ArticleService.java ```java public Page

search(List kwTypes, String kw, Pageable pageable) { // 수정 : return articleRepository.findAll(pageable); return articleRepository.search(kwTypes, kw, pageable); } ``` # 11강 - 실습 - sql 쿼리 확인 - QueryDSL 은 자바로 SQL 을 짤 수 있게 해준다. 단 그 SQL은 MySQL 이 아니라 JPQL 이다. ## SQL : http://localhost:8080/article/list?kwType=authorUsername&kwType=title&kwType=body&kw=3 ```sql # http://localhost:8080/article/list?kwType=authorUsername&kwType=title&kwType=body&kw=3 # 혹은 # http://localhost:8080/article/list?kwType=authorUsername&kwType=title&kwType=body&kw=3&page=0 # 1페이지 # 현재 페이지와, 페이지 사이즈를 고려하여 현재 화면에 보여질 10개 이하의 데이터 SELECT DISTINCT A.* FROM article AS A JOIN `member` AS AM ON A.author_id = AM.id WHERE LOWER(A.title) LIKE '%3%' OR LOWER(A.body) LIKE '%3%' OR LOWER(AM.username) LIKE '%3%' ORDER BY A.id DESC LIMIT 0, 10; # 하단에 페이지 메뉴를 완성하기 위해서, 조건에 일치하는 전체 게시물의 수(FROM절과 JOIN절과 WHERE절이 같을 수 밖에 없다.) SELECT COUNT(A.id) FROM article AS A JOIN `member` AS AM ON A.author_id = AM.id WHERE LOWER(A.title) LIKE '%3%' OR LOWER(A.body) LIKE '%3%' OR LOWER(AM.username) LIKE '%3%'; ``` ## SQL : http://localhost:8080/article/list?kwType=authorUsername&kwType=title&kwType=body&kw=3&page=1 ```sql # 2페이지 # 현재 페이지와, 페이지 사이즈를 고려하여 현재 화면에 보여질 10개 이하의 데이터 SELECT DISTINCT A.* FROM article AS A JOIN `member` AS AM ON A.author_id = AM.id WHERE LOWER(A.title) LIKE '%3%' OR LOWER(A.body) LIKE '%3%' OR LOWER(AM.username) LIKE '%3%' ORDER BY A.id DESC LIMIT 10, 10; # 하단에 페이지 메뉴를 완성하기 위해서, 조건에 일치하는 전체 게시물의 수(FROM절과 JOIN절과 WHERE절이 같을 수 밖에 없다.) SELECT COUNT(A.id) FROM article AS A JOIN `member` AS AM ON A.author_id = AM.id WHERE LOWER(A.title) LIKE '%3%' OR LOWER(A.body) LIKE '%3%' OR LOWER(AM.username) LIKE '%3%'; ``` ## SQL : http://localhost:8080/article/list?kwType=authorUsername&kwType=title&kwType=body&kw=3&page=2 ```sql # 3페이지 # 현재 페이지와, 페이지 사이즈를 고려하여 현재 화면에 보여질 10개 이하의 데이터 SELECT DISTINCT A.* FROM article AS A JOIN `member` AS AM ON A.author_id = AM.id WHERE LOWER(A.title) LIKE '%3%' OR LOWER(A.body) LIKE '%3%' OR LOWER(AM.username) LIKE '%3%' ORDER BY A.id DESC LIMIT 20, 10; # 하단에 페이지 메뉴를 완성하기 위해서, 조건에 일치하는 전체 게시물의 수(FROM절과 JOIN절과 WHERE절이 같을 수 밖에 없다.) SELECT COUNT(A.id) FROM article AS A JOIN `member` AS AM ON A.author_id = AM.id WHERE LOWER(A.title) LIKE '%3%' OR LOWER(A.body) LIKE '%3%' OR LOWER(AM.username) LIKE '%3%'; ```

itemsPage.content

itemsPage.number

itemsPage.totalPages

itemsPage.totalElements

itemsPage.size

itemsPage.numberOfElements

itemsPage.first

itemsPage.last

게시물 목록

JPA 심화 - 검색, QueryDSL 기능구현