sqlserveritemdao.java~3~

一个基于Java的新闻发布系统

package com.hope.itissue.sys_info.dao.sqlserver;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import com.hope.common.exception.BaseException;
import com.hope.common.util.db.DBHelper;
import com.hope.itissue.sys_info.dao.ItemDAO;
import com.hope.itissue.sys_info.bean.ItemDTO;
import java.util.ArrayList;
import java.io.File;
import com.hope.itissue.shared.Pager;

public class SqlserverItemDAO implements ItemDAO {
    Log log = LogFactory.getLog(SqlserverItemDAO.class);

    public ArrayList getItemList(String menuid, int pageSize, Pager page) throws BaseException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList itemList = new ArrayList();
        int toPage = Integer.parseInt(page.getQueryParameter("toPage"));
        if (toPage < 1) toPage = 1;
        try {
//            String sql = "select * from DN_Item Where ParentID=? order by ID DESC";
            String sql = "select top " + pageSize + " * from DN_Item "
                         +" where (id not in (select top " + pageSize*(toPage-1)
                         +" id from DN_Item where ParentID = '" + menuid
                         + "' order by ID DESC)) "
                         +" and ParentID = '" + menuid + "' order by ID DESC";
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                ItemDTO itemDTO = new ItemDTO();
                itemDTO.setID(rs.getString("ID"));
                itemDTO.setParentID(rs.getString("ParentID"));
                itemDTO.setDnetName(rs.getString("DnetName"));
                itemDTO.setDnetContent(rs.getString("DnetContent"));
                itemDTO.setDnetDate(rs.getString("DnetDate"));
                itemDTO.setIsUse(rs.getString("IsUse"));
                itemList.add(itemDTO);
            }

        } catch (BaseException e) {
            //设置记入日志的信息，并将异常封装为自定义异常抛出
            log.error("error.OptDataBase.Error", e);
            throw e;
        } catch (SQLException e) {
            log.error("error.OptDataBase.Error", e);
            throw new BaseException("error.OptDataBase.Error");
        } finally {
            //关闭连接
            DBHelper.release(conn, pstm, rs);
        }
        return itemList;
    }

    public ItemDTO getItemDTO(String itemid) throws BaseException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ItemDTO itemDTO = null;
        String sql = "select * from DN_Item where ID=?";
        try {
            //定义连接数据库的Connection对象及PreparedStatement对象
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, itemid);
            rs = pstm.executeQuery();
            //如果结果集存在则将信息封装入DeptDTO对象
            if (rs.next()) {
                itemDTO = new ItemDTO();
                itemDTO.setID(rs.getString("ID"));
                itemDTO.setParentID(rs.getString("ParentID"));
                itemDTO.setDnetName(rs.getString("DnetName"));
                itemDTO.setDnetContent(rs.getString("DnetContent"));
                itemDTO.setDnetDate(rs.getString("DnetDate"));
                itemDTO.setIsUse(rs.getString("IsUse"));
            }
        } catch (BaseException e) {
            //设置记入日志的信息，并将异常封装为自定义异常抛出
            log.error("error.OptDataBase.Error", e);
            throw e;
        } catch (SQLException e) {
            log.error("error.OptDataBase.Error", e);
            throw new BaseException("error.OptDataBase.Error");
        } finally {
            //关闭连接
            DBHelper.release(conn, pstm, rs);
        }
        return itemDTO;
    }

    public void addItem(ItemDTO itemDTO) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.getConnection();

            String sql = "insert into DN_Item(ParentID, DnetName, DnetContent, "
                         +"DnetDate, IsUse)values('"
                         +(String)itemDTO.getParentID()+"','"
                         +((String)itemDTO.getDnetName()).replaceAll("'","''")+"','"
                         +((String)itemDTO.getDnetContent())+"','"
                         +(String)itemDTO.getDnetDate()+"','"
                         +(String)itemDTO.getIsUse()+"')";
            pstm = conn.prepareStatement(sql);
            pstm.executeUpdate();
        } catch (BaseException e) {
            //设置记入日志的信息，并将异常封装为自定义异常抛出
            log.error("error.OptDataBase.Error", e);
            throw e;
        } catch (SQLException e) {
            log.error("error.OptDataBase.Error", e);
            throw new BaseException("error.OptDataBase.Error");
        }
        finally {
            //关闭连接
            DBHelper.release(conn, pstm, rs);
        }
    }

    public void editItem(ItemDTO itemDTO, String newSaveFile,
                         String fileBaseDir) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.getConnection();
            if (newSaveFile != null && !newSaveFile.equalsIgnoreCase("no_file") &&
                !newSaveFile.equalsIgnoreCase("no_match")) {
                String sql_sel = "select DnetImage from DN_Item where ID='" +
                                 itemDTO.getID() + "'";
                pstm = conn.prepareStatement(sql_sel);
                rs = pstm.executeQuery();
                String[] fileNames = null;
                if (rs.next()) {
                    fileNames = rs.getString("DnetImage").split(":");
                    if (fileNames != null && fileNames.length == 2) {
                        File file = new File(fileBaseDir + "files\\upload\\" +
                                             fileNames[1]);
                        if (file.exists()) {
                            file.delete();
                        }
                    }
                }
            }

            String sql = "update DN_Item set ParentID = '" + itemDTO.getParentID()
                         + "', DnetName = '" + (itemDTO.getDnetName()).replaceAll("'","''")
                         + "', DnetContent = '" + (itemDTO.getDnetContent()).replaceAll("'","''")
                         + "', DnetDate = '" + itemDTO.getDnetDate();
            if ((itemDTO.getSourceFileName() != null &&
                 itemDTO.getSourceFileName().length() != 0) &&
                (itemDTO.getWriteFileName() != null &&
                 !itemDTO.getWriteFileName().equalsIgnoreCase("no_file") &&
                 !itemDTO.getWriteFileName().equalsIgnoreCase("no_match"))) {
                String Slave = itemDTO.getSourceFileName() + ":"
                               + itemDTO.getWriteFileName();
                sql += "',DnetImage='" + Slave.replaceAll("'","''");
            }
            sql += "' where ID='" + itemDTO.getID() + "'";

            pstm = conn.prepareStatement(sql);
            pstm.executeUpdate();
        } catch (BaseException e) {
            //设置记入日志的信息，并将异常封装为自定义异常抛出
            log.error("error.OptDataBase.Error", e);
            throw e;
        } catch (SQLException e) {
            log.error("error.OptDataBase.Error", e);
            throw new BaseException("error.OptDataBase.Error");
        }

        finally {
            //关闭连接
            DBHelper.release(conn, pstm, rs);
        }
    }

    public void delItem(String itemid, String fileBaseDir) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String sql_sel = "select DnetImage from DN_Item where ID='" + itemid + "'";
        String sql = "delete from DN_Item where ID = '" + itemid + "'";
        try {
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql_sel);
            rs = pstm.executeQuery();
            String[] fileNames = null;
            if (rs.next()) {
                fileNames = rs.getString("DnetImage").split(":");
                if (fileNames != null && fileNames.length == 2) {
                    File file = new File(fileBaseDir + "files\\upload\\" +
                                         fileNames[1]);
                    if (file.exists()) {
                        file.delete();
                    }
                }
            }
            pstm = conn.prepareStatement(sql);
            pstm.executeUpdate();
        } catch (BaseException e) {
            //设置记入日志的信息，并将异常封装为自定义异常抛出
            log.error("error.OptDataBase.Error", e);
            throw e;
        } catch (SQLException e) {
            log.error("error.OptDataBase.Error", e);
            throw new BaseException("error.OptDataBase.Error");
        } finally {
            //关闭连接
            DBHelper.release(conn, pstm, rs);
        }
    }

    public void stopItem(String itemid) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.getConnection();
            String sql = "update DN_Item set IsUse = '0' where ID='" + itemid + "'";
            pstm = conn.prepareStatement(sql);
            pstm.executeUpdate();
        } catch (BaseException e) {
            //设置记入日志的信息，并将异常封装为自定义异常抛出
            log.error("error.OptDataBase.Error", e);
            throw e;
        } catch (SQLException e) {
            log.error("error.OptDataBase.Error", e);
            throw new BaseException("error.OptDataBase.Error");
        }

        finally {
            //关闭连接
            DBHelper.release(conn, pstm, rs);
        }
    }

    public void startItem(String itemid) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.getConnection();
            String sql = "update DN_Item set IsUse = '1' where ID='" + itemid + "'";
            pstm = conn.prepareStatement(sql);
            pstm.executeUpdate();
        } catch (BaseException e) {
            //设置记入日志的信息，并将异常封装为自定义异常抛出
            log.error("error.OptDataBase.Error", e);
            throw e;
        } catch (SQLException e) {
            log.error("error.OptDataBase.Error", e);
            throw new BaseException("error.OptDataBase.Error");
        }

        finally {
            //关闭连接
            DBHelper.release(conn, pstm, rs);
        }
    }

    public int getItemNum(String menuid) throws BaseException{
        String sql="select count(*) from DN_Item where ParentID = ? ";
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int i=0;
        try {
            //连接数据库
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,menuid);
            rs = pstm.executeQuery();
            if(rs.next()){
                i=rs.getInt(1);
            }
        }
        catch (BaseException e) {
            // 设置记入日志的信息，并将异常封装为自定义异常抛出
            log.error("error.OracleUserDAO.userNum", e);
            throw e;
        }
        catch (SQLException e) {
            log.error("error.OracleUserDAO.userNum", e);
            throw new BaseException("error.OracleUserDAO.userNum");
        }
        finally {
            // 关闭Connection，PreparedStatement，ResultSet对象
            DBHelper.release(conn, pstm, rs);
        }
       return i;
    }

}