bbsdao.java

数学网

	public ArrayList bbsStateList(int offset, int limit,String state)
	{
		ArrayList list=new ArrayList();
		Connection con=null;
		  PreparedStatement ps=null;
		  ResultSet rs=null;
		  String sql="select a.id,a.title ,a.state ,a.qdate ,a.childid,a.fen,b.counta" +
			" from bbs a,(SELECT childid,count(*)-1 as counta FROM bbs group by childid) b  where a.childid=b.childid and ask=1 and a.state=?";
	   
		  try {
				con=ds.getConnection();
				ps=con.prepareStatement(sql,
			    		   ResultSet.TYPE_SCROLL_INSENSITIVE,
			               ResultSet.CONCUR_READ_ONLY);
				ps.setInt(1,Integer.parseInt(state));
				rs=ps.executeQuery();
				if(offset>0)
				{
					rs.absolute(offset);
					
				}
			       int cout=0;
			       while((cout++<limit) && (rs.next()))
			       {BbsList bbsl=new BbsList();
			    	bbsl.setId(rs.getInt("id"));
			    	bbsl.setTitle(rs.getString("title"));
			    	bbsl.setCount(rs.getInt("counta"));
			    	bbsl.setState(rs.getInt("state"));
			    	bbsl.setDate(rs.getDate("qdate"));
			    	bbsl.setFen(rs.getInt("fen"));
			    	bbsl.setChildid(rs.getInt("childid"));
			    	list.add(bbsl);
			       }
				   close(rs);
				   close(ps);
			} catch (SQLException e) {
				   close(rs);
				   close(ps);
				e.printStackTrace();
			}finally{
				close(con);
			}  
			  return list;
	}
	public ArrayList bbsCatQueList(int offset, int limit,String bbsid)
	{ ArrayList list=new ArrayList();
	  Connection con=null;
	  PreparedStatement ps=null;
	  ResultSet rs=null;
	  String sql="select a.id,a.title ,a.userid,a.state ,a.qdate ,a.childid,a.fen,b.counta" +
		" from bbs a,(SELECT childid,count(*)-1 as counta FROM bbs group by childid) b  where a.childid=b.childid and ask=1 and a.bbsid=?";
   
	  try {
		con=ds.getConnection();
		ps=con.prepareStatement(sql,
	    		   ResultSet.TYPE_SCROLL_INSENSITIVE,
	               ResultSet.CONCUR_READ_ONLY);
		ps.setInt(1,Integer.parseInt(bbsid));
		rs=ps.executeQuery();
		if(offset>0)
		{
			rs.absolute(offset);
			
		}
	       int cout=0;
	       while((cout++<limit) && (rs.next()))
	       {BbsList bbsl=new BbsList();
	    	bbsl.setId(rs.getInt("id"));
	    	bbsl.setTitle(rs.getString("title"));
	    	bbsl.setCount(rs.getInt("counta"));
	    	bbsl.setState(rs.getInt("state"));
	    	bbsl.setDate(rs.getDate("qdate"));
	    	bbsl.setFen(rs.getInt("fen"));
	    	bbsl.setChildid(rs.getInt("childid"));
	    	bbsl.setUserid(rs.getInt("userid"));
	    	list.add(bbsl);
	       }
		   close(rs);
		   close(ps);
	} catch (SQLException e) {
		   close(rs);
		   close(ps);
		e.printStackTrace();
	}finally{
		close(con);
	}  
	  return list;
	}
    public void bbsAnswer(Bbs bbs)
    { Connection con=null;
      PreparedStatement ps=null;
      ResultSet rs=null;
      String sql="insert into bbs (contance,qdate,bbsid,childid,userid,username,ip,fen)" +
		" values(?,Now(),?,?,?,?,?,?)";
  	try {
		con=ds.getConnection();
		ps=con.prepareStatement(sql);
		ps.setString(1,bbs.getContance());
		ps.setInt(2,bbs.getBbsid());
		ps.setInt(3,bbs.getChildid());
		ps.setInt(4,bbs.getUserid());
		ps.setString(5,bbs.getUsername());
		ps.setString(6,bbs.getIp());
		ps.setInt(7,bbs.getFen());
		ps.executeUpdate();
		close(ps);
	} catch (SQLException e) {
         close(ps);
         rollback(con);
		e.printStackTrace();
	}finally{
		close(con);
	}   	
    }
	public ArrayList bbsQueShow(int offset, int limit,String childid,int userid)
    {   ArrayList list=new ArrayList();
       Connection con=null;
       PreparedStatement ps=null;
       ResultSet rs=null;
       String sql="select * from bbs where childid=? order by ask desc,nest desc";
       int child=Integer.parseInt(childid);
       try {
    	  boolean isgood=bbsFlag(childid);
		con=ds.getConnection();
		ps=con.prepareStatement(sql,
	    		   ResultSet.TYPE_SCROLL_INSENSITIVE,
	               ResultSet.CONCUR_READ_ONLY);
		ps.setInt(1,child);
		rs=ps.executeQuery();
		if(offset>0)
		   {
	    	   rs.absolute(offset);
	       }
	    int cout=0;
	    while((cout++<limit) && (rs.next()))
	    { BbsList bbsl=new BbsList();
	      bbsl.setBbsid(rs.getInt("bbsid"));
	      bbsl.setAsk(rs.getInt("ask"));
	      bbsl.setChildid(rs.getInt("childid"));
	      bbsl.setDate(rs.getDate("qdate"));
	      bbsl.setFen(rs.getInt("fen"));/////////////
	      bbsl.setId(rs.getInt("id"));
	      bbsl.setNest(rs.getInt("nest"));
	      bbsl.setState(rs.getInt("state"));
	      bbsl.setTitle(rs.getString("title"));
	      bbsl.setContance(rs.getString("contance"));
	      bbsl.setUserid(rs.getInt("userid"));
	      bbsl.setUsername(rs.getString("username"));
	      bbsl.setCount(rs.getInt("count"));
	      bbsl.setAsk_id(userid);
	      bbsl.setIsgood(isgood);
	      list.add(bbsl);
	    }
		   close(rs);
		   close(ps);
	} catch (SQLException e) {
		close(rs);
	    close(ps);
	    rollback(con);
		e.printStackTrace();
	}  finally{
		close(con);
	}
       return list;
    	
    }
	public ArrayList bbsList(int offset, int limit)
    {ArrayList list=new ArrayList();
     Connection con=null;
     PreparedStatement ps=null;
     ResultSet rs=null;
     /*String sql="select b.*, (SELECT count(*) FROM bbs where childid=b.childid)-1 as count" +
     		" from bbs b where b.ask=1 ";*/
     String sql="select a.id,a.title ,a.userid,a.username,a.state ,a.qdate ,a.childid,a.fen,b.counta" +
     		" from bbs a,(SELECT childid,count(*)-1 as counta FROM bbs group by childid) b  where a.childid=b.childid and ask=1";
     
     try {
		con=ds.getConnection();
		ps=con.prepareStatement(sql,
	    		   ResultSet.TYPE_SCROLL_INSENSITIVE,
	               ResultSet.CONCUR_READ_ONLY);
	   rs=ps.executeQuery();
	   if(offset>0)
	   {
    	   rs.absolute(offset);
       }
       int cout=0;
       while((cout++<limit) && (rs.next()))
       {BbsList bbsl=new BbsList();
    	bbsl.setId(rs.getInt("id"));
    	bbsl.setTitle(rs.getString("title"));
    	bbsl.setCount(rs.getInt("counta"));
    	bbsl.setState(rs.getInt("state"));
    	bbsl.setDate(rs.getDate("qdate"));
    	bbsl.setFen(rs.getInt("fen"));
    	bbsl.setChildid(rs.getInt("childid"));
    	bbsl.setUserid(rs.getInt("userid"));
    	bbsl.setUsername(rs.getString("username"));
    	list.add(bbsl);
       }
       
	   close(rs);
	   close(ps);
	} catch (SQLException e) {
	    
	    close(rs);
	    close(ps);
	    rollback(con);
		e.printStackTrace();
	} finally{
		close(con);
	}
     return list;	
    }
	public void updateFen(int fen,int id)
	{
		Connection con=null;
    	PreparedStatement ps=null;
    	String sql="update users set users_award=users_award-? where id=?";
    	try {
			con=ds.getConnection();
			ps=con.prepareStatement(sql);
			ps.setInt(1,fen);
			ps.setInt(2,id);
			ps.executeUpdate();
			close(ps);
		} catch (SQLException e) {
			 close(ps);
             rollback(con);
			e.printStackTrace();
		}finally{
			close(con);
		}
	}
	public void add(Bbs bbs)
    {
    	Connection con=null;
    	PreparedStatement ps=null;
    	String sql="insert into bbs (title,contance,qdate,bbsid,childid,ask,fen,userid,username,ip)" +
    			" values(?,?,Now(),?,?,?,?,?,?,?)";
    	try {
			con=ds.getConnection();
			ps=con.prepareStatement(sql);
			ps.setString(1,bbs.getTitle());
			ps.setString(2,bbs.getContance());
			ps.setInt(3,bbs.getBbsid());
			ps.setInt(4,bbs.getChildid());
			ps.setInt(5,bbs.getAsk());
			ps.setInt(6,bbs.getFen());
			ps.setInt(7,bbs.getUserid());
			ps.setString(8,bbs.getUsername());
			ps.setString(9,bbs.getIp());
			ps.executeUpdate();
			close(ps);
		} catch (SQLException e) {
             close(ps);
             rollback(con);
			e.printStackTrace();
		}finally{
			close(con);
		}
    }
   public List categorylist()
   {ArrayList list=new ArrayList();
	Connection con=null;
   PreparedStatement ps=null;
   ResultSet rs=null;
   String sql="select * from bbscat";
   try {
	con=ds.getConnection();
	ps=con.prepareStatement(sql);
	rs=ps.executeQuery();
	while(rs.next())
	{BbsCategory bbsc=new BbsCategory();
	 bbsc.setId(rs.getInt("id"));
	 bbsc.setCategory(rs.getString("category"));
	 list.add(bbsc);
	}
} catch (SQLException e) {
	e.printStackTrace();
}
return list;
	   
   }
}