Back-end/JSP

[JSP] JSP를 이용하여 투표 페이지 만들기

peridott 2024. 7. 24. 18:10

vote_table.sql

CREATE TABLE votelist (
  num number PRIMARY KEY,
  question varchar2(200) NOT NULL,
  sdate date,
  edate date,
  wdate date,
  type number DEFAULT 1 NOT NULL,
  active number DEFAULT 1
);

COMMENT ON COLUMN VOTELIST.NUM IS '설문번호';
COMMENT ON COLUMN VOTELIST.QUESTION IS '설문내용';
COMMENT ON COLUMN VOTELIST.SDATE IS '투표시작날짜';
COMMENT ON COLUMN VOTELIST.EDATE IS '투표종료날짜';
COMMENT ON COLUMN VOTELIST.WDATE IS '설문작성날짜';
COMMENT ON COLUMN VOTELIST.TYPE IS '중복투표허용여부';
COMMENT ON COLUMN VOTELIST.ACTIVE IS '설문활성화여부';

CREATE TABLE voteitem (
  listnum number,
  itemnum number,
  item varchar2(50),
  count number DEFAULT 0,
  PRIMARY KEY (listnum, itemnum)
); 

COMMENT ON COLUMN VOTEITEM.LISTNUM IS '답변이소속된설문번호';
COMMENT ON COLUMN VOTEITEM.ITEMNUM IS '답변번호';
COMMENT ON COLUMN VOTEITEM.ITEM IS '답변내용';
COMMENT ON COLUMN VOTEITEM.COUNT IS '투표수';

CREATE SEQUENCE SEQ_VOTE;

 

 

VoteItem.java

package ch09;

import java.util.Arrays;

public class VoteItem {
	private int listnum;
	private int itemnum;
	private String[] item;
	private int count;
	
	public VoteItem() {
	}

	public VoteItem(int listnum, int itemnum, String[] item, int count) {
		super();
		this.listnum = listnum;
		this.itemnum = itemnum;
		this.item = item;
		this.count = count;
	}

	public int getListnum() {
		return listnum;
	}

	public void setListnum(int listnum) {
		this.listnum = listnum;
	}

	public int getItemnum() {
		return itemnum;
	}

	public void setItemnum(int itemnum) {
		this.itemnum = itemnum;
	}

	public String[] getItem() {
		return item;
	}

	public void setItem(String[] item) {
		this.item = item;
	}

	public int getCount() {
		return count;
	}

	public void setCount(int count) {
		this.count = count;
	}

	@Override
	public String toString() {
		return "VoteItem [listnum=" + listnum + ", itemnum=" + itemnum + ", item=" + Arrays.toString(item) + ", count="
				+ count + "]";
	}
}

 

 

VoteList.java

package ch09;

public class VoteList {
	private int num;
	private String question;
	private String sdate;
	private String edate;
	private String wdate;
	private int type;
	private int active;
	
	public VoteList() {
	}
	public VoteList(int num, String question, String sdate, String edate, String wdate, int type, int active) {
		super();
		this.num = num;
		this.question = question;
		this.sdate = sdate;
		this.edate = edate;
		this.wdate = wdate;
		this.type = type;
		this.active = active;
	}
	public int getNum() {
		return num;
	}
	public void setNum(int num) {
		this.num = num;
	}
	public String getQuestion() {
		return question;
	}
	public void setQuestion(String question) {
		this.question = question;
	}
	public String getSdate() {
		return sdate;
	}
	public void setSdate(String sdate) {
		this.sdate = sdate;
	}
	public String getEdate() {
		return edate;
	}
	public void setEdate(String edate) {
		this.edate = edate;
	}
	public String getWdate() {
		return wdate;
	}
	public void setWdate(String wdate) {
		this.wdate = wdate;
	}
	public int getType() {
		return type;
	}
	public void setType(int type) {
		this.type = type;
	}
	public int getActive() {
		return active;
	}
	public void setActive(int active) {
		this.active = active;
	}
	@Override
	public String toString() {
		return "VoteList [num=" + num + ", question=" + question + ", sdate=" + sdate + ", edate=" + edate + ", wdate="
				+ wdate + ", type=" + type + ", active=" + active + "]";
	}
}

 

 

