BBS的4个DAO

public class UserDAO
{
    JDBCTemplate userTemplate =new JDBCTemplate()
    {
         @Override
        protected RowMapper getRowMapper()
        {
            //返回具体的Mapper映射类
            return new UserMapper();
        }
    };
    /**
     * 得到当前用户编号的最大值
     */
    public int getMaxId()
    {
        String sql = "select max(userId) from bbsuser";
        int result = userTemplate.selectmaxId(sql);
        return result;  
    }
    
    /**
     * 添加用户，参数是User对象，返回值是新增用户的Id
     */
    public Object addUser(BBSUser bbsuser)
    {
      int id=getMaxId()+1;
       String sql="insert into bbsuser values(?,?,?,?,?,?) ";
       Object pk=userTemplate.save(sql,id,bbsuser.getUserName(),bbsuser.getUserPwd()
          ,bbsuser.getGender(),bbsuser.getHeadImg(),bbsuser.getUserState());
       return pk.toString(); 
    }
    /**
     *判断用户名是否存在，参数是用户名，返回值true表示已存在，false表示不存在、可用
     */
    public boolean exists(String name)
    {
        String sql ="select * from bbsuser where userName=?";
        List userList =userTemplate.query(sql,name);
        if(userList.size()>0)
        {
            return true;
        }
         return false;
    }
    /**
     *根据用户Id查找用户对象
     */
    public BBSUser finUser(int userid)
    {
        String sql="select * from bbsuser where userId=?";
        List userList =userTemplate.query(sql,userid);
        return (BBSUser)userList.get(0);
    }
    
    /**
     * 验证用户是否合法，参数是仅包含用户名和密码的User对象
     * 返回值true表示验证通过，且User对象中包含用户所有信息，返回值false表示验证失败
     */
    public boolean validUser(BBSUser bbsuser)
    {
        String sql ="select * from bbsuser where userName=? and userPwd=?";
        List userList =userTemplate.query(sql,bbsuser.getUserName(),bbsuser.getUserPwd());
        if(userList.size()>0)
        {
            BBSUser  user=(BBSUser)userList.get(0);
            bbsuser.setUserId(user.getUserId());
            bbsuser.setHeadImg(user.getHeadImg());
            bbsuser.setGender(user.getGender());
            bbsuser.setUserState(user.getUserState());
            return true;
        }
         return false;
    }
     
}




 

public class SectionDAO
{
    JDBCTemplate sectionTemplate =new JDBCTemplate()
    {
         @Override
        protected RowMapper getRowMapper()
        {
            //返回具体的Mapper映射类
            return new SectionMapper();
        }
    };
    
   /**
    * 查询父版块内所有的子版块，如果父版块编号为0,则查找所有的父版块 
    */
    public List<Section> getAllSection(int supersectionId)
    {
     String sql="select * from section where supersectionId=?";
     List sectionList=sectionTemplate.query(sql,supersectionId);
       return sectionList;
    }
    
    /**
     *查询编号为sectionId的指定版块
     */
    public Section getSection(int sectionId)
    {
      String sql="select * from section where sectionId=?";
        List sectionList=sectionTemplate.query(sql,sectionId);
       return (Section)sectionList.get(0);
    }
    
    
}


 

 

public class ArticleDAO
{

    JDBCTemplate articleTemplate =new JDBCTemplate()
    {
         @Override
        protected RowMapper getRowMapper()
        {
            //返回具体的Mapper映射类
            return new ArticleMapper();
        }
    };
    
