2022-03-22 스프링부트 : 프로시저를 이용한 게시판#2

 

회원가입 - 아이디체크

 

 

MemberController

 

@RequestMapping("/idcheck")
public ModelAndView idcheck(@RequestParam("userid") String userid) {

    ModelAndView mav = new ModelAndView();

    HashMap<String, Object> paramMap = new HashMap<>();
    paramMap.put("userid", userid);
    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("userid", userid);
    mav.setViewName("member/idcheck");

    return mav;
}

 

 

-> getMember 메소드는 로그인 할 때 만들었음

 

---

 

회원가입 - insert

 

 

MemberController

 

@RequestMapping(value="/memberJoin", method=RequestMethod.POST) 
public ModelAndView memberJoin(@ModelAttribute("dto") @Valid MemberVO membervo, 
        BindingResult result,
        Model model, @RequestParam("re_id") String re_id,
        @RequestParam("pw_check") String pw_check) {

    ModelAndView mav = new ModelAndView();
    mav.setViewName("member/memberJoinForm");
    if(re_id != null || re_id.equals("") || !re_id.equals(membervo.getUserid())) 
         mav.addObject("re_id", re_id);

    if(result.getFieldError("userid") != null) 
        mav.addObject("message", result.getFieldError("userid").getDefaultMessage());
     else if(result.getFieldError("pwd") != null) 
         mav.addObject("message", "비밀번호를 입력하세요");
     else if(result.getFieldError("name") != null) 
         mav.addObject("message", "이름을 입력하세요");
     else if(result.getFieldError("email") != null) 
         mav.addObject("message", "이메일을 입력하세요");
     else if(result.getFieldError("phone") != null) 
         mav.addObject("message", "전화번호를 입력하세요");
     else if(!pw_check.equals(membervo.getPwd())) 
         mav.addObject("message", "비밀번호와 비밀번호 확인이 일치하지 않습니다.");
     else if (!membervo.getUserid().equals(re_id)) 
         mav.addObject("message", "아이디 중복체크를 해주세요");
     else {
        HashMap<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("userid", membervo.getUserid());
        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;
}

 

 

MemberDao

 

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

 

 

프로시저

 

create or replace PROCEDURE insertMember(
    p_userid IN member2.userid%type,
    p_pwd IN member2.pwd%type,
    p_name IN member2.name%type,
    p_email IN member2.email%type,
    p_phone IN member2.phone%type
)
IS
BEGIN
    insert into member2(userid, pwd, name, email, phone)
    values (p_userid, p_pwd, p_name, p_email, p_phone);
    commit;
END;

 

---

 

 

회원가입 - 회원정보 수정(Update)

 

 

MemberController

 

@RequestMapping("/memberEditForm")
public ModelAndView mem_edit_form(Model model, HttpServletRequest request) {

    HttpSession session = request.getSession();
    ModelAndView mav = new ModelAndView();
    HashMap<String, Object> loginUser 
        = (HashMap<String, Object>) session.getAttribute("loginUser");
    MemberVO dto = new MemberVO();
    dto.setUserid((String)loginUser.get("USERID"));
    dto.setPwd((String)loginUser.get("PWD"));
    dto.setEmail((String)loginUser.get("EMAIL"));
    dto.setPhone((String)loginUser.get("PHONE"));
    dto.setName((String)loginUser.get("NAME"));

    mav.addObject("dto", dto);
    mav.setViewName("member/memberEditForm");
    return mav;
}

@RequestMapping("memberEdit")
public String memberEdit(@ModelAttribute("dto") @Valid MemberVO membervo, 
        BindingResult result, @RequestParam("pw_check") String pw_check, 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(result.getFieldError("email") != null) 
         model.addAttribute("message", "이메일 입력하세요");
     else if(result.getFieldError("phone") != null) 
         model.addAttribute("message", "전화번호를 입력하세요");
     else if(!pw_check.equals(membervo.getPwd())) 
         model.addAttribute("message", "비밀번호와 비밀번호 확인이 일치하지 않습니다.");
     else {
        HashMap<String, Object> mvo = new HashMap<>();
        mvo.put("USERID", membervo.getUserid());
        mvo.put("PWD", membervo.getPwd());
        mvo.put("NAME", membervo.getName());
        mvo.put("PHONE", membervo.getPhone());
        mvo.put("EMAIL", membervo.getEmail());
        ms.updateMember(mvo);

        HttpSession session = request.getSession();
        session.setAttribute("loginUser", mvo);
        url = "redirect:/main";
    }
    return url;
}

-> Validation 을 위해 MemberVO 사용

 

 

MemberDao

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

 

 

프로시저

create or replace PROCEDURE updateMember(
    p_userid IN member2.userid%type,
    p_pwd IN member2.pwd%type,
    p_name IN member2.name%type,
    p_email IN member2.email%type,
    p_phone IN member2.phone%type
)
IS
BEGIN
    update member2 set pwd=p_pwd, name=p_name,
    email=p_email, phone=p_phone where userid=p_userid;
    commit;
END;

 

---

 

 

게시물 상세보기

 

 

BoardController

 

@RequestMapping("/boardView")
 public ModelAndView board_view(@RequestParam("num") int num, HttpServletRequest request) {
    ModelAndView mav = new ModelAndView();

    HashMap<String, Object> paramMap = new HashMap<>();
    paramMap.put("num", num);
    paramMap.put("ref_cursor1", null);
    paramMap.put("ref_cursor2", null);

    bs.boardView(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");

    mav.setViewName("board/boardView");
    return mav;
}

 

 

BoardDao

 

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

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

 

- ref_cursor1이 resultMap으로 boardMap을 사용 => boardMap의 구조가 board테이블을 따라감

--> 이 boardMap을 ref_cursor2에서 그대로 사용하게되면 sql 에러가 발생하게됨 (부적합한 열)

---> 새로운 resultMap을 만들어 사용할것

 

 

프로시저

 

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


create or replace PROCEDURE boardView(
    p_num IN board.num%type,
    p_boardcur OUT SYS_REFCURSOR,
    p_replycur OUT SYS_REFCURSOR 
)
IS
BEGIN
    open p_boardcur for 
        select * from board where num=p_num order by num desc;
    open p_replycur for
        select * from reply2 where boardnum=p_num order by num desc;
END;

 

 

※boardView.jsp 의 el 태그 안 필드명을 대문자로 바꾸어 주어야한다 (해쉬맵으로 전달되는 경우)

 

 

---

 

 

댓글작성 / 삭제

 

BoardController

 

@RequestMapping("/addReply")
public String addReply(
        @RequestParam("boardnum") int boardnum, 
        @RequestParam("userid") String userid, @RequestParam("content") String content,
        HttpServletRequest request) {


    HashMap<String, Object> paramMap = new HashMap<String, Object>();
    paramMap.put("userid", userid);
    paramMap.put("boardnum", boardnum);
    paramMap.put("content", content);

    bs.insertReply(paramMap);

    return "redirect:/boardViewWithoutCount?num="+boardnum;
}


@RequestMapping("/boardViewWithoutCount")
 public ModelAndView board_view_without_count(
         @RequestParam("num") int num, HttpServletRequest request) {
    ModelAndView mav = new ModelAndView();

    HashMap<String, Object> paramMap = new HashMap<>();
    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");

    mav.setViewName("board/boardView");
    return mav;
}

@RequestMapping("/deleteReply")
public String reply_delete(@RequestParam("num") int num, 
        @RequestParam("boardnum") int boardnum,
        HttpServletRequest reqeust) {
    HashMap<String, Object> paramMap = new HashMap<String, Object>();
    paramMap.put("num", num);
    bs.deleteReply(paramMap);
    return "redirect:/boardViewWithoutCount?num="+boardnum;
}

 

 

BoardDao

 

<insert id="insertReply" statementType="CALLABLE" parameterType="java.util.HashMap">
    {CALL insertReply( #{boardnum}, #{userid}, #{content} )}
</insert>

<delete id="deleteReply" statementType="CALLABLE" parameterType="java.util.HashMap">
    {CALL deleteReply(#{num})}
</delete>

 

 

프로시저

 

create or replace PROCEDURE insertReply(
    p_boardnum IN reply2.boardnum%type,
    p_userid IN reply2.userid%type,
    p_content IN reply2.content%type
)
IS
BEGIN
    INSERT into reply2 (num, boardnum, userid, content) 
    values (reply_seq.nextval, p_boardnum, p_userid, p_content);
    commit;
END;

create or replace PROCEDURE deleteReply(
    p_num IN reply2.num%type
)
IS
BEGIN
    delete from reply2 where num=p_num;
    commit;
END;

 

---