프로시저를 이용한 댓글 구현(코드만 정리)

--댓글 추가
CREATE OR REPLACE PROCEDURE replyInsert (
  pCno spring_reply2.cno%TYPE,
  pType spring_reply2.type%TYPE,
  pId spring_reply2.id%TYPE,
  pName spring_reply2.name%TYPE,
  pMsg spring_reply2.msg%TYPE
)
IS 
BEGIN
  INSERT INTO spring_reply2 VALUES(
    (SELECT NVL(MAX(no)+1,1) FROM spring_reply2),
    pCno, pType, pId, pName, pMsg, SYSDATE
  );
  COMMIT;
END;
/

--댓글 읽기
CREATE OR REPLACE PROCEDURE replyListData(
  pCno spring_reply2.cno%TYPE,
  pType spring_reply2.type%TYPE,
  pResult OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN pResult FOR
    SELECT no, cno, type, id, name, msg, TO_CHAR(regdate, 'YYYY-MM-DD HH24:MI:SS')
      FROM spring_reply2
      WHERE cno=pCno AND type=pType
      ORDER BY no DESC;
END;
/

--댓글 수정
CREATE OR REPLACE PROCEDURE replyUpdate(
  pNo spring_reply2.no%TYPE,
  pMsg spring_reply2.msg%TYPE
)
IS
BEGIN
  UPDATE spring_reply2 SET
  msg = pMsg
  WHERE no=pNo;
  commit;
END;
/

--댓글 삭제
CREATE OR REPLACE PROCEDURE replyDelete(
  pNo spring_reply2.no%TYPE
)
IS
BEGIN
  DELETE FROM spring_reply2 
  WHERE no=pNo;
  COMMIT;
END;
/

 

package doo.doo.dao;

import java.util.*;
import doo.doo.vo.*;
import oracle.jdbc.OracleTypes;

import java.sql.*;
import org.springframework.stereotype.Repository;

@Repository
public class ReplyDAO {
  private Connection conn;
  private CallableStatement cs; //프로시저 호출할 때 씀
  private final String URL = "jdbc:oracle:thin:@localhost:1521:XE";

  public ReplyDAO() {
    try {
      Class.forName("oracle.jdbc.driver.OracleDriver");
    } catch (Exception ex) {}
  }

  public void getConnection() {
    try {
      conn = DriverManager.getConnection(URL, "user", "pwd");
    } catch (Exception ex) {}
  }

  public void disConnection() {
    try {
      if (cs != null) cs.close();
      if (conn != null) conn.close();
    } catch (Exception ex) {}
  }

  //댓글 올리기
  /*CREATE OR REPLACE PROCEDURE replyInsert (
      pCno spring_reply2.cno%TYPE,
      pType spring_reply2.type%TYPE,
      pId spring_reply2.id%TYPE,
      pName spring_reply2.name%TYPE,
      pMsg spring_reply2.msg%TYPE
  )*/
  public void replyInsert(ReplyVO vo) {
    try {
      getConnection();
      String sql = "{CALL replyInsert(?,?,?,?,?)}";
      cs = conn.prepareCall(sql);
      cs.setInt(1, vo.getCno());
      cs.setInt(2, vo.getType());
      cs.setString(3, vo.getId());
      cs.setString(4, vo.getName());
      cs.setString(5, vo.getMsg());
      cs.executeQuery();
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      disConnection();
    }
  }
  //댓글 읽기
  /*CREATE OR REPLACE PROCEDURE replyListData(
      pCno spring_reply2.cno%TYPE,
      pType spring_reply2.type%TYPE,
      pResult OUT SYS_REFCURSOR
  )*/
  public List < ReplyVO > replyListData(ReplyVO vo) {
    List < ReplyVO > list = new ArrayList < ReplyVO > ();
    try {
      getConnection();
      String sql = "{CALL replyListData(?,?,?)}";
      cs = conn.prepareCall(sql);
      cs.setInt(1, vo.getCno());
      cs.setInt(2, vo.getType());
      cs.registerOutParameter(3, OracleTypes.CURSOR); //결과값을 담아줌
      cs.executeQuery();
      ResultSet rs = (ResultSet) cs.getObject(3);
      while (rs.next()) {
        ReplyVO rvo = new ReplyVO();
        rvo.setNo(rs.getInt(1));
        rvo.setCno(rs.getInt(2));
        rvo.setType(rs.getInt(3));
        rvo.setId(rs.getString(4));
        rvo.setName(rs.getString(5));
        rvo.setMsg(rs.getString(6));
        rvo.setDbday(rs.getString(7));
        list.add(rvo);
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      disConnection();
    }
    return list;
  }

  public void replyDelete(int no) {
    try {
      getConnection();
      String sql = "{CALL replyDelete(?)}";
      cs = conn.prepareCall(sql);
      cs.setInt(1, no);
      cs.executeQuery();
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      disConnection();
    }
  }

  public void replyUpdate(ReplyVO vo) {
    try {
      getConnection();
      String sql = "{CALL replyUpdate(?,?)}";
      cs = conn.prepareCall(sql);
      cs.setInt(1, vo.getNo());
      cs.setString(2, vo.getMsg());
      cs.executeQuery();
    } catch (Exception ex) {
      ex.printStackTrace();
    } finally {
      disConnection();
    }
  }
}

 

Controller

package doo.doo.web;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import doo.doo.dao.ReplyDAO;
import doo.doo.vo.ReplyVO;
import java.util.*;

import javax.servlet.http.HttpSession;

@Controller
public class ReplyController {
  @Autowired
  private ReplyDAO dao;

  @PostMapping("reply/insert.do")
  public String reply_insert(ReplyVO vo, HttpSession session, RedirectAttributes ra) {
    String id = (String) session.getAttribute("id");
    String name = (String) session.getAttribute("name");
    vo.setId(id);
    vo.setName(name);
    dao.replyInsert(vo);
    //RedirectAttributes : redirect하면서 데이터를 넘길 때 사용함
    ra.addAttribute("tab", vo.getType());
    ra.addAttribute("no", vo.getCno());
    return "redirect:../seoul/detail.do";
    //		return "redirect:../seoul/detail.do?tab="+vo.getType()+"&no="+vo.getCno();
  }

  @GetMapping("reply/delete.do")
  public String reply_delete(ReplyVO vo, RedirectAttributes ra) {
    dao.replyDelete(vo.getNo());
    ra.addAttribute("tab", vo.getType());
    ra.addAttribute("no", vo.getCno());
    return "redirect:../seoul/detail.do";
  }

  @PostMapping("reply/update.do")
  public String reply_update(ReplyVO vo, RedirectAttributes ra) {
    dao.replyUpdate(vo);
    ra.addAttribute("tab", vo.getType());
    ra.addAttribute("no", vo.getCno());
    return "redirect:../seoul/detail.do";
  }

}

 

RedirectAttributes 객체

package doo.doo.web;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.servlet.mvc.support.RedirectAttributes;

import doo.doo.dao.ReplyDAO;
import doo.doo.vo.ReplyVO;
import java.util.*;

import javax.servlet.http.HttpSession;

@Controller
public class ReplyController {
  @Autowired
  private ReplyDAO dao;

  @PostMapping("reply/insert.do")
  public String reply_insert(ReplyVO vo, HttpSession session, RedirectAttributes ra) {
    //RedirectAttributes : redirect하면서 데이터를 넘길 때 사용함
    ra.addAttribute("tab", vo.getType());
    ra.addAttribute("no", vo.getCno());
    return "redirect:../seoul/detail.do";
  }
}

return "redirect:../seoul/detail.do?tab=" + vo.getType() + "&no=" + vo.getCno();