VoteDao.java

package ch09;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

public class VoteDao {
	private DBConnectionMgr pool;
	Connection con = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	String sql = null;
	
	public VoteDao() {
		pool = DBConnectionMgr.getInstance();
	}
	
	// 설문 등록하기
	public boolean voteInsert(VoteList vlist, VoteItem vitem) {
		boolean flag = false;
		
		try {
			con = pool.getConnection();
			sql = "insert into votelist values(seq_vote.nextval,?,?,?,sysdate,?,default)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, vlist.getQuestion());
			pstmt.setString(2, vlist.getSdate());
			pstmt.setString(3, vlist.getEdate());
			pstmt.setInt(4, vlist.getType());
			
			int result = pstmt.executeUpdate();
			
			int result2 = 0;
			if(result == 1) {
				sql = "insert into voteitem values(seq_vote.currval,?,?,default)";
				pstmt = con.prepareStatement(sql);
				
				String item[] = vitem.getItem();
				for(int i=0; i<item.length; i++) {
					if(item[i] == null || item[i].equals(""))
						break;
					pstmt.setInt(1, i);
					pstmt.setString(2, item[i]);
					result2 = pstmt.executeUpdate();
				}
			}
			
			if(result2 == 1)
				flag = true;
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con);
		}
		return flag;
	}
	
	// 설문폼(투표하기)에 넣을 질문1개 가져오기
	public VoteList getOneVote(int num) {
		VoteList vlist = new VoteList();
		
		try {
			con = pool.getConnection();
			if(num == 0)
				sql = "select * from votelist order by num desc";
			else
				sql = "select * from votelist where num=" + num;
			
			rs = con.createStatement().executeQuery(sql);
			if(rs.next()) {
				vlist.setQuestion(rs.getString("question"));
				vlist.setType(rs.getInt("Type"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con);
		}
		return vlist;
	}
	
	public int getMaxNum() {
		int max = 0;
		try {
			con = pool.getConnection();
			sql = "select max(num) from votelist";
			rs = con.createStatement().executeQuery(sql);
			if(rs.next()) {
				max = rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con);
		}
		return max;
	}
	
	// 설문폼(투표하기)에 넣을 item들 가져오기
	public ArrayList<String> getItem(int num) {
		ArrayList<String> alist = new ArrayList<String>();
		
		try {
			con = pool.getConnection();
			if(num == 0) {
				num = getMaxNum();
			}
			sql = "select item from voteitem where listnum=" + num;
			rs = con.createStatement().executeQuery(sql);
			while(rs.next()) {
				alist.add(rs.getString(1));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con);
		}
		return alist;
	}
	
	public ArrayList<VoteList> getList() {
		ArrayList<VoteList> alist = new ArrayList<VoteList>();
		try {
			con = pool.getConnection();
			sql = "select * from votelist order by num desc";
			rs = con.createStatement().executeQuery(sql);
			while(rs.next()) {
				VoteList vlist = new VoteList();
				vlist.setNum(rs.getInt(1));
				vlist.setQuestion(rs.getString(2));
				vlist.setSdate(rs.getString(3));
				vlist.setEdate(rs.getString(4));
				alist.add(vlist);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return alist;
	}
	
	// 투표시 count증가
	public boolean updateCount(int num, String[] itemnum) {
		boolean flag = false;
		
		try {
			con = pool.getConnection();
			sql = "update voteitem set count = count+1 where listnum=? and itemnum=?";
			pstmt = con.prepareStatement(sql);
			if(num == 0)
				num = getMaxNum();
			
			for(int i=0; i<itemnum.length; i++) {
				if(itemnum[i] == null || itemnum[i].equals(""))
					break;
				
				pstmt.setInt(1, num);
				pstmt.setInt(2, Integer.parseInt(itemnum[i]));
				// int result = pstmt.executeUpdate();
				if(pstmt.executeUpdate() == 1)
					flag = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con);
		}
		return flag;
	}
	
	// listnum에 해당하는 전체 count가져오기
	public int sumCount(int num) {
		int count = 0;
		
		try {
			con = pool.getConnection();
			sql = "select sum(count) from voteitem where listnum=?";
			pstmt = con.prepareStatement(sql);
			if(num == 0)
				pstmt.setInt(1, getMaxNum());
			else
				pstmt.setInt(1, num);
			
			rs = pstmt.executeQuery();
			if(rs.next())
				count = rs.getInt(1);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con);
		}
		return count;
	}
	
	// listnum에 해당하는 각 item의 count 얻어오기
	public ArrayList<VoteItem> getView(int num) {
		ArrayList<VoteItem> alist = new ArrayList<VoteItem>();
		
		try {
			con = pool.getConnection();
			sql = "select item, count from voteitem where listnum=?";
			pstmt = con.prepareStatement(sql);
			
			if(num == 0)
				pstmt.setInt(1, getMaxNum());
			else
				pstmt.setInt(1, num);
			
			rs = pstmt.executeQuery();
			while(rs.next()) {
				VoteItem vitem = new VoteItem();
				String item[] = new String[1];
				item[0] = rs.getString(1);
				
				vitem.setItem(item);
				vitem.setCount(rs.getInt(2));
				alist.add(vitem);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			pool.freeConnection(con);
		}
		return alist;
	}
}

 

 

voteForm.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" %>
<%@ page import="ch09.*, java.util.*" %>
<jsp:useBean id="vDao" class="ch09.VoteDao" />
<%
	int num = 0;
	if(!(request.getParameter("num")==null || request.getParameter("num").equals(""))) {
		num = Integer.parseInt(request.getParameter("num"));
	}
	VoteList vlist = vDao.getOneVote(num);
	ArrayList<String> vItem = vDao.getItem(num);

	int type = vlist.getType();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" ></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
	* {margin: 0 auto;}
	.voteFrom {width:400px;}
	h2, h5{text-align:center; }
	.m50 {margin-top:50px;}
	table {margin-top: 30px;}
</style>
</head>
<body>
	<div class="voteFrom">
		<h5 class="m50">설문폼</h5>

		<form action="voteFormProc.jsp" method="post">
			<table class="table">
				<tr>
					<td>Q : <%=vlist.getQuestion() %></td>
				</tr>
				<tr>
					<td style="padding-left:30px;">
					<%
					for(int i=0; i<vItem.size(); i++) {
						String itemList = vItem.get(i);
						if(type == 1) {
							out.print("<p><input type='checkbox' name='itemnum' value='" + i + "'>");
						} else {
							out.print("<p><input type='radio' name='itemnum' value='" + i + "'>");
						}
						out.print(itemList + "<p/>");
					}
					%>
					</td>
				</tr>
				<tr>
					<td align="center">
						<input type="submit" value=" 투 표 ">&emsp;&emsp;
						<input type="button" value=" 결 과 " onclick="window.open('voteView.jsp?num=<%=num%>', 'voteView', 'width=600, height=400')">
					</td>
				</tr>
			</table>
			<input type="hidden" name="num" value="<%=num %>" >
		</form>
	</div>
</body>
</html>

 

 

voteFormProc.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:useBean id="vDao" class="ch09.VoteDao" />
<%
	int num = Integer.parseInt(request.getParameter("num"));
	String[] itemNum = request.getParameterValues("itemnum");
	
	boolean flag = vDao.updateCount(num, itemNum);
	String msg = "투표가 등록되지 않았습니다.";
	if(flag) {
		msg = "투표가 정상적으로 등록되었습니다.";
	}
%>
<script>
	alert("<%=msg %>");
	location.href = "voteList.jsp?num=<%=num%>";
</script>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

</body>
</html>

 

 

voteInsert.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>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" ></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
	* {margin: 0 auto;}
	div {width:600px;}
	h2, h5{text-align:center; }
	.m50 {margin-top:50px;}
	.m30 {margin-top:30px;}
	table {margin-top: 30px; width:600px;}
</style>
</head>
<body>
	<div>
		<h2 class="m50">투표 프로그램</h2>
		<hr>
			
		<h5 class="m30">설문작성</h5>
		<hr>
		
		<form action="voteInsertProc.jsp" method="post">
			<table class="table">
				<tr>
					<td>질문</td>
					<td colspan="2">q:<input name="question" size="57"></td>
				</tr>
				<tr>
					<td rowspan="7">항목</td>
				<%
					for(int i=1; i<=4; i++) {
						out.print("<td>" + (i*2-1) + ":<input name='item'></td>");
						out.print("<td>" + (i*2) + ":<input name='item'></td>");
						out.print("</tr>");
						if(i<4)
							out.print("<tr>");
					}
				%>
				<!--	
					<td>1:<input name="item"></td>
					<td>2:<input name="item"></td>
				</tr>
				<tr>
					<td>3:<input name="item"></td>
					<td>4:<input name="item"></td>
				</tr>
				<tr>
					<td>5:<input name="item"></td>
					<td>6:<input name="item"></td>
				</tr>
				<tr>
					<td>7:<input name="item"></td>
					<td>8:<input name="item"></td>
				</tr>
				-->
				<tr>
					<td>시작일</td>
					<td>
						<select name = "sdateY">
						<%
							for(int i=2024; i<=2030; i++) {
								out.print("<option value='" + i + "'>" + i);
							}
						%>
						</select>년&ensp;
						<select name = "sdateM">
						<%
							for(int i=1; i<=12; i++) {
								out.print("<option value='" + i + "'>" + i);
							}
						%>
						</select>월&ensp;
						<select name = "sdateD">
						<%
							for(int i=1; i<=31; i++) {
								out.print("<option value='" + i + "'>" + i);
							}
						%>
						</select>일
					</td>
				</tr>
				<tr>
					<td>종료일</td>
					<td>
						<select name = "edateY">
						<%
							for(int i=2024; i<=2030; i++) {
								out.print("<option value='" + i + "'>" + i);
							}
						%>
						</select>년&ensp;
						<select name = "edateM">
						<%
							for(int i=1; i<=12; i++) {
								out.print("<option value='" + i + "'>" + i);
							}
						%>
						</select>월&ensp;
						<select name = "edateD">
						<%
							for(int i=1; i<=31; i++) {
								out.print("<option value='" + i + "'>" + i);
							}
						%>
						</select>일
					</td>
				</tr>
				<tr>
					<td>이중답변</td>
					<td>
						<input type="radio" name="type" value="1" checked>yes&emsp;
						<input type="radio" name="type" value="0">no
					</td>
				</tr>
				<tr>
					<td colspan="3" align="center">
						<input type="submit" value="작성하기">&emsp;
						<input type="reset" value="초기화">&emsp;
						<input type="button" value="리스트보기" onclick="location.href='voteList.jsp'">
					</td>
				</tr>
			</table>
		</form>
	</div>
</body>
</html>

 

 

voteInsertProc.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<jsp:useBean id="vDao" class="ch09.VoteDao"/> 
<jsp:useBean id="vList" class="ch09.VoteList"/>
<jsp:setProperty name="vList" property="*" />
<jsp:useBean id="vItem" class="ch09.VoteItem"/> 
<jsp:setProperty name="vItem" property="*" />
<%
	String sdate = request.getParameter("sdateY") + "-"
				 + request.getParameter("sdateM") + "-"
				 + request.getParameter("sdateD");
	String edate = request.getParameter("edateY") + "-"
			 	 + request.getParameter("edateM") + "-"
			 	 + request.getParameter("edateD");
	
	vList.setSdate(sdate);
	vList.setEdate(edate);
	
	boolean result = vDao.voteInsert(vList, vItem);
	
	String msg = "설문 추가에 실패하였습니다.";
	String location = "voteInsert.jsp";
	if(result) {
		msg = "설문이 추가 되었습니다.";
		location = "voteList.jsp";
	}
%>
<script>
	alert("<%=msg %>");
	location.href = "<%=location %>";
</script>  
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>

</body>
</html>

 

 

voteList.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.*, ch09.*" %>
<jsp:useBean id="vDao" class="ch09.VoteDao" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" ></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<style>
	* {margin: 0 auto;}
	div {width:800px;}
	h2, h5, .cen {text-align:center; }
	.m50 {margin-top:50px;}
	.m30 {margin-top:30px;}
	table {margin-top: 30px; width:800px;}
	table th {text-align:center;}
	.a {text-decoration:none; color:black; cursor:pointer;}
</style>
</head>
<body>
	<div>
		<h2 class="m50">투표 프로그램</h2>
		<hr>
			
		<jsp:include page="voteForm.jsp" />
		<hr>
		
		<h5 class="m30">설문리스트</h5>
		<table class="table">
			<tr>
				<th>번호</th>
				<th>제목</th>
				<th>시작일~종료일</th>
			</tr>
		<%
			ArrayList<VoteList> alist = vDao.getList();
			
			for(int i=0; i<alist.size(); i++) {
				VoteList vlist = alist.get(i);
				String sdate = vlist.getSdate().substring(0,10);
				String edate = vlist.getEdate().substring(0,10);
		%>
			<tr>
				<td class="cen"><%=vlist.getNum() %></td>
				<td><a href="voteList.jsp?num=<%=vlist.getNum() %>" class="a"><%=vlist.getQuestion() %></a>
				<td class="cen"><%=sdate %> ~ <%=edate %><td>
			</tr>
		<%
			}
		%>
			<tr>
				<td colspan="3" align="right"><a href="voteInsert.jsp" class="a">설문 작성하기</a><td>
			</tr>
		</table>
	</div>
</body>
</html>

 

 

voteView.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.util.*, ch09.*" %>
<jsp:useBean id="vDao" class="ch09.VoteDao" />
<%
	int num = 0;
	if(!(request.getParameter("num")==null || request.getParameter("num").equals(""))) {
		num = Integer.parseInt(request.getParameter("num"));
	}
	
	int sum = vDao.sumCount(num);
	ArrayList<VoteItem> alist = vDao.getView(num);
	VoteList vlist = vDao.getOneVote(num);
	Random r = new Random();
%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" ></script>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet">
<style type="text/css">
	.divView {width:450px; margin:20px 0 0 40px;}
</style>
</head>
<body>
	<div class="divView" >
		<table class="table">
			<tr>
				<th colspan="4">Q : <%=vlist.getQuestion() %><th>
			</tr>
			<tr>
				<td colspan="4">총 투표수 : <%=sum %><td>
			</tr>
			<tr>
				<th width="15%">번호</th>
				<th width="30%">item</th>
				<th align="left">그래프</th>
				<th width="15%">득표수</th>
			</tr>
		<%
			for(int i=0; i<alist.size(); i++) {
				VoteItem vitem = alist.get(i);
				
				int rgb = r.nextInt(255*255*255);		// 랜덤으로 rgb를 10진수로 추출하기
				String rgbHex = Integer.toHexString(rgb);  // rgb10진수를 16진수로 변환하기
				String hRGB = "#" + rgbHex;				// 16진수 앞에 # 붙이기(= #ff8b6a )
				
				int ratio = (int)(Math.ceil(vitem.getCount() / (double)sum * 100));
		%>
			<tr>
				<td><%=i+1 %></td>
				<td><%=vitem.getItem()[0] %></td>
				<td>
					<table width="<%=ratio %>" height="15">
						<tr>
							<td bgcolor="<%=hRGB %>" style="border:none"></td>
						</tr>
					</table>
				</td>
				<td><%=vitem.getCount() %></td>
			</tr>
		<%
			}
		%>
		</table>
	</div>
</body>
</html>
저작자표시 비영리 변경금지 (새창열림)

'Back-end > JSP' 카테고리의 다른 글

[JSP/AJAX] AJAX  (0) 2024.07.29
[JSP] JSP를 이용하여 게시판 페이지 만들기  (0) 2024.07.29
[JSP] JSP를 이용하여 회원가입, 로그인 페이지 만들기  (4) 2024.07.24
[JavaSpring] 세션과 쿠키  (0) 2024.07.18
[JavaSpring] Bean과 DBConnectionPool을 이용한 ORACLE연동 실습문제  (0) 2024.07.17

'Back-end/JSP'의 다른글

  • 현재글[JSP] JSP를 이용하여 투표 페이지 만들기

관련글

티스토리툴바