[Boot] Use procedure

Spring/Boot 2022. 9. 10. 21:30

libs 폴더 생성해 cos.jar 추가하기

\src\main\resources\application.properties

# Server port
server.port=8070

# JSP 
spring.mvc.view.prefix=/WEB-INF/views/
spring.mvc.view.suffix=.jsp

# multi-part Form
spring.servlet.multipart.enabled=false

# Oracle
spring.datasource.driver-class-name=oracle.jdbc.OracleDriver
spring.datasource.url=jdbc:oracle:thin:@localhost:1521:xe
spring.datasource.username=scott
spring.datasource.password=tiger

# mybatis
mybatis.mapper-locations=classpath:mybatis/mapper/**/**.xml

mybatis 폴더 안 파일, static 폴더 안 파일, jsp 파일은 G14_Board 프로젝트와 같음

로그인 form

MemberController

package com.ecl.g15.controller;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.ecl.g15.service.MemberService;


@Controller
public class MemberController {
	
	@Autowired
	MemberService ms;
	
	@RequestMapping("/")
	public String index(HttpServletRequest request) {
		HttpSession session = request.getSession();
		if( session.getAttribute("loginUser") == null ) 
			return "member/loginForm";
		else return "redirect:/main";
	}
}

프로시져를 호출할 때 IN 변수에는 id를 보내서 검색하게 하고 OUT 변수에는 비어있는 MemberVO 변수를 보내서 프로시져가 나에게 보내는 MemberVO 자료를 담게 함. 프로시져는 MemberDao.xml에서 호출. 그 이전 클래스들에서는 필요한 변수들을 준비해서 전달해주는 역할을 함

로그인 form

Member Controller

package com.ecl.g15.service;

import java.util.HashMap;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.ecl.g15.dao.IMemberDao;

@Service
public class MemberService {

	@Autowired
	IMemberDao mdao;

	public void getMember(HashMap<String, Object> paramMap) {
		return mdao.getMember(paramMap);
	}
}

interface Member DAO

package com.ecl.g15.dao;

import java.util.HashMap;

import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface IMemberDao {

	public void getMember(HashMap<String, Object> paramMap);
}

MemberDAO xml

<?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.ecl.g15.dao.IMemberDao">

	<resultMap id="boardMap" type="java.util.HashMap"></resultMap>
	<select id="getMember" statementType="CALLABLE" 
				parameterType="java.util.HashMap">
		{
		CALL getMember( 
			#{ id<!-- , mode=IN 생략가능 --> }, 
			#{ ref_cursor, mode=OUT, jdbcType=CURSOR, 
				javaType=ResultSet, resultMap=boardMap } 
			)
		}
	</select>
</mapper>

PL/SQL

create or replace procedure getMember(
    p_id IN member.id%type,
    p_curvar OUT SYS_REFCURSOR
)

IS
    result_cur SYS_REFCURSOR;
BEGIN
    OPEN result_cur For select * from member where id = p_id;
    p_curvar := result_cur;
END;

로그인 하기

