sqlservermenudao.java~7~

一个基于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.MenuDAO;
import com.hope.itissue.sys_info.bean.MenuDTO;
import com.hope.itissue.sys_info.bean.MenuTypeDTO;
import com.hope.itissue.sys_info.bean.TemplateDTO;
import java.util.ArrayList;
import java.io.File;

public class SqlserverMenuDAO implements MenuDAO {
    Log log = LogFactory.getLog(SqlserverMenuDAO.class);

    public ArrayList getMenus() throws BaseException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList menuList = new ArrayList();
        try {
            String sql =
                    "select * from DN_Menu order by MenuOrder,ID";
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                MenuDTO menuDTO = new MenuDTO();
                menuDTO.setID(rs.getString("ID"));
                menuDTO.setMenuName(rs.getString("MenuName"));
                menuDTO.setParentID(rs.getString("ParentID"));
                menuList.add(menuDTO);
            }

        } 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 menuList;
    }

    public ArrayList getMenuList(String menuid) throws BaseException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList menuList = new ArrayList();
        try {
            String sql = "select a.*, b.TypeName from DN_Menu a, DN_Type b "
                +" Where a.Type = b.ID and a.ParentID=? order by a.MenuOrder, a.ID";
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, menuid);
            rs = pstm.executeQuery();
            while (rs.next()) {
                MenuDTO menuDTO = new MenuDTO();
                menuDTO.setID(rs.getString("ID"));
                menuDTO.setMenuName(rs.getString("MenuName"));
                menuDTO.setParentID(rs.getString("ParentID"));
                menuDTO.setMenuOrder(rs.getString("MenuOrder"));
                menuDTO.setIsUse(rs.getString("IsUse"));
                menuDTO.setType(rs.getString("TypeName"));
                menuList.add(menuDTO);
            }

        } 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 menuList;
    }

    public MenuDTO getMenuDTO(String menuid) throws BaseException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        MenuDTO menuDTO = null;
        String sql = "select * from DN_Menu where ID=?";
        try {
            //定义连接数据库的Connection对象及PreparedStatement对象
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, menuid);
            rs = pstm.executeQuery();
            //如果结果集存在则将信息封装入DeptDTO对象
            if (rs.next()) {
                menuDTO = new MenuDTO();
                menuDTO.setID(rs.getString("ID"));
                menuDTO.setMenuName(rs.getString("MenuName"));
                menuDTO.setParentID(rs.getString("ParentID"));
                menuDTO.setMenuOrder(rs.getString("MenuOrder"));
                menuDTO.setIsUse(rs.getString("IsUse"));
                menuDTO.setType(rs.getString("Type"));
                menuDTO.setTemplate(rs.getString("Template"));
            }
        } 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 menuDTO;
    }

    public void addMenu(MenuDTO menuDTO) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.getConnection();
            String sql = "insert into DN_Menu(MenuName, ParentID, MenuOrder, "
                         +"IsUse, Type, Template)values(?,?,?,?,?,?)";
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, menuDTO.getMenuName());
            pstm.setString(2, menuDTO.getParentID());
            pstm.setString(3, menuDTO.getMenuOrder());
            pstm.setString(4, menuDTO.getIsUse());
            pstm.setString(5, menuDTO.getType());
            pstm.setString(6, menuDTO.getTemplate());
            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 editMenu(MenuDTO menuDTO) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.getConnection();
            String sql = "update DN_Menu set MenuName = '" + (menuDTO.getMenuName()).replaceAll("'","''")
                         + "', MenuOrder = '" + menuDTO.getMenuOrder()
                         + "', IsUse = '" + menuDTO.getIsUse()
                         + "', Type = '" + menuDTO.getType()
                         + "' where ID='" + menuDTO.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 delMenu(String menuid, String fileBaseDir) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String sql_sel = "select DnetImage from DN_Item where ParentID='" + menuid + "'";
        String sql_item = "delete from DN_Item where ParentID='" + menuid + "'";
        String sql_chd = "select * from DN_Menu where ParentID='" + menuid + "'";
        String sql = "delete from DN_Menu where ID = '" + menuid + "'";
        try {
            conn = DBHelper.getConnection();
            //删当前栏目下的文章中的上传得文件
            pstm = conn.prepareStatement(sql_sel);
            rs = pstm.executeQuery();
            String[] fileNames = null;
            while (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_item);
            pstm.executeUpdate();
            //删子栏目
            pstm = conn.prepareStatement(sql_chd);
            rs = pstm.executeQuery();
            while (rs.next()) {
                delMenu(rs.getString("ID"), fileBaseDir);
            }
            //最后删除当前栏目
            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 ArrayList getMenuTypeList() throws BaseException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList menuTypeList = new ArrayList();
        try {
            String sql =
                    "select * from DN_Type Where IsUse=?";
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, "1");
            rs = pstm.executeQuery();
            while (rs.next()) {
                MenuTypeDTO menuTypeDTO = new MenuTypeDTO();
                menuTypeDTO.setID(rs.getString("ID"));
                menuTypeDTO.setTypeName(rs.getString("TypeName"));
                menuTypeDTO.setIsUse(rs.getString("IsUse"));
                menuTypeList.add(menuTypeDTO);
            }

        } 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 menuTypeList;
    }

    public ArrayList getTemplateList() throws BaseException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList templateList = new ArrayList();
        try {
            String sql =
                    "select * from DN_Template";
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                TemplateDTO templateDTO = new TemplateDTO();
                templateDTO.setID(rs.getString("ID"));
                templateDTO.setTemplateName(rs.getString("TemplateName"));
                templateList.add(templateDTO);
            }

        } 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 templateList;
    }


    public void stopMenu(String menuid) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.getConnection();
            String sql = "update DN_Menu set IsUse = '0' where ID='" + menuid + "'";
            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 startMenu(String menuid) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        try {
            conn = DBHelper.getConnection();
            String sql = "update DN_Menu set IsUse = '1' where ID='" + menuid + "'";
            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);
        }
    }

}