[Spring] 설문 투표 및 결과 보기

이번에는 투표를 할 수 있는 설문지를 만들어 보자.

---

SurveyDTO

import org.springframework.web.multipart.MultipartFile;

// 	TABLE : SURVEY
//	IDX     NOT NULL NUMBER        
//	TITLE   NOT NULL VARCHAR2(500) 
//	WRITER           VARCHAR2(500) 
//	OPTION1 NOT NULL VARCHAR2(500) 
//	OPTION2 NOT NULL VARCHAR2(500) 
//	IMAGE1  NOT NULL VARCHAR2(500) 
//	IMAGE2  NOT NULL VARCHAR2(500) 

public class SurveyDTO {

	private int idx;
	private String title;
	private String writer;
	private String option1;
	private String option2;
	private String image1;
	private String image2;
	
	private MultipartFile upload1;
	private MultipartFile upload2;
	
	private int responseCount;	// 설문에 참여한 인원의 수
	private int choice1Count;
	private int choice2Count;
	private double choice1Rate;
	private double choice2Rate;
	
	public double getChoice1Rate() {
		return choice1Rate;
	}
	public void setChoice1Rate(double choice1Rate) {
		this.choice1Rate = choice1Rate;
	}
	public double getChoice2Rate() {
		return choice2Rate;
	}
	public void setChoice2Rate(double choice2Rate) {
		this.choice2Rate = choice2Rate;
	}
	public int getChoice1Count() {
		return choice1Count;
	}
	public void setChoice1Count(int choice1Count) {
		this.choice1Count = choice1Count;
	}
	public int getChoice2Count() {
		return choice2Count;
	}
	public void setChoice2Count(int choice2Count) {
		this.choice2Count = choice2Count;
	}
	public int getIdx() {
		return idx;
	}
	public void setIdx(int idx) {
		this.idx = idx;
	}
	public String getTitle() {
		return title;
	}
	public void setTitle(String title) {
		this.title = title;
	}
	public String getWriter() {
		return writer;
	}
	public void setWriter(String writer) {
		this.writer = writer;
	}
	public String getOption1() {
		return option1;
	}
	public void setOption1(String option1) {
		this.option1 = option1;
	}
	public String getOption2() {
		return option2;
	}
	public void setOption2(String option2) {
		this.option2 = option2;
	}
	public String getImage1() {
		return image1;
	}
	public void setImage1(String image1) {
		this.image1 = image1;
	}
	public String getImage2() {
		return image2;
	}
	public void setImage2(String image2) {
		this.image2 = image2;
	}
	public MultipartFile getUpload1() {
		return upload1;
	}
	public void setUpload1(MultipartFile upload1) {
		this.upload1 = upload1;
	}
	public MultipartFile getUpload2() {
		return upload2;
	}
	public void setUpload2(MultipartFile upload2) {
		this.upload2 = upload2;
	}
	public int getResponseCount() {
		return responseCount;
	}
	public void setResponseCount(int responseCount) {
		this.responseCount = responseCount;
	}

}

 

SurveyController

@RequestMapping (@GetMapping, @PostMapping)
: 특정 주소, 특정 메서드로 요청을 받으면 자동으로 실행되는 이벤트 함수의 성격을 가진다.

 

@ExceptionHandler
: 예외가 발생하면 @ExceptionHandler 어노테이션이 붙은 함수가 자동으로 실행된다.

이때, 발생하는 예외의 타입에 따라 서로 다른 함수를 실행할 수 있다.

컨트롤러 내부에서도  ExceptionHandler 를 작성할 수 있지만
컨트롤러는 본래, 요청에 따른 처리를 작성하는 클래스이므로,
별도의 클래스를 만들어서 예외만 전문적으로 처리하는 스프링 빈을 작성할 수도 있다.

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.itbank.model.ChoiceDTO;
import com.itbank.model.SurveyDTO;
import com.itbank.service.SurveyService;

@Controller
@RequestMapping("/survey")
public class SurveyController {
	
	@Autowired private SurveyService service;
	
	@ExceptionHandler(DuplicateKeyException.class)
	public ModelAndView dupKey() {
		ModelAndView mav = new ModelAndView("alert");
		mav.addObject("msg", "이미 참여한 투표입니다");
		return mav;
	}

	@GetMapping("/add")
	public void add() {}
	
	@PostMapping("/add")
	public String add(SurveyDTO dto) {
		int row = service.add(dto);
		System.out.println(row != 0 ? "등록 성공" : "등록 실패");
		return "redirect:/";
	}
	
	@GetMapping("/list")
	public ModelAndView list() {
		ModelAndView mav = new ModelAndView();
		List<SurveyDTO> list = service.getList();
		mav.addObject("list", list);
		return mav;
	}
	
