23/03/31(JSP+투표#1)

조영문·2023년 3월 31일
0

JSP

목록 보기
10/13

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

<!-- 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>

0개의 댓글