[Spring] Q&A 게시판 만들기 (1) - DB 및 DTO, DAO, Mapper

Q&A 게시판 만들기

1. DB - QNA 테이블 생성 

CREATE TABLE QNA (
    NUM NUMBER CONSTRAINT QNA_NUM_PK PRIMARY KEY,
    TITLE VARCHAR2(100) NOT NULL,
    CONTENTS VARCHAR2(4000),
    WRITER VARCHAR2(100) NOT NULL,
    REGDATE DATE,
    HIT NUMBER,
    REF NUMBER,
    STEP NUMBER,
    DEPTH NUMBER
);

- 생성된 QNA 테이블

SELECT * FROM QNA

- SEQUENCE 생성 : num에 사용

CREATE SEQUENCE QNA_SEQ 
 INCREMENT BY 1
 START WITH 1;

---

2. 파일 추가

1. QnaDTO class 생성
2. QnaDAO class 생성
3. qnaMapper.xml 생성

3. Test Case 생성

1. 테스트 케이스로 insert 등 확인

---

<Source Code>

QnaDTO 

- getter / setter, 기본 생성자

package com.spring.s1.qna;
 
import java.util.Date;
 
public class QnaDTO {
    
    private long num;
    private String title;
    private String contents;
    private String writer;
    private Date regDate;
    private Integer hit;
    private Integer ref;
    private Integer step;
    private Integer depth;
    
    public QnaDTO() {
        // TODO Auto-generated constructor stub
    }
 
    public long getNum() {
        return num;
    }
 
    public void setNum(long num) {
        this.num = num;
    }
 
    public String getTitle() {
        return title;
    }
 
    public void setTitle(String title) {
        this.title = title;
    }
 
    public String getContents() {
        return contents;
    }
 
    public void setContents(String contents) {
        this.contents = contents;
    }
 
    public String getWriter() {
        return writer;
    }
 
    public void setWriter(String writer) {
        this.writer = writer;
    }
 
    public Date getRegDate() {
        return regDate;
    }
 
    public void setRegDate(Date regDate) {
        this.regDate = regDate;
    }
 
    public Integer getHit() {
        return hit;
    }
 
    public void setHit(Integer hit) {
        this.hit = hit;
    }
 
    public Integer getRef() {
        return ref;
    }
 
    public void setRef(Integer ref) {
        this.ref = ref;
    }
 
    public Integer getStep() {
        return step;
    }
 
    public void setStep(Integer step) {
        this.step = step;
    }
 
    public Integer getDepth() {
        return depth;
    }
 
    public void setDepth(Integer depth) {
        this.depth = depth;
    }
    
    
}
 

 

QnaDAO

package com.spring.s1.qna;
 
import java.util.List;
 
import org.apache.ibatis.session.SqlSession;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
 
@Repository
public class QnaDAO {
 
    @Autowired
    private SqlSession sqlSession;
    private final String NAMESPACE = "com.spring.s1.qna.QnaDAO.";
    
    //Detail
    public QnaDTO detail(QnaDTO qnaDTO) throws Exception {
        return sqlSession.selectOne(NAMESPACE + "detail", qnaDTO);
    }
    
    //List
    public List<QnaDTO> list() throws Exception {
        return sqlSession.selectList(NAMESPACE + "list");
    }
    
    //Insert(Add)
    public int add(QnaDTO qnaDTO) throws Exception {
        return sqlSession.insert(NAMESPACE + "add", qnaDTO);
    }
    
    //update
    public int update(QnaDTO qnaDTO) throws Exception {
        return sqlSession.update(NAMESPACE + "update", qnaDTO);
    }
    
    //Delete
    public int delete(QnaDTO qnaDTO) throws Exception {
        return sqlSession.delete(NAMESPACE + "delete", qnaDTO);
    }
}
 

 

 

qnaMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    
<mapper namespace="com.spring.s1.qna.QnaDAO">
    <select id="list" resultType="QnaDTO">
        SELECT * FROM QNA
    </select>
    
    <select id="detail" resultType="QnaDTO">
        SELECT * FROM QNA WHERE num = #{num}
    </select>
    
    <insert id="add" parameterType="QnaDTO">
        INSERT INTO QNA
        VALUES(QNA_SEQ.nextval, #{title}, #{contents}, #{writer}, SYSDATE, 0, 0, 0, 0)
    </insert>
 
    <update id="update" parameterType="QnaDTO">
        UPDATE QNA
        SET TITLE = #{title}, CONTENTS = #{contents}            
        WHERE NUM = #{num}
    </update>
 
    <delete id="delete" parameterType="QnaDTO">
        DELETE QNA WHERE NUM = #{num}
    </delete>
</mapper>

 

QnaDAOTest

package com.spring.s1.qna;
 
import static org.junit.Assert.*;
 
import java.util.List;
 
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
 
import com.spring.s1.MyJunitTest;
 
public class QnaDAOTest extends MyJunitTest{
 
    @Autowired
    private QnaDAO qnaDAO;
    
    @Test
    public void check() {
        assertNotNull(qnaDAO);
    }
 
    @Test
    public void listTest() throws Exception{
        List<QnaDTO> ar = qnaDAO.list();
        
        assertNotEquals(0, ar.size());
    }
    
    @Test
    public void detailTest() throws Exception {
        QnaDTO qnaDTO = new QnaDTO();
        qnaDTO.setNum(1L);
        qnaDTO = qnaDAO.detail(qnaDTO);
        assertNotNull(qnaDTO);
    }
    
    //@Test
    public void addTest() throws Exception {
        QnaDTO qnaDTO = new QnaDTO();
        int result = 0;
        
        for(int i = 2; i <= 100; i++) {
            qnaDTO.setTitle("title" + i);
            qnaDTO.setContents("content" + i);
            qnaDTO.setWriter("writer" + i);
 
            result = qnaDAO.add(qnaDTO);
        }
        
        assertEquals(100, result);
    }
    
    @Test
    public void deleteTest() throws Exception {
        QnaDTO qnaDTO = new QnaDTO();
        qnaDTO.setNum(101L);
        int result = qnaDAO.delete(qnaDTO);
        
        assertEquals(1, result);
    }
}