Member Controller

	@RequestMapping(value="login", method=RequestMethod.POST)
	public String login(
			@ModelAttribute("dto") @Valid MemberVO membervo,
			BindingResult result, HttpServletRequest request, Model model ) {
		
		if( result.getFieldError("userid") != null ) {
			model.addAttribute("message", result.getFieldError("userid").getDefaultMessage());
			return "member/loginForm";
		} else if( result.getFieldError("pwd") != null ) {
			model.addAttribute("message", 
					result.getFieldError("pwd").getDefaultMessage() );
			return "member/loginForm";
		}
		
		// 프로시져 형식: 
		// 		프로시져이름(IN변수, OUT변수 ....)
		// 		BEGIN
		// 		END;
		
		HashMap<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("id", membervo.getId()); // 해쉬맵에 검색에 필요한 아이디를 넣어줌
		// 이해를 돕기 위해 위에서는 MemberVO라는 자료형으로 설명했지만
		// 프로시져에서는 커서를 사용하기 때문에 아래는 커서 변수가 담기게 됨
		paramMap.put("ref_cursor", null); // 비어 있는 자료형을 전송
		ms.getMember(paramMap);
		
		// 오라클 프로시져에서 커서에 담겨오는 자료형은 한개 이상의 레코드들입니다
		// 위 getMember의 결과는 아이디로 검색한 한명의 데이터이지만
		// 결과는 리스트형태로 담겨옴
		// 그중 첫번째 MemberVO에 꺼내어 담아서 사용함
		
		// 1. 리스트부터 꺼냄
		ArrayList<HashMap<String,Object>> list 
			= (ArrayList<HashMap<String,Object>>)paramMap.get("ref_cursor");
		// 프로시져의 결과는 레코드의 리스트들인데
		// 각 레코드는 <필드명, 필드값> 형태의 해쉬맵
		// 해쉬맵 하나가 하나의 레코드를 이루고 그 안에는 각 필드명과 값들이 들어있음
		// 그 레코드들의 집합이 ref_cursor라는 키에 저장되어 돌아와 있는 형태
		
		// ArrayList 안에 담겨져 있는 HashMap<String,Object>는 <필드명, 필드값>임
		// HashMap 안에 {"ID", "hong"} {"PWD", "1111"} {"NAME","홍동길"}...
		// 위와 같이 저장되어 있음. 지금은 아이디로 검색한 결과 한개이지만 
		// 보통 여러개가 저장되어 옴
		// 해쉬맵으로 받아오는 루틴의 특성상 키값이 모두 "대문자"임
		// 그래서 jsp에서 사용할 때도 모두 대문자를 사용해야 함
		
		// * 리스트의 결과가 아무것도 없는지를 먼저 조사함
		if(list.size()==0) {
			model.addAttribute("message","no id");
			return "member/loginForm";
		}
		
		// 2. 리스트의 첫번째 항목을 해쉬맵 mvo에 담음
		HashMap<String,Object> mvo = list.get(0);
		
		System.out.println(mvo.get("ID"));
		System.out.println(mvo.get("NAME"));
		System.out.println(mvo.get("PWD"));
		System.out.println(mvo.get("PHONE"));
		System.out.println(mvo.get("EMAIL"));
		
		String url="member/loginForm";
		
		if( mvo.get("PWD") == null)
			model.addAttribute("message","error password");
		else if(! mvo.get("PWD").equals(membervo.getPwd()))
				model.addAttribute("message","wrong password");
		else if( mvo.get("PWD").equals(membervo.getPwd())){
			HttpSession session = request.getSession();
			session.setAttribute("loginUser", mvo);
			url="redirect:/main";
		}
		return url;
	}

게시글 메인

Board Controller

package com.ecl.g15.controller;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.ecl.g15.service.BoardService;

@Controller
public class BoardController {

	@Autowired
	BoardService bs;

	@RequestMapping("/main")
	public ModelAndView goMain(HttpServletRequest request) {
		ModelAndView mav = new ModelAndView();
		mav.setViewName("board/main");
		return mav;
	}
}

main.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>main.jsp</title>
<link rel="stylesheet" type="text/css" href="/css/board.css" >
</head>
<body>
	<div id="wrap" align="center">
	<h1>게시글 리스트</h1>
	<table class="list">
		<tr><td colspan="5" style="border: white; text-align: right">
			<div style="float:left;"> 
				${loginUser.NAME}(${loginUser.ID})님 로그인 
				<input type="button" value="정보수정" 
					onClick="location.href='memberEditForm'"/>
				<input type="button" value="로그아웃"	
					onClick="location.href='logout'"></div>
			<div style="float:right;">
			<a href="boardWriteForm">게시글 등록</a></div></td></tr>
	</table><br><br>
	</div>
</div>
</body>
</html>

로그아웃

Member Controller

	@RequestMapping(value="/logout")
	public String logout(HttpServletRequest request) {
		HttpSession session = request.getSession();
		session.invalidate();
		return "redirect:/";
	}

회원 가입