	@GetMapping("/vote/{idx}")
	public ModelAndView view(@PathVariable("idx") int idx) {
		ModelAndView mav = new ModelAndView("/survey/vote");
		SurveyDTO dto = service.getSurvey(idx);
		mav.addObject("dto", dto);
		return mav;
	}
	
	@PostMapping("/vote/{idx}")
	public String view(ChoiceDTO dto) {
		int row = service.addChoice(dto);
		System.out.println(row != 0 ? "응답 성공" : "응답 실패");
		return "redirect:/survey/result/{idx}";
	}
	
	@GetMapping("/result/{idx}")
	public ModelAndView result(@PathVariable("idx") int idx) {
		ModelAndView mav = new ModelAndView("/survey/result");
		SurveyDTO dto = service.getResult(idx);
		mav.addObject("dto", dto);
		return mav;
	}
	
}

 

ServeyService

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.itbank.component.FileComponent;
import com.itbank.model.ChoiceDTO;
import com.itbank.model.SurveyDTO;
import com.itbank.repository.SurveyDAO;

@Service
public class SurveyService {
	
	@Autowired private SurveyDAO dao;
	@Autowired private FileComponent fileComponent;

	public int add(SurveyDTO dto) {
		String image1 = fileComponent.upload(dto.getUpload1());
		String image2 = fileComponent.upload(dto.getUpload2());
		dto.setImage1(image1);
		dto.setImage2(image2);
		return dao.insert(dto);
	}

	public List<SurveyDTO> getList() {
		return dao.selectList();
	}

	public SurveyDTO getSurvey(int idx) {
		return dao.selectOne(idx);
	}

	public int addChoice(ChoiceDTO dto) {
		return dao.insertChoice(dto);
	}

	public SurveyDTO getResult(int idx) {
		return dao.selectResult(idx);
	}

}

 

SurveyDAO

import java.util.List;

import com.itbank.model.ChoiceDTO;
import com.itbank.model.SurveyDTO;

public interface SurveyDAO {

	int insert(SurveyDTO dto);

	List<SurveyDTO> selectList();

	SurveyDTO selectOne(int idx);

	int insertChoice(ChoiceDTO dto);

	SurveyDTO selectResult(int idx);

}

 

survey-mapper.xml

SurveyDAO의 상세 쿼리문

