검색 및 pagination에 동적 쿼리 사용해보기

내가 속한 모임 게시글, 내가 속하지 않은 모임 게시글 정보를 검색 및 페이징 처리를 거쳐서 가져오려 한다.

해당 모임 게시글을 카테고리와 유저의 지역정보, 게시글의 제목과 내용으로 검색하도록 구현하려 했다.

mapper에 해당 조건마다 쿼리를 만들어야 해서 이를 해결하고자 동적 쿼리를 사용해보았다.

 

 

---

mapper interface

package com.example.batisproject.mapper.jungi;

import com.example.batisproject.dto.GatherDTO;
import com.example.batisproject.dto.GatherResponseDTO;
import com.example.batisproject.dto.PageRequestDTO;
import com.example.batisproject.entity.Gather;
import com.example.batisproject.entity.GatherResponse;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Optional;

@Mapper
public interface GatherMapper {

    @Select("Select * from gather where enddate > now() order by g_id desc")
    @Results(id = "gatherMap", value = {
            @Result(property = "id", column = "g_id"),
            @Result(property = "user", column = "u_id"),
            @Result(property = "location", column = "l_id"),
            @Result(property = "category", column = "c_id"),
            @Result(property = "title", column = "title"),
            @Result(property = "content", column = "content"),
            @Result(property = "regDate", column = "regdate"),
            @Result(property = "startDate", column = "startdate"),
            @Result(property = "endDate", column = "enddate"),
            @Result(property = "modifyDate", column = "modifydate"),
            @Result(property = "allDay", column = "allday"),
            @Result(property = "textColor", column = "textColor"),
            @Result(property = "backgroundColor", column = "backgroundColor"),
            @Result(property = "borderColor", column = "borderColor"),
            @Result(property = "viewCnt", column = "view_cnt"),
            @Result(property = "peopleNum", column = "people_num"),
            @Result(property = "point", column = "point")
    })
    List<Gather> getAll();

    @Select("select * from gather where l_id = #{location} and enddate > now() order by g_id desc")
    @ResultMap("gatherMap")
    List<Gather> getAllByLocation(Integer location);


    @Select("select * from gather where u_id = (select u_id from user where nickname = #{nickname}) and enddate > now() order by g_id desc")
    @ResultMap("gatherMap")
    List<Gather> getAllByNickname(@Param("nickname") String nickname);

    @Insert("INSERT INTO gather (u_id, l_id, c_id, title, content, startdate, enddate, people_num, point) values " +
            " (#{gather.user}, #{gather.location}, #{gather.category}, #{gather.title}, #{gather.content}, #{gather.startDate}, #{gather.endDate}," +
            " #{gather.peopleNum}, #{gather.point})")
    @Options(useGeneratedKeys = true, keyProperty = "id") // auto_increment할 id설정
    int insert(@Param("gather") Gather gather);

    @Select("select * from gather where c_id = #{category} and enddate > now() order by g_id desc")
    @ResultMap("gatherMap")
    List<Gather> getByCategory(Integer category);

    @Select("select * from gather where c_id = #{category} and u_id = (select u_id from user where nickname = #{nickname}) and enddate > now() order by g_id desc")
    @ResultMap("gatherMap")
    List<Gather> getByCategoryAndNickname(Integer category, String nickname);
    @Select("select count(*) from gather")
    int getCount(PageRequestDTO pageRequestDTO);

    List<GatherResponse> selectMyList(PageRequestDTO pageRequestDTO);

    List<GatherResponse> selectOtherList(PageRequestDTO pageRequestDTO);

    int getOtherListCount(PageRequestDTO pageRequestDTO);
    int getMyListCount(PageRequestDTO pageRequestDTO);

}

 