Member Controller

	@RequestMapping("/memberJoinForm")
	public String join_form( ) {
		return "member/memberJoinForm";
	}
	@RequestMapping("/idcheck")
	public ModelAndView idcheck( 
			@RequestParam("id") String id ) {
		
		ModelAndView mav = new ModelAndView();
		HashMap<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("id", id);
		paramMap.put("ref_cursor", null);
		
		ms.getMember(paramMap);
		
		ArrayList<HashMap<String,Object>> list 
			= (ArrayList<HashMap<String,Object>>)paramMap.get("ref_cursor");

		if( list.size() == 0 ) mav.addObject("result", -1);
		else mav.addObject("result" , 1);
		
		mav.addObject("id", id);
		mav.setViewName("member/idcheck");
		return mav;
	}
	@RequestMapping(value="/memberJoin", method=RequestMethod.POST)
	public ModelAndView memberJoin(
			@ModelAttribute("dto") @Valid MemberVO membervo,
			BindingResult result,
			@RequestParam(value="re_id") String reid,
			@RequestParam(value="pwd_check") String pwdchk,
			Model model
	) {
		ModelAndView mav = new ModelAndView();
		mav.setViewName("member/memberJoinForm");  
		if(reid!=null&&reid.equals("")) mav.addObject("re_id", reid);
		
		if( result.getFieldError("id")!=null)
			mav.addObject("message", "아이디를 입력하세요" );
		else if( result.getFieldError("pwd")!=null)
			mav.addObject("message", "비밀번호를 입력하세요" );
		else if( result.getFieldError("name")!=null)
			mav.addObject("message", result.getFieldError("name").getDefaultMessage() );
		else if( !membervo.getId().equals(reid) ) 
			mav.addObject("message", "아아디 중복체크가 되지 않았습니다");
		else if( !membervo.getPwd().equals(pwdchk)) 
			mav.addObject("message","비밀번호 확인이 일치하시 않습니다.");
		else {
			HashMap<String,Object> paramMap = new HashMap<String,Object>();
			
			paramMap.put("id",membervo.getId());
			paramMap.put("pwd",membervo.getPwd());
			paramMap.put("name",membervo.getName());
			paramMap.put("email",membervo.getEmail());
			paramMap.put("phone",membervo.getPhone());
			
			ms.insertMember( paramMap );
			mav.addObject("message", "회원가입이 완료되었습니다. 로그인 하세요");
			mav.setViewName("member/loginForm"); 
		}
		return mav;
	}

Service

	public void insertMember(HashMap<String, Object> paramMap) {
		mdao.insertMember(paramMap);		
	}

DAO

	public void insertMember(HashMap<String, Object> paramMap);

xml

	<insert id="insertMember" statementType="CALLABLE" 
				parameterType="java.util.HashMap">
				{
		CALL insertMember( 
			#{ id }, #{ pwd }, #{ name }, #{ email }, #{ phone }
			)
		}
	</insert>

PLSQL

create or replace procedure getMember(
    p_id IN member.id%type,
    p_pwd IN member.pwd%type,
    p_name IN member.name%type,
    p_email IN member.email%type,
    p_phone IN member.phone%type
)
IS
BEGIN
    insert into member(id, pwd, name, email, phone)
    values(p_id, p_pwd, p_name, p_email, p_phone);
    commit;
END;

회원정보 수정 form

Controller

	@RequestMapping("/memberEditForm")
	public ModelAndView memberEditForm( 
			Model model, HttpServletRequest request) {
		ModelAndView mav = new ModelAndView();
		HttpSession session = request.getSession();
		HashMap<String,Object> loginUser 
			= (HashMap<String,Object>)session.getAttribute("loginUser");
		MemberVO dto = new MemberVO();
		
		dto.setId((String)loginUser.get("ID"));
		dto.setPwd((String)loginUser.get("PWD"));
		dto.setName((String)loginUser.get("NAME"));
		dto.setEmail((String)loginUser.get("EMAIL"));
		dto.setPhone((String)loginUser.get("PHONE"));
		
		mav.addObject("dto", dto);
		mav.setViewName("member/memberEditForm");
		return mav;
	}

회원정보 수정하기

