230220 Spring Boot 카테고리 처리-2

목록에 카테고리를 붙이는 건 N:1

 

 

 

MySQL

select * from product
where prod_writer = 'admin'
and prod_name like '%샘숭%' ## +조건 +조건
order by prod_price asc, prod_id desc
limit 0,10;

SELECT
	   #A1.*,
       #A2.CATEGORY_DETAIL_NM,
       #A3.CATEGORY_DETAIL_NM,
	   #CONCAT(A1.group_ID, A1.CATEGORY_ID, A1.category_LV,  A1.category_detail_LV) AS CATEGORY_KEY, ##카테고리 키
       CONCAT(A1.group_ID, A1.CATEGORY_ID) AS CATEGORY_KEY, ##카테고리 키 (그룹 + 키 형태로 카테고리분류)
 	   CASE A1.category_parent_LV
 	        WHEN 0 THEN A1.category_detail_NM
 			WHEN 1 THEN CONCAT(A2.category_detail_NM,' > ', A1.category_detail_NM)
 			WHEN 2 THEN CONCAT(A3.category_detail_NM, ' > ', A2.category_detail_NM,' > ', A1.category_detail_NM)
 	   END as CATEGORY_NAV
FROM PRODUCT_CATEGORY A1
LEFT OUTER JOIN PRODUCT_CATEGORY A2
ON A1.CATEGORY_PARENT_LV = A2.CATEGORY_LV AND A1.CATEGORY_DETAIL_PARENT_LV = A2.CATEGORY_DETAIL_LV AND A1.GROUP_ID = A2.GROUP_ID
LEFT OUTER JOIN PRODUCT_CATEGORY A3
ON A2.CATEGORY_PARENT_LV = A3.CATEGORY_LV AND A2.CATEGORY_DETAIL_PARENT_LV = A3.CATEGORY_DETAIL_LV
ORDER BY CATEGORY_NAV ASC;



## 조인
select p.*, k.category_nav 
from (
		select * from product
		where prod_writer = 'admin'
		and prod_name like '%샘숭%'
) p
left join (
	SELECT
       CONCAT(A1.group_ID, A1.CATEGORY_ID) AS CATEGORY_KEY, ##카테고리 키 (그룹 + 키 형태로 카테고리분류) 
 	   CASE A1.category_parent_LV
 	        WHEN 0 THEN A1.category_detail_NM
 			WHEN 1 THEN CONCAT(A2.category_detail_NM,' > ', A1.category_detail_NM)
 			WHEN 2 THEN CONCAT(A3.category_detail_NM, ' > ', A2.category_detail_NM,' > ', A1.category_detail_NM)
 	   END as CATEGORY_NAV
		FROM PRODUCT_CATEGORY A1
		LEFT OUTER JOIN PRODUCT_CATEGORY A2
		ON A1.CATEGORY_PARENT_LV = A2.CATEGORY_LV AND A1.CATEGORY_DETAIL_PARENT_LV = A2.CATEGORY_DETAIL_LV AND A1.GROUP_ID = A2.GROUP_ID
		LEFT OUTER JOIN PRODUCT_CATEGORY A3
		ON A2.CATEGORY_PARENT_LV = A3.CATEGORY_LV AND A2.CATEGORY_DETAIL_PARENT_LV = A3.CATEGORY_DETAIL_LV
		ORDER BY CATEGORY_NAV ASC
) k
on p.prod_category=k.category_key
order by prod_price asc, prod_id desc
limit 0,10;

 

---

ProductVO

package com.codehistory.myweb.command;

import java.time.LocalDateTime;

import javax.validation.constraints.Min;
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.Pattern;

import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
public class ProductVO {
	
	private int prod_id;
	private LocalDateTime prod_regdate;
	
	@NotBlank(message="판매종료일은 공백일 수 없습니다.")
	@Pattern(regexp = "[0-9]{4}-[0-9]{2}-[0-9]{2}")
	private String prod_enddate;
	
	private String prod_category;//카테고리
	private String category_nav;//카테고리 조인된 결과
	
	@NotBlank(message = "작성자는 공백일 수 없습니다.")
	private String prod_writer;
	private String prod_name;
	
	@Min(value = 0, message="가격은 0원 이상이어야 합니다.")
	private int prod_price;
	private int prod_count;
	private int prod_discount;
	private String prod_purchase_yn;
	private String prod_content;
	private String prod_comment;
	
}

prod_category, category_nav 변수 추가.

 

AjaxController, ProductService, ProductServiceImpl, ProductMapper.java는 변동 없음.

 