동적 쿼리를 위한 xml파일 (namespace로 interface 위치를 명시해야 한다.)

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE mapper PUBLIC
        "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.example.batisproject.mapper.jungi.GatherMapper">

    <resultMap id="GatherMapping" type="Gather">
        <result property = "id" column = "g_id"/>
        <result property = "user" column = "u_id"/>
        <result property = "location" column = "l_id"/>
        <result property = "category" column = "c_id" />
        <result property = "title" column = "title"/>
        <result property = "content" column = "content"/>
        <result property = "regDate" column = "regdate"/>
        <result property = "startDate" column = "startdate"/>
        <result property = "endDate" column = "enddate"/>
        <result property = "modifyDate" column = "modifydate"/>
        <result property = "allDay" column = "allday"/>
        <result property = "textColor" column = "textColor"/>
        <result property = "backgroundColor" column = "backgroundColor"/>
        <result property = "borderColor" column = "borderColor" />
        <result property = "viewCnt" column = "view_cnt"/>
        <result property = "peopleNum" column = "people_num"/>
        <result property = "point" column = "point"/>
    </resultMap>

    <resultMap id="ResponseGatherMapping" type="GatherResponse">
        <result property = "id" column = "g_id"/>
        <result property = "user" column = "u_id"/>
        <result property = "location" column = "l_id"/>
        <result property = "category" column = "c_id" />
        <result property = "title" column = "title"/>
        <result property = "content" column = "content"/>
        <result property = "regDate" column = "regdate"/>
        <result property = "startDate" column = "startdate"/>
        <result property = "endDate" column = "enddate"/>
        <result property = "modifyDate" column = "modifydate"/>
        <result property = "viewCnt" column = "view_cnt"/>
        <result property = "peopleNum" column = "people_num"/>
        <result property = "point" column = "point"/>
        <result property = "saveFileName" column="save_file_name" />
    </resultMap>

    <select id="selectMyList" resultType="GatherResponse" resultMap="ResponseGatherMapping">
        SELECT g.g_id
             , g.u_id
             , g.l_id
             , g.c_id
             , g.title
             , g.content
             , g.regdate
             , g.startdate
             , g.enddate
             , g.modifydate
             , g.view_cnt
             , g.people_num
             , g.point
             , fi.save_file_name
        FROM gather g
        RIGHT JOIN gather_comment gc on g.g_id = gc.g_id
        LEFT JOIN gather_image gi on gi.g_id = g.g_id
        LEFT join file_info fi on fi.f_id = gi.f_id
            <include refid="searchMyList"/>
        ORDER BY g.g_id DESC LIMIT #{skip}, #{size}
    </select>

    <select id="selectOtherList" resultType="GatherResponse" resultMap="ResponseGatherMapping">
        select g.g_id,
               g.u_id,
               g.l_id,
               g.c_id,
               g.title,
               g.content,
               g.regdate,
               g.startdate,
               g.enddate,
               g.modifydate,
               g.view_cnt,
               g.people_num,
               g.point,
               fi.save_file_name
        from gather g
        left join gather_image gi on gi.g_id = g.g_id
        left join file_info fi on fi.f_id = gi.f_id
        <include refid="searchOtherList"/>
        order by g_id desc LIMIT #{skip}, #{size}
    </select>

    <sql id="searchMyList">
        <where>
            gc.u_id = (select u_id from user where nickname = #{nickname})
            and g.enddate > now()
            and l_id = #{location}
        <choose>
            <when test="category != null">
                and c_id = #{category}
            </when>
             <when test="keyword != null and type == 'title'.toString()">
                 and title like concat('%', #{keyword}, '%')
             </when>
        </choose>
        </where>
    </sql>

    <sql id="searchOtherList">
         <where>
             g.g_id NOT IN (select g_id from gather_comment gc
             where u_id = (select u_id from user u where nickname = #{nickname}))
                 and enddate > now()
                 and l_id = #{location}
             <choose>
                 <when test="category != null">
                     and c_id = #{category}
                 </when>
                 <when test="keyword != null and type == 'title'.toString()">
                     and title like concat('%', #{keyword}, '%')
                 </when>
                 <when test="allSearchKeyword != null">
                    or title like concat('%', #{allSearchKeyword}, '%')
                    or content like concat('%', #{allSearchKeyword}, '%')
                 </when>
             </choose>
         </where>
    </sql>

    <sql id="search">
        <where>
            <if test="keyword != null and type == 'title'.toString()">
                title like concat('%', #{keyword}, '%')
            </if>
        </where>
    </sql>

    <select id="getMyListCount" resultType="int">
        select count(*) from gather g
        right join gather_comment gc on g.g_id = gc.g_id
        left join gather_image gi on gi.g_id = g.g_id
        left join file_info fi on fi.f_id = gi.f_id
        <include refid="searchMyList"/>
    </select>

    <select id="getOtherListCount" resultType="int">
        select count(*) from gather g
        left join gather_image gi on gi.g_id = g.g_id
        left join file_info fi on fi.f_id = gi.f_id
        <include refid="searchOtherList"/>
    </select>



</mapper>

 

---

자세한 내용은 github 참조

 

https://github.com/CodingGamePlayer/unstoppable