영보의 SystemOut.log

[WEB] Java와 Oracle연동하여 HTML로 게시판 만들기 본문

Web

[WEB] Java와 Oracle연동하여 HTML로 게시판 만들기

영보로그 2020. 8. 14. 18:03
반응형

BoardVO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
package com.sist.dao;
import java.util.*;
public class BoardVO {
    private int no;
    private String name;
    private String subject;
    private String content;
    private String pwd;
    private Date regdate;
    private int hit;
    public int getNo() {
        return no;
    }
    public void setNo(int no) {
        this.no = no;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getSubject() {
        return subject;
    }
    public void setSubject(String subject) {
        this.subject = subject;
    }
    public String getContent() {
        return content;
    }
    public void setContent(String content) {
        this.content = content;
    }
    public String getPwd() {
        return pwd;
    }
    public void setPwd(String pwd) {
        this.pwd = pwd;
    }
    public Date getRegdate() {
        return regdate;
    }
    public void setRegdate(Date regdate) {
        this.regdate = regdate;
    }
    public int getHit() {
        return hit;
    }
    public void setHit(int hit) {
        this.hit = hit;
    }
}
 cs

 

 

 

 

BoardDAO.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
package com.sist.dao;
import java.util.*;
import javax.xml.transform.Result;
import java.sql.*;
public class BoardDAO{
   //연결
   private Connection conn;
   //SQL문장 전송
   private PreparedStatement ps;
   //URL
   private final String URL="jdbc:oracle:thin:@localhost:1521:XE";
   // 연결 준비
   // 1. 드라이버 등록
   public BoardDAO(){
      try{ 
         Class.forName("oracle.jdbc.driver.OracleDriver"); 
      }catch(Exception ex){
         System.out.println(ex.getMessage());
      }
   }
   //연결
   public void getConnection() {
      try{
      conn=DriverManager.getConnection(URL,"hr","happy");   
      }catch(Exception ex){}
   }
   //해제
   public void disConnection() {
   try{
      if(ps!=null) ps.close();
      if(conn!=null) conn.close();
      }catch(Exception ex){}
   }
   //기능
   //1.목록(게시판) SELECT
   public ArrayList<BoardVO> boardListData(){
      ArrayList<BoardVO> list=new ArrayList<BoardVO>();
      try {
         //연결
         getConnection();
         //SQL문장 전송
         String sql="SELECT no,subject,name,regdate,hit FROM freeboard "
                + "ORDER BY no DESC"; //단점: 속도 늦음→INDEX
         ps=conn.prepareStatement(sql);
         //SQL 실행 후 결과값 받기
         ResultSet rs=ps.executeQuery();      
         //결과값 ArrayList에 첨부
         while(rs.next()) {
            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) {
         System.out.println(ex.getMessage());
      }finally {
         disConnection();
      }
      return list;
      
   }
   //2.내용보기 SELECT (WHERE) ?no=1
   public BoardVO boardDetail(int no) {
       BoardVO vo=new BoardVO();
       try {
           // 연결
           getConnection();
           // SQL문장 전송 ==> 조회수 증가
           String sql="UPDATE freeboard SET hit=hit+1 WHERE no=?";
           ps=conn.prepareStatement(sql);
           ps.setInt(1, no); // ?에 값을 채운다
           // 실행
           ps.executeUpdate();
           // 내용물 데이터를 가지고 온다
           sql="SELECT no, name, subject, content, regdate, hit FROM freeboard WHERE no=?";
           ps=conn.prepareStatement(sql);
           ps.setInt(1, no);
           ResultSet rs=ps.executeQuery();
           rs.next();
           
           vo.setNo(rs.getInt(1));
           vo.setName(rs.getString(2));
           vo.setSubject(rs.getString(3));
           vo.setContent(rs.getString(4));
           vo.setRegdate(rs.getDate(5));
           vo.setHit(rs.getInt(6));
           rs.close();
       }catch(Exception ex) {
           System.out.println(ex.getMessage());
       }
       finally {
           disConnection();
       }
       return vo;
   }
   //3.글쓰기 INSERT
   public void boardInsert(BoardVO vo) {
       try {
           // 연결
           getConnection();
           String sql="INSERT INTO freeboard(no,name,subject,content,pwd) VALUES((SELECT NVL(MAX(no)+1,1) FROM freeboard),?,?,?,?)";
           ps=conn.prepareStatement(sql);
           ps.setString(1, vo.getName());
           ps.setString(2, vo.getSubject());
           ps.setString(3, vo.getContent());
           ps.setString(4, vo.getPwd());
           
           ps.executeUpdate(); //auto COMMIT
       }catch(Exception ex) {
           
       }
       finally {
       
       }
   }
   //4.글수정 UPDATE
   //5.글삭제 DELETE
   //6.찾기 SELECT
}
 cs

 

미리 oracle에서 작성해놨던 데이터들과 연결해놨습니다.

 

 

 

 

BoardList.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
package com.sist.board;
 
import java.io.*;
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.*;
 
@WebServlet("/BoardList")
public class BoardList extends HttpServlet {
   private static final long serialVersionUID = 1L;
 
   protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
   //브라우저에서 실행하는 화면:HTML
      //브라우저에 알림 : HTML문서 전송
      response.setContentType("text/html;charset=EUC-KR");
      //HTML을 브라우저로 전송 시작
      PrintWriter out=response.getWriter();
      out.println("<html>");
      out.println("<head>");
      out.println("<Link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css\">");      
      out.println("<style type=text/css>");
      out.println(".row {margin:0px auto; width:700px}"); //가운데 출력:margin
      out.println("h2 {text-align:center}");
      out.println("</style>");
      out.println("</head>");
      out.println("<body>");
      out.println("<div class=container>");
      out.println("<h2>자유게시판</h2>");
      out.println("<div class=row>");
      
      out.println("<table class=\"table\">");
      out.println("<tr>");
      out.println("<td>");
      out.println("<a href=BoardInsert class=\"btn btn-sm btn-success\">새글</a>");
      //btn-sm 크기 / btn-lg : 버튼 커짐 / btn-xs : 버튼 작아짐 
      // success - 녹색
      out.println("</td>");
      out.println("</tr>");
      out.println("<table>");
      
      out.println("<table class=\"table table-hover\">");
      out.println("<tr class=info>"); //색상 빨간색:danger(table-hover)
      out.println("<th class=text-center width=10%>번호</th>");
      out.println("<th class=text-center width=45%>제목</th>");
      out.println("<th class=text-center width=15%>이름</th>");
      out.println("<th class=text-center width=20%>작성일</th>");
      out.println("<th class=text-center width=10%>조회수</th>");
      out.println("</tr>");
      //출력
      BoardDAO dao=new BoardDAO();
      ArrayList<BoardVO> list=dao.boardListData();
      for(BoardVO vo:list) {
         out.println("<tr>");
         out.println("<th class=text-center width=10%>"+vo.getNo()+"</th>");
         out.println("<th class=text-left width=45%>"
         +"<a href=BoardDetail?no="+vo.getNo()+">"
         +vo.getSubject()+"</a></td>");
         out.println("<th class=text-center width=15%>"+vo.getName()+"</th>");
         out.println("<th class=text-center width=20%>"+vo.getRegdate().toString()+"</th>");
         out.println("<th class=text-center width=10%>"+vo.getHit()+"</th>");
         out.println("</tr>");
      }
      out.println("</table>");
      out.println("<hr>");
      
      out.println("<table class=\"table\">");
      out.println("<tr>");
      
      out.println("<td class=text-left>");
      out.println("Search:");
      out.println("<select class=input-sm>");
      out.println("<option>이름<option>");
      out.println("<option>제목<option>");
      out.println("<option>내용<option>");
      out.println("</select>");
      out.println("<input type=text size 15 class=input-sm>");
      out.println("<input type=button value=찾기 class=\"btn btn-sm btn-danger\">");
      out.println("</td>");
      
