votemgr.java

用jsp编写的网上投票系统

package votebean;
import java.sql.*;
import java.util.*;
import votebean.MysqlDAO;

/**
 * @author xiaoq
 *
 */
public class VoteMgr {
	//下面是增加投票addVote()功能
	public int addVote(Questions question){
		Collection items=question.getItem();
		MysqlDAO db=new MysqlDAO();
		Connection conn=null;
		Statement stat=null;
		ResultSet rs=null;
		try{
			conn=db.getConnection();
			stat=conn.createStatement();
			int maxId=0;
			String sql="select max(id) as maxId from vote_questions";
			rs=stat.executeQuery(sql);
			if(rs.next()){
				maxId=rs.getInt("maxId");
			}
			sql="insert into vote_questions(id,Title,Stile,"
				+"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");
			stat.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");
					stat.addBatch(sql);
				}
			}
			stat.executeBatch();
			return 1;
		}catch(SQLException e){
			e.getMessage();
		    return -1;
		}
		catch(Exception e){
			e.getStackTrace();
			return -2;
		}finally{
			try {
				if(rs!=null){
					rs.close();
				}
				if(stat!=null){
					stat.close();
				}
				if(conn!=null){
					conn.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
	//删除投票deleteVote
	public int deleteVote(int id){
		MysqlDAO db=new MysqlDAO();
		Connection conn=null;
		Statement stat=null;
		try{
			conn=db.getConnection();
			stat=conn.createStatement();
			String sql="delete from vote_questions where id='"+id+"'";
			stat.addBatch(sql);
			sql="delete from vote_items where Qid='"+id+"'";
			stat.addBatch(sql);
			stat.executeBatch();
			return 1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}catch(Exception e){
			e.getStackTrace();
			return -2;
		}finally{
			try {
				if(stat!=null){
					stat.close();
				}
				if(conn!=null){
					conn.close();
				}
			}catch(SQLException e){
				e.getStackTrace();
			}
		}
	}
	//更新代码
	public int updateVote(int id,String startime,String endtime,int active){
		MysqlDAO db=new MysqlDAO();
		Connection conn=null;
		Statement stat=null;
		try{
			conn=db.getConnection();
			stat=conn.createStatement();
			String sql="update vote_questions set startime='"+startime+"',endtime='"+
			endtime+"',active='"+active+"'where id='"+id+"'";
			int i=stat.executeUpdate(sql);
			return i;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}catch(Exception e){
			e.printStackTrace();
			return -2;
		}finally{
			try{
				if(stat!=null){
					stat.close();
				}
				if(conn!=null){
					conn.close();
				}
			}catch(SQLException e){
				e.getStackTrace();
			}
		}
	}
	//获取投票明细getVote()
	public Questions getVote(int id){
		MysqlDAO db=new MysqlDAO();
		Connection conn=null;
		Statement stat=null;
		ResultSet rs=null;
		try{
			conn=db.getConnection();
			stat=conn.createStatement();
			String sql="select * from vote_questions where id='"+id+"'";
			rs=stat.executeQuery(sql);
			Questions question=null;
			if(rs.next()){
				question=new Questions();
				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("stile"));
				question.setVotecount(rs.getInt("votecount"));
			}
			sql="select * from vote_items where Qid='"+id+"'";
			rs=stat.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.setItem(items);
			return question;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}catch (Exception e){
			e.getStackTrace();
			return null;
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(stat!=null){
					stat.close();
				}
				if(conn!=null){
					conn.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
	//获取所有的投票明细getAllMessage()
	public Collection getAllMessage(){
		MysqlDAO db=new MysqlDAO();
		MysqlDAO db1=new MysqlDAO();
		Connection conn=null;
		Connection conn1=null;
		Statement stat=null;
		Statement stat1=null;
		ResultSet rs=null;
		ResultSet rs1=null;
		try{
			conn=db.getConnection();
			stat=conn.createStatement();
			conn1=db1.getConnection();
			stat1=conn1.createStatement();
			String sql="select * from vote_questions";
			rs=stat.executeQuery(sql);
			Collection c=new ArrayList();
			Questions question=null;
			while(rs.next()){
				question=new Questions();
				question.setId(rs.getInt("id"));
				question.setStyle(rs.getString("Title"));
				question.setActive(rs.getInt("active"));
				question.setEndtime(rs.getDate("endtime"));
				question.setStartime(rs.getDate("startime"));
				question.setStyle(rs.getString("Stile"));
				sql="select * from vote_items where Qid='"
					+question.getId()+"'";
				rs1=stat1.executeQuery(sql);
				Collection items=new ArrayList();
				Item item=null;
				while(rs1.next()){
					item=new Item();
					item.setId(rs1.getInt("id"));
					item.setOptions(rs1.getString("Options"));
					item.setQid(rs1.getInt("Qid"));
					item.setVote(rs1.getInt("vote"));
					items.add(item);
					item=null;
				}
				question.setItem(items);
				c.add(question);
				question=null;
			}
			return c;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}catch(Exception e){
			e.getStackTrace();
			return null;
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(rs1!=null){
					rs1.close();
				}
				if(stat!=null){
					stat.close();
				}
				if(stat1!=null){
					stat1.close();
				}
				if(conn!=null){
					conn.close();
				}
				if(conn1!=null){
					conn1.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
	//分页获取投票信息
	public Collection getAllMessage(int pagesize,int page){
		MysqlDAO db=new MysqlDAO();
		MysqlDAO db1=new MysqlDAO();
		Connection conn=null;
		Connection conn1=null;
		Statement stat=null;
		Statement stat1=null;
		ResultSet rs=null;
		ResultSet rs1=null;
		try{
			conn=db.getConnection();
			stat =conn.createStatement();
			conn1=db1.getConnection();
			stat1=conn1.createStatement();
			String sql="select * from vote_questions order by id desc limit "
				+(page-1)*pagesize+","+pagesize;
			rs=stat.executeQuery(sql);
			Collection c=new ArrayList();
			Questions question=null;
			while(rs.next()){
				question=new Questions();
				question.setId(rs.getInt("id"));
				question.setActive(rs.getInt("active"));
				question.setStyle(rs.getString("Stile"));
				question.setTitle(rs.getString("Title"));
				question.setEndtime(rs.getDate("endtime"));
				question.setStartime(rs.getDate("startime"));
				question.setVotecount(rs.getInt("votecount"));
				sql="select * from vote_items where Qid='"
					+question.getId()+"'";
				rs1=stat1.executeQuery(sql);
				Collection items=new ArrayList();
				Item item=null;
				while(rs1.next()){
					item=new Item();
					item.setId(rs1.getInt("id"));
					item.setOptions(rs1.getString("Options"));
					item.setQid(rs1.getInt("Qid"));
					item.setVote(rs1.getInt("vote"));
					items.add(item);
					item=null;
				}
				question.setItem(items);
				c.add(question);
				question=null;
			}
			return c;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}catch(Exception e){
			e.getStackTrace();
			return null;
		}finally{
			try{
				if(rs!=null){
					rs.close();
				}
				if(rs1!=null){
					rs1.close();
				}
				if(stat!=null){
					stat.close();
				}
				if(stat1!=null){
					stat1.close();
				}
				if(conn!=null){
					conn.close();
				}
				if(conn1!=null){
					conn1.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
	//所有投票主题个数
	public int getCount(){
		if(getAllMessage()!=null){
			return getAllMessage().size();
		}else{
			return 0;
		}
	}
	//一下是修改投票主题的方法
	public int updateQuestion(int id){
		MysqlDAO db =new MysqlDAO();
		Connection conn=null;
		Statement stat=null;
		try{
			conn=db.getConnection();
			stat=conn.createStatement();
			String sql="update vote_Questions set votecount=votecount+1 "
				+"where id='"+id+"'";
			int i=stat.executeUpdate(sql);
			return i;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}catch(Exception e){
			e.getStackTrace();
			return -2;
		}finally{
			try{
				if(stat!=null){
					stat.close();
				}
				if(conn!=null){
					conn.close();
				}
			}catch (SQLException e){
				e.getStackTrace();
			}
		}
	}
	//一下修改投票选项updateItem()
	public int updateItem(String[] vote){
		MysqlDAO db=new MysqlDAO();
		Connection conn=null;
		Statement stat=null;
		try{
			conn=db.getConnection();
			stat=conn.createStatement();
			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]+"'";
					stat.addBatch(sql);
				}
				stat.executeBatch();
			}
			return 1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}catch(Exception e){
			e.getStackTrace();
			return -2;
		}finally{
			try{
				if(stat!=null){
					stat.close();;
				}
				if(conn!=null){
					conn.close();
				}
			}catch (SQLException e){
				e.printStackTrace();
			}
		}
	}
}