[Web_JSP] 22

실습(MVC(board))

- [Web_JSP] 21 이어서

 

 

1. dBeaver에서 테이블 생성

create table tbl_reply(
	reply_number int unsigned auto_increment primary key,
	reply_content varchar(700),
	board_number int unsigned,
	member_number int unsigned,
	foreign key (board_number) references tbl_board(board_number) on delete cascade,
	foreign key (member_number) references tbl_member(member_number) on delete cascade
);

select * from tbl_reply;

 

 

 

2. ReplyVO.java

package com.board.app.domain.vo;

public class ReplyVO {
	
	private int replyNumber;
	private String replyContent;
	private int boardNumber;
	private int memberNumber;
	
	public ReplyVO() {;}

	public int getReplyNumber() {
		return replyNumber;
	}

	public void setReplyNumber(int replyNumber) {
		this.replyNumber = replyNumber;
	}
 
	public String getReplyContent() {
		return replyContent;
	}

	public void setReplyContent(String replyContent) {
		this.replyContent = replyContent;
	}

	public int getBoardNumber() {
		return boardNumber;
	}

	public void setBoardNumber(int boardNumber) {
		this.boardNumber = boardNumber;
	}

	public int getMemberNumber() {
		return memberNumber;
	}

	public void setMemberNumber(int memberNumber) {
		this.memberNumber = memberNumber;
	}
}

 

 

 

3. ReplyDTO.java

package com.board.app.domain.vo;

public class ReplyDTO {
	
	private int replyNumber;
	private String replyContent;
	private int boardNumber;
	private int memberNumber;
	private String memberId;
	
	public ReplyDTO() {;} 

	public int getReplyNumber() {
		return replyNumber;
	}

	public void setReplyNumber(int replyNumber) {
		this.replyNumber = replyNumber;
	}

	public String getReplyContent() {
		return replyContent;
	}

	public void setReplyContent(String replyContent) {
		this.replyContent = replyContent;
	}

	public int getBoardNumber() {
		return boardNumber;
	}

	public void setBoardNumber(int boardNumber) {
		this.boardNumber = boardNumber;
	}

	public int getMemberNumber() {
		return memberNumber;
	}

	public void setMemberNumber(int memberNumber) {
		this.memberNumber = memberNumber;
	}

	public String getMemberId() {
		return memberId;
	}

	public void setMemberId(String memberId) {
		this.memberId = memberId;
	}
}

 

 

 

4. ReplyMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http//mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="Reply">
	<!-- 댓글 목록 -->
	<select id="select" parameterType="_int" resultType="replyDTO">
		select reply_number, reply_content, member_id, board_number, r.member_number
		from tbl_reply r join tbl_member m
		on r.member_number = m.member_number and board_number = #{boardNumber}
		order by 1 desc
	</select>
	
	<!-- 댓글 등록 -->
	<insert id="insert" parameterType="replyVO">
		insert into tbl_reply (reply_content, board_number, member_number)
		values(#{replyContent}, #{boardNumber}, #{memberNumber})
	</insert>
	
	<!-- 댓글 삭제 -->
	<delete id="delete" parameterType="_int">
		delete from tbl_reply where reply_number = #{replyNumber}
	</delete>
	
	<!-- 댓글 수정 -->
	<update id="update" parameterType="replyVO">
		update tbl_reply
		set reply_content = #{replyContent}
		where reply_number = #{replyNumber}
	</update>
</mapper>

 

 

 

5. config.xml

<?xml version="1.0" encoding="UTF-8"?>
http://mybatis.org/dtd/mybatis-3-config.dtd">

<configuration>
	<settings>
		<setting name="mapUnderscoreToCamelCase" value="true"/>
	</settings>
	 
	<typeAliases>
		<typeAlias type="com.board.app.domain.vo.MemberVO" alias="memberVO"/>
		<typeAlias type="com.board.app.domain.vo.FileVO" alias="fileVO"/>
		<typeAlias type="com.board.app.domain.vo.BoardVO" alias="boardVO"/>
		<typeAlias type="com.board.app.domain.vo.ReplyVO" alias="replyVO"/>
		<typeAlias type="com.board.app.domain.vo.ReplyDTO" alias="replyDTO"/>
		<typeAlias type="com.board.app.domain.vo.BoardDTO" alias="boardDTO"/>
	</typeAliases>

	<environments default="mysql">
		<environment id="mysql">
			<transactionManager type="JDBC"/>
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
				<property name="url" value="jdbc:mysql://localhost:3306/board"/>
				<property name="username" value="root"/>
				<property name="password" value="1234"/>
			</dataSource>
		</environment>
	</environments>
	
	<mappers>
		<mapper resource="com/mybatis/mapper/MemberMapper.xml"/>
		<mapper resource="com/mybatis/mapper/BoardMapper.xml"/>
		<mapper resource="com/mybatis/mapper/FileMapper.xml"/>
		<mapper resource="com/mybatis/mapper/ReplyMapper.xml"/>
	</mappers>
	
</configuration>

 

 

 

6. ReplyDAO.java

package com.board.app.domain.dao;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import com.board.app.domain.vo.ReplyDTO;
import com.board.app.domain.vo.ReplyVO;
import com.mybatis.config.MyBatisConfig;

public class ReplyDAO { 
	SqlSessionFactory sqlSessionFactory = MyBatisConfig.getSqlSessionFactory();
	SqlSession sqlSession;
	
	public ReplyDAO() {
		sqlSession = sqlSessionFactory.openSession(true);
	}

	public List<ReplyDTO> getList(int boardNumber) {
		return sqlSession.selectList("Reply.select", boardNumber);
	} 
	
	public void insert(ReplyVO replyVO) {
		sqlSession.insert("Reply.insert", replyVO);
	}
	
	public void delete(int replyNumber) {
		sqlSession.delete("Reply.delete", replyNumber);
	}
	public void update(ReplyVO replyVO) {
		sqlSession.update("Reply.update", replyVO);
	}
	
}

 

 

 

7. ReplyListOk.java

package com.board.app.reply;

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

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject; 

import com.board.app.action.Action;
import com.board.app.action.ActionInfo;
import com.board.app.domain.dao.ReplyDAO;
import com.board.app.domain.vo.ReplyDTO;
import com.board.app.domain.vo.ReplyVO;

public class ReplyListOk implements Action {

	@Override
	public ActionInfo execute(HttpServletRequest req, HttpServletResponse resp) throws IOException {
		req.setCharacterEncoding("UTF-8");
		resp.setCharacterEncoding("UTF-8");
		
		PrintWriter out = resp.getWriter();
		
		ReplyDAO replyDAO = new ReplyDAO();
		JSONArray replyJSONs = new JSONArray();
		
		int boardNumber = Integer.parseInt(req.getParameter("boardNumber"));
		List<ReplyDTO> replies = replyDAO.getList(boardNumber);
		
		replies.forEach(reply -> {
			JSONObject replyJSON = new JSONObject();
			replyJSON.put("replyNumber", reply.getReplyNumber());
			replyJSON.put("boardNumber", reply.getBoardNumber());
			replyJSON.put("memberNumber", reply.getMemberNumber());
			replyJSON.put("memberId", reply.getMemberId());
			replyJSON.put("replyContent", reply.getReplyContent());
			
			replyJSONs.add(replyJSON);
		});
		
		out.print(replyJSONs.toJSONString());
		out.close();
		return null;
	}

}