bbsdao.java

数学网

package math.bbs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import math.bbs.controller.BbsAction;
import math.bbs.model.Bbs;
import math.bbs.model.BbsCategory;
import math.bbs.model.BbsList;
import math.dao.DAO;

public class BbsDao extends DAO {

	public BbsDao(DataSource ds) {
		super(ds);
	}
	
	public void bbsNestFen( String id,String fen)
	{
		Connection con=null;
	    PreparedStatement ps=null;
	    String sql="update users set users_award= users_award+ ?" +
	    		
	    		" where id=(select userid from bbs where id=? )";
	    try {
			con=ds.getConnection();
			ps=con.prepareStatement(sql);
			ps.setInt(1,Integer.parseInt(fen));
			ps.setInt(2,Integer.parseInt(id));
			ps.executeUpdate();
			close(ps);
       } catch (SQLException e) {
			close(ps);
			e.printStackTrace();
       } finally{
			close(con);
	  }		
		
	}
	public void bbsNestState(String childid)
	{
		Connection con=null;
	    PreparedStatement ps=null;
	    String sql="update bbs set state=1  where id= (select id from bbs where ask=1 and childid=?)";
	    try {
			con=ds.getConnection();
			ps=con.prepareStatement(sql);
			ps.setInt(1,Integer.parseInt(childid));
			ps.executeUpdate();
			close(ps);
       } catch (SQLException e) {
			close(ps);
			e.printStackTrace();
       } finally{
			close(con);
	  }		
	}
	public void bbsNest(String id)
	{
     Connection con=null;
     PreparedStatement ps=null;
     String sql="update bbs set nest=1 " +
     		"where id=?";
     try {
		con=ds.getConnection();
		ps=con.prepareStatement(sql);
		/*ps.setInt(1,Integer.parseInt(childid));*/
		ps.setInt(1,Integer.parseInt(id));
		ps.executeUpdate();
		close(ps);
     } catch (SQLException e) {
		close(ps);
		e.printStackTrace();
	} finally{
		close(con);
	}		
	}
	public boolean bbsFlag(String childid)
	{  boolean coutflag=false;
	   Connection con=null;
	   PreparedStatement ps=null;
       ResultSet rs=null;
       String sql="select count(*) from bbs where ask=1 and state=0 and childid="+childid;
	 
      try {
		 con=ds.getConnection();
		 ps=con.prepareStatement(sql);
		 rs=ps.executeQuery();
		 rs.next();
	      if(rs.getInt(1)==1)
	      {
	    	  coutflag=true;
	      }
		 close(rs);
		 close(ps);
		
	} catch (SQLException e) {
		 close(rs);
		 close(ps);
		 rollback(con);
		e.printStackTrace();
	}finally{
		close(con);
	}  
	return coutflag;
		
	}
	public int bbsAcount(String childid)
	{
		int coutanswer=0;
		Connection con=null;
		 PreparedStatement ps=null;
	     ResultSet rs=null;
	     String sql="select b.counta" +
			" from bbs a,(SELECT childid,count(*)-1 as counta FROM bbs group by childid) b  where a.childid=b.childid and a.ask=1 and a.childid="+childid;
		 
	       try {
			 con=ds.getConnection();
			 ps=con.prepareStatement(sql);
			 rs=ps.executeQuery();
			 rs.next();
			 coutanswer = rs.getInt(1);
			 close(rs);
			 close(ps);
			
		} catch (SQLException e) {
			 close(rs);
			 close(ps);
			 rollback(con);
			e.printStackTrace();
		}finally{
			close(con);
		}  
		return coutanswer;
	}
	public int bbsIsState( String  state)
	{   int countsate=0;
		Connection con=null;
		 PreparedStatement ps=null;
	     ResultSet rs=null;
	     String sql="select count(*) from bbs where ask=1 and state="+state;
	       try {
			 con=ds.getConnection();
			 ps=con.prepareStatement(sql);
			 rs=ps.executeQuery();
			 rs.next();
		       countsate = rs.getInt(1);
			 close(rs);
			 close(ps);
			
		} catch (SQLException e) {
			 close(rs);
			 close(ps);
			 rollback(con);
			e.printStackTrace();
		}finally{
			close(con);
		}  
		 return countsate ;
	}
	public ArrayList bbsStateShow(String state)
	{
		 ArrayList list=new ArrayList();
		 Connection con=null;
		 PreparedStatement ps=null;
	     ResultSet rs=null;
	     String sql="select b.*,c.category from bbs b,bbscat c where b.bbsid=c.id and b.ask=1 and b.state="+state;
	     try {
			con=ds.getConnection();
		    ps=con.prepareStatement(sql);
		    rs=ps.executeQuery();
		    while(rs.next())
		    {Bbs bbsl=new Bbs();
		    	bbsl.setCategory(rs.getString("category"));
		    	bbsl.setId(rs.getInt("id"));
		    	bbsl.setTitle(rs.getString("title"));
                bbsl.setState(rs.getInt("state"));
		    	bbsl.setDate(rs.getDate("qdate"));
		    	bbsl.setFen(rs.getInt("fen"));
		    	bbsl.setChildid(rs.getInt("childid"));
		    	bbsl.setBbsid(rs.getInt("bbsid"));
		    	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 bbsMyaskList(int offset,int limit,int userid)
	{
		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 a.ask=1 and a.userid=?";
		  try {
				con=ds.getConnection();
				ps=con.prepareStatement(sql,
			    		   ResultSet.TYPE_SCROLL_INSENSITIVE,
			               ResultSet.CONCUR_READ_ONLY);
				ps.setInt(1,userid);
				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 bbsFindList(int offset,int limit,String title)
	{
		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 a.ask=1 and a.title like ?";
		  try {
				con=ds.getConnection();
				ps=con.prepareStatement(sql,
			    		   ResultSet.TYPE_SCROLL_INSENSITIVE,
			               ResultSet.CONCUR_READ_ONLY);
				ps.setString(1,"%"+title+"%");
				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;
	}