git
https://github.com/youngmoon97/JSP
투표 만들기
화면(View)
DB파일
DROP TABLE IF EXISTS tblPollList;
create table tblPollList(
num int primary key auto_increment,
question varchar(200) not null,
sdate date,
edate date,mydb2
wdate date,
type smallint default 1,
active smallint default 1
);
DROP TABLE IF EXISTS tblPollItem;
create table tblPollItem(
listnum int not null,
itemnum smallint default 0,
item varchar(50) not null,
count int,
primary key(listnum, itemnum)
);
JAVA파일
PollListBean.java
package ch16;
public class PollListBean {
int num;
String question;
String sdate;
String edate;
String wdate;
int type;
int 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;
}
}
PollItemBean.java
package ch16;
public class PollItemBean {
//item은 배열로 선언
int listnum;
int itemnum;
String[] item;
int 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;
}
}
PollMgr.java
package ch16;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;
public class PollMgr {
private DBConnectionMgr pool;
public PollMgr() {
pool = DBConnectionMgr.getInstance();
}
// Max Num : 가장 최신의 num값 리턴
public int getMaxNum() {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
int maxNum=0;
try {
con = pool.getConnection();
sql = "select max(num) from tblPollList";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
if(rs.next()) maxNum = rs.getInt(1);
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return maxNum;
}
// Poll Insert
public boolean insertPool(PollListBean plbean, PollItemBean pibean) {
Connection con = null;
PreparedStatement pstmt = null;
String sql = null;
boolean flag = false;
try {
con = pool.getConnection();
sql = "insert tblPollList(question,sdate,edate,wdate,type)"
+ "values(?,?,?,now(),?)";
pstmt = con.prepareStatement(sql);
pstmt.setString(1, plbean.getQuestion());
pstmt.setString(2, plbean.getSdate());
pstmt.setString(3, plbean.getEdate());
pstmt.setInt(4, plbean.getType()); //1복수, 0단일
int cnt = pstmt.executeUpdate();
pstmt.close();
if(cnt==1) {//정산적인 tblPollList 저장
sql = "insert tblPollItem values(?,?,?,0) ";
pstmt = con.prepareStatement(sql);
int listNum = getMaxNum(); // 방금 저장한 리스트의 num값
String item[] = pibean.getItem();
for (int i = 0; i < item.length; i++) {
if(item[i]==null||item[i].trim().equals("")) {
break;
}
pstmt.setInt(1, listNum);
pstmt.setInt(2, i);
pstmt.setString(3, item[i]);
if(pstmt.executeUpdate()==1)
flag =true;
}//--for
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt);
}
return flag;
}
// Poll List
public Vector<PollListBean> getPollList(){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
Vector<PollListBean> vlist = new Vector<PollListBean>();
try {
con = pool.getConnection();
sql = "select * from tblPollList order by num desc";
pstmt = con.prepareStatement(sql);
rs = pstmt.executeQuery();
while(rs.next()) {
PollListBean plbean = new PollListBean();
plbean.setNum(rs.getInt("num"));
plbean.setQuestion(rs.getString("question"));
plbean.setSdate(rs.getString("sdate"));
plbean.setEdate(rs.getString("edate"));
vlist.addElement(plbean);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return vlist;
}
// Poll Read
public PollListBean getPoll(int num) {
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
PollListBean plbean = new PollListBean();
try {
con = pool.getConnection();
sql = "select num, question, type, active "
+ "from tblPollList where num = ?";
pstmt = con.prepareStatement(sql);
if(num==0) {
num = getMaxNum(); // 가장 최신의 설문 아이템 리턴
}
pstmt.setInt(1, num);
rs = pstmt.executeQuery();
if(rs.next()) {
plbean.setNum(rs.getInt(1));
plbean.setQuestion(rs.getString(2));
plbean.setType(rs.getInt(3));
plbean.setActive(rs.getInt(4));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return plbean;
}
// Poll Item List
public Vector<String> getItemList(int listNum){
Connection con = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
String sql = null;
Vector<String> vlist = new Vector<String>();
try {
con = pool.getConnection();
sql = "select item from tblPollItem where listNum = ?";
pstmt = con.prepareStatement(sql);
if(listNum==0) {
listNum = getMaxNum(); // 가장 최신의 설문 아이템 리턴
}
pstmt.setInt(1, listNum);
rs = pstmt.executeQuery();
while(rs.next()) {
vlist.addElement(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
pool.freeConnection(con, pstmt, rs);
}
return vlist;
}
// Count sum : 설문 투표수
// Poll Update : 투표 실행
// Max Item Count : 아이템 중에 가장 높은 투표값
}
JSP파일
pollInsert.jsp
<%@ page contentType="text/html; charset=UTF-8" %>
<html>
<head>
<title>JSP Poll</title>
<link href="style.css" rel="stylesheet" type="text/css">
<script type="text/javascript">
function send() {
f = document.frm;
f.sdate.value = f.sdateY.value+"-"
+ f.sdateM.value+"-"+f.sdateD.value;
f.edate.value = f.edateY.value+"-"
+ f.edateM.value+"-"+f.edateD.value;
f.submit();
}
</script>
</head>
<body bgcolor="#FFFFCC">
<div align="center">
<br />
<h2>Poll Program</h2>
<hr width="600" />
<b>설문작성</b>
<hr width="600" />
<form name="frm" method="post" action="pollInsertProc.jsp">
<table border="1" width="500">
<tr>
<td><b>질문</b></td>
<td colspan="2"><input name="question" size="30"></td>
</tr>
<tr>
<td rowspan="10"><b>항목</b></td>
<%
for (int i = 1; i <= 4; i++) {
out.println("<td>" + (i * 2 - 1)
+ ": <input name='item'></td>");
out.println("<td>" + (i * 2)
+ ": <input name='item'></td>");
out.println("</tr>");
if (i == 9) {
out.println("");
} else {
out.println("<tr>");
}
}//for end
%>
<tr>
<td>시작일</td>
<td colspan="2"><select name="sdateY">
<option value="2023">2023
<option value="2024">2024
</select>년 <select name="sdateM">
<%
for (int i = 1; i <= 12; i++) {
out.println("<option value='" + i + "'>" + i);
}
%>
</select>월 <select name="sdateD">
<%
for (int i = 1; i <= 31; i++) {
out.println("<option value='" + i + "'>" + i);
}
%>
</select>일</td>
</tr>
<tr>
<td>종료일</td>
<td colspan=2><select name="edateY">
<option value="2023">2023
<option value="2024">2024
</select>년 <select name="edateM">
<%
for (int i = 1; i <= 12; i++) {
out.println("<option value='" + i + "'>" + i);
}
%>
</select>월 <select name="edateD">
<%
for (int i = 1; i <= 31; i++) {
out.println("<option value='" + i + "'>" + i);
}
%>
</select>일</td>
</tr>
<tr>
<td>복수투표</td>
<td colspan=2>
<input type="radio" name="type" value="1" checked>yes
<input type="radio" name="type" value="0">no
</td>
</tr>
<tr>
<td colspan=3>
<input type="button" value="작성하기" onclick="send()">
<input type="reset" value="다시쓰기">
<input type="button" value="리스트" onClick="javascript:location.href='pollList.jsp'">
</td>
</tr>
</table>
<input type="hidden" name="sdate">
<input type="hidden" name="edate">
</form>
</div>
</body>
</html>
pollInseerProc.jsp
<%@ page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="ch16.PollMgr"/>
<jsp:useBean id="plbean" class="ch16.PollListBean"/>
<jsp:setProperty property="*" name="plbean"/>
<jsp:useBean id="pibean" class="ch16.PollItemBean"/>
<jsp:setProperty property="*" name="pibean"/>
<%
boolean result = mgr.insertPool(plbean, pibean);
String msg = "설문 추가 실패";
String url = "pollInsert.jsp";
if(result){
msg = "설문 추가 성공";
url = "pollList.jsp";
}
%>
<script>
alert("<%=msg%>");
location.href="<%=url%>";
</script>
pollList.jsp
<%@page import="ch16.PollListBean"%>
<%@page import="java.util.Vector"%>
<%@page contentType="text/html; charset=UTF-8"%>
<jsp:useBean id="mgr" class="ch16.PollMgr"/>
<html>
<head>
<title>JSP Poll</title>
<link href="style.css" rel="stylesheet" type="text/css">
</head>
<body bgcolor="#FFFFCC">
<div align="center">
<h2>Poll Program</h2>
<hr width="60%">
<jsp:include page="pollForm.jsp"/>
<b>설문 리스트</b>
<table>
<tr>
<td>
<table border="1">
<tr>
<th width="50">번호</th>
<th width="250" align="left">질문</th>
<th width="200">시작일~종료일</th>
</tr>
<%
Vector<PollListBean> vlist = mgr.getPollList();
for(int i=0;i<vlist.size();i++){
PollListBean plBean = vlist.get(i);
int num = plBean.getNum();
String question = plBean.getQuestion();
String sdate = plBean.getSdate();
String edate = plBean.getEdate();
%>
<tr>
<td align="center"><%=vlist.size()-i%></td>
<td><a href="pollList.jsp?num=<%=num%>"><%=question%></a></td>
<td align="center"><%=sdate+"~"+edate%></td>
</tr>
<%}//---for%>
</table>
</td>
</tr>
<tr>
<td align="center">
<a href="pollInsert.jsp">설문작성하기</a>
</td>
</tr>
</table>
</div>
</body>
</html>