<?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="com.itbank.repository.SurveyDAO">
	
	<insert id="insert" parameterType="survey">
		insert into survey (writer, title, option1, option2, image1, image2)
			values (
				#{writer},
				#{title},
				#{option1},
				#{option2},
				#{image1},
				#{image2}
			)
	</insert>
	
	<select id="selectList" resultType="survey">
		select 
		    survey.*,
		    (select count(*) from survey_choice where survey_idx = survey.idx) as responseCount
		        from survey order by survey.idx desc
	</select>
	
	<select id="selectOne" parameterType="int" resultType="survey">
		select * from survey
			where
				idx = #{idx}
	</select>
	
	<insert id="insertChoice" parameterType="choice">
		insert into survey_choice (writer, survey_idx, choice) 
			values (#{writer}, #{survey_idx}, #{choice})
	</insert>
	
	<select id="selectResult" parameterType="int" resultType="survey">
		select 
		    A.*, 
		    trunc(choice1Count * 100 / responseCount, 2) as choice1Rate, 
		    trunc(choice2Count * 100 / responseCount, 2) as choice2Rate
		    from (
		    select 
		    	S.*,
		        (select count(*) from survey_choice where survey_idx = #{idx}) as responseCount,
		        (select count(*) from survey_choice where survey_idx = #{idx} and choice = 1) as choice1Count,    
		        (select count(*) from survey_choice where survey_idx = #{idx} and choice = 2) as choice2Count
		            from survey S
		            where S.idx = #{idx}
		    ) A
	</select>
	
</mapper>

 

add.jsp

설문조사 추가 , FileComponent 를 이용하여 이미지 업로드도 가능하도록 했다.

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ include file="../header.jsp" %>
<style>
	input[type="text"] {
		all: unset;
		border-bottom: 1px solid #dadada;
		padding: 10px 0;
		margin: 10px auto;
	}
	input[name="title"] {
		font-size: 24px;	
		width: 500px;
	}
	div.flex {
		display: flex;
		justify-content: space-around;
		width: 800px;
		margin: 20px auto;
	}
	form div.flex > div {
		box-shadow: 5px 5px 5px grey;
		background-color: #eee;
		padding: 20px;
	}
	form > *:not(.flex) {
		display: flex;
		justify-content: center;
	} 
	
</style>
<h3>추가</h3>

<form method="POST" enctype="multipart/form-data">
	<div><input type="text" name="title" placeholder="설문 제목" required autofocus></div>
	<div class="flex">
		<div>
			<div><input type="file" name="upload1" required></div>
			<div><input type="text" name="option1" placeholder="문항1" required></div>
		</div>
		<div>
			<div><input type="file" name="upload2" required></div>
			<div><input type="text" name="option2" placeholder="문항2" required></div>
		</div>
	</div>
	<input type="hidden" name="writer" value="${login.userid }" readonly>
	<p><input type="submit"></p>
</form>

</body>
</html>

 

list.jsp

설문 리스트

<style>
	table.surveyList {
		border: 1px solid black;
		border-collapse: collapse;
		width: 800px;
		margin: 20px auto;
	}
	td, th {
		padding: 5px 10px;
	}
	tr {
		border-bottom: 1px solid grey;
	}
	table tr:first-child {
		background-color: #dadada;
	}
	.sb {
		display: flex;
		justify-content: space-between;
	}
</style>

<h3>설문 목록</h3>

<table class="surveyList">
	<tr>
		<th>번호</th>
		<th>제목</th>
		<th>작성자</th>
		<th>참여인원</th>
	</tr>
	<c:forEach var="dto" items="${list }">
	<tr>
		<td>${dto.idx }</td>
		<td>
			<div class="sb">
				<div>${dto.title }</div>
				<div>
					<a href="${cpath }/survey/vote/${dto.idx}"><button>설문참여</button></a>
					<a href="${cpath }/survey/result/${dto.idx}"><button>결과보기</button></a>
				</div>
			</div>
		</td>
		<td>${dto.writer }</td>
		<td>${dto.responseCount }</td>
	</tr>
	</c:forEach>
</table>

</body>
</html>

 

vote.jsp

설문 참여

input radio 와 value 를 이용하여, choice 값을 선택할 수 있도록 한다.

설문 참여를 하면, 결과값이 insert 되어야한다.

<style>
	div.flex {
		display: flex;
		justify-content: space-around;
		width: 800px;
		margin: 20px auto;
	}
	form div.flex > div {
		box-shadow: 5px 5px 5px grey;
		background-color: #eee;
		padding: 20px;
		border: 3px solid transparent; 
	}
	form > *:not(.flex) {
		display: flex;
		justify-content: center;
	} 
	form div.flex > div.selected {
		border: 3px solid lime; 
	}
	input[type="radio"] {
		display: none;
	}
</style>

<h3>${dto.title }</h3>

<form method="POST">
	<input type="hidden" name="writer" value="${login.userid }">
	<input type="hidden" name="survey_idx" value="${dto.idx }">
	<div class="flex">
		<div>
			<h4>${dto.option1 }</h4>
			<label>
				<img src="${cpath }/upload/${dto.image1}" height="200">
				<input type="radio" name="choice" value="1">
			</label>
		</div>
		<div>
			<h4>${dto.option2 }</h4>
			<label>
				<img src="${cpath }/upload/${dto.image2}" height="200">
				<input type="radio" name="choice" value="2">
			</label>
		</div>
	</div>
	<p align="center"><input type="submit"></p>
</form>

<script>
	const itemList = document.querySelectorAll('.flex > div')

	itemList.forEach(item => item.onclick = function() {
		itemList.forEach(e => e.classList.remove('selected'))
		item.classList.add('selected')
	})
</script>

</body>
</html>

 

result.jsp

설문 결과

 

[  choiceCount 구하는 쿼리문 ]

->   select count(*) from survey_choice where survey_idx = #{idx} and choice = 1) as choice1Count

 

[ choiceRate 구하는 쿼리문 ]

->   trunc(choice1Count * 100 / responseCount, 2) as choice1Rate

<%@ include file="../header.jsp" %>

<style>
	div.flex {
		display: flex;
		justify-content: space-around;
		width: 800px;
		margin: 20px auto;
	}
</style>

<h3>${dto.title }</h3>

<div class="flex">
		<div>
			<h4>${dto.option1 }</h4>
			<img src="${cpath }/upload/${dto.image1}" height="200">
			<div>득표 수 : ${dto.choice1Count } (${dto.choice1Rate }%)</div>
		</div>
		<div>
			<h4>${dto.option2 }</h4>
			<img src="${cpath }/upload/${dto.image2}" height="200">
			<div>득표 수 : ${dto.choice2Count } (${dto.choice2Rate }%)</div>
		</div>
	</div>

</body>
</html>

 

---

ExceptionHandler가 초면이라 너무 생소하였다.

알고보니까 JdbcTemplate 가 있어서 사용할 수 있는 것이었다.

 

비율을 구하는 쿼리문을 작성하는 것이 조금 어려웠다.