categorycom.java

一个信息发布系统 基于jsp+servlet+tomcat 用户分管理员跟普通用户 普通用户有发布信息 查询信息 修改信息 回复信息 管理员拥有普通用户的功能 此外还具有删除信息 管理信息的回复的功能

package control;
import model.*;
import java.util.*;
import java.sql.*;

public class CategoryCom 
{
	private Connection con;
    /**
     * 构造函数 取得与数据库的连接
     * @param url  数据库的url
     * @param database 数据库名
     * @param pasw 数据库的密码
     */
    public CategoryCom(String url,String database,String pasw)
    {
    	con = new GetConnection(url,database,pasw).getConnection();
    }
    
    public boolean isExitCategory(String category)
    {
    	PreparedStatement pre;
    	String sql = "select CName from category where CName = ?";
    	try
    	{
    		pre = con.prepareStatement(sql);
    		pre.setString(1, category);
    		ResultSet rs = pre.executeQuery();
    		if(rs.next())
    			return true;
    		else
    			return false;
    	}
    	catch(SQLException e){return false;}
    }
    /**
     * 增加新的分类
     * @param name 分类名字
     * @param date 创建日期
     */
    public void addCategory(String name,String date,int count, String detail,String CPath)
    {
    		PreparedStatement pre;
        	String sql = "insert into category(CName,CCreateDate,CCount,Cdetail,CPath) values(?,?,?,?,?)";
        	try
        	{
        		pre = con.prepareStatement(sql);
        		
        		pre.setString(1, name);
        		pre.setString(2,date);
        		pre.setInt(3, count);   //默认为0
        		pre.setString(4, detail);
        		pre.setString(5, CPath);
        		pre.execute();
        	}
        	catch(SQLException e){}
    }
    /**
     * 删除该分类  牵连到4个表  注意
     * @param CName
     */
    public void delCategory(String 	CName)
    {
    	
    		PreparedStatement pre,cardpre;
    		String cardsql = "select CardID from cardinf where CName = ?";  //查出该分类中所有文章ID  然后删除
    		String sql = "delete from category where CName = ?";
    		CardCom com = new CardCom("jdbc:mysql://localhost/mybbs","root", "root123");
			RelationCom com2 = new RelationCom("jdbc:mysql://localhost/mybbs","root", "root123");
    		try
    		{    			  			   			   			
    			Vector vc = com2.getChildren(CName);  			
    			if(vc!=null)                                       //从子分类开始  顺序不可以变
    			{
    			     for(int i = 0;i < vc.size();++i)
    			     {
    				     delCategory((String)vc.get(i));
    			     }
    			}
    			
    			int CardID = 0;	   			
    			cardpre = con.prepareStatement(cardsql);
    			cardpre.setString(1, CName);
    			ResultSet rs = cardpre.executeQuery();
    			while(rs.next())                                          //先删文章
    			{
    				CardID = Integer.parseInt(rs.getString("CardID"));   //取得所有文章ID
    				com.delCard(CardID);                                 //删除所有文章 文章回复  减少文章发布者的发帖数（件CardCom中的delCard方法）
    			}
    			pre = con.prepareStatement(sql);                       //最后删分类
    			pre.setString(1, CName);
    			pre.execute();
    			com2.delRelation(CName);           //在删关系表
    		}
    		catch(SQLException e){}
    }
    
    public void updateCategory(String CName,String detail)
    {
    	if(isExitCategory(CName))
    	{
    		PreparedStatement pre;
        	String sql = "update  category set Cdetail = ? where CName = ?";
        	try
        	{
        		pre = con.prepareStatement(sql);  
        		pre.setString(1, detail);
        		pre.setString(2,CName);     		
        		pre.execute();
        	}
        	catch(SQLException e){}
    	}
    }
    