      out.println("<td class=text-right>");
      //primary -진한 청색
      out.println("<a href=BoardInsert class=\"btn btn-sm btn-primary\">이전</a>");
      out.println("0 page / 0 pages");
      out.println("<a href=BoardInsert class=\"btn btn-sm btn-primary\">다음</a>");
      out.println("</td>");
      
      out.println("</tr>");
      out.println("<table>");
      
      out.println("</div>");
      out.println("</div>");
      out.println("</body>");
      out.println("</html>");
   }
}
 cs

 

 

 

 

BoardInsert.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
package com.sist.board;
 
import java.io.IOException;
import java.io.PrintWriter;
 
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 com.sist.dao.BoardDAO;
import com.sist.dao.BoardVO;
 
@WebServlet("/BoardInsert")
public class BoardInsert extends HttpServlet {
    private static final long serialVersionUID = 1L;
    // 폼 작업(화면 출력)
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
         //브라우저에서 실행하는 화면:HTML
          //브라우저에 알림 : HTML문서 전송
          response.setContentType("text/html;charset=EUC-KR");
          //HTML을 브라우저로 전송 시작
          PrintWriter out=response.getWriter();
          out.println("<html>");
          out.println("<head>");
          out.println("<Link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css\">");      
          out.println("<style type=text/css>");
          out.println(".row {margin:0px auto; width:500px}"); //가운데 출력:margin
          out.println("h2 {text-align:center}");
          out.println("</style>");
          out.println("</head>");
          out.println("<body>");
          out.println("<div class=container>");
          out.println("<h2>글쓰기</h2>");
          out.println("<div class=row>");
          
          out.println("<form method=post action=BoardInsert>");
          // post 보안 / get 다보여짐
          out.println("<table class=\"table\">");
          out.println("<tr>");
          out.println("<td width=15% class-right>이름</td>");
          out.println("<td width=85%>");
          out.println("<input type=text size=15 class=input-sm name=name>");
          out.println("</td>");
          out.println("</tr>");
          
          out.println("<tr>");
          out.println("<td width=15% class-right>제목</td>");
          out.println("<td width=85%>");
          out.println("<input type=text size=45 class=input-sm name=subject>");
          out.println("</td>");
          out.println("</tr>");
          
          out.println("<tr>");
          out.println("<td width=15% class=text-right>내용</td>");
          out.println("<td width=85%>");
          out.println("<textarea cols=50 rows=10 name=content></textarea>");
          out.println("</td>");
          out.println("</tr>");
          
          out.println("<tr>");
          out.println("<td width=15% class=text-right>비밀번호</td>");
          out.println("<td width=85%>");
          out.println("<input type=password size=10 class=input-sm name=pwd>");
          out.println("</td>");
          out.println("</tr>");
          
          out.println("<tr>");
          out.println("<td colspan=2 class=text-center>");
          out.println("<input type=submit class=\"btn btn-sm btn-danger\" value=글쓰기>");
          out.println("<input type=button class=\"btn btn-sm btn-info\" value=취소 onclick=\"javascript:history.back()\">");
          out.println("</td>");
          out.println("</tr>");
          
          out.println("</table>");
          out.println("</form>");
          out.println("</div>");
          out.println("</div>");
          out.println("</body>");
          out.println("</html>");
    }
    // 데이터베이스 연결 => 요청 처리
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        try {
            request.setCharacterEncoding("EUC-KR");
        }catch(Exception ex) {}
        String name=request.getParameter("name");
        String subject=request.getParameter("subject");
        String content=request.getParameter("content");
        String pwd=request.getParameter("pwd");
    
        BoardVO vo=new BoardVO();
        vo.setName(name);
        vo.setSubject(subject);
        vo.setPwd(pwd);
        vo.setContent(content);
        
        // DAO로 전송 => 오라클 INSERT
        BoardDAO dao=new BoardDAO();
        dao.boardInsert(vo);
        
        // 목록으로 이동    
        response.sendRedirect("BoardList");
    }
}
 cs

 

 

 

 

BoardDetail.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
package com.sist.board;
 