    /**
     * 添加帖子，参数是Article 对象，返回值是新增贴子的ID
     */
    public Object addArticle(Article article)
    {
        int id=getMaxId()+1;
        String sql="insert into article values(?,?,?,sysDate,sysDate,?,?,?)";
        Object pk=articleTemplate.save(sql,id,article.getArticleTitle(),article.getArticleContent()
        ,article.getUserId()
        ,article.getSectionId(),article.getArticleState());
        return pk.toString();
    }
    /**
     * 查找贴子，根据指定的贴子编号查找贴子对象
     */
    public  Article  findArticleById(int articleId)
    {
        String sql="select * from article where articleId=? and articleState=1";
        List articleList= articleTemplate.query(sql, articleId);
        return (Article)articleList.get(0);
    }
    /**
     * 修改贴子，修改指定 article的贴子的内容修改为 article中的内容
     */
    public void  updateArticle (Article article)
    {
      String sql="update article set articleTitle=?,articleContent=?,articleupdateTime=sysDate where articleId=?";
      articleTemplate.update(sql,article.getArticleTitle(),article.getArticleContent()
          ,article.getArticleId());
      
    }
    /**
     * 查找编号为sectionId版块中最后发表的贴子
     */
    public Article getlastArticle(int sectionId)
    {
       
     String sql="select * from article where  articleId =(select max(articleId) from article where sectionId =? and articleState=1 )";
     List articleList= articleTemplate.query(sql,sectionId);
     if(articleList.size()>0)
     {
         return (Article)articleList.get(0); 
     }
     return null;
     
    }
    /**
     * 查找编号为sectionId版块中贴子的数量
     */
    public int getarticleCount(int sectionId)
    {
        
        String sql = "select count(articleId) from article where sectionId =? and articleState=1";
         int result = articleTemplate.selectmaxId(sql,sectionId);
        return result;  
     }
    /**
     * 查找指定页号的贴子
     */
    public List<Article> getallArticle(int sectionId,int currentPage ,int pageSize)
    {
        StringBuffer sbSql = new StringBuffer();
        sbSql.append("select * ")
        .append("from (")
            .append("select i.*, rownum rn from (")
            .append("select a.* ")
            .append("from article a where a.sectionId=? and a.articleState=1 ")
            .append(" order by a.articleId")
            .append(") i where rownum<=? ")
            .append(") ")
            .append("where rn >? ");
      //  System.out.println("sql=" + sbSql.toString());
        Object[] params = new Object[3];
        params[0]=sectionId;
        params[1]=currentPage * pageSize;
        params[2]=(currentPage - 1) * pageSize;
        List articleList =articleTemplate.query(sbSql.toString(), params);
         return articleList;
    }
    
    /**
     * 得到当前贴子编号的最大值
     */
    public int getMaxId()
    {
        String sql = "select max(articleId) from article  ";
        int result = articleTemplate.selectmaxId(sql);
        return result;  
    }
    
    /**
     * 返回总页数
     */
    public int getTotalPages(int sectionId,int pageSize) 
    {
        int   totalRecords =getarticleCount(sectionId);
        return (totalRecords + pageSize - 1) / pageSize;
    }
    /**
     * 删除贴子
     */
    public void deleteArticle(int articleId,int userId)
    {
        String sql="update article set articleState=0 where articleId=? and userId=?";
        articleTemplate.delete(sql,articleId,userId);
    }
  
    
}





 


 public class MessageDAO
{
    JDBCTemplate messageTemplate =new JDBCTemplate()
    {
         @Override
        protected RowMapper getRowMapper()
        {
            //返回具体的Mapper映射类
            return new MessageMapper();
        }
    };
    /**
     * 新增回贴，返回值为贴子编号
     */
    public Object addMessage(Message message)
    {
        int id=getMaxId()+1;
        String sql="insert into message values(?,?,?,sysDate,sysDate,?,?,?)";
      Object pk=messageTemplate.save(sql,id,message.getMessageTitle(),message.getMessageContent()
          ,message.getUserId(),message.getArticleId(),message.getMessageState());
        
        return pk.toString();
    }
    /**
     * 得到当前回贴编号的最大值
     */
    public int getMaxId()
    {
        String sql = "select max(messageId) from message";
        int result = messageTemplate.selectmaxId(sql);
        return result;  
    }
    /**
     * 更新回贴，将指定编号messageId的回复贴子更新为 message对象中的内容
     */
    public void updateMessage(Message message)
    {
        String sql="update message set messageTitle=?,messageContent=?,messageupdateTime=sysDate where messageId=?";
        messageTemplate.update(sql,message.getMessageTitle(),message.getMessageContent()
            ,message.getMessageId());
    }
    /**
     * 删除回贴
     */
    public  void deleteMessage(int messageId ,int userId)
    {
        String sql="update message set messageState=0 where  messageId=? and userId=?";
        messageTemplate.update(sql, messageId,userId);
    }
    /**
     * 得到制定贴子的回贴数量
     */
    public int getSelectArticleReply(int articleId)
    {
        String sql="select count(*) from message where articleId=? and messageState=1";
        int count =messageTemplate.selectmaxId(sql,articleId);
        return count;
    }
    public List<Message> getAllReply(int articleId)
    {
        String sql="select * from message where articleId=? and messageState=1";
        List mList=messageTemplate.query(sql, articleId);
        return mList;
    }
    
   
    
}