76일차 : 정규화, SpringMVC - 게시판

20230316

1. 테이블생성을 위한 정규화이론

/*
    1. 학생테이블이 존재
    2. 학생은 수업을 듣는다. -> 수업갯수는 학생마다 다름(어떻게 표현?)
       방법1)가장 원시적인 방법은 과목컬럼(SUBJECT1)을 과목마다 추가하는 것 
       -> 문제점 : 수업갯수가 학생마다 다르므로 몇개를 해야할지 모름
       방법2)과목을 모두 입력할 컬럼을 하나 추가
       -> 문제점 : 특정과목에 대한 조회/입력/수정/삭제 부분이 어려워짐
       방법3) 과목을 저장할 컬럼을 1개추가하고 ROW를 늘려준다
       REG_CLASS 컬럼에 독립적인 데이터 1개만 저장하는 경우 과목에 대한 추가,수정,삭제,조회가 수월해짐
       --> 제 1정규화 : 컬럼에 데이터를 독립적으로 1개씩 저장하는 형태
       -> 문제점 : 쓸데없이 중복된 데이터가 너무많다 / 수정 시 수정해야 하는 ROW가 너무많음
                  제1정규화가 끝나면 과목에 대한 처리는 쉬워지는 대신 중복데이터가 너무 많다
                  학생관련된 데이터의 수정/삭제 로직이 불편하다.
       -> 해결법 : 과목은 학생의 주정보가 아닌 서브정보 -> (방법4)과목관련 테이블을 만들어준다.
       방법4) 과목관련 테이블 생성 
       --> 제 2정규화 : 제 1정규화가 완련된 상태에서 메인주제와 관련이 없는 컬럼(REG_CLASS)을 테이블로 분리하는 것
       --> 관계형 데이터베이스는 제2정규화를 하지만, 비관계형 데이터베이스는 중복에대한 단점을 안고 제2정규화를 하지 않는 경우도 있음.
       -> 문제점 : 테이블의 특정컬럼을 업데이트 할 때 수정할 컬럼이 너무 많음(CLASS_NAME - CLASS_PRICE가 종속관계)
       -> 해결법 : CLASS_PRICE 를 새로운 테이블로 생성
       방법5) 과목테이블 / 금액테이블 추가
       --> 제 3정규화 : 제 2정규화가 완료된 상태에서 종속적인 컬럼을 테이블로 분리하는 것
*/
-- 방법1 : 과목수만큼 컬럼추가
CREATE TABLE STUDENT(
    STUDENT_ID      VARCHAR2(20),
    STUDENT_PW      VARCHAR2(20),
    STUDENT_NAME    VARCHAR2(20),
    STUDENT_PHONE   CHAR(13)
    
);

-- 방법2 : 과목전체를 작성할 컬럼 추가
CREATE TABLE STUDENT(
    STUDENT_ID      VARCHAR2(20),
    STUDENT_PW      VARCHAR2(20),
    STUDENT_NAME    VARCHAR2(20),
    STUDENT_PHONE   CHAR(13),
    REG_CLASS       VARCHAR2(1000) -- 자바, 오라클, HTML 을 전부 작성할 컬럼
);
INSERT INTO STUDENT VALUES('stu1', '1234', '학생1', '010-1234-4321', '자바, 오라클');
INSERT INTO STUDENT VALUES('stu2', '2345', '학생2', '010-4321-4321', '자바, 오라클, HTML');
SELECT * FROM STUDENT;
SELECT * FROM STUDENT WHERE REG_CLASS LIKE '%HTML%';
DROP TABLE STUDENT;

-- 방법3 : ROW수를 늘림(제 1정규화)
CREATE TABLE STUDENT(
    STUDENT_ID      VARCHAR2(20),
    STUDENT_PW      VARCHAR2(20),
    STUDENT_NAME    VARCHAR2(20),
    STUDENT_PHONE   CHAR(13),
    REG_CLASS       VARCHAR2(30)    -- 한 과목만 저장
);
-- 한과목만 저장하고 ROW를 늘림
INSERT INTO STUDENT VALUES('stu1', '1234', '학생1', '010-1234-4321', '자바');
INSERT INTO STUDENT VALUES('stu1', '1234', '학생1', '010-1234-4321', '오라클');
INSERT INTO STUDENT VALUES('stu2', '2345', '학생2', '010-4321-4321', '자바');
INSERT INTO STUDENT VALUES('stu2', '2345', '학생2', '010-4321-4321', '오라클');
INSERT INTO STUDENT VALUES('stu2', '2345', '학생2', '010-4321-4321', 'HTML');
SELECT * FROM STUDENT;

