✿∘˚˳°∘°
79일차 : Dynamic Mybatis 본문
20230321
Dynamic Query(동적쿼리) : 상황에따라 쿼리문이 동적으로 변화한다.
SQL문을 여러번 호출해야하는경우 DAO를 여러번 호출하여 실행하였지만 Mybatis에서 이를 동적으로 제어하는 구문을 제공하여 쉽게 쿼리를 사용하도록 지원
- if / choose / trim(쿼리문에서특정부분을없앨때사용) / foreach
- login과 searchMemberId의 로직이 굉장히 유사
Controller
login
@RequestMapping(value="/login.do")
public String login(Member m, HttpSession session) {
Member loginMember = service.selectOneMember(m);
if(loginMember != null) {
session.setAttribute("m", loginMember);
}
return "redirect:/";
}searchMemberId
@RequestMapping(value="/searchMemberId.do")
public String searchMemberId(String memberId, Model model) {
Member m = service.selectOneMember(memberId);
if(m != null) {
model.addAttribute("m", m);
return "member/searchMemberId";
}else {
return "redirect:/";
}
}memberMapper
login
<select id="selectOneMember" parameterType="m" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl where member_id=#{memberId} and member_pw=#{memberPw}
</select>searchMemberId
<select id="searchMemberId" parameterType="string" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl where member_id=#{_parameter}
</select>
1. if
-> searchMemberId에서 매개변수를 member형으로 받아도 무관 -> 수정
(매개변수가 동일하여, 오버로딩이 불가능하므로 메소드 이름도 변경해준다)
selectOneMember(String memberId) -> selectOneMemberId(Member member)
Controller
@RequestMapping(value="/searchMemberId.do")
public String searchMemberId(Member member, Model model) {
Member m = service.selectOneMemberId(member);
if(m != null) {
model.addAttribute("m", m);
return "member/searchMemberId";
}else {
return "redirect:/";
}
}Service
public Member selectOneMemberId(Member member) {
return dao.selectOneMemberId(member);
}Dao
public Member selectOneMemberId(Member member) {
Member m = sqlSession.selectOne("member.selectOneMemberId", member);
return m;
}memberMapper
<select id="selectOneMember" parameterType="m" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl where member_id=#{memberId}
</select>
<select id="searchMemberId" parameterType="m" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl where member_id=#{memberId}
</select>login과 searchMemberId의 쿼리문이 where절의 and member_pw = #{memberPw} 를 제외하고 동일한걸 확인할 수 있음
-> 이럴 때 동적쿼리의 if문을 사용
최종 Mapper
<select id="selectOneMember" parameterType="m" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl where member_id=#{memberId}
<if test="memberPw != null">
and member_pw=#{memberPw}
</if>
</select>Mapper에서 쿼리문 하나만 남기고, 동일한 Dao를 불러오면된다.
2. choose
<form action="/searchMember1.do" method="post">
<select name="type">
<option value="id">아이디</option>
<option value="name">이름</option>
</select>
<input type="text" name="keyword">
<input type="submit" value="검색">
</form>Controller
@RequestMapping(value="/searchMember1.do")
public String searchMember1(String type, String keyword, Model model) {
//type, keyword라는 변수가 없으므로 VO를 만들어주던지 String으로 받아와야함
//아이디로 검색하면 : where member_id = ?
//이름으로 검색하면 : where member_name like %검색어%
//조회결과가 1개일수도(아이디) 여러개일수도(이름)있으므로 list로 받는게 좋다
ArrayList<Member> list = service.search1(type, keyword);
model.addAttribute("list", list);
return "member/allMember";
}Service
public ArrayList<Member> search1(String type, String keyword) {
//쿼리문 수행을 위해서는 매개변수(type, keyword) 2개를 전송해줘야함
//Mybatis는 매개변수를 1개만 받을 수 있음
//해결방법 1) type과 keyword를 묶을 VO생성 - VO를 생성하면 애초에 컨트롤러에서 매개변수로 VO로 받으면된다
SearchVO search = new SearchVO(type, keyword);
ArrayList<Member> list = dao.search1(search);
//해결방법 2) HashMap<String, Object>사용(VO대신 Map으로 묶겠다)
return list;
}Dao
public ArrayList<Member> search1(SearchVO search) {
List list = sqlSession.selectList("member.search1", search);
return (ArrayList<Member>)list;
}memberSQL
<!-- choose -->
<select id="search1" parameterType="kr.or.iei.member.model.vo.SearchVO" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl
<!-- 조건절이 type에 따라 달라짐 -->
<choose>
<when test="type.equals('id')">
where member_id = #{keyword}
</when>
<when test="type.equals('name')">
where member_name like '%'||#{keyword}||'%'
</when>
</choose>
</select>
3. trim
<h3>아이디 or 이름으로 검색</h3>
<p>
아이디만 입력하고 검색하면 아이디로 조회,
이름만 입력하고 검색하면 이름으로 조회,
둘 다 입력하고 검색하면 두개 and조건으로 조회
</p>
<form action="/searchMember2.do" method="get">
아이디 : <input type="text" name="memberId"><br>
이름 : <input type="text" name="memberName"><br>
<input type="submit" value="입력">
</form>Controller
@RequestMapping(value="/searchMember2.do")
public String searchMember2(Member member, Model model) {
ArrayList<Member> list = service.search2(member);
//넘어오는 값중 id 또는 name을 입력하지 않으면 null이 아닌 빈값('')
//choose로 처리할 수 있지만 case가 여러개이기 때문에 코드가 굉장히 복잡해짐
model.addAttribute("list", list);
return "member/allMember";
}Service
public ArrayList<Member> search2(Member member) {
ArrayList<Member> list = dao.search2(member);
return list;
}Dao
public ArrayList<Member> search2(Member member) {
List list = sqlSession.selectList("member.search2", member);
return (ArrayList<Member>)list;
}memberSQL
<!-- trim -->
<select id="search2" parameterType="m" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl
<!-- 이대로만 작성하면 where가 없기때문에 에러발생(if문이 실행되지않으면 에러X) : trim을 사용 -->
<!-- 경우의 수에따라 where가 필요할때도 and가 필요할때도 있음 -->
<trim prefix="where" prefixOverrides="and|or">
<!-- trim내부에 있는게 하나라도 동작하면, prefix에 있는 값을 붙여라 -->
<!-- 실행된게 and 또는 or로 시작되면 그걸 지워라 -->
<!--
1. 둘다 입력 안한 경우 : 아무런동작X
2. 아이디만 입력한 경우 : prefix에 의해 where가 붙음 :
where member_id like '%'||#{memberId}||'%'
3. 이름만 입력한 경우 : prefix에 의해 where가 붙고, prefixOverrides에 의해 and가 지워짐
where member_name like '%'||#{memberName}||'%'
4. 둘다 입력한 경우 : prefix에 의해 where가 붙고 prefixOverrides는 실행 X
where member_id like '%'||#{memberId}||'%' and member_name like '%'||#{memberName}||'%'
-->
<if test="!memberId.equals('')">
member_id like '%'||#{memberId}||'%'
</if>
<if test="!memberName.equals('')">
and member_name like '%'||#{memberName}||'%'
</if>
</trim>
</select>
4. foreach
<h3><a href="/searchMember3.do">전체 아이디목록조회</a></h3> @RequestMapping(value="/searchMember3.do")
public String searchMember3(Model model) {
//전체회원의 아이디만 받아올것 -> 그래도 여러개이기때문에 ArrayList -> 내부자료형은 String
ArrayList<String> idList = service.search3();
model.addAttribute("idList", idList);
return "member/idList";
} public ArrayList<String> search3() {
ArrayList<String> list = dao.search3();
return list;
} public ArrayList<String> search3() {
List list = sqlSession.selectList("member.search3");
return (ArrayList<String>)list;
} <select id="search3" resultType="string">
select member_id from member_tbl
</select><%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>아이디 목록</h1>
<hr>
<form action="/searchMember4.do" method="post">
<c:forEach items="${idList }" var="memberId">
<p>
<input type="checkbox" name="memberId" value="${memberId }">
${memberId }
</p>
</c:forEach>
<input type="submit" value="선택회원조회">
</form>
</body>
</html>Controller
@RequestMapping(value="/searchMember4.do")
public String searchMember4(String[] memberId, Model model) {
ArrayList<Member> list = service.search4(memberId);
model.addAttribute("list", list);
return "member/allMember";
}Service
public ArrayList<Member> search4(String[] memberId) {
ArrayList<Member> list = dao.search4(memberId);
return list;
}Dao
public ArrayList<Member> search4(String[] memberId) {
List list = sqlSession.selectList("member.search4", memberId);
//Mybatis에서 매개변수로 배열을 전달하는 경우
/* 이방식으로 전달을 한다.
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("array", memberId); key가 array value가 memberId
List list = sqlSession.selectList("member.search4", map);
*/
return (ArrayList<Member>)list;
}memberSQL
<select id="search4" parameterType="map" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl
where member_id in
<!-- collection : 대상이되는 배열의 객체 / item : 배열에서 한바퀴 돌 때마다 꺼낼 값-->
<!-- open : foreach시작 시 넣을값 / close : foreach종료 시 넣을 값 / separator : for문 사이 구분자-->
<foreach collection="array" item="id" open="(" close=")" separator=",">
#{id}
</foreach>
</select>
5. <![CDATA[ > ]]>
<h3><a href="/searchMember5.do">회원목록조회(특정조건)</a></h3>Controller
@RequestMapping(value="/searchMember5.do")
public String searchMember5(Model model) {
ArrayList<Member> list = service.serach5();
model.addAttribute("list", list);
return "member/allMember";
}Service
public ArrayList<Member> serach5() {
ArrayList<Member> list = dao.search5();
return list;
}Dao
public ArrayList<Member> search5() {
List list = sqlSession.selectList("member.search5");
return (ArrayList<Member>)list;
}memberSQL
<!-- 부등호 -->
<select id="search5" resultType="m">
select
member_no as memberNo,
member_id as memberId,
member_pw as memberPw,
member_name as memberName,
member_phone as memberPhone,
member_email as memberEmail
from member_tbl
<!-- 에러발생 <가 부등호가 아닌 여는태그로 인식되기때문 : 부등호로 인식시켜줘야함 <![CDATA[ > ]]>
<![CDATA[ > ]]> 대괄호 영역안에 쿼리문 전체를 넣어줘도된다. -->
where member_no <![CDATA[ > ]]> 20
</select>
- [ Board ]
1. 게시글 목록(+페이징처리)
Controller
@RequestMapping(value="/boardList.do")
public String boardList(int reqPage, Model model) {
BoardPageData bpd = service.selectBoardList(reqPage);
model.addAttribute("list", bpd.getList());
model.addAttribute("pageNavi", bpd.getPageNavi());
return "board/boardList";
}Service
public BoardPageData selectBoardList(int reqPage) {
//한 페이지당 보여줄 게시물 수
int numPerPage = 5;
//reqPage = 1 : 1~2 / 2 : 3~4
int end = reqPage * numPerPage;
int start = end - numPerPage + 1;
//계산된 start, end를 가지고 게시물 목록 조회
//Mybatis는 매개변수를 하나만 받을 수 있기 때문에 HashMap 사용
//String : 쿼리문에서 사용하기 쉽게하려고 / Object : 여러자료형을 받을 수 잇도록
HashMap<String, Object> map = new HashMap<String, Object>();
map.put("start", start);
map.put("end", end);
ArrayList<Board> list = dao.selectBoardList(map);
//pageNavi 제작 시작
//전체페이지 수 계산 필요 -> 전체 게시물 수 조회
int totalCount = dao.selectBoardCount();
//전체게시물로 전체 페이지 수 계산
//방법1
/*
int totalPage = 0;
if(totalCount%numPerPage == 0) {
totalPage = totalCount/numPerPage;
}else {
totalPage = (totalCount/numPerPage) + 1;
}
*/
//방법2
int totalPage = (int)Math.ceil(totalCount/(double)numPerPage); //ceil : 올림
//pageNavi 사이즈
int pageNaviSize = 5;
int pageNo = 1;
if(reqPage > 3) {
pageNo = reqPage-2;
}
//페이지네비 생성시작
String pageNavi = "";
//이전버튼
if(pageNo != 1) {
pageNavi +="<a href='/boardList.do?reqPage="+(pageNo-1)+"'>[이전]</a>";
}
//페이지 숫자 생성
for(int i=0; i<pageNaviSize; i++) {
if(pageNo == reqPage) {
//현재페이지
pageNavi += "<span>"+pageNo+"</span>";
}else {
pageNavi += "<a href='/boardList.do?reqPage="+pageNo+"'>"+pageNo+"</a>";
}
pageNo++;
if(pageNo > totalPage) {
break;
}
}
//다음버튼
if(pageNo <= totalPage) {
pageNavi +="<a href='/boardList.do?reqPage="+pageNo+"'>[다음]</a>";
}
BoardPageData bpd = new BoardPageData(list, pageNavi);
return bpd;
}Dao
public ArrayList<Board> selectBoardList(HashMap<String, Object> map) {
List list = sqlSession.selectList("board.selectBoardList", map);
return (ArrayList<Board>)list;
}
public int selectBoardCount() {
int totalCount = sqlSession.selectOne("board.totalCount");
return totalCount;
}boardSQL
<select id="selectBoardList" parameterType="map" resultType="b">
select * from
(select rownum as rnum, b.* from
(select
board_no as boardNo,
board_title as boardTitle,
board_writer as boardWriter,
board_date as boardDate
from board
order by 1 desc)b)
where rnum between #{start} and #{end}
</select>
<select id="totalCount" resultType="_int">
select count(*) from board
</select>
2. 게시글 작성
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h1>게시글작성</h1>
<hr>
<form action="/boardWrite.do" method="post" enctype="multipart/form-data">
<table border="1">
<tr>
<th>제목</th>
<td><input type="text" name="boardTitle"></td>
</tr>
<tr>
<th>작성자</th>
<td>
${sessionScope.m.memberId }
<input type="hidden" name="boardWriter" value="${sessionScope.m.memberId }">
</td>
</tr>
<tr>
<th>첨부파일</th>
<td><input type="file" name="boardFile" multiple></td>
</tr>
<tr>
<th>내용</th>
<td><textarea name="boardContent"></textarea></td>
</tr>
<tr>
<th colspan="2"><input type="submit" value="작성하기"></th>
</tr>
</table>
</form>
<a href="/">메인으로</a>
</body>
</html>Controller
@RequestMapping(value="/boardWriteFrm.do")
public String boardWriteFrm() {
return "board/boardWriteFrm";
}
@RequestMapping(value="/boardWrite.do")
public String boardWrite(Board b, MultipartFile[] boardFile, HttpServletRequest request) {
ArrayList<FileVO> fileList = new ArrayList<FileVO>();
if(!boardFile[0].isEmpty()){
//파일이 존재할 때
String savePath = request.getSession().getServletContext().getRealPath("/resources/upload/board/");
for(MultipartFile file : boardFile) {
String filename = file.getOriginalFilename();
String filepath = manager.upload(savePath, file);
FileVO fileVO = new FileVO();
fileVO.setFilename(filename);
fileVO.setFilepath(filepath);
fileList.add(fileVO);
}
}
int result = service.insertBoard(b, fileList);
if(result == fileList.size()+1) {
return "redirect:/boardList.do?reqPage=1";
}else {
return "redirect:/";
}
}Service
public int insertBoard(Board b, ArrayList<FileVO> fileList) {
//DB작업 3번 - 1.board테이블에 insert / 2.파일에서 참조할 board_no를 조회 / 3.file_tbl테이블에 insert
int result = dao.insertBoard(b);
//Mybatis에서 boardNo를 넣어주었기 때문에 boardNo가 0 이 아닌 해당 게시글번호로 나온다.
if(result > 0) {
//int boardNo = dao.selectBoardNo();
for(FileVO file : fileList) {
file.setBoardNo(b.getBoardNo());
result += dao.insertFile(file);
}
}
return result;
}Dao
public int insertBoard(Board b) {
int result = sqlSession.insert("board.insertBoard", b);
return result;
}
public int selectBoardNo() {
int boardNo = sqlSession.selectOne("board.selectBoardNo");
return boardNo;
}
public int insertFile(FileVO file) {
int result = sqlSession.insert("board.insertFile", file);
return result;
}boardSQL
<insert id="insertBoard" parameterType="b">
insert into board values
(board_seq.nextval, #{boardTitle}, #{boardWriter}, #{boardContent}, to_char(sysdate, 'yyyy-mm-dd'))
<!-- 정규화가 이루어져있어 외래키로 연결되어있을 경우 selectKey를 사용하면 별도의 select를 진행하지 않아도된다. -->
<!-- key값을 다시 조회할건데 해당태그(현재insert)가 동작하고나면 조회할 때 -->
<selectKey resultType="_int" order="AFTER" keyProperty="boardNo">
<!-- order:insert문 이후에 실행, keyProperty: boardNo변수에 넣어줄것 -->
select max(board_no) from board
</selectKey>
</insert>
<!-- <select id="selectBoardNo" resultType="_int">
select max(board_no) from board
</select> -->
<insert id="insertFile" parameterType="f">
insert into file_tbl values(file_seq.nextval, #{boardNo}, #{filename}, #{filepath})
</insert>
<!--
<select id="selectOneBoard" parameterType="_int" resultType="b">
select
board_no as boardNo,
board_title as boardTitle,
board_writer as boardWriter,
board_content as boardContent,
board_date as boardDate
from board where board_no = ${_parameter}
</select>
-->
3. 게시글 상세보기
Controller
@RequestMapping(value="/boardView.do")
public String boardView(int boardNo, Model model) {
Board b = service.selectOneBoard(boardNo);
model.addAttribute("b", b);
return "board/boardView";
}Service
public Board selectOneBoard(int boardNo) {
//1.board테이블조회
Board b = dao.selectOneBoard(boardNo);
//2.file테이블조회
/*
if(b != null) {
ArrayList<FileVO> fileList = dao.selectFileList(boardNo);
b.setFileList(fileList);
}
*/
return b;
}Dao
public Board selectOneBoard(int boardNo) {
Board b = sqlSession.selectOne("board.selectOneBoard", boardNo);
return b;
}
public ArrayList<FileVO> selectFileList(int boardNo) {
List list = sqlSession.selectList("board.selectFileList", boardNo);
return (ArrayList<FileVO>)list;
}boardSQL
<!--
<select id="selectOneBoard" parameterType="_int" resultType="b">
select
board_no as boardNo,
board_title as boardTitle,
board_writer as boardWriter,
board_content as boardContent,
board_date as boardDate
from board where board_no = ${_parameter}
</select>
-->
<select id="selectFileList" parameterType="_int" resultType="f">
select
file_no as fileNo,
board_no as boardNo,
filename,
filepath
from file_tbl where board_no = #{_parameter}
</select>
<select id="selectOneBoard" parameterType="_int" resultMap="getBoard">
select * from board where board_no = ${_parameter}
</select>
<!-- type:최종적으로 되돌려주는 type / id:연결을 위함 -->
<!-- 사용은 할 수 있지만 효율은낮음 DB에 쓸데없는 데이터가 많아지기 때문에 -->
<resultMap type="b" id="getBoard">
<result column="board_no" property="boardNo" />
<result column="board_title" property="boardTitle" />
<result column="board_Writer" property="boardWriter" />
<result column="board_Content" property="boardContent" />
<result column="board_date" property="boardDate" />
<collection property="fileList"
select="selectFileList"
column="board_no"
ofType="f"
javaType="java.util.ArrayList"
/>
</resultMap>4. 게시글 수정
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<body>
<h1>게시물 수정</h1>
<hr>
<form action="/boardUpdate.do" method="post" enctype="multipart/form-data" id="updateFrm">
<table border="1">
<tr>
<th>글번호</th>
<td>
${b.boardNo }
<input type="hidden" name="boardNo" value="${b.boardNo }">
</td>
<th>작성자</th>
<td>${b.boardWriter }</td>
<th>작성일</th>
<td>${b.boardDate }</td>
</tr>
<tr>
<th>제목</th>
<td colspan="5"><input type="text" name="boardTitle" value="${b.boardTitle }"></td>
</tr>
<tr>
<th>첨부파일</th>
<td colspan="5">
<c:forEach items="${b.fileList }" var="f">
<p>
${f.filename}
<button type="button" onclick="deleteFile(this, ${f.fileNo}, '${f.filepath }');">삭제</button>
</p>
</c:forEach>
</td>
</tr>
<tr>
<th>첨부파일 추가</th>
<td colspan="5"><input type="file" name="boardFile" multiple></td>
</tr>
<tr>
<td colspan="6" style="min-hight:300px;">
<textarea name="boardContent">${b.boardContent }</textarea>
</td>
</tr>
<tr>
<td colspan="6">
<input type="submit" value="작성완료">
</td>
</tr>
</table>
</form>
<script>
function deleteFile(obj, fileNo, filepath){
const fileNoInput = $("<input>");
fileNoInput.attr("name", "fileNo");
fileNoInput.val(fileNo);
fileNoInput.hide();
const filepathInput = $("<input>");
filepathInput.attr("name", "filepath");
filepathInput.val(filepath);
filepathInput.hide();
$("#updateFrm").append(fileNoInput).append(filepathInput);
$(obj).parent().remove();
}
</script>
</body>
</html>Controller
@RequestMapping(value="/boardUpdateFrm.do")
public String boardUpdateFrm(int boardNo, Model model) {
Board b = service.selectOneBoard(boardNo);
model.addAttribute("b", b);
return "board/boardUpdateFrm";
}
@RequestMapping(value="/boardUpdate.do")
public String boardUpdate(Board b, int[] fileNo, String[] filepath, MultipartFile[] boardFile, HttpServletRequest request) {
System.out.println("넘어오나");
ArrayList<FileVO> fileList = new ArrayList<FileVO>();
String savePath = request.getSession().getServletContext().getRealPath("/resources/upload/board/");
if(!boardFile[0].isEmpty()) {
for(MultipartFile file : boardFile) {
String filename = file.getOriginalFilename();
String newfilepath = manager.upload(savePath, file);
FileVO fileVO = new FileVO();
fileVO.setFilename(filename);
fileVO.setFilepath(newfilepath);
fileList.add(fileVO);
}
}//파일업로드종료
int result = service.boardUpdate(b, fileList, fileNo);
if(fileNo != null && result == (fileList.size() + fileNo.length + 1)) {
for(String delFile : filepath) {
manager.deleteFile(savePath, delFile);
}
return "redirect:/boardView.do?boardNo="+b.getBoardNo();
}else if(fileNo == null && result == fileList.size()+1){
return "redirect:/boardView.do?boardNo="+b.getBoardNo();
}else {
return "redirect:/boardList.do?reqPage=1";
}
}Service
public int boardUpdate(Board b, ArrayList<FileVO> fileList, int[] fileNo) {
int result = dao.updateBoard(b);
if(result > 0) {
if(fileNo != null) {
for(int no : fileNo) {
result += dao.deleteFile(no);
}
}
for(FileVO f : fileList) {
f.setBoardNo(b.getBoardNo());
result += dao.insertFile(f);
}
}
return result;
}Dao
public int updateBoard(Board b) {
int result = sqlSession.update("board.updateBoard", b);
return result;
}
public int deleteFile(int no) {
int result = sqlSession.delete("board.deleteFile", no);
return result;
}boardSQL
<update id="updateBoard" parameterType="b">
update board set board_title = #{boardTitle}, board_content = #{boardContent} where board_no = #{boardNo}
</update>
<delete id="deleteFile" parameterType="_int">
delete from file_tbl where file_no = #{_parameter}
</delete>5. 게시글 삭제
Controller
@RequestMapping(value="/boardDelete.do")
public String boardDelete(int boardNo, HttpServletRequest request) {
ArrayList<FileVO> list = service.deleteBoard(boardNo);
if(list == null) {
return "redirect:/boardView.do?boardNo="+boardNo;
}else {
String savePath = request.getSession().getServletContext().getRealPath("/resources/upload/board/");
for(FileVO file : list) {
manager.deleteFile(savePath, file.getFilepath());
}
return "redirect:/boardList.do?reqPage=1";
}
}Service
public ArrayList<FileVO> deleteBoard(int boardNo) {
//삭제이후에 조회를 하면 on delete cascade 때문에 이미 지워진 상태이기 때문에 확인불가
ArrayList<FileVO> fileList = dao.selectFileList(boardNo);
int result = dao.deleteBoard(boardNo);
if(result > 0) {
return fileList;
}else {
return null;
}
}Dao
public int fileDelete(int boardNo) {
int result = sqlSession.delete("board.deleteBoardFile", boardNo);
return result;
}
public int deleteBoard(int boardNo) {
int result = sqlSession.delete("board.deleteBoard", boardNo);
return result;
}boardSQL
<delete id="deleteBoardFile" parameterType="_int">
delete from file_tbl where board_no = #{_parameter}
</delete>
<delete id="deleteBoard" parameterType="_int">
delete from board where board_no = #{_parameter}
</delete>'국비수업 > Spring' 카테고리의 다른 글
| 81일차 : ajax, 에러처리, 웹소켓 (0) | 2023.03.24 |
|---|---|
| 80일차 : AOP, Transaction, Schedul (0) | 2023.03.22 |
| 78일차 : Mybatis (0) | 2023.03.21 |
| 77일차 : SpringMVC - 게시판2 (0) | 2023.03.17 |
| 76일차 : 정규화, SpringMVC - 게시판 (0) | 2023.03.16 |
'국비수업/Spring' Related Articles
more
Comments