postdaoimpljdbc.java

一个留言板 系统平台： JSP+SQLServer 支持开通无限个数的留言板

/*
 * PostDaoImplJDBC.java
 *
 * Created on 2006年7月27日, 上午11:35
 *
 * To change this template, choose Tools | Options and locate the template under
 * the Source Creation and Management node. Right-click the template and choose
 * Open. You can then make changes to the template in the Source Editor.
 */

package tot.dao.jdbc;
import tot.dao.AbstractDao;
import tot.db.DBUtils;
import java.sql.*;
import java.util.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
/**
 *
 * @author Administrator
 */
public class PostDaoImplJDBC extends AbstractDao{
    private static Log log = LogFactory.getLog(PostDaoImplJDBC.class);
    /** Creates a new instance of PostDaoImplJDBC */
    public PostDaoImplJDBC() {
    }
    public Collection getPostList(int catalogId,int pageSize,int currentPage){
        StringBuffer sql=new StringBuffer(512);
        String fieldArr="id,f_user,f_title,f_content,f_reply,f_emot,f_homepage,f_email,f_im,f_ip,f_date,f_pass,f_hidden,f_catalogid";
        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_SQLSERVER) {
            sql.append("select top ");
            sql.append(pageSize);
            sql.append(fieldArr);
            sql.append(" from t_post where id not in(select top ");
            sql.append((currentPage-1)*pageSize);
            sql.append(" id from t_post where f_catalogid=");
            sql.append(catalogId);
            sql.append(" order by id desc)");
            sql.append(" and f_catalogid=");
            sql.append(catalogId);
            sql.append(" order by id desc");
        }
        else if(DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL){
            sql.append("select ");            
            sql.append(fieldArr);
            sql.append(" from t_post");
            sql.append(" where f_catalogid=");
            sql.append(catalogId);
            sql.append(" order by id desc limit ");
            sql.append((currentPage-1)*pageSize);
            sql.append(",");
            sql.append(pageSize);
        }
        return this.getData(sql.toString(),fieldArr);
    }
    public boolean reply(String content,int postid,Timestamp replydate){
        Connection conn = null;
        PreparedStatement ps = null;        
        boolean returnValue=true;
        try{
            conn = DBUtils.getConnection();
            ps=conn.prepareStatement("update t_post set f_reply=?,f_replytime=? where id=?");
            ps.setString(1,content);            
            ps.setTimestamp(2, replydate);
            ps.setInt(3,postid);            
            if(ps.executeUpdate()!=1) returnValue=false;
        } catch(SQLException e){            
            e.printStackTrace();
            return false;
        } finally{
            DBUtils.closePrepareStatement(ps);
            DBUtils.closeConnection(conn);
        }
        return returnValue;
    }
    public boolean add(String username,String title,String contnet,String emot,
            String homepage,String email,String im,String ip,
            Timestamp postDate,String pass,int hidden,int catalogid){
        Connection conn = null;
        PreparedStatement ps = null;        
        boolean returnValue=true;
        String sql="insert into t_post(f_user,f_title,f_content,f_emot,f_homepage,f_email,f_im,f_ip,f_date,f_pass,f_hidden,f_catalogid) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        try{
            conn = DBUtils.getConnection();
            ps=conn.prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, title);
            ps.setString(3, contnet);
            ps.setString(4, emot);
            ps.setString(5, homepage);
            ps.setString(6, email);
            ps.setString(7, im);
            ps.setString(8, ip);
            ps.setTimestamp(9, postDate);
            ps.setString(10, pass);
            ps.setInt(11,hidden);
            ps.setInt(12,catalogid);
            if(ps.executeUpdate()!=1) returnValue=false;
        } catch(SQLException e){
            log.info("catch exception when call add method: \n"+sql);
            e.printStackTrace();
            return false;
        } finally{
            DBUtils.closePrepareStatement(ps);
            DBUtils.closeConnection(conn);
        }
        return returnValue;
    }
    public boolean delePost(int postid){
        return exe("delete from t_post where id="+postid);
    }
    public int getPostNum(int catalogId){
        int returnValue=0;
        String sql="select count(*) from t_post where f_catalogid="+catalogId;
        returnValue=getDataCount(sql);
        return returnValue;
    }
}