Spring
Spring 29 - adminlte3을 사용한 CRUD (상세보기, 수정, 삭제,MERGE INTO)
inderrom
2023. 2. 17. 15:32
DETAIL
-create.jsp 복붙 후 수정
<list.jsp>에서
아이디를 누르면 상세보기 페이지로 이동할 수 있게
a링크를 건다.
<product_SQL.xml>
<!-- 상품 상세 -->
<select id="detail" parameterType="productVO" resultType="productVO">
SELECT PRODUCT_ID, PNAME, UNIT_PRICE, DESCRIPTION, MANUFACTURER
, CATEGORY, UNITS_IN_STOCK, CONDITION, FILENAME, QUANTITY
FROM PRODUCT
WHERE PRODUCT_ID = #{productId}
</select><ProductMapper.xml>
// 상세보기
public ProductVO detail(ProductVO productVO);
<ProductService.java>
// 상세보기
public ProductVO detail(ProductVO productVO);
<ProductServiceImple.java>
// 상세보기
@Override
public ProductVO detail(ProductVO productVO) {
return this.productMapper.detail(productVO);
}
<ProductController.java>
// 요청URI : /product/detail?productId=P1234
// 요청URI : /product/detail
// 요청파라미터 : productId = P1234
@GetMapping("/detail")
public String detail(@ModelAttribute ProductVO productVO, Model model) {
log.info("productVO : " + productVO);
//detail서비스를 구현해보자
//구현전 : productVO{productId=P1234,pname=null,unitPrice=0...}
productVO = this.productService.detail(productVO);
//구현후 : productVO{productId=P1234,pname=iphone 6s, unitPrice=800000...}
model.addAttribute("data", productVO);
//forwarding -> /views/product/폴더 안의 detail.jsp를 리턴
return "product/detail";
}
<detail.jsp>
- value="${data.~~"} 추가해주자
- textarea에서는 value 속성에 넣지 않고 밖에 써준다.
- option에서는 <c:if test="${data.condition=='New'}">selcted</c:if> 작성
- class="form-control" readonly 속성주기
- textarea도 ..ckeditor ..readonly
// 맨 처음 시작 시 폼데이터를 읽기전용으로 처리
$(".form-control").attr("readonly","true");
CKEDITOR.instances['description'].setReadOnly(true);
})<%@ page language="java" contentType="text/html;charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags"%>
<script type="text/javascript" src="/resources/ckeditor/ckeditor.js"></script>
<script type="text/javascript" src="/resources/js/jquery-3.6.0.js"></script>
<script type="text/javascript">
$(function(){
$("#btnAuto").on("click",function(){
$.ajax({
url:"/product/getproductId",
type:"post",
beforeSend : function(xhr) { // 데이터 전송 전 헤더에 csrf값 설정
xhr.setRequestHeader("${_csrf.headerName}", "${_csrf.token}");
},
success:function(res){
$("input[name='productId']").val(res);
}
});
});
// 맨 처음 시작 시 폼데이터를 읽기전용으로 처리
$(".form-control").attr("readonly","true");
CKEDITOR.instances['description'].setReadOnly(true);
})
</script>
<div class="card card-primary">
<div class="card-header">
<h3 class="card-title">상품 등록</h3>
</div>
<!--
요청URI : /product/create
요청방식 : post
메소드명 : createPost
PRODUCT 테이블에 입력해보자
-->
<form name="frm" action="/product/create" method="post">
<div class="card-body">
<div class="form-group">
<label for="productId">상품아이디</label> <input
type="text" name="productId" class="form-control" id="productId"
placeholder="상품아이디를 입력해주세요" required readonly value="${data.productId }" />
<button type="button" id="btnAuto" class="btn bg-gradient-primary">자동생성</button>
</div>
<div class="form-group">
<label for="pname">상품명</label> <input
type="text" name="pname" class="form-control" id="pname"
placeholder="상품명을 입력해주세요" value="${data.pname}" required readonly/>
</div>
<div class="form-group">
<label for="unitPrice">판매 가격</label> <input
type="number" name="unitPrice" class="form-control" id="unitPrice"
placeholder="판매 가격을 입력해주세요" value="${data.unitPrice}" required />
</div>
<div class="form-group">
<label for="description">상품 설명</label>
<textarea cols="30" rows="5" name="description" class="form-control" id="description"
placeholder="판매 가격을 입력해주세요" >${data.description}</textarea>
</div>
<div class="form-group">
<label for="manufacturer">제조사</label> <input
type="text" name="manufacturer" class="form-control" id="manufacturer"
placeholder="제조사를 입력해주세요" required value="${data.manufacturer}"/>
</div>
<div class="form-group">
<label for="category">카테고리</label> <input
type="text" name="category" class="form-control" id="category"
placeholder="카테고리를 입력해주세요" required value="${data.category}"/>
</div>
<div class="form-group">
<label for="unitsInStock">재고수</label> <input
type="number" name="unitsInStock" class="form-control" id="unitsInStock"
placeholder="재고수를 입력해주세요" value="${data.unitsInStock}" />
</div>
<div class="form-group">
<label for="category">상태</label>
<div class="col-sm-6">
<div class="form-group">
<select id="condition" name="condition" class="form-control">
<option value="New"
<c:if test="${data.condition=='New'}">selcted</c:if>
>New</option>
<option value="Old"
<c:if test="${data.condition=='Old'}">selcted</c:if>
>Old</option>
<option value="Refurbished"
<c:if test="${data.condition=='Refurbished'}">selcted</c:if>
>Refurbished</option>
</select>
</div>
</div>
</div>
</div>
<div class="card-footer">
<button type="submit" class="btn btn-primary">등록</button>
</div>
<!-- Cross Site Request Forgery -->
<sec:csrfInput/>
</form>
</div>
<script type="text/javascript">
CKEDITOR.replace("description");
</script>
UDATE
- FOOTER에서 일반모드와 수정모드를 만들어준다
<div class="card-footer"> <!-- 일반모드 시작 --> <span id="spn1"> <p> <button type="button" id="edit" class="btn btn-primary" style="float:left;">수정</button> <button type="button" id="delete" class="btn btn-primary">삭제</button> <a href="/article/list" class="btn btn-success"> 목록 </a> </p> </span> <!-- 일반모드 끝 --> <!-- 수정모드 시작 --> <span id="spn2" style="display:none;"> <button type="submit" class="btn btn-primary"> 확인 </button> <a href="/article/detail?articleNo=${param.articleNo}" class="btn btn-success"> 취소 </a> </span> <!-- 수정모드 끝 --> </div> <!-- Cross Site Request Forgery --> <sec:csrfInput/>
- jquery
//수정버튼 클릭 -> 수정모드로 전환
$("#edit").on("click", function(){
console.log("수정클릭했다!")
//spn1 : 가림 / spn2 : 보임
$("#spn1").css("display","none");
$("#spn2").css("display","block");
// 입력란 활성화
$(".form-control").removeAttr("readonly");
$("#productId").attr("readonly", "true");
CKEDITOR.instances['description'].setReadOnly(false);
// 폼의 action을 /product/updatePost로 변경
$("form[name='frm']").attr("action","/product/updatePost")
});
<detail.jsp>
<%@ page language="java" contentType="text/html;charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags"%>
<script type="text/javascript" src="/resources/ckeditor/ckeditor.js"></script>
<script type="text/javascript" src="/resources/js/jquery-3.6.0.js"></script>
<script type="text/javascript">
$(function(){
$("#btnAuto").on("click",function(){
$.ajax({
url:"/product/getproductId",
type:"post",
beforeSend : function(xhr) { // 데이터 전송 전 헤더에 csrf값 설정
xhr.setRequestHeader("${_csrf.headerName}", "${_csrf.token}");
},
success:function(res){
$("input[name='productId']").val(res);
}
});
});
// 맨 처음 시작 시 폼데이터를 읽기전용으로 처리
$(".form-control").attr("readonly","true");
CKEDITOR.instances['description'].setReadOnly(true);
})
</script>
<div class="card card-primary">
<div class="card-header">
<h3 class="card-title">상품 등록</h3>
</div>
<!--
요청URI : /product/create
요청방식 : post
메소드명 : createPost
PRODUCT 테이블에 입력해보자
-->
<form name="frm" action="/product/create" method="post">
<div class="card-body">
<div class="form-group">
<label for="productId">상품아이디</label> <input
type="text" name="productId" class="form-control" id="productId"
placeholder="상품아이디를 입력해주세요" required readonly value="${data.productId }" />
<button type="button" id="btnAuto" class="btn bg-gradient-primary">자동생성</button>
</div>
<div class="form-group">
<label for="pname">상품명</label> <input
type="text" name="pname" class="form-control" id="pname"
placeholder="상품명을 입력해주세요" value="${data.pname}" required readonly/>
</div>
<div class="form-group">
<label for="unitPrice">판매 가격</label> <input
type="number" name="unitPrice" class="form-control" id="unitPrice"
placeholder="판매 가격을 입력해주세요" value="${data.unitPrice}" required />
</div>
<div class="form-group">
<label for="description">상품 설명</label>
<textarea cols="30" rows="5" name="description" class="form-control" id="description"
placeholder="판매 가격을 입력해주세요" >${data.description}</textarea>
</div>
<div class="form-group">
<label for="manufacturer">제조사</label> <input
type="text" name="manufacturer" class="form-control" id="manufacturer"
placeholder="제조사를 입력해주세요" required value="${data.manufacturer}"/>
</div>
<div class="form-group">
<label for="category">카테고리</label> <input
type="text" name="category" class="form-control" id="category"
placeholder="카테고리를 입력해주세요" required value="${data.category}"/>
</div>
<div class="form-group">
<label for="unitsInStock">재고수</label> <input
type="number" name="unitsInStock" class="form-control" id="unitsInStock"
placeholder="재고수를 입력해주세요" value="${data.unitsInStock}" />
</div>
<div class="form-group">
<label for="category">상태</label>
<div class="col-sm-6">
<div class="form-group">
<select id="condition" name="condition" class="form-control">
<option value="New"
<c:if test="${data.condition=='New'}">selected</c:if>
>New</option>
<option value="Old"
<c:if test="${data.condition=='Old'}">selected</c:if>
>Old</option>
<option value="Refurbished"
<c:if test="${data.condition=='Refurbished'}">selected</c:if>
>Refurbished</option>
</select>
</div>
</div>
</div>
</div>
<div class="card-footer">
<!-- 일반모드 시작 -->
<span id="spn1">
<p>
<button type="button" id="edit"
class="btn btn-primary"
style="float:left;">수정</button>
<button type="button" id="delete"
class="btn btn-primary">삭제</button>
<a href="/product/list" class="btn btn-success">
목록
</a>
</p>
</span>
<!-- 일반모드 끝 -->
<!-- 수정모드 시작 -->
<span id="spn2" style="display:none;">
<button type="submit" class="btn btn-primary">
확인
</button>
<a href="/product/detail?productId=${param.productId}" class="btn btn-success">
취소
</a>
</span>
<!-- 수정모드 끝 -->
</div>
<!-- Cross Site Request Forgery -->
<sec:csrfInput/>
</form>
</div>
<script type="text/javascript">
CKEDITOR.replace("description");
//수정버튼 클릭 -> 수정모드로 전환
$("#edit").on("click", function(){
console.log("수정클릭했다!")
//spn1 : 가림 / spn2 : 보임
$("#spn1").css("display","none");
$("#spn2").css("display","block");
// 입력란 활성화
$(".form-control").removeAttr("readonly");
$("#productId").attr("readonly", "true");
CKEDITOR.instances['description'].setReadOnly(false);
// 폼의 action을 /product/updatePost로 변경
$("form[name='frm']").attr("action","/product/updatePost")
});
</script>UPDATE
merge into를 사용하여
insert와 update를 동시에 가능하게 하자
- <selectKey resultType="String" order="AFTER" keyProperty="productId" >
<product_SQL.xml>
<!-- insert / update / delete의 경우 resultType은 생략함 -->
<insert id="updatePost" parameterType="productVO">
<selectKey resultType="String" order="AFTER" keyProperty="productId" >
SELECT NVL(SUBSTR(MAX(PRODUCT_ID),1,1)
|| (SUBSTR(MAX(PRODUCT_ID),2) + 1),'P1234')
FROM PRODUCT
</selectKey>
MERGE INTO PRODUCT A
USING DUAL
ON(A.PRODUCT_ID = #{productId})
WHEN MATCHED THEN
UPDATE SET PNAME = #{pname}
, UNIT_PRICE = #{unitPrice}
, DESCRIPTION = #{description}
, MANUFACTURER = #{manufacturer}
, CATEGORY = #{category}
, UNITS_IN_STOCK = #{unitsInStock}
, CONDITION = #{condition}
WHEN NOT MATCHED THEN
INSERT (PRODUCT_ID, PNAME, UNIT_PRICE, DESCRIPTION, MANUFACTURER
, CATEGORY, UNITS_IN_STOCK, CONDITION)
VALUES(#{productId},#{pname},#{unitPrice},#{description},#{manufacturer}
, #{category}, #{unitsInStock}, #{condition})
</insert><ProductMapper.java>
//상품 수정
public int updatePost(ProductVO productVO);
<ProductService.java>
//상품 수정
public int updatePost(ProductVO productVO);
<ProductServiceImpl.java>
// 상품 수정
@Override
public int updatePost(ProductVO productVO) {
return this.productMapper.updatePost(productVO);
}
<ProdController.java>
//요청URI : /product/updatePost
//요청파라미터 : {productId=P1234,pname=iPhone 6s,unitPrice=800000...}
//요청방식 : post
@PostMapping("/updatePost")
public String updatePost(@ModelAttribute ProductVO productVO) {
log.info("productVO : " + productVO);
//기본키 데이터 백업
String oldProductId = productVO.getProductId();
//서비스 처리(merge into로 처리해보자)
int result = this.productService.updatePost(productVO);
log.info("result : " + result);
//redirect
return "redirect:/product/detail?productId="+oldProductId;
}
DELETE
<detail.jsp>
- confirm 사용
// 삭제버튼 눌렀을 때
$("#delete").on("click", function(){
// 폼의 action을 /product/deletePost로 변경
$("form[name='frm']").attr("action", "/product/deletePost");
//confirm
let result = confirm("삭제하시겠습니까?");
if(result > 0 ){ //confirm 결과가 0보다 크면 폼 submit => 삭제 기능 구현
$("form[name='frm']").submit();
}else{ //confirm 결과가 1보다 작으면 "삭제가 취소되었습니다"
alert("삭제가 취소 되었습니다")
}<%@ page language="java" contentType="text/html;charset=UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="sec" uri="http://www.springframework.org/security/tags"%>
<script type="text/javascript" src="/resources/ckeditor/ckeditor.js"></script>
<script type="text/javascript" src="/resources/js/jquery-3.6.0.js"></script>
<script type="text/javascript">
$(function(){
$("#btnAuto").on("click",function(){
$.ajax({
url:"/product/getproductId",
type:"post",
beforeSend : function(xhr) { // 데이터 전송 전 헤더에 csrf값 설정
xhr.setRequestHeader("${_csrf.headerName}", "${_csrf.token}");
},
success:function(res){
$("input[name='productId']").val(res);
}
});
});
// 맨 처음 시작 시 폼데이터를 읽기전용으로 처리
$(".form-control").attr("readonly","true");
CKEDITOR.instances['description'].setReadOnly(true);
})
</script>
<div class="card card-primary">
<div class="card-header">
<h3 class="card-title">상품 등록</h3>
</div>
<!--
요청URI : /product/create
요청방식 : post
메소드명 : createPost
PRODUCT 테이블에 입력해보자
-->
<form name="frm" action="/product/create" method="post">
<div class="card-body">
<div class="form-group">
<label for="productId">상품아이디</label> <input
type="text" name="productId" class="form-control" id="productId"
placeholder="상품아이디를 입력해주세요" required readonly value="${data.productId }" />
<button type="button" id="btnAuto" class="btn bg-gradient-primary">자동생성</button>
</div>
<div class="form-group">
<label for="pname">상품명</label> <input
type="text" name="pname" class="form-control" id="pname"
placeholder="상품명을 입력해주세요" value="${data.pname}" required readonly/>
</div>
<div class="form-group">
<label for="unitPrice">판매 가격</label> <input
type="number" name="unitPrice" class="form-control" id="unitPrice"
placeholder="판매 가격을 입력해주세요" value="${data.unitPrice}" required />
</div>
<div class="form-group">
<label for="description">상품 설명</label>
<textarea cols="30" rows="5" name="description" class="form-control" id="description"
placeholder="판매 가격을 입력해주세요" >${data.description}</textarea>
</div>
<div class="form-group">
<label for="manufacturer">제조사</label> <input
type="text" name="manufacturer" class="form-control" id="manufacturer"
placeholder="제조사를 입력해주세요" required value="${data.manufacturer}"/>
</div>
<div class="form-group">
<label for="category">카테고리</label> <input
type="text" name="category" class="form-control" id="category"
placeholder="카테고리를 입력해주세요" required value="${data.category}"/>
</div>
<div class="form-group">
<label for="unitsInStock">재고수</label> <input
type="number" name="unitsInStock" class="form-control" id="unitsInStock"
placeholder="재고수를 입력해주세요" value="${data.unitsInStock}" />
</div>
<div class="form-group">
<label for="category">상태</label>
<div class="col-sm-6">
<div class="form-group">
<select id="condition" name="condition" class="form-control">
<option value="New"
<c:if test="${data.condition=='New'}">selected</c:if>
>New</option>
<option value="Old"
<c:if test="${data.condition=='Old'}">selected</c:if>
>Old</option>
<option value="Refurbished"
<c:if test="${data.condition=='Refurbished'}">selcted</c:if>
>Refurbished</option>
</select>
</div>
</div>
</div>
</div>
<div class="card-footer">
<!-- 일반모드 시작 -->
<span id="spn1">
<p>
<button type="button" id="edit"
class="btn btn-primary"
style="float:left;">수정</button>
<button type="button" id="delete"
class="btn btn-primary">삭제</button>
<a href="/product/list" class="btn btn-success">
목록
</a>
</p>
</span>
<!-- 일반모드 끝 -->
<!-- 수정모드 시작 -->
<span id="spn2" style="display:none;">
<button type="submit" class="btn btn-primary">
확인
</button>
<a href="/product/detail?productId=${param.productId}" class="btn btn-success">
취소
</a>
</span>
<!-- 수정모드 끝 -->
</div>
<!-- Cross Site Request Forgery -->
<sec:csrfInput/>
</form>
</div>
<script type="text/javascript">
CKEDITOR.replace("description");
//수정버튼 클릭 -> 수정모드로 전환
$("#edit").on("click", function(){
console.log("수정클릭했다!")
//spn1 : 가림 / spn2 : 보임
$("#spn1").css("display","none");
$("#spn2").css("display","block");
// 입력란 활성화
$(".form-control").removeAttr("readonly");
$("#productId").attr("readonly", "true");
CKEDITOR.instances['description'].setReadOnly(false);
// 폼의 action을 /product/updatePost로 변경
$("form[name='frm']").attr("action","/product/updatePost")
});
// 삭제버튼 눌렀을 때
$("#delete").on("click", function(){
// 폼의 action을 /product/deletePost로 변경
$("form[name='frm']").attr("action", "/product/deletePost");
//confirm
let result = confirm("삭제하시겠습니까?");
if(result > 0 ){ //confirm 결과가 0보다 크면 폼 submit => 삭제 기능 구현
$("form[name='frm']").submit();
}else{ //confirm 결과가 1보다 작으면 "삭제가 취소되었습니다"
alert("삭제가 취소 되었습니다")
}
});
</script>
<product_SQL.xml>
<!-- 상품 삭제 -->
<delete id="deletePost" parameterType="productVO">
DELETE FROM PRODUCT
WHERE PRODUCT_ID = #{productId}
</delete>
<ProductMapper.java>
// 상품 삭제
public int deletePost(ProductVO productVO);
<ProductService.java>
// 상품 삭제
public int deletePost(ProductVO productVO);<ProductServiceImlp.java>
//상품 삭제
@Override
public int deletePost(ProductVO productVO) {
return this.productMapper.deletePost(productVO);
}
<ProductController.java>
@PostMapping("/deletePost")
public String deletePost(@ModelAttribute ProductVO productVO) {
int result = this.productService.deletePost(productVO);
if(result >0) {
return "redirect:/product/list";
}else {
return "redirect:/detail?productId"+productVO.getProductId();
}
}