[MyBatis/MariaDB] 조건으로 검색 기능 구현하기 2

앞단과 서버단은 아래링크에!!

 

2021.11.05 - [분류 전체보기] - [Java/Spring] 조건으로 검색 기능 구현하기

[Java/Spring] 조건으로 검색 기능 구현하기

 

• mapper.xml

<sql id="reportSqlId">
	<![CDATA[
		SELECT /*******reportSqlId******/
			 A.REPORT_ID AS reportId
			,A.REPORT_TITLE AS reportTitle
			,A.REPORT_TYPE AS reportType
			,A.REPORT_CONTENT AS reportContent
			,A.OKAY_YN AS okayYn
			,A.ORDER_BY_TOP AS orderByTop
			,A.ORDER_BY_ROW AS orderByRow
			,A.START_DATE AS startDate
			,A.END_DATE AS endDate
			,A.REG_ID AS regId
			,( SELECT ADMIN_LOGIN_ID FROM ADMIN_USER U WHERE A.REG_ID = U.ADMIN_ID) AS regIdStr
			,A.REG_DT AS regDt
			,DATE_FORMAT(A.REG_DT,'%Y-%m-%d %H:%i:%S') AS regDtStr
			,A.MOD_ID AS modId
			,( SELECT ADMIN_LOGIN_ID FROM ADMIN_USER U WHERE A.MOD_ID = U.ADMIN_ID) AS modIdStr
			,A.MOD_DT AS modDt
			,DATE_FORMAT(A.MOD_DT,'%y-%m-%d') AS modDtStr
			,B.FILE_ID AS fileId
			,B.FILE_NM AS fileNm
			,B.FILE_PATH AS filePath
			,B.FILE_ORG_NM AS fileOrgNm
			,B.REG_ID AS regId
			,( SELECT ADMIN_LOGIN_ID FROM ADMIN_USER U WHERE B.REG_ID = U.ADMIN_ID) AS regIdStr
			,B.REG_DT AS regDt
			,DATE_FORMAT(B.REG_DT,'%Y-%m-%d-%H:%i') AS regDtStr
			,B.MOD_ID AS modId
			,( SELECT ADMIN_LOGIN_ID FROM ADMIN_USER U WHERE B.MOD_ID = U.ADMIN_ID) AS modIdStr
			,B.MOD_DT AS modDt
			,DATE_FORMAT(B.MOD_DT,'%Y-%m-%d-%H:%i') AS modDtStr
		FROM DAILY_REPORT A
		LEFT OUTER JOIN DAILY_REPORT_FILE B
		ON A.REPORT_ID = B.REPORT_ID
		WHERE 1=1
	]]>
	</sql>

↑↑↑↑↑include refid 부분

 

<select id="selectPageReportList" parameterType="vo.ReportVO" resultMap="report">
      <include refid="reportSqlId"></include>
      
      <if test="selectTxt != null and selectTxt != '' ">
         <if test="searchType != null and searchType != '' ">
            <choose>
               <when test="searchType == 'reportTitle' ">
                  AND REPORT_TITLE LIKE CONCAT ('%', #{selectTxt}, '%')
               </when>
               <when test="searchType == 'reportContent' ">
                  AND REPORT_CONTENT LIKE CONCAT ('%', #{selectTxt}, '%')
               </when>
               <otherwise>
               
               </otherwise>
            </choose>
         </if>
      </if>
      AND A.REPORT_ID > 0  
      
      ORDER BY
      	<choose>
      		<when test='orderByType == "regId" '>
      			A.REG_ID
      		</when>
      		<when test='orderByType == "reportTitle" '>
      			A.REPORT_TITLE
      		</when>
      		<when test='orderByType == "regDt" '>
      			A.REG_DT
      		</when>
      		<otherwise>
      			A.REG_DT
      		</otherwise>
      	</choose>
      	
      	<choose>
      		<when test='orderBy == "orderByRow" '>
      			DESC
      		</when>
      		<when test='orderBy == "orderByTop" '>
      			ASC
      		</when>
      		<otherwise>
      			ASC
      		</otherwise>
      	</choose>
     <!--  ORDER BY A.REPORT_ID DESC -->
      LIMIT #{startRow}, #{pageSize}
   </select>

 

MYBATIS ORDER BY 문법을 잘 몰라서 애먹은 부분 ㅠㅠ..

어찌저찌 에러는 안나게 했었으나 알고보니 조건절이 잘못되어있었다...!

(경우의 수가 너무 많아지는, 리팩토링이 필요한 조건문이였다.)

 

<if test="selectTxt != null and selectTxt != '' ">
   <if test="searchType != null and searchType != '' ">
       <choose>
          <when test="searchType == 'reportTitle' ">
              AND REPORT_TITLE LIKE CONCAT ('%', #{selectTxt}, '%')
          </when>
          <when test="searchType == 'reportContent' ">
              AND REPORT_CONTENT LIKE CONCAT ('%', #{selectTxt}, '%')
          </when>
          <otherwise>
               
          </otherwise>
        </choose>
    </if>
</if>

이 부분이 검색 쿼리 조건부분이다.

LIKE CONCAT 으로 검색어중 일치하는 부분까지 조회되게 작성하였다.

 

 

	ORDER BY
      	<choose>
      		<when test='orderByType == "regId" '>
      			A.REG_ID
      		</when>
      		<when test='orderByType == "reportTitle" '>
      			A.REPORT_TITLE
      		</when>
      		<when test='orderByType == "regDt" '>
      			A.REG_DT
      		</when>
      		<otherwise>
      			A.REG_DT
      		</otherwise>
      	</choose>
      	
      	<choose>
      		<when test='orderBy == "orderByRow" '>
      			DESC
      		</when>
      		<when test='orderBy == "orderByTop" '>
      			ASC
      		</when>
      		<otherwise>
      			ASC
      		</otherwise>
      	</choose>
     <!--  ORDER BY A.REPORT_ID DESC -->

 

최종적으로 고친 ORDER BY 조건부분이다.

처음엔 두개를 같이 비교해야한다고 생각해서 
<when test='orderByType == "regId" and orderBy == "orderByRow" '>이런식으로 했었지만

이렇게 하게되면 경우의 수가 너무 많아진다는걸 알게되었다.

 

결국은 타입별로 비교~!그 뒤에 정렬방식 비교 따로!

 

검색화면

• 검색타입 : 제목
• 검색어 : 프론트
• 정렬타입 : 제목
• 정렬방식 : 오름차순

의 조건대로 검색화면이 잘 나온것을 확인할 수 있다.

 

※ 안에 데이터는 테스트용으로 마구잡이로 넣은거라 무시바람 ※

 

검색화면

위와 같은 조건에 정렬방식만 내림차순으로 조회한 화면이다.

 

성공~!!