HanXiaoLe.rar_图书管理

package votebean; import java.sql.*; import java.util.*; import votebean.MysqlDAO; /** * @author HanXiaoLe * */ 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,Style," +"votecount,startime,endtime,active) values('" +(++maxId)+"','"+question.getTitle()+"','"+question.getStyle() +"','0','"+question.getStrStartime()+"','"+question.getStrEndtime() +"','"+question.getActive()+"')"; //System.out.println(sql); //sql=new String(sql.getBytes("ISO8859-1"),"GBK"); 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"),"gbk"); 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("Style")); 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("Style")); 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("Style")); 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"))