게시판 구축 프로젝트

1. board 테이블 작성

-- 게시판 테이블 작성

create table board
( num NUMBER(4) CONSTRAINT board_num_pk PRIMARY KEY,
  title VARCHAR2(200) CONSTRAINT board_title_nn NOT NULL,
  author VARCHAR2(20) CONSTRAINT board_author_nn NOT NULL,
  content VARCHAR2(400),
  writeday DATE DEFAULT SYSDATE,
  readcnt NUMBER(4) DEFAULT 0); 
  
create sequence board_seq;
  
insert into board ( num, title, author, content )
values ( board_seq.nextval , '테스트', '홍길동', '테스트입니다' );
commit;

 

 

2. 오라클 + mybatis 연동

⇒ 프로젝트 생성후 WEB-INF/lib 폴더에 2개의 jar 복사

 

 

 

 

3. 4가지 정보를 가진 jdbc.properties 작성 (com.config 패키지)

 

 

 

4. BoardMapper.xml 작성 (com.config 패키지)

- 게시판 전체 목록 조회 기능

게시판 전체 목록 조회 기능

 

 

 

 

 

5. BoardDTO.java 작성 (com.dto 패키지)

 

 

 

6. Configuration.xml 작성 (com.config 패키지)

- jdbc.properties 등록 및 ${} 설정
- BoardDTO 별칭

- BoardMapper.xml 등록

 

 

 

7. MySqlSessionFactory.java 작성 (com.config 패키지)

- Configuration.xml 지정해서 읽고

- SqlSession 반환

 

 

 

======================================⇒ ✅ 기본준비 완료

 

 

 

 

---

 

 

 

---

 

 

 

8. BoardDAO.java (com.dao 패키지)

- BoardServiceImpl에서 전달해준 SqlSession과 파라미터(옵션)를 이용해서 실제 DB와 연동.

 

 

 

 

9. BoardService.java와 BoardServiceImpl.java 작성 (com.service 패키지)

- BoardDAO 메서드를 복사해서 SqlSession 파라미터를 제거한다.

- BoardDAO 연동

 

 

 

 

 

10. BoardListServlet.java 작성 ( com.controller 패키지)

- BoardService 연동해서 화면에 보여줄 데이터를 반환

- 데이터를 scope에 저장

          request scope

          session scope

          application scope

- jsp에 요청위임

          forward

          redirect

 

 

 

 

 

11. list.jsp 작성

- BoardListServlet 에서 scope에 저장된 데이터를 얻어서 화면에 출력

 

 

 

 

=======================================⇒ ✅ DB 연동 완료

 

 

 

---

 

 

 

테이블 꾸미기

 

 

 

---

 

 

 

jdbc.properties

#주석문
# jdbc.properties
jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:xe
jdbc.username=SCOTT
jdbc.password=TIGER

 

BoardMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
  
<mapper namespace="BoardMapper">

    <select id="list" resultType="BoardDTO">
    	select num, title, author, content, writeday, readcnt
    	from board
    	order by num desc
    </select>
    
</mapper>

 

BoardDTO.java

package com.dto;

public class BoardDTO {
	
	int num;  //글번호
	String title;  //제목
	String author;  //작성자
	String content;  //내용
	String writeday;  //작성일
	int readcnt;  //조회수
	
	public BoardDTO() {	}

	public BoardDTO(int num, String title, String author, String content, String writeday, int readcnt) {
		super();
		this.num = num;
		this.title = title;
		this.author = author;
		this.content = content;
		this.writeday = writeday;
		this.readcnt = readcnt;
	}

	public int getNum() {
		return num;
	}

	public void setNum(int num) {
		this.num = num;
	}

	public String getTitle() {
		return title;
	}

	public void setTitle(String title) {
		this.title = title;
	}

	public String getAuthor() {
		return author;
	}

	public void setAuthor(String author) {
		this.author = author;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

	public String getWriteday() {
		return writeday;
	}

	public void setWriteday(String writeday) {
		this.writeday = writeday;
	}

	public int getReadcnt() {
		return readcnt;
	}

	public void setReadcnt(int readcnt) {
		this.readcnt = readcnt;
	}

	@Override
	public String toString() {
		return "BoardDTO [num=" + num + ", title=" + title + ", author=" + author + ", content=" + content
				+ ", writeday=" + writeday + ", readcnt=" + readcnt + "]";
	}

	
}

 

configuration.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
  <!-- 주석 -->
   
   <!--  jdbc.properties 등록 -->
   <properties resource="com/config/jdbc.properties"></properties>
   
   <typeAliases>
      <typeAlias alias="BoardDTO" type="com.dto.BoardDTO"/>
   </typeAliases>
  