Controller

	@RequestMapping(value="/memberEdit" , method=RequestMethod.POST)
	public String memberEdit(
		@ModelAttribute("dto") @Valid MemberVO membervo,
		BindingResult result,
		@RequestParam(value="pwd_check") String pwdchk,
		Model model,
		HttpServletRequest request
	) {
		String url = "member/memberEditForm";
		
		if( result.getFieldError("pwd")!=null)
			model.addAttribute("message", "비밀번호를 입력하세요" );
		else if( result.getFieldError("name")!=null)
			model.addAttribute("message", "이름을 입력하세요" );
		else if( !membervo.getPwd().equals(pwdchk)) 
			model.addAttribute("message","비밀번호 확인이 일치하시 않습니다.");
		else if( result.getFieldError("email")!=null)
			model.addAttribute("message", "이메일을 입력하세요" );
		else if( result.getFieldError("phone")!=null)
			model.addAttribute("message", "전화번호를 입력하세요" );
		
		else {
			HashMap<String,Object> mvo = new HashMap<String,Object>();
			mvo.put("ID",membervo.getId());
			mvo.put("PWD",membervo.getPwd());
			mvo.put("NAME",membervo.getName());
			mvo.put("EMAIL",membervo.getEmail());
			mvo.put("PHONE",membervo.getPhone());
			
			ms.updateMember( mvo );
			HttpSession session = request.getSession();
			session.setAttribute("loginUser", membervo);
			url = "redirect:/main"; 
		}
		return url;
	}

Service

	public void updateMember(HashMap<String, Object> mvo) {
		mdao.updateMember(mvo);
	}

DAO

	public void updateMember(HashMap<String, Object> mvo);

xml

	<update id="updateMember" statementType="CALLABLE" 
				parameterType="java.util.HashMap">
		{
		CALL updateMember( 
			#{ ID }, #{ PWD }, #{ NAME }, #{ EMAIL }, #{ PHONE }
			)
		}
	</update>

PL/SQL

create or replace procedure updateMember(
    p_id IN member.id%type,
    p_pwd IN member.pwd%type,
    p_name IN member.name%type,
    p_email IN member.email%type,
    p_phone IN member.phone%type
)

IS
BEGIN
    update member set pwd = p_pwd, name = p_name, 
    email = p_email, phone = p_phone where id = p_id;
    commit;
END;

게시글 목록

	@RequestMapping("/main")
	public ModelAndView goMain(HttpServletRequest request) {
		ModelAndView mav = new ModelAndView();
		
		HashMap<String,Object> paramMap = new HashMap<String,Object>();
		paramMap.put("ref_cursor", null);
		
		bs.selectBoard(paramMap);
		
		ArrayList<HashMap<String,Object>> list 
			= (ArrayList<HashMap<String,Object>>)paramMap.get("ref_cursor");

		mav.addObject("boardList", list );
		
		mav.setViewName("board/main");
		return mav;
	}

	public void selectBoard(HashMap<String, Object> paramMap) {
		bdao.selectBoard( paramMap );
	}

MemberDao.xml에 resultMap id=boardMap을 memberMap으로 변경

	<resultMap id="memberMap" type="java.util.HashMap"></resultMap>

BoardDao.xml

<?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.ecl.g15.dao.IBoardDao">
<resultMap id="boardMap" type="java.util.HashMap"></resultMap>

	<select id="selectBoard" statementType="CALLABLE" 
				parameterType="java.util.HashMap">
		{
		CALL selectBoard( 
			#{ ref_cursor, mode=OUT, jdbcType=CURSOR, 
				javaType=ResultSet, resultMap=boardMap } 
			)
		}
	</select>
	
</mapper>

PL/SQL

create or replace procedure selectBoard(
    p_curvar OUT SYS_REFCURSOR
)

IS
    temp_cur SYS_REFCURSOR;
BEGIN
    OPEN temp_cur FOR select * from board order by num desc;
    p_curvar := temp_cur;
END;

