국비 교육 50일차 - PL/SQL 활용 게시판 만들기, CSS

오라클에서 PL/SQL로 게시판에 필요한 함수를 만들어주고, 그걸 자바로 연동해서 게시판 기능을 만드는 연습을 해봤다. 

우선 오라클에서 작성해둔 프로시저 목록이다 

 

 

우선 목록을 전부 출력해주는 함수부터 

CREATE OR REPLACE PROCEDURE board_list(
    pStart freeboard.no%TYPE,
    pEnd freeboard.no%TYPE,
    pResult OUT SYS_REFCURSOR
)
IS
  -- 변수 선언
BEGIN
  -- 구현 
  OPEN pResult FOR
    SELECT no,subject,name,regdate,hit,num 
    FROM (SELECT no,subject,name,regdate,hit,rownum as num
    FROM (SELECT no,subject,name,regdate,hit
    FROM freeboard ORDER BY no DESC))
    WHERE num BETWEEN pStart AND pEnd;
END;
/

 페이지 단위로 목록을 출력하기 때문에 start페이지와 end페이지가 있는 변수를 선언해줬다. 이 값을 입력받아야 내용을 출력할 수 있기 때문이다. 

 pResult가 out변수이기 때문에 여기에 값을 전부 넣어준다. begin안에는 sql문장을 써준다. 써주는 sql문장은 기존에 자바에서 써주던것과 똑같은 형식이다. 

 

-- 총페이지 (MVC,Mybatis)
CREATE OR REPLACE PROCEDURE board_totalpage(
   pTotal OUT NUMBER
)
IS
BEGIN
   SELECT CEIL(COUNT(*)/10.0) INTO pTotal
   FROM freeboard;
END;
/

  pTotal에 총페이지를 계산해서 넣어준뒤 결과값으로 out해주는 함수다. 

 

 

CREATE OR REPLACE PROCEDURE board_detail(
   pNo freeboard.no%TYPE,
   pResult OUT SYS_REFCURSOR
)
IS
BEGIN
  -- 조회수 증가 
  UPDATE freeboard SET
  hit=hit+1
  WHERE no=pNo; -- ?
  COMMIT;
  
  OPEN pResult FOR
    SELECT no,name,subject,content,regdate,hit,like1
    FROM freeboard
    WHERE no=pNo;
END;
/

 디테일 정보를 출력해주는 함수. 조회수를 먼저 증가 시켜준 뒤에 다시 pResult에 값을 담아준다. 상세정보를 보려면 상세정보를 볼 게시글의 번호를 입력 받아야 하기 때문에 pNo를 설정해준다.

 

CREATE OR REPLACE PROCEDURE board_insert(
   pName freeboard.name%TYPE,
   pSubject freeboard.subject%TYPE,
   pContent freeboard.content%TYPE,
   pPwd freeboard.pwd%TYPE
)
IS
BEGIN
   INSERT INTO freeboard VALUES(
     (SELECT NVL(MAX(no)+1,1) FROM freeboard),
     pName,pSubject,pContent,pPwd,SYSDATE,0,0
   );
   COMMIT;
END;
/

 게시글을 새로 넣어주는 프로시저. out해줄 값은 따로 없고 사용자한테서 이름, 제목, 내용, 비밀번호를 받아서 전체 게시판 목록에 insert로 넣어주면 된다. 이때 게시글 번호는 가장 마지막 번호에서 자동으로 1이 증가된것으로 써주면 된다. 

 

-- 데이터 수정 : 비밀번호 
CREATE OR REPLACE PROCEDURE board_updateData(
   pNo freeboard.no%TYPE,
   pResult OUT SYS_REFCURSOR
)
IS
BEGIN 
  -- 구현 데이터 추가 , 수정 , 데이터 읽기 
  OPEN pResult FOR
    SELECT no,name,subject,content
    FROM freeboard
    WHERE no=pNo;
END;
/

  이거는 수정 전에 수정할 내용을 미리 띄워주는 프로시저. 비밀번호는 바꿀 수 없기 때문에 불러오지 않는다. 

 

-- 실제로 수정 
CREATE OR REPLACE PROCEDURE board_update(
   pNo freeboard.no%TYPE,
   pName freeboard.name%TYPE,
   pSubject freeboard.subject%TYPE,
   pContent freeboard.content%TYPE,
   pPwd freeboard.pwd%TYPE,
   pResult OUT freeboard.name%TYPE --비밀번호 확인
)
IS
   db_pwd freeboard.pwd%TYPE;