ProductVO에 추가적으로 변수를 담는다.

---

ProductMapper.xml에서

<select id="getList" resultType="ProductVO">
		select p.*, k.category_nav
			from (
				select * from product
					where prod_writer = #{user_id}
			
					<if test="cri.startDate!=null and cri.startDate != ''">
			         <![CDATA[and date_format(prod_enddate, '%Y-%m-%d') >= date_format(#{cri.startDate}, '%Y-%m-%d')]]>
					</if>
					<if test="cri.endDate != null and cri.endDate != ''">
			         <![CDATA[and date_format(prod_enddate, '%Y-%m-%d') <= date_format(#{cri.endDate}, '%Y-%m-%d')]]>
					</if>
					<if test="cri.searchName != null and cri.searchName != ''">
						and prod_name like concat('%', #{cri.searchName}, '%')
					</if>
					<if test="cri.searchContent != null and cri.searchContent != ''">
						and prod_name like concat('%', #{cri.searchContent}, '%')
					</if>
					order by
					<if test="cri.searchPrice == 'asc'">
						prod_price asc,
					</if>
					<if test="cri.searchPrice == 'desc'">
						prod_price desc,
					</if>
					prod_id desc
			
					limit #{cri.pageStart}, #{cri.amount}
			) p
		left outer join (
			SELECT CONCAT(A1.group_ID, A1.CATEGORY_ID) AS CATEGORY_KEY, 
			CASE A1.category_parent_LV
				WHEN 0 THEN A1.category_detail_NM
				WHEN 1 THEN CONCAT(A2.category_detail_NM,' > ', A1.category_detail_NM)
				WHEN 2 THEN CONCAT(A3.category_detail_NM, ' > ', A2.category_detail_NM,' > ', A1.category_detail_NM)
			END as CATEGORY_NAV
			FROM PRODUCT_CATEGORY A1
			LEFT OUTER JOIN
				PRODUCT_CATEGORY A2
			ON A1.CATEGORY_PARENT_LV = A2.CATEGORY_LV AND
				A1.CATEGORY_DETAIL_PARENT_LV = A2.CATEGORY_DETAIL_LV AND A1.GROUP_ID =
				A2.GROUP_ID
			LEFT OUTER JOIN PRODUCT_CATEGORY A3
				ON A2.CATEGORY_PARENT_LV
				= A3.CATEGORY_LV AND A2.CATEGORY_DETAIL_PARENT_LV =
				A3.CATEGORY_DETAIL_LV
			ORDER BY CATEGORY_NAV ASC
		) k
		on p.prod_category=k.category_key
		order by prod_price asc, prod_id desc
		limit 0,10
	</select>

 

 

 

ProductList.html에서

<table cellpadding="0" cellspacing="0" class="col_14" width="100%;">
	<tr>
		<th>순서</th>
		<th>표시</th>
		<th>상품번호</th>
		<th>카테고리</th>
		<th>제목(상품명)</th>
		<th>등록일</th>
		<th>판매가</th>
		<th>재고상태</th>
		<th>할인율</th>
		<th>상품 구매제한</th>
		<th>판매마감일</th>
		<th>등록자</th>
	</tr>
												
	<tr th:each="vo, status : ${list}">
		<td>[[${status.count}]]</td>
		<td>
			<a href="#" class="normal_btn">정보수정</a>
			<a href="#" class="normal_btn modalOn">이미지수정</a>													
		</td>
		<td>[[${vo.prod_id}]]</td>
		<td>[[${vo.category_nav==null?'없음':vo.category_nav}]]</td>

		<td>
			<a title="상세보기(클릭)" th:href="@{ productDetail(prod_id = ${vo.prod_id}) }">
				[[${vo.prod_name}]]
			</a>
		</td>
		<td>[[${ #temporals.format(vo.prod_regdate, 'yyyy-MM-dd') }]]</td>
		<td>[[${vo.prod_price}]]원</td>
		<td>[[${vo.prod_count}]]개</td>
		<td>[[${vo.prod_discount}]]%</td>
		<td>[[${vo.prod_purchase_yn == 'Y' ? '제한없음' : '1회제한'}]]</td>
		<td>[[${vo.prod_enddate}]]</td>
		<td>[[${vo.prod_writer}]]</td>

	</tr>
</table>

 

 

 

<td>[[${vo.category_nav==null?'없음':vo.category_nav}]]</td>

로 join을 통해 뽑아온 내용 추가.

이 목록에 N:1로 한개가 들어간 것.

 

 

MySql은 테이블 엘리어스를 반드시 줘야 한다.