Complex Search

검색조건이 하나 또는 두개로 고정일 경우, JPA 의 쿼리자동생성 기능으로 처리가 가능합니다.

하지만, 실무에서는 1 ~ N 개의 복합 검색조건을 요구하는 경우가 매우 많아 JPA 만으로 감당이 안됩니다. 그래서 쓰이는 라이브러리 중에 querydsl 을 이용한 다이나믹 쿼리 생성을 해봅니다.

개발환경

• Spring Boot 2.1.x
• Gradle 4.10.2

프로젝트는 이전 글에서 작성된 프로젝트에 파일을 추가 또는 수정하는 방식으로 진행됩니다. 이전 글을 따라 하지 않은 경우, 먼저 이전 글대로 프로젝트를 구성하시기 바랍니다.

파일추가 및 수정

querydsl 의 정상적인 컴파일을 위해 build.gradle 을 아래와 같이 수정합니다.

build.gradle

buildscript {
    ext {
        springBootVersion = '2.1.13.RELEASE'
    }
    repositories {
        mavenCentral()
        jcenter()
    }
    dependencies {
        classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

apply plugin: 'java'
apply plugin: 'war'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'

group 'kr.co.episode.example'
version '0.0.1-SNAPSHOT'
sourceCompatibility = 11

repositories {
    mavenCentral()
    jcenter()
}

dependencies {
    compile('org.springframework.boot:spring-boot-starter-web')
    compile('org.projectlombok:lombok')
    compile('org.springframework.boot:spring-boot-starter-thymeleaf')
    compile('com.querydsl:querydsl-jpa')
    compile('com.querydsl:querydsl-apt')
    compile('org.springframework.boot:spring-boot-starter-data-jpa')
    compile('org.springframework.session:spring-session-jdbc')
    testCompile('org.springframework.boot:spring-boot-starter-test')
    testCompile("org.springframework.security:spring-security-test")
}

/** QueryDSL Class Generate Script */
def generatedJavaSrcDir = 'src/main/generated'
def queryDslOutput = file(generatedJavaSrcDir)

sourceSets {
    main {
        java {
            srcDirs = ['src/main/java', generatedJavaSrcDir]
        }
    }
}

/** QClass 생성 */
task generateQueryDSL(type: JavaCompile, group: 'build') {
    doFirst {
        delete queryDslOutput
        queryDslOutput.mkdirs()
    }
    source = sourceSets.main.java
    classpath = configurations.compile
    destinationDir = queryDslOutput
    options.compilerArgs = [
            '-proc:only',
            '-processor',
            'com.querydsl.apt.jpa.JPAAnnotationProcessor'
    ]
}
compileJava.dependsOn(generateQueryDSL)

/** clean 태스크 실행시 QClass 삭제 */
clean {
    delete queryDslOutput
}

두개의 검색조건과 한개의 정렬순서를 전달할 클래스를 생성합니다.

src/main/java/kr/co/episode/example/domain/posts/PostsSearch.java

@Getter
public class PostsSearch {

    private String title;
    private String author;
    private boolean orderBy;

    public PostsSearch(String title, String author, boolean orderBy) {
        this.title = title;
        this.author = author;
        this.orderBy = orderBy;
    }
}

우선 커스텀 인터페이스를 생성합니다.

src/main/java/kr/co/episode/example/domain/posts/PostsPagingRepositoryCustom.java

public interface PostsPagingRepositoryCustom {

    Page<Posts> search(Pageable pageable, PostsSearch postsSearch);
}

QClass 를 찾지 못하지만 프로젝트는 정상적으로 실행됩니다. 그래도 오류를 수정하고 싶으면 메뉴에서 Build > Rebuild Project 하면 오류가 사라집니다.

src/main/java/kr/co/episode/example/domain/posts/PostsPagingRepositoryImpl.java

public class PostsPagingRepositoryImpl extends QuerydslRepositorySupport implements PostsPagingRepositoryCustom {

    public PostsPagingRepositoryImpl() {
        super(Posts.class);
    }

    @Override
    public Page<Posts> search(Pageable pageable, PostsSearch postsSearch) {

        QPosts posts = QPosts.posts;

        JPQLQuery query = from(posts);

        if (!"".equals(postsSearch.getTitle())) {
            query.where(posts.title.contains(postsSearch.getTitle()));
        }

        if (!"".equals(postsSearch.getAuthor())) {
            query.where(posts.author.contains(postsSearch.getAuthor()));
        }

        if (postsSearch.isOrderBy()) {
            query.orderBy(posts.id.asc());
        } else {
            query.orderBy(posts.id.desc());
        }

        long totalCount = query.fetchCount();
        List<Posts> results = getQuerydsl().applyPagination(pageable, query).fetch();

        return new PageImpl<>(results, pageable, totalCount);
    }
}

src/main/java/kr/co/episode/example/domain/posts/PostsPagingRepository.java

public interface PostsPagingRepository extends JpaRepository<Posts, Long>, PostsPagingRepositoryCustom {

    // ......

    Page<Posts> search(Pageable pageable, PostsSearch postsSearch);
}

src/main/java/kr/co/episode/example/web/dto/PostsSearchDto.java

@Getter
public class PostsSearchDto {

    private String title;
    private String author;
    private boolean orderBy;

    @Builder
    public PostsSearchDto(String title, String author, boolean orderBy) {
        this.title = title;
        this.author = author;
        this.orderBy = orderBy;
    }

    public PostsSearchDto(Map<String, String> params) {
        this.title = params.get("title") == null ? "" : params.get("title");
        this.author = params.get("author") == null ? "" : params.get("author");
        this.orderBy = params.get("orderBy") != null;
    }

    public PostsSearch toEntity() {
        return new PostsSearch(title, author, orderBy);
    }
}

src/main/java/kr/co/episode/example/service/posts/PostsPagingService.java

@RequiredArgsConstructor
@Service
public class PostsPagingService {

    private final PostsPagingRepository postsPagingRepository;

    // ......

    @Transactional(readOnly = true)
    public Page<PostsListResponseDto> search(Pageable pageable, PostsSearchDto postsSearchDto) {
        Page<Posts> posts = postsPagingRepository.search(pageable, postsSearchDto.toEntity());
        List<PostsListResponseDto> results = posts.getContent().stream()
                .map(PostsListResponseDto::new)
                .collect(Collectors.toList());
        long totalCount = posts.getTotalElements();

        return new PageImpl<>(results, pageable, totalCount);
    }
}

src/main/resources/templates/index.html

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>게시판</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.2/css/bootstrap.min.css">
    <style>
        .container p { display: inline }
    </style>
</head>
<body class="container">

<h1>스프링 부트 게시판</h1>

<div class="col-md-12">
    <div class="row">
        <div class="col-md-6">
            <a href="/posts/save" role="button" class="btn" btn-primary>글 등록</a>
        </div>
    </div>
</div>

<div style="height: 80px;">

</div>

<table class="table">
    <tr>
        <th>글 번호</th>
        <th>글쓴이</th>
        <th>글 제목</th>
        <th>최종수정</th>
    </tr>
    <tr th:each="posts: ${posts}">
        <td th:text="${posts.id}"></td>
        <td>
            <a th:href="${posts.link}" th:text="${posts.author}" />
        </td>
        <td>
            <a th:href="${posts.link}" th:text="${posts.title}" />
        </td>
        <td th:text="${posts.modifiedDate}"></td>
    </tr>
</table>

<div class="col-md-12">
    <div class="col-md-4">
        <form name="frm">
            <input type="hidden" name="page" value="0">
            <div class="form-group">
                <label for="title">제목</label>
                <input type="text" class="form-control" id="title" placeholder="제목을 입력하세요." name="title" th:value="${search.title}" />
            </div>
            <div class="form-group">
                <label for="author">작성자</label>
                <input type="text" class="form-control" id="author" name="author" placeholder="작성자를 입력하세요." th:value="${search.author}" />
            </div>
            <div class="custom-control custom-checkbox">
                <input type="checkbox" class="custom-control-input" id="orderBy" name="orderBy" th:checked="${search.orderBy}" />
                <label class="custom-control-label" for="orderBy">글번호 순으로</label>
            </div>
        </form>

        <button type="button" class="btn btn-primary" id="btn-search">검색</button>
    </div>
</div>

<nav style="text-align: center;">
    <ul class="pagination"
        th:with="start=${T(Math).floor(posts.number/10)*10},
                    last=(${start + 9 < posts.totalPages-1 ? start + 9 : posts.totalPages-1})">
        <li>
            <a th:onclick="|javascript:gotoPage(0)|" aria-label="First">
                <span aria-hidden="true">First</span>
            </a>
        </li>

        <li th:class="${posts.first} ? 'disabled'">
            <a th:onclick="|javascript:gotoPage(${posts.number-1})|" aria-label="Previous">
                <span aria-hidden="true"><</span>
            </a>
        </li>

        <li th:each="page: ${#numbers.sequence(start, last)}" th:class="${page == posts.number} ? 'active'">
            <a th:text="${page+1}" th:onclick="|javascript:gotoPage(${page})|" ></a>
        </li>

        <li th:class="${posts.last} ? 'disabled'">
            <a th:onclick="|javascript:gotoPage(${posts.number+1})|" aria-label="Next">
                <span aria-hidden="true">></span>
            </a>
        </li>

        <li>
            <a th:onclick="|javascript:gotoPage(${posts.totalPages-1})|" aria-label="Last">
                <span aria-hidden="true">Last</span>
            </a>
        </li>
    </ul>
</nav>

<script src="https://code.jquery.com/jquery-3.3.1.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>

<script src="/js/app/index.js"></script>
<script>
    function gotoPage(pg) {
        document.frm.page.value = pg;
        document.frm.submit();
    }
</script>
</body>
</html>

src/main/java/kr/co/episode/example/web/IndexController.java

    // ......
    @GetMapping("/")
    public String index(Model model, @PageableDefault Pageable pageable, @RequestParam Map<String, String> params) {
        pageable = PageRequest.of(pageable.getPageNumber(), 3);
        PostsSearchDto postsSearchDto = new PostsSearchDto(params);

        model.addAttribute("posts", postsPagingService.search(pageable, postsSearchDto));
        model.addAttribute("search", postsSearchDto);

        return "index";
    }
    // ......