BEGIN
   -- 게시물 번호에 해당하는 비밀번호를 받아 온다 
   SELECT pwd INTO db_pwd
   FROM freeboard
   WHERE no=pNo;
   
   -- 검색
   IF (pPwd=db_pwd) THEN
     pResult:='true'; -- 변수에 값을 대입시 :=
     -- 수정 
     UPDATE freeboard SET
     name=pName,subject=pSubject,content=pContent
     WHERE no=pNo;
     COMMIT;
   ELSE
     pResult:='false';
   END IF;
   
END;
/

 비밀번호가 맞는지 아닌지 확인을 하고 비밀번호를 바꿔줘야 하기 때문에 맞는지 아닌지 확인해주는 변수를 하나 설정한다. 그리고 db에 저장되어 있는 비밀번호를 받아오는 db_pwd를 하나 선언해준다. 

 

 select를 통해서 pwd를 db_pwd에 저장을 해준뒤, 사용자로부터 입력받은 pPwd와 비교를 해준다. 

만약 두개가 일치하면 pResult에 true를 입력하고 내용을 수정해주는 update문을 작성해준다. 꼭 commit까지 날려줘야 한다. 

 

CREATE OR REPLACE PROCEDURE board_delete(
   pNo freeboard.no%TYPE,
   pPwd freeboard.pwd%TYPE,
   pResult OUT freeboard.name%TYPE
)
IS
  db_pwd freeboard.pwd%TYPE;
BEGIN
  -- 비밀번호 읽기 
  SELECT pwd INTO db_pwd
  FROM freeboard
  WHERE no=pNo;
  
  -- 비밀번호 체크 
  IF (db_pwd=pPwd) THEN
    pResult:='true';
    -- 실제 삭제
    DELETE FROM freeboard
    WHERE no=pNo;
    COMMIT;
  ELSE
    pResult:='false';
  END IF;
END;
/

 삭제도 수정과 똑같다. 대신 update문이 delete로 바뀌었다는 것만 다르다. 

 

-- 데이터 찾기 
CREATE OR REPLACE PROCEDURE board_find(
   pSearch freeboard.subject%TYPE,
   pField freeboard.name%TYPE,
   pResult OUT SYS_REFCURSOR
)
IS
BEGIN
   OPEN pResult FOR
     SELECT no,name,subject,content,regdate,hit
     FROM freeboard
     WHERE pField LIKE '%'||pSearch||'%';
END;
/

 *****데이터를 찾기 위해서는 

-> 여기가 잘 이해가 안간다. 다시 질문해서 알아 두도록 해야겠다. 

 

이렇게 오라클에서 전부 만들어 줬으면 자바로 가서 dao파일을 완성해 준다. 오라클연결-해제 메소드까지는 늘 항상 똑같기 때문에 생략하고 기능 부분부터 적도록 하겠다. 

 

// 7-1 . 목록 출력 
	/*
	 *      CREATE OR REPLACE PROCEDURE board_list(
			    pStart freeboard.no%TYPE,
			    pEnd freeboard.no%TYPE,
			    pResult OUT SYS_REFCURSOR
			)
	 */
	 // BoardVO(한개의 게시물) => 게시물 여러개를 모아서 브라우저로 전송 
	 public ArrayList<BoardVO> board_list(int page)
	 {
		 ArrayList<BoardVO> list=new ArrayList<BoardVO>();
		 try
		 {
			 // 1. 연결
			 getConnection();
			 // 2. SQL문장을 만든다 
			 String sql="{CALL board_list(?,?,?)}";
			 cs=conn.prepareCall(sql);
			 // ?에 값을 채운후에 실행 
			 int rowSize=10;
			 int start=(rowSize*page)-(rowSize-1);
			 int end=rowSize*page;
			 /*
			  *   1pgae 1 - 10
			  *   2page 11 - 20
			  */
			 cs.setInt(1, start);
			 cs.setInt(2, end);
			 cs.registerOutParameter(3, OracleTypes.CURSOR);
			 //   => 오라클의 데이터형을 설정 
			 /*
			  *   SYS_REFCURSOR => OracleTypes.CURSOR
			  *   VARCHAR2      => OracleTypes.VARCHAR
			  *   NUMBER        => OracleTypes.INTEGER
			  */
			 // 실행 요청 
			 cs.executeUpdate(); // procedure => INSERT,UPDATE,DELETE,SELECT 
			                     // cs.executeUpdate() : 함수 호출 
			 ResultSet rs=(ResultSet)cs.getObject(3);// 저장된 데이터를 받는다 
			 /*
			  *     cs.getString(index)
			  *     cs.getInt()
			  *     cs.getDate()
			  *     CURSOR => cs.getObject() => 자바에서 존재하는 데이터형이 아니다 
			  */
			 while(rs.next())
			 {
				 // no,subject,name,regdate,hit
				 BoardVO vo=new BoardVO();
				 vo.setNo(rs.getInt(1));
				 vo.setSubject(rs.getString(2));
				 vo.setName(rs.getString(3));
				 vo.setRegdate(rs.getDate(4));
				 vo.setHit(rs.getInt(5));
				 
				 list.add(vo);
			 }
			 rs.close();
			 
		 }catch(Exception ex)
		 {
			 // 오류 처리
			 ex.printStackTrace();
		 }
		 finally
		 {
			 disConnection();
		 }
		 return list;
	 }

 기존에 자바에서 모든 메소드를 받아오던 코드와 다른 점은, 프로시저에서 설정해준 out변수를 registerOutParameter로 값을 변경해와서 받아온다는 점이다. 