  <environments default="development">
    <environment id="development">
      <transactionManager type="JDBC"/>
      <dataSource type="POOLED">
        <property name="driver" value="${jdbc.driver}"/>
        <property name="url" value="${jdbc.url}"/>
        <property name="username" value="${jdbc.username}"/>
        <property name="password" value="${jdbc.password}"/>
      </dataSource>
    </environment>
  </environments>
  <mappers>
    <mapper resource="com/config/BoardMapper.xml"/>
  </mappers>
</configuration>

 

MySqlSessionFactory.java

package com.config;

import java.io.IOException;
import java.io.InputStream;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

public class MySqlSessionFactory {
	static SqlSessionFactory sqlSessionFactory = null;
	static {
		String resource = "com/config/Configuration.xml";
		InputStream inputStream=null;
		try {
			inputStream = Resources.getResourceAsStream(resource);
		} catch (IOException e) {
			e.printStackTrace();
		}
		sqlSessionFactory =
		  new SqlSessionFactoryBuilder().build(inputStream);
		
	}//end static 블럭
	
	// ServiceImpl에서 필요한 SqlSession 리턴하는 메서드
	// new 없이 사용하기 위해서 static 지정한다.
	public static SqlSession getSession() {
		SqlSession session = 
				sqlSessionFactory.openSession();
		return session;
	}
}

 

BoardDAO.java

package com.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.dto.BoardDTO;

public class BoardDAO {
	
	// 전체 목록
	public List<BoardDTO> list(SqlSession session){
		
		List<BoardDTO> list = session.selectList("BoardMapper.list");
		return list;
	}

}

 

BoardService.java

package com.service;

import java.util.List;

import com.dto.BoardDTO;

public interface BoardService {

	public List<BoardDTO> list();
}

 

BoardServiceImpl.java

package com.service;

import java.util.List;

import org.apache.ibatis.session.SqlSession;

import com.config.MySqlSessionFactory;
import com.dao.BoardDAO;
import com.dto.BoardDTO;

public class BoardServiceImpl implements BoardService {

	@Override
	public List<BoardDTO> list() {
		List<BoardDTO> list = null;    //return 하기 위해 밖에서 선언
		SqlSession session = MySqlSessionFactory.getSession();
		try {
			// DAO 연동코드
			BoardDAO dao = new BoardDAO();    //객체생성
			list = dao.list(session); 
			
		}finally {
			session.close();
		}
		return list;
	}

}

 

BoardListServlet.java

package com.controller;

import java.io.IOException;
import java.util.List;

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.dto.BoardDTO;
import com.service.BoardService;
import com.service.BoardServiceImpl;

/**
 * Servlet implementation class BoardListServlet
 */
@WebServlet("/list")
public class BoardListServlet extends HttpServlet {
	
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		//BoardService 연동
		BoardService service = new BoardServiceImpl();
		List<BoardDTO> list = service.list();     //다형성
		
		// 이전에는 서블릿에서 응답처리를 했음. => list.jsp한테 위임
		// list.jsp 에서 List<BoardDTO>를 보여주기 위해서는
		// List<BoardDTO>를 scope에 저장해야 된다. 목록보기는 request scope가 가장 최적임.
		/*
		 * request scope ( <=여기에 저장 )
		 * session scope
		 * application scope
		 */
		request.setAttribute("boardList", list);
		
		// 요청위임. 목록보기는 request scope에 저장했기 때문에 포워드로 사용.
		/*
		 * 포워드(forward)   <= 사용
		 * 리다이렉트(redirect)
		 */
		request.getRequestDispatcher("list.jsp").forward(request, response);
	}

	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

}

 

list.jsp

<%@page import="com.dto.BoardDTO"%>
<%@page import="java.util.List"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
<h2>게시판 목록보기</h2>
<%
	List<BoardDTO> list 
	= (List<BoardDTO>)request.getAttribute("boardList");	
%>
<table border="1">
	<tr>
		<th>글번호</th>
		<th>제목</th>
		<th>작성자</th>
		<th>작성일</th>
		<th>조회수</th>
	</tr>
	
<%
  for(BoardDTO dto: list){
	  int num = dto.getNum();
	  String title = dto.getTitle();
	  String author = dto.getAuthor();
	  String writeday = dto.getWriteday();
%>	
	<tr>
		<td><%= num %></td>
		<td><%= title %></td>
		<td><%= author %></td>
		<td><%= writeday %></td>
		<td><%= dto.getReadcnt() %></td>
	</tr>
<%
  }
%>
</table>

</body>
</html>

 

 

 

---

 

 

 

💡 최종결과