-- 방법4 : 과목테이블추가(제 2정규화)
CREATE TABLE STUDENT(
    STUDENT_ID      VARCHAR2(20),
    STUDENT_PW      VARCHAR2(20),
    STUDENT_NAME    VARCHAR2(20),
    STUDENT_PHONE   CHAR(13)
);
CREATE TABLE REG_CLASS(
    STUDENT_ID      VARCHAR2(20),
    CLASS_NAME      VARCHAR2(20),
    CLASS_PRICE     NUMBER
);
INSERT INTO STUDENT VALUES('stu1', '1234', '학생1', '010-1234-4321');
INSERT INTO STUDENT VALUES('stu2', '2345', '학생2', '010-4321-4321');

INSERT INTO REG_CLASS VALUES('stu1', '자바', 1000000);
INSERT INTO REG_CLASS VALUES('stu1', '오라클', 500000);
INSERT INTO REG_CLASS VALUES('stu2', '자바', 1000000);
INSERT INTO REG_CLASS VALUES('stu2', '오라클', 500000);
INSERT INTO REG_CLASS VALUES('stu2', 'HTML', 800000);

SELECT * FROM STUDENT;
SELECT * FROM REG_CLASS;
DROP TABLE REG_CLASS;

-- 방법5 : 과목테이블 / 금액(과목에 종속)테이블(제3정규화)
CREATE TABLE STUDENT(
    STUDENT_ID      VARCHAR2(20),
    STUDENT_PW      VARCHAR2(20),
    STUDENT_NAME    VARCHAR2(20),
    STUDENT_PHONE   CHAR(13)
);
CREATE TABLE REG_CLASS(
    STUDENT_ID      VARCHAR2(20),
    CLASS_NAME      VARCHAR2(20)
);
CREATE TABLE A_CLASS(
    CLASS_NAME      VARCHAR2(30),
    CLASS_PRICE     NUMBER
);
INSERT INTO A_CLASS VALUES('자바', 1000000);
INSERT INTO A_CLASS VALUES('오라클', 500000);
INSERT INTO A_CLASS VALUES('HTML', 800000);
SELECT * FROM A_CLASS;
INSERT INTO REG_CLASS VALUES('st1', '자바');
INSERT INTO REG_CLASS VALUES('st1', '오라클');
INSERT INTO REG_CLASS VALUES('st2', '자바');
INSERT INTO REG_CLASS VALUES('st2', '오라클');
INSERT INTO REG_CLASS VALUES('st2', 'HTML');

 

 

2. 게시판

- 파일 업로드가 되는 게시판

- 사전작업

BOARD 테이블과 시퀀스

CREATE TABLE BOARD(
    BOARD_NO        NUMBER          PRIMARY KEY,
    BOARD_TITLE     VARCHAR2(100)   NOT NULL,
    BOARD_WRITER    VARCHAR2(20)    REFERENCES MEMBER_TBL(MEMBER_ID) ON DELETE CASCADE,
    BOARD_CONTENT   VARCHAR2(1000)  NOT NULL,
    BOARD_DATE      CHAR(10)
);
CREATE SEQUENCE BOARD_SEQ;

FILE_TBL 테이블과 시퀀스

CREATE TABLE FILE_TBL(
    FILE_NO     NUMBER          PRIMARY KEY,
    BOARD_NO    NUMBER          REFERENCES BOARD ON DELETE CASCADE, -- BOARD테이블의 PK참조
    FILENAME    VARCHAR2(100)   NOT NULL,
    FILEPATH    VARCHAR2(100)   NOT NULL
);
CREATE SEQUENCE FILE_SEQ;

 

패키지/클래스 생성

패키지를 새로 생성했으므로 sevlet-context에 component-scan 추가(추가해야 @AutoWired 사용가능)

	<context:component-scan base-package="kr.or.board" />

파일 업로드를 위해 파일업로드용 객체 생성

	<!-- 파일업로드용 객체 -->
	<beans:bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
		<beans:property name="maxUploadSize" value="10485760" />
	</beans:bean>