그 값을 ResultSet변수에 넣어줘서 그 다음 while문을 돌려 우리가 아는 리스트 값을 출력해주는 메소드를 완성해준다. 

 

그리고 전부 출력해주기 위해서는 총페이지를 계산해줘야 하기 때문에 총페이지를 가지고 오는 메소드 역시 만들어 준다. 

 

// 7-2. 총페이지 가지고 오기 
	 /*
	  *   CREATE OR REPLACE PROCEDURE board_totalpage(
              pTotal OUT NUMBER
          )
	  */
	 public int board_totalpage()
	 {
		  int total=0;
		  try
		  {
			  // 1. 연결
			  getConnection();
			  // 2. SQL문장 
			  String sql="{CALL board_totalpage(?)}";
			  cs=conn.prepareCall(sql);
			  // ?에 값을 채운다 
			  cs.registerOutParameter(1, OracleTypes.INTEGER);
			  // 실행
			  cs.executeUpdate();
			  total=cs.getInt(1);
		  }catch(Exception ex)
		  {
			  ex.printStackTrace();
		  }
		  finally
		  {
			  disConnection();
		  }
		  return total;
	 }

 int total 변수에 페이지수를 넣어주고 함수를 불러와서 값을 채워준 다음 반환하는 메소드이다. 

 

package com.sist.board;

import java.io.*;
import java.text.SimpleDateFormat;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import java.util.*;//ArrayList
import com.sist.dao.*;// BoardVO,BoardDAO