import java.io.*;
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 com.sist.dao.*;
 
 
@WebServlet("/BoardDetail")
public class BoardDetail extends HttpServlet {
    private static final long serialVersionUID = 1L;
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        //브라우저에서 실행하는 화면:HTML
          //브라우저에 알림 : HTML문서 전송
          response.setContentType("text/html;charset=EUC-KR");
          //HTML을 브라우저로 전송 시작
          PrintWriter out=response.getWriter();
          
          // 번호 받는다 ?no=10 
          String no=request.getParameter("no");
          BoardDAO dao=new BoardDAO();
          BoardVO vo=dao.boardDetail(Integer.parseInt(no));
          
          
          out.println("<html>");
          out.println("<head>");
          out.println("<Link rel=\"stylesheet\" href=\"https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css\">");      
          out.println("<style type=text/css>");
          out.println(".row {margin:0px auto; width:600px}"); //가운데 출력:margin
          out.println("h2 {text-align:center}");
          out.println("</style>");
          out.println("</head>");
          out.println("<body>");
          out.println("<div class=container>");
          out.println("<h2>내용 보기</h2>");
          out.println("<div class=row>");
     
          // post 보안 / get 다보여짐
          out.println("<table class=\"table\">");
          out.println("<tr>");
          out.println("<td class=\"success text-center\" width=25%>번호</td>");
          out.println("<td width=25% class=text-center>"+vo.getNo()+"</td>");
          out.println("<td class=\"success text-center\" width=25%>작성일</td>");
          out.println("<td width=25% class=text-center>"+vo.getRegdate()+"</td>");
          out.println("</tr>");
          
          out.println("<tr>");
          out.println("<td class=\"success text-center\" width=25%>이름</td>");
          out.println("<td width=25% class=text-center>"+vo.getName()+"</td>");
          out.println("<td class=\"success text-center\" width=25%>조회수</td>");
          out.println("<td width=25% class=text-center>"+vo.getHit()+"</td>");
          out.println("</tr>");
          
          out.println("<tr>");
          out.println("<td class=\"success text-center\" width=25%>제목</td>");
          out.println("<td colspan=3>"+vo.getSubject()+"</td>");
          out.println("</tr>");
 
          out.println("<tr>");
          out.println("<td colspan=4 height=200 valign=top>"+vo.getContent()+"</td>");
          out.println("</tr>");
          
          out.println("<tr>");
          out.println("<td colspan=4 class=text-right>");
          out.println("<a href=# class=\"btn btn-xs btn-success\">수정</a>");
          out.println("<a href=# class=\"btn btn-xs btn-success\">삭제</a>");
          out.println("<a href=BoardList class=\"btn btn-xs btn-success\">목록</a>");
          out.println("</td>"); 
          out.println("</tr>");
          
          out.println("</table>");
          out.println("</div>");
          out.println("</div>");
          out.println("</body>");
          out.println("</html>");
    }
}
 
 cs
 

 

 

 

 

 

 

< 실행 화면 >

 

 

[BoardList.java] 를 실행하면 저 화면이 켜집니다.

 

 

 

 

[새글] 눌러서 글을 작성해봅시다.

 

 

 

 

 

 

 

[BoardInsert] 화면입니다

대충 내용을 작성했어요

 

 

 

 

 

 

 

잘 보이네요

 

 

 

 

 

 

내용보기 눌러보니

조회수와 작성일도 잘 나오네요

 

허접하지만 간단하게 게시판 만들기였습니다.

 

반응형
저작자표시

'Web' 카테고리의 다른 글

[Web] JSTL , CSS 이용하여 레시피 그리드 목록 출력-(2)  (0) 2020.10.07
[Web] JSTL , CSS 이용하여 레시피 그리드 목록 출력-(1)  (0) 2020.10.07
[Web] JSTL 속성 / JSTL 태그  (0) 2020.10.07
[WEB] GET방식과 POST방식 차이  (0) 2020.09.30
[Web] JSP 게시판만들기 #페이징/ 컨테이너/ 유형별 리스트  (0) 2020.08.27
'Web' Related Articles more