파일업로드를 위해 라이브러리 추가 - commons-io / commons-fileupload

		<!-- 파일 업로드를 위해 필요한 라이브러리 추가 2개(commons-io, commons-fileupload -->
		<!-- https://mvnrepository.com/artifact/commons-fileupload/commons-fileupload -->
		<dependency>
		    <groupId>commons-fileupload</groupId>
		    <artifactId>commons-fileupload</artifactId>
		    <version>1.5</version>
		</dependency>		
		<!-- https://mvnrepository.com/artifact/commons-io/commons-io -->
		<dependency>
		    <groupId>commons-io</groupId>
		    <artifactId>commons-io</artifactId>
		    <version>2.11.0</version>
		</dependency>

1. 글작성하기

boardWriteFrm.jsp

<%@ 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>
	<h1>게시글 작성</h1>
	<hr>
	<%-- 첨부파일일 때, method="post" / enctype="multipart/form-data" 반드시 입력 --%>
	<form action="/boardWrite.do" method="post" enctype="multipart/form-data">
		<fieldset>
			제목 : <input type="text" name="boardTitle"><br>
			첨부파일 : <input type="file" name="boardFile" multiple><br>
			<%--
				보통 name은 vo의 변수명과 동일하게 만들어주지만, 첨부파일은 반드시 vo와 다르게 넣어야한다 
				file타입이라 문자열로 들어가지 않기때문에 겹쳐서 에러발생 
				multiple : 파일을 여러개 받기 위해추가 / shift로 선택가능 / 하나선택하고 또 하나선택은X
				기존에는 cos.jar 라이브러리를 사용했지만 이번엔 다른 라이브러리를 추가할것
				-> 라이브러리추가는 항상 pom.xml에 추가
			--%>
			내용 : <textarea name="boardContent"></textarea>
			<input type="submit" value="작성하기">
			<input type="hidden" name="boardWriter" value="${sessionScope.m.memberId }">
		</fieldset>
	</form>
	<a href="/">메인으로</a>
</body>
</html>

Controller

	@RequestMapping(value="/boardWrite.do")
	//파일업로드를 진행하기위해 request를 불러옴
	public String boardWrite(Board b, MultipartFile[] boardFile, HttpServletRequest request) {
		//파일목록을 저장할 list생성
		ArrayList<FileVO> fileList = new ArrayList<FileVO>();
		//제목,내용,작성자는 매개변수 b에 모두 들어있음
		//파일이름은 b에 들어있지X 매개변수로 받음 -> 여러개를 받을거기때문에 자료형은 배열형태로
		//첨부파일은 input type="file"의 name으로 매개변수를 생성 -> 첨부파일 갯수만큼 배열길이 생성
		//첨부파일을 첨부하지 않아도 배열은 길이가 1
		//System.out.println(b);
		//System.out.println(boardFile.length);
		//첨부파일 존재유무를 구분하는 법 - 배열의 첫번째가 비어있는지 아닌지 확인
		//if(!boardFile[0].isEmpty()) {} if문은 이렇게 작성해주는게 좋다.
		if(boardFile[0].isEmpty()) {
			//첨부파일이 없는경우 진행할 로직이 없음
		}else {
			//첨부파일이 있는경우 파일업로드작업 수행
			//1. 파일업로드 경로 설정
			//getRealPath까지가 webapp
			String savePath = request.getSession().getServletContext().getRealPath("/resources/upload/board/");
			//2. 배열이므로 반복문을 이용해서 파일업로드 처리
			for(MultipartFile file : boardFile) {
				//파일명이 기존업로드한 파일명과 중복되면 기존파일을 삭제하고 새파일로 덮어쓰기됨 -> 파일명이 중복되지않게 처리해야 함
				//(cos라이브러리에서는 new DefaultFileRenamePolicy가 파일이름이 중복일 경우 새로운이름을 붙여줬음 name1 name2)
				String filename = file.getOriginalFilename(); //사용자가 업로드한 이름
				//이걸 그대로 업로드하면 파일명이 중복되엇을 때 문제가 발생
				//가정 : filename => test.txt인데 중복인 경우 => test_1.txt 확장자기준 앞을 바꿈
				//우선 text    .txt를 분리함
				String onlyFilename = filename.substring(0, filename.lastIndexOf("."));//test 0부터 뒤에서부터 .의 위치의 숫자를세서 그만큼까지
				String extention = filename.substring(filename.lastIndexOf(".")); //.txt 매개변수를 하나만주면 그 매개변수부터 끝까지라는 의미
				//실제 업로드할 파일명
				String filepath = null;
				//파일명 중복 시 뒤에 붙일 숫자
				int count = 0; //횟수가 정해져있지 않음 > while문을 무한으로 돌리고 중복이아닐때 빠져나옴
				while(true) {
					if(count == 0) {
						//첫번째 검증인 경우 숫자를 붙이지X
						filepath = onlyFilename+extention;				//test.txt
					}else {
						filepath = onlyFilename+"_"+count+extention;	//test_1.txt
					}
					File checkFile = new File(savePath+filepath);
					if(!checkFile.exists()) {
						//checkFile이 존재하는지 물어보는 것 -> 중복이 아닐 때 break 
						break;
					}
					count++;
				}
				//파일명 중복체크 끝 -> 업로드할 파일명 확정 -> 파일업로드진행
				//2-2. 중복처리가 끝난 파일 업로드
				try {
					//파일업로드를 위한 주스트림생성
					FileOutputStream fos = new FileOutputStream(savePath+filepath);
					//성능향상을 위한 보조스트림 생성
					BufferedOutputStream bos = new BufferedOutputStream(fos);
					//파일업로드
					byte[] bytes = file.getBytes();
					bos.write(bytes);
					bos.close();
				} catch (FileNotFoundException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				//파일업로드 끝(1개)
				//DB에 저장하기 위해 FileVO형태의 객체를 생성해서 fileList추가
				FileVO fileVO = new FileVO();
				fileVO.setFilename(filename);
				fileVO.setFilepath(filepath);
				fileList.add(fileVO);
			}//for문 끝
		}
		//비즈니스로직(board와 file_tbl에 insert진행)
		int result = service.insertBoard(b, fileList);
		if(result == (fileList.size()+1)) {
			//insert가 총 첨부파일+1개만큼 일어남 -> board인서트(1번) + 첨부파일인서트(여러번)
			//result를 누계했으므로 
			return "board/boardList";
		}else {
			return "redirect:/";
		}
	}

Service

	public int insertBoard(Board b, ArrayList<FileVO> fileList) {
		//board insert, file_tbl insert - board테이블에 먼저 insert를 해줘야한다
		int result = dao.insertBoard(b);
		if(result > 0) {
			//실패하면 file_tbl insert를 할 필요가 없음 - 트랜잭션관리는 나중에
			//여기서 b.getBoardNo()는 0 - 화면에서 번호를 준게 아니라 insert하면서 시퀀스로 주었기때문에
			//보드번호를 조회해와야한다.
			//file_tbl에 insert를 하기 위해서는 boardNo가 필요 -> 방금 insert한 boardNo를 조회 -> 가장 최근의 board_no
			int boardNo = dao.selectBoardNo(); //최근걸 조회할 것이므로 매개변수 필요없음!
			//insert는 list에 담긴 갯수만큼 해줘야하므로 for문 사용
			for(FileVO file : fileList) {
				file.setBoardNo(boardNo);
				//최종적으로 잘 끝났는지 확인하기위해 결과를 result에 누계
				result += dao.insertFile(file);
			}
		}
		return result;
	}

Dao

- BOARD 테이블에 INSERT

	public int insertBoard(Board b) {
		String query = "insert into board values(board_seq.nextval, ?, ?, ?, to_char(sysdate, 'yyyy-mm-dd'))";
		Object[] params = {b.getBoardTitle(), b.getBoardWriter(), b.getBoardContent()};
		int result = jdbc.update(query, params);
		return result;
	}

- BOARD 테이블의 BOARD_NO를 찾기위한 SELECT

	public int selectBoardNo() {
		String query ="select max(board_no) from board"; //max : board_no중에 가장큰걸 가져와라는 의미
		//만약에 조회결과가 1행 1열인 경우 rowMapper를 만들기 너무 귀찮음
		//List list = jdbc.query(query, rowMapper);
		//jdbc에서 제공하는 queryForObject 이용
		int boardNo = jdbc.queryForObject(query, int.class); //쿼리, 내가받을 자료형
		return boardNo;
	}

- FILE_TBL 테이블에 INSERT

	public int insertFile(FileVO file) {
		String query = "insert into file_tbl values(file_seq.nextval, ?, ?, ?)";
		Object[] params = {file.getBoardNo(), file.getFilename(), file.getFilepath()};
		int result = jdbc.update(query, params);
		return result;
	}