@WebServlet("/BoardListServlet")
public class BoardListServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		// 1. HTML문서 전송 = 브라우저 (web(브라우저): XML(사용자 정의),HTML:고정)
		response.setContentType("text/html;charset=UTF-8");
		/*
		 *    1. 한글을 받는 경우 
		 *       EUC-KR => EUC-KR (O)
		 *       UTF-8  => UTF-8 (O)
		 *       EUC-KR => UTF-8 (O)
		 *       UTF-8  => EUC-KR (X)
		 */
		// 어디 출력 
		PrintWriter out=response.getWriter(); // 사용자의 브라우저에 읽어갈 위치 지정 
		/*
		 *   = 서블릿에서만 존재하는 것이 아니다 
		 *     JSP ,Spring,장고,NodeJS
		 *   request : 사용자가 보내준 데이터를 저장 (브라우저정보(서버정보,URI),사용자 정보(IP,PORT,데이터)
		 *        http://localhost/ProcedureBoardProject/BoardListServlet
		 *        ======================================================== URL
		 *                        ======================================== URI
		 *               ==========
		 *               localhost:80
		 *               ========= ==
		 *               서버 주소    서버포트 
		 *   response : 응답 (결과값을 브라우저 전송) , 화면 이동 정보 (redirect())
		 *   session  : 클라이언트의 일부정보를 서버에 저장 
		 *   ==========
		 *   cookie   : 클라이언트의 일부정보를 클라이언트에 저장 (최근 본 내용)
		 *   
		 *   서블릿의 단점 : 수정할때마다 컴파일을 한다 , HTML코딩이 어렵다 
		 *                장점 : 보안 (소스를 노출하지 않는다)
		 *   JSP의 단점  : 장점: 컴파일없이 실행 (수정..) , HTML코딩이 편하다 
		 *               소스가 노출 (보안 허점) , 자바와 HTML을 구분해서 사용
		 *               
		 *   서블릿과 JSP의 장점만 사용하면 어떨까? MVC
		 *     Model : 자바(VO,DAO)
		 *     View : JSP
		 *     Controller : 서블릿 => Spring에서 제공 => 자바와 HTML을 연결 
		 *     
		 *    *** request,response=>톰캣이 생성해서 매개변수를 채운다 (톰캣에 의해 호출)
		 *    
		 *    생명주기 
		 *     생성자 호출 : 메모리 할당
		 *    => init() : 멤버변수의 초기화   ====> _jspInit()
		 *    => doGet()|doPost() : 사용자가 브라우저에서 요청시마다 호출  ====> _jspService()
		 *     => 화면 이동 , 새로고침 
		 *     destory() : 메모리 해제   ====> _jspDestory()
		 *     ****** JSP를 실행하면 => 자동으로 서블릿 파일로 변경 
		 *     a.jsp ==> class a_jsp
		 */
		// 2. 사용자가 보내준 데이터를 받는다 
		String page=request.getParameter("page");
		if(page==null)
			page="1"; // Default 
		
		int curpage=Integer.parseInt(page);
		// 3. 오라클에서 데이터 읽기
		BoardDAO dao=new BoardDAO();
		// 3-1 목록
		ArrayList<BoardVO> list=dao.board_list(curpage);
		// 3-2 총페이지 
		int totalpage=dao.board_totalpage();
		// 화면에 데이터를 출력(HTML)
		
		out.println("<html>");
		out.println("<head>");
		out.println("<link rel=stylesheet href=\"css/table.css\">");
		out.println("<style>");
		out.println("th,td{font-size:9pt;font-family:맑은 고딕}");
		out.println("</style>");
		out.println("</head>");
		out.println("<body>");
		out.println("<center>");
		out.println("<h1>게시판</h1>");
		out.println("<table width=700>");
		out.println("<tr>");
		out.println("<td>");
		out.println("<a href=BoardInsertServlet>새글</a>");
		out.println("</td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("<div style=\"height:400px;\">");
		out.println("<table class=table-content width=700>");
		out.println("<tr height=35>");
		out.println("<th width=10%>번호</th>");
		out.println("<th width=45%>제목</th>");
		out.println("<th width=15%>이름</th>");
		out.println("<th width=20%>작성일</th>");
		out.println("<th width=10%>조회수</th>");
		out.println("</tr>");
		// 1. 오늘 날짜 
		String today=new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		for(BoardVO vo:list)
		{
			String dbday=vo.getRegdate().toString();
			out.println("<tr class=dataTr height=35>");
			out.println("<td width=10% align=center>"+vo.getNo()+"</td>");
			out.println("<td width=45%>"+vo.getSubject()+"&nbsp;");
			if(today.equals(dbday))
			{
				out.println("<sup style=\"color:red\">new</sup>");
				// <!ENTITY nbsp " "> XML에서 특수문자 정의 
				// => 호출 &nbsp;
				// <!ENTITY lt "<">
				// <!ENTITY gt ">">  
				// &lt;Hello&gt;  <Hello>
			}
			out.println("</td>");
			out.println("<td width=15% align=center>"+vo.getName()+"</td>");
			out.println("<td width=20% align=center>"+vo.getRegdate().toString()+"</td>");
			out.println("<td width=10% align=center>"+vo.getHit()+"</td>");
			out.println("</tr>");
		}
		out.println("</table>");
		out.println("</div>");
		out.println("<table width=700>");
		out.println("<tr>");
		out.println("<td>");
		out.println("Search:");
		out.println("<select name=fd>");
		out.println("<option value=name>이름</option>");
		out.println("<option value=subject>제목</option>");
		out.println("<option value=content>내용</option>");
		out.println("</select>");
		out.println("<input type=text name=ss size=10>");
		out.println("<input type=submit value=검색>");
		out.println("</td>");
		out.println("<td align=right>");
		out.println("<a href=BoardListServlet?page="+(curpage>1?curpage-1:curpage)+">이전</a>&nbsp;");
		out.println(curpage+" page / "+totalpage+" pages");
		out.println("<a href=BoardListServlet?page="+(curpage<totalpage?curpage+1:curpage)+">다음</a>");
		out.println("</td>");
		out.println("</tr>");
		out.println("</table>");
		out.println("</center>");
		out.println("</body>");
		out.println("</html>");
	}

}

 일단 어제는 여기까지 값을 채워줬고, 오늘은 여기서 기능을 추가해서 완성해볼 것 같다.