main.jsp에 추가

		<tr><th>번호</th><th>제목</th><th>작성자</th><th>작성일</th><th>조회</th></tr>
		<c:forEach var="board" items="${boardList}">
			<tr class="record">	<td align="center">${board.NUM }</td>
				<td><a href="boardView?num=${board.NUM}">${board.TITLE}</a></td>
				<td align="center">${board.ID}</td>
				<td align="center"><fmt:formatDate value="${board.WRITEDATE }" /></td>
				<td align="center">${board.READCOUNT}</td></tr>
		</c:forEach>

	@RequestMapping("/main")
	public ModelAndView goMain(HttpServletRequest request) {
		ModelAndView mav = new ModelAndView();
		
		HttpSession session = request.getSession();
		if( session.getAttribute("loginUser") == null)	
			mav.setViewName("loginform");
		else {
			int page = 1;
			if( request.getParameter("page") != null) {
				page = Integer.parseInt( request.getParameter("page") );
				session.setAttribute("page", page);
			}else if( session.getAttribute("page") != null) {
				page = (Integer)session.getAttribute("page");
			}else {
				session.removeAttribute("page");
			}
			
			HashMap<String,Object> paramMap = new HashMap<String,Object>();
			paramMap.put("paging", page );
			paramMap.put("ref_cursor", null);
		
			bs.selectBoard(paramMap);
			ArrayList<HashMap<String,Object>> list 
				= (ArrayList<HashMap<String,Object>>)paramMap.get("ref_cursor");
			mav.addObject("boardList", list );
			mav.addObject("paging", (Paging)paramMap.get("paging") );
			mav.setViewName("board/main");
		}
		return mav;
	}

	public void selectBoard(HashMap<String, Object> paramMap) {
		int page = (Integer) paramMap.get("page");
		Paging paging = new Paging();
		paging.setPage(page);
		paramMap.put("cnt", 0); // out 매개 변수에 전달된 변수를 해시맵에 추가
		bdao.getAllCount( paramMap ); 
		// 게시물 총 갯수를 카운트해서 cnt 항목에 담아 오는 메서드 호출
		
		int count = (Integer)paramMap.get("cnt");
		// 카운트되서 전달된 cnt 값 추출
		paging.setTotalCount(count); // paging에 적용
		paging.paging(); // paging() 메서드 호출 -> paging 객체 완성
		
		paramMap.put("startNum", paging.getStartNum());
		paramMap.put("endNum", paging.getEndNum());
		paramMap.put("paging", paging);
		
		bdao.selectBoard( paramMap );
	}

	public void getAllCount(HashMap<String, Object> paramMap);

	<select id="selectBoard" statementType="CALLABLE" 
				parameterType="java.util.HashMap">
		{
		CALL selectBoard( 
			#{startNum}, #{endNum},
			#{ ref_cursor, mode=OUT, jdbcType=CURSOR, 
				javaType=ResultSet, resultMap=boardMap } 
			)
		}
	</select>
	
	<select id="getAllCount" statementType="CALLABLE" 
				parameterType="java.util.HashMap">
		{
		CALL getAllCount( 
			#{ cnt, mode=OUT, jdbcType=INTEGER } 
			)
		}
	</select>

PL/SQL

create or replace procedure selectBoard(
    p_startNum IN NUMBER,
    p_endNum IN NUMBER,
    p_curvar OUT SYS_REFCURSOR
)

IS
    temp_cur SYS_REFCURSOR;
BEGIN
    OPEN temp_cur FOR 
    select * from(
    select * from(
        select rownum as rn, b.* from 
        ((select * from board order by num desc) b)
    ) where rn >= p_startNum
    ) where rn <= p_endNum;
    p_curvar := temp_cur;
END;

create or replace procedure getAllCount(
    p_cnt OUT NUMBER
)

IS
    v_cnt NUMBER;
BEGIN
    select count (*) into v_cnt FROM board;
    p_cnt := v_cnt;
END;

main.jsp에 추가

	<div id="paging">
		<c:if test="${paging.prev}">
			<a href="main?page=${paging.beginPage-1}">◀</a></c:if> 
		<c:forEach begin="${paging.beginPage}" end="${paging.endPage}" step="1" var="index">
		    <c:choose>
		        <c:when test="${paging.page==index}"> ${index} </c:when>
		        <c:otherwise><a href="main?page=${index}">${index}</a></c:otherwise>
		    </c:choose>
		</c:forEach>
		<c:if test="${paging.next}">
			<a href="main?page=${paging.endPage+1}">▶</a></c:if> 
	</div>

PL/SQL

create or replace procedure selectBoard(
    p_startNum IN NUMBER,
    p_endNum IN NUMBER,
    p_curvar OUT SYS_REFCURSOR
)

IS
    temp_cur SYS_REFCURSOR;
    v_rownum NUMBER;
    v_num NUMBER;
    v_cnt NUMBER;
BEGIN
    -- board 테이블에서 startNum과 endNum 사이의 게시물을 조회하되, 게시물 번호 (num) 값만 취함
    -- num 값으로 reply 테이블에서 boardnum이 num 인 레코드가 몇 개인지 갯수를 구함
    -- num 값과 댓글 갯수를 이용해서 board 테이블의 replycnt 필드를 update 함
   OPEN temp_cur FOR 
    select * from(
    select * from( 
        select rownum as rn, b.num from 
        ((select * from board order by num desc) b)
    ) where rn >= p_startNum 
    ) where rn <= p_endNum;  
    LOOP 
        FETCH temp_cur INTO v_rownum, v_num;
        EXIT WHEN temp_cur%NOTFOUND;
        select count(*) into v_cnt from reply where boardnum = v_num;
        update board set replycnt = v_cnt where num = v_num;
    END LOOP;
    commit;
