votemgr.java

用java实现一个功能完善的投票系统

package com.wxpn.tutorial.servlet;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import com.wxpn.tutorial.db.ConnectionPool;
import com.wxpn.tutorial.db.DB;

public class VoteMgr {

	public int addVote(Question question) {
		Collection items = question.getItems();
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();
			int maxid = 0;
			String sql = "select max(id) as maxid from vote_questions";
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				maxid = rs.getInt("maxid");
			}
			// sql语句：
			sql = "insert into vote_questions(id,title,style,"
					+ "votecount,startime,endtime,active) values('" + (++maxid)
					+ "','" + question.getTitle() + "','" + question.getStyle()
					+ "','0','" + question.getStrStartime() + "','"
					+ question.getStrEndtime() + "','" + question.getActive()
					+ "')";
			sql = new String(sql.getBytes("ISO8859-1"), "UTF-8");
			stmt.addBatch(sql);
			if (items != null) {
				Iterator iterator = items.iterator();
				while (iterator.hasNext()) {

					sql = "insert into vote_items(qid,options,vote) values('"
							+ maxid + "','"
							+ ((Item) (iterator.next())).getOptions()
							+ "','0')";
					sql = new String(sql.getBytes("ISO8859-1"), "UTF-8");
					stmt.addBatch(sql);
				}
			}
			// 执行sql语句：
			stmt.executeBatch();
			return 1;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public int deleteVote(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "delete from vote_questions where id = '" + id + "'";
			stmt.addBatch(sql);
			sql = "delete from vote_items where qid = '" + id + "'";
			stmt.addBatch(sql);
			// 执行sql语句：
			stmt.executeBatch();
			return 1;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public int updateVote(int id, String startTime, String endTime, int active) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "update vote_questions set startime='" + startTime
					+ "',endtime='" + endTime + "',active='" + active
					+ "' where id='" + id + "'";
			// 执行sql语句：
			int i = stmt.executeUpdate(sql);
			return i;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Question getVote(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		ResultSet rs = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "select * from vote_questions where id='" + id + "'";
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Question question = null;
			if (rs.next()) {
				question = new Question();
				question.setId(rs.getInt("id"));
				question.setTitle(rs.getString("title"));
				question.setActive(rs.getInt("active"));
				question.setEndtime(rs.getDate("endtime"));
				question.setStartime(rs.getDate("startime"));
				question.setStyle(rs.getString("style"));
				question.setVotecount(rs.getInt("votecount"));
			}
			sql = "select * from vote_items where qid='" + id + "'";
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection items = new ArrayList();
			Item item = null;
			while (rs.next()) {
				item = new Item();
				item.setId(rs.getInt("id"));
				item.setOptions(rs.getString("options"));
				item.setQid(rs.getInt("qid"));
				item.setVote(rs.getInt("vote"));
				items.add(item);
				item = null;
			}
			question.setItems(items);
			return question;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getAllMessages() {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		Statement stmt2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();
			stmt2 = conn.createStatement();
			// sql语句：
			String sql = "select * from vote_questions";
			// 执行sql语句：
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			Question question = null;
			while (rs.next()) {
				question = new Question();
				question.setId(rs.getInt("id"));
				question.setTitle(rs.getString("title"));
				question.setActive(rs.getInt("active"));
				question.setEndtime(rs.getDate("endtime"));
				question.setStartime(rs.getDate("startime"));
				question.setStyle(rs.getString("style"));

				sql = "select * from vote_items where qid='" + question.getId()
						+ "'";
				// 执行sql语句，返回一个记录集到rs：
				rs2 = stmt2.executeQuery(sql);
				Collection items = new ArrayList();
				Item item = null;
				while (rs2.next()) {
					item = new Item();
					item.setId(rs2.getInt("id"));
					item.setOptions(rs2.getString("options"));
					item.setQid(rs2.getInt("qid"));
					item.setVote(rs2.getInt("vote"));
					items.add(item);
					item = null;
				}
				question.setItems(items);

				c.add(question);
				question = null;
			}
			return c;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (rs2 != null) {
					rs2.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (stmt2 != null) {
					stmt2.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public Collection getAllMessages(int pagesize, int page) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		Statement stmt2 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();
			stmt2 = conn.createStatement();
			// sql语句：
			String sql = "select * from vote_questions order by id desc limit "
					+ (page - 1) * pagesize + "," + pagesize;
			// 执行sql语句：
			// 执行sql语句，返回一个记录集到rs：
			rs = stmt.executeQuery(sql);
			Collection c = new ArrayList();
			Question question = null;
			while (rs.next()) {
				question = new Question();
				question.setId(rs.getInt("id"));
				question.setTitle(rs.getString("title"));
				question.setStyle(rs.getString("style"));
				question.setVotecount(rs.getInt("votecount"));
				question.setStartime(rs.getDate("startime"));
				question.setEndtime(rs.getDate("endtime"));
				question.setActive(rs.getInt("active"));

				sql = "select * from vote_items where qid='" + question.getId()
						+ "'";
				// 执行sql语句，返回一个记录集到rs：
				rs2 = stmt2.executeQuery(sql);
				Collection items = new ArrayList();
				Item item = null;
				while (rs2.next()) {
					item = new Item();
					item.setId(rs2.getInt("id"));
					item.setQid(rs2.getInt("qid"));
					item.setOptions(rs2.getString("options"));
					item.setVote(rs2.getInt("vote"));
					items.add(item);
					item = null;
				}
				question.setItems(items);

				c.add(question);
				question = null;
			}
			return c;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (rs != null) {
					rs.close();
				}
				if (rs2 != null) {
					rs2.close();
				}
				if (stmt != null) {
					stmt.close();
				}
				if (stmt2 != null) {
					stmt2.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}
	}

	public int getCount() {
		if (getAllMessages() != null) {
			return getAllMessages().size();
		} else {
			return 0;
		}
	}

	public int updateQuestion(int id) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = "update vote_questions set votecount=votecount+1 "
					+ "where id='" + id + "'";
			// 执行sql语句：
			int i = stmt.executeUpdate(sql);
			return i;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}

	}

	public int updateItem(String[] vote) {
		// 创建数据库连接对象：
		ConnectionPool connPool = DB.getConnPool();
		Connection conn = connPool.getConnection();
		Statement stmt = null;
		try {
			// 创建数据记录集对象：
			stmt = conn.createStatement();

			// sql语句：
			String sql = null;
			if (vote != null) {
				for (int i = 0; i < vote.length; i++) {
					sql = "update vote_items set vote=vote+1 " + "where id='"
							+ vote[i] + "'";
					stmt.addBatch(sql);
				}
				// 执行sql语句：
				stmt.executeBatch();
			}
			return 1;
		} catch (SQLException sqlExc) {
			sqlExc.printStackTrace();
			return -1;
		} catch (Exception e) {
			e.printStackTrace();
			return -2;
		} finally {
			// 关闭连接，释放数据库资源：
			try {
				if (stmt != null) {
					stmt.close();
				}
				connPool.freeConnection(conn);
			} catch (SQLException sqlExc) {
				sqlExc.printStackTrace();
			}
		}

	}

}