[JSP] JSP를 이용하여 투표 페이지 만들기
컴퓨터/JSPvote_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=" 투 표 ">  
<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>년 
<select name = "sdateM">
<%
for(int i=1; i<=12; i++) {
out.print("<option value='" + i + "'>" + i);
}
%>
</select>월 
<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>년 
<select name = "edateM">
<%
for(int i=1; i<=12; i++) {
out.print("<option value='" + i + "'>" + i);
}
%>
</select>월 
<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 
<input type="radio" name="type" value="0">no
</td>
</tr>
<tr>
<td colspan="3" align="center">
<input type="submit" value="작성하기"> 
<input type="reset" value="초기화"> 
<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>'컴퓨터 > 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 |