END;

main.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>
<%@ taglib prefix="fmt" uri="http://java.sun.com/jsp/jstl/fmt"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>main.jsp</title>
<link rel="stylesheet" type="text/css" href="/css/board.css" >
</head>
<body>
	<div id="wrap" align="center">
	<h1>게시글 리스트</h1>
	<table class="list">
		<tr><td colspan="5" style="border: white; text-align: right">
			<div style="float:left;"> 
				${loginUser.NAME}(${loginUser.ID})님 로그인 
				<input type="button" value="정보수정" 
					onClick="location.href='memberEditForm'"/>
				<input type="button" value="로그아웃"	
					onClick="location.href='logout'"></div>
			<div style="float:right;">
			<a href="boardWriteForm">게시글 등록</a></div></td></tr>
		
		<tr><th>번호</th><th>제목</th><th>작성자</th><th>작성일</th><th>조회</th></tr>
		<c:forEach var="board" items="${boardList}">
			<tr class="record">	<td align="center">${board.NUM }</td>
				<td>
					<a href="boardView?num=${board.NUM}">${board.TITLE}</a></td>
				<c:if test="${board.REPLYCNT > 0}">
						&nbsp;<span style="color:red;font-weight:bold;">
                        [${board.REPLYCNT}] </span>
					</c:if>
				<td align="center">${board.ID}</td>
				<td align="center"><fmt:formatDate value="${board.WRITEDATE }" /></td>
				<td align="center">${board.READCOUNT}</td></tr>
		</c:forEach> 
	</table><br><br>
	
	<div id="paging">
		<c:if test="${paging.prev}">
			<a href="main?page=${paging.beginPage-1}">◀</a></c:if> 
		<c:forEach begin="${paging.beginPage}" end="${paging.endPage}" step="1" var="index">
		    <c:choose>
		        <c:when test="${paging.page==index}"> ${index} </c:when>
		        <c:otherwise><a href="main?page=${index}">${index}</a></c:otherwise>
		    </c:choose>
		</c:forEach>
		<c:if test="${paging.next}">
			<a href="main?page=${paging.endPage+1}">▶</a></c:if> 
	</div>
</div>
</body>
</html>

게시글 상세보기

Controller

	@RequestMapping("/boardView")
	public ModelAndView boardView( 
			@RequestParam("num") int num,  
			HttpServletRequest request) {
		
		ModelAndView mav = new ModelAndView();
		
		HashMap<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("num", num);
		paramMap.put("ref_cursor1", null);
		bs.boardView(paramMap);	
		// 조회수 증가, 게시물 조회 
		
		ArrayList<HashMap<String,Object>> list 
		= (ArrayList<HashMap<String,Object>>)paramMap.get("ref_cursor1");

		mav.addObject("board" , list.get(0));
		mav.setViewName("board/boardView");		
		return mav;
	}

	public void boardView(HashMap<String, Object> paramMap) {
		// 조회수 증가
		bdao.plusOneReadCount(paramMap);
		// 게시물 조회
		bdao.getBoard(paramMap);
	}

	public void plusOneReadCount(HashMap<String, Object> paramMap);

	public void getBoard(HashMap<String, Object> paramMap);

	<update id="plusOneReadCount" statementType="CALLABLE" 
				parameterType="java.util.HashMap">
		{
			CALL plusOneReadCount( #{ num } )
		}
	</update>
	
	<select id="getBoard" statementType="CALLABLE" 
				parameterType="java.util.HashMap">
		{
			CALL getBoard( 
				#{ num },
				#{  ref_cursor1, mode=OUT, jdbcType=CURSOR, 
					javaType=ResultSet, resultMap=boardMap } 
				#{  ref_cursor2, mode=OUT, jdbcType=CURSOR, 
					javaType=ResultSet, resultMap=resultMap } 
				)
		}
	</select>

SQL