    public int getCCount(String CName)
    {
    	int count;
    	PreparedStatement pre;
    	if(CName.equals("all"))
    	{
    		String sql = "select sum(CCount) total from category where CName=CPath";
    		try
    		{
    			Statement st = con.createStatement();
    			ResultSet rs = st.executeQuery(sql);
    			if(rs.next())
        		{
        			count = rs.getInt("total");
        		}
        		else
        			count = 0;
        		return count;
    		}
    		catch(Exception e){return 0;}
    	}
    	
    	else
    	{
    		String sql = "select CCount from category where CName = ?";
        	try
        	{
        		pre = con.prepareStatement(sql);
        		pre.setString(1, CName);
        		ResultSet rs = pre.executeQuery();
        		if(rs.next())
        		{
        			count = rs.getInt("CCount");
        		}
        		else
        			count = 0;
        		return count;
        	}
        	catch(SQLException e){return 0;}
    	}
    	
    }
    /**
     * 增加该分类的帖子数（当该分类中有新帖子被审核通过时或者是管理员发布帖子时）
     * @param name  分类名字
     */
    public void addCCount(String CName)
    {
    	int count = 0;
    	PreparedStatement pre1;
    	String sql = "update category set CCount = ? where CName = ?";
    	RelationCom com = new RelationCom("jdbc:mysql://localhost/mybbs","root", "root123");
    	String Parent =  com.getParent(CName);
    	try
    	{
    		count = getCCount(CName);
    		pre1 = con.prepareStatement(sql);
    		pre1.setInt(1,count+1);
    		pre1.setString(2,CName);
    		pre1.execute(); 
    	}
		catch(SQLException e){}  
		
    	if(Parent!=null)
    		addCCount(Parent);

    		       	
    }
    /**
     * 减少该分类的帖子数量（当该分类中的某个帖子被管理员删除时）
     * @param name
     */
    public void delCCount(String CName)
    {
    	int count = 0;
    	PreparedStatement pre1;
    	String sql = "update category set CCount = ? where CName = ?";
    	RelationCom com = new RelationCom("jdbc:mysql://localhost/mybbs","root", "root123");
    	String Parent =  com.getParent(CName);
    	try
 		  {
 	        	
 	        count = getCCount(CName);
 			pre1 = con.prepareStatement(sql);
 			pre1.setInt(1, count-1);
 			pre1.setString(2, CName);
 			pre1.execute();    
 		  }
 		  catch(SQLException e){}
 		  
    	if(Parent!=null)
    		delCCount(Parent);

    	  
    }
    
    public Category getCategory(String CName)
    {
    	Category category = new Category(CName);
    	PreparedStatement pre;
    	String sql = "select * from category where CName = ?";
    	try
      	{
      		pre = con.prepareStatement(sql);
      		pre.setString(1, CName);
      		ResultSet rs = pre.executeQuery();
      		if(rs.next())
      		{
                 category.setCCreateDate(rs.getString("CCreateDate"));
                 category.setCdetail(rs.getString("Cdetail"));
                 category.setCount(rs.getInt("CCount"));
                 category.setCPath(rs.getString("CPath"));
                 return category;
      		}
      		
      		else
      			return null;
      	}
      	catch(SQLException e){return null;}
    }
    /**
     * 返回数据库中所有的记录
     * @return
     */
    public Vector getAllCatefory()
    {
    	Statement st;
    	String sql = "select * from category order by CPath asc";
    	Vector vc = new Vector();
    	try
    	{
    		st = con.createStatement();
    		ResultSet rs = st.executeQuery(sql);
    		while(rs.next())
    		{
    			Category category = new Category(rs.getString("CName"));
    			category.setCdetail(rs.getString("Cdetail"));
    			category.setCCreateDate(rs.getString("CCreateDate"));
                category.setCount(rs.getInt("CCount"));
                category.setCPath(rs.getString("CPath"));
                vc.add(category);
    		}
    		return vc;
    	}
    	catch(SQLException e){return null;}
    }
}