create or replace procedure plusOneReadCount(
    p_num IN board.num%type
)
IS
BEGIN
    update board set readcount = readcount + 1
    where num = p_num;
    commit;
END;

create or replace procedure getBoard(
    p_num IN board.num%type,
    p_cur1 OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN p_cur1 FOR
    select * from board where num = p_num;
END;

@RequestMapping("/boardWriteForm")
	public String write_form(HttpServletRequest request) {
		
		String url = "board/boardWriteForm";
		
		HttpSession session = request.getSession();
		if( session.getAttribute("loginUser") == null)	
			url="member/loginForm";		
		return url;
	}
	
	@RequestMapping("/selectimg")
	public String selectimg() {
		return "board/selectimg";
	}
	
	@Autowired
	ServletContext context;
	@RequestMapping(value="/fileupload" , method = RequestMethod.POST)
	public String fileupload(Model model, HttpServletRequest request) {
		String path = context.getRealPath("/upload");
		try {
			MultipartRequest multi = new MultipartRequest(
					request, path, 5*1024*1024, "UTF-8", new DefaultFileRenamePolicy()
			);
			model.addAttribute("image", multi.getFilesystemName("image") );
		} catch (IOException e) {  e.printStackTrace();
		}
		return "board/completupload";
	}
	
	@RequestMapping( value="/boardWrite", method=RequestMethod.POST)
	public String boardWrite(
			@ModelAttribute("dto") @Valid BoardVO boardvo,
			BindingResult result,
			Model model,
			HttpServletRequest request) {
		String url = "board/boardWriteForm";
		if( result.getFieldError("pass") != null ) 
			model.addAttribute("message", result.getFieldError("pass").getDefaultMessage());
		else if( result.getFieldError("title")!=null)
			model.addAttribute("message", result.getFieldError("title").getDefaultMessage());
		else if( result.getFieldError("content")!=null)
			model.addAttribute("message", 
                 result.getFieldError("content").getDefaultMessage());
		else {
			HashMap<String, Object> paramMap = new HashMap<String, Object>();
			paramMap.put("userid", boardvo.getUserid());
			paramMap.put("pass",  boardvo.getPass());
			paramMap.put("content",  boardvo.getPass());
			paramMap.put("title",  boardvo.getPass());
			paramMap.put("email",  boardvo.getPass());
			paramMap.put("imgfilenme",  boardvo.getPass());
			
			bs.insertBoard(boardvo);
			url = "redirect:/main";
		}
		return url;
	}

	public void insertBoard(BoardVO boardvo) {
		bdao.insertBoard(boardvo);
	}

	public void insertBoard(BoardVO boardvo);

xml

	<select id="insertBoard" statementType="CALLABLE" 
				parameterType="java.util.HashMap">
		{
			CALL insertBoard( 
				#{ userid }, #{ pass }, #{ email }, #{ content }, #{ imgfilename }
				)
		}
	</select>

SQL

create or replace procedure insertBoard(
    p_userid IN board.USERID%type,
    p_pass IN board.pass%type,
    p_email IN board.email%type,
    p_title IN board.title%type,
    p_content IN board.content%type,
    p_imgfilename IN board.imgfilename%type
)
IS
BEGIN
    insert into board( num, pass, userid, email, title, content, imgfilename)
    values ( board_seq.nextVal, p_pass, p_userid, p_email, p_title, p_content, p_imgfilename);
    commit;
END;

게시글 수정 form

	@RequestMapping("/boardEditForm")
	public String board_edit_form(
			@RequestParam("num") String num,
			Model model, HttpServletRequest request) {
		model.addAttribute("num", num);
		return "board/boardCheckPassForm";
	}
	@RequestMapping("/boardEdit")
	public String board_edit(
			@RequestParam("num") int num,
			@RequestParam("pass") String pass, 
			Model model, HttpServletRequest request) {

		HashMap<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("num", num);
		paramMap.put("ref_cursor", null);
		bs.getBoard(paramMap);
		
		ArrayList<HashMap<String,Object>> list 
		= (ArrayList<HashMap<String,Object>>)paramMap.get("ref_cursor");

		HashMap<String, Object> bvo = list.get(0);
		
		if( pass.equals(bvo.get("PASS")) )
			return "board/boardCheckPass";
		else {
			model.addAttribute("message", "비밀번호가 맞지 않습니다. 확인해주세요");
			return "board/boardCheckPassForm";
		}
	}

	public void getBoard(HashMap<String, Object> paramMap) {
		bdao.getBoard(paramMap);
	}

	@RequestMapping(value="/boardUpdate", method = RequestMethod.POST)
	public String boardUpdate( 
			@ModelAttribute("dto") @Valid BoardVO boardvo,
			BindingResult result, 
			@RequestParam("oldfilename") String oldfilename, 
			HttpServletRequest request, Model model) {
		
		String url = "board/boardEditForm";
		if( result.getFieldError("pass")!=null) 
			model.addAttribute("message" , "비밀번호는 게시물 수정 삭제시 필요합니다");
		else  if(result.getFieldError("title")!=null) 
			model.addAttribute("message" , "제목은 필수입력 사항입니다");
		else if(result.getFieldError("content")!=null) 
			model.addAttribute("message" , "게시물 내용은 비워둘수 없습니다.");
		else {
			if( boardvo.getImgfilename()==null || boardvo.getImgfilename().equals("") )	
				boardvo.setImgfilename(oldfilename);
			HashMap<String, Object> paramMap = new HashMap<String, Object>();
			paramMap.put("num", boardvo.getNum());
			paramMap.put("userid", boardvo.getUserid());
			paramMap.put("pass",  boardvo.getPass());
			paramMap.put("content",  boardvo.getPass());
			paramMap.put("title",  boardvo.getTitle());
			paramMap.put("email",  boardvo.getEmail());
			paramMap.put("imgfilenme",  boardvo.getImgfilename());
			bs.updateBoard( boardvo );
			url = "redirect:/boardViewWithoutCount?num=" + boardvo.getNum();
		}	
		return url;
	}

	public void updateBoard(BoardVO boardvo) {
		bdao.updateBoard(boardvo);
	}

	public void updateBoard(BoardVO boardvo);

	<update id="updateBoard" statementType="CALLABLE" 
				parameterType="java.util.HashMap">
		{
			CALL updateBoard(
				#{num}, #{userid }, #{ pass }, #{ email }, #{ content }, #{ imgfilename }
			)
		}
	</update>

create or replace procedure updateBoard(
    p_num IN board.num%type,
    p_userid IN board.USERID%type,
    p_pass IN board.pass%type,
    p_email IN board.email%type,
    p_title IN board.title%type,
    p_content IN board.content%type,
    p_imgfilename IN board.imgfilename%type
)
IS
BEGIN
    update board set pass=p_pass, userid=p_userid, email=p_email, 
    title=p_title, content=p_content, imgfilename=p_imgfilename
    where num=p_num;
    commit;
END;

	@RequestMapping("/boardViewWithoutCount")
	public ModelAndView boardViewWithoutCount( 
			@RequestParam("num") int num,  
			HttpServletRequest request) {
		
		ModelAndView mav = new ModelAndView();
		HashMap<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("num", num);
		paramMap.put("ref_cursor1", null);
		paramMap.put("ref_cursor2", null);
		bs.boardViewWithoutCount(paramMap);	
		// 조회수 증가, 게시물 조회 
		
		ArrayList<HashMap<String,Object>> list1
		= (ArrayList<HashMap<String,Object>>)paramMap.get("ref_cursor1");
		ArrayList<HashMap<String,Object>> list2
		= (ArrayList<HashMap<String,Object>>)paramMap.get("ref_cursor2");

		mav.addObject("board" , list1.get(0));
		mav.addObject("replyList" , list2);
		mav.setViewName("board/boardView");		
		return mav;
	}

	public void boardViewWithoutCount(HashMap<String, Object> paramMap) {
		bdao.getBoard(paramMap);
	}

저작자표시 비영리 변경금지

'Spring > Boot' 카테고리의 다른 글

[Boot] Transaction (0)	2022.09.10
[Boot] MyBatis (0)	2022.09.10
[Boot] JDBC (0)	2022.09.10
[Boot] Validation (0)	2022.09.10
[Boot] Lombok (0)	2022.09.10

ABOUT ME

im hippo im hippo

'Spring > Boot' 카테고리의 다른 글

티스토리툴바

ABOUT ME

'Spring > Boot' 카테고리의 다른 글

관련글 관련글 더보기

티스토리툴바