sqlservermenudao.java~12~

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

package com.hope.itissue.index_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.index_info.dao.MenuDAO;
import com.hope.itissue.index_info.bean.MenuDTO;
import com.hope.itissue.index_info.bean.ItemDTO;
import com.hope.itissue.index_info.bean.MenuTypeDTO;
import java.util.ArrayList;
import java.io.File;
import com.hope.itissue.shared.Pager;

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

    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, c.TemplateName from DN_Menu a, "
                +" DN_Type b, DN_Template c Where a.Type = b.ID and a.Template = c.ID "
                +" and a.ParentID=? and a.IsUse=1 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"));
                menuDTO.setTemplate(rs.getString("Template"));
                menuDTO.setTemplateName(rs.getString("TemplateName"));
                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, String type) throws BaseException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList menuList = new ArrayList();
        try {
            String sql = "select a.*, b.TypeName, c.TemplateName from DN_Menu a, "
                +" DN_Type b, DN_Template c Where a.Type = b.ID and a.Template = c.ID "
                +" and a.ParentID=? and a.Type<>? and a.IsUse=1"
                +" order by a.MenuOrder, a.ID";
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, menuid);
            pstm.setString(2, type);
            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"));
                menuDTO.setTemplate(rs.getString("Template"));
                menuDTO.setTemplateName(rs.getString("TemplateName"));
                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"));
            }
        } 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 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 String getCurrenPlace(String menuid) throws BaseException{
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        String returnString = "";
        try {
            String theID = menuid;
            String sql = "";
            boolean flag = false;
            conn = DBHelper.getConnection();

            MenuDTO chdmenuDTO = new MenuDTO();
            chdmenuDTO = this.getFirChdMenuDTO(menuid);//看当前栏目有没有子栏目
            if (chdmenuDTO != null) {
                sql = "select top 1 MenuName from DN_Menu Where ParentID='"
                      + theID +"' order by MenuOrder";
                pstm = conn.prepareStatement(sql);
                rs = pstm.executeQuery();
                if (rs.next()) {
                    returnString = rs.getString("MenuName") + "->" + returnString;
                }
            }
            do {
                flag = false;
                sql = "select MenuName, ParentID from DN_Menu Where ID='" + theID +"'";
                pstm = conn.prepareStatement(sql);
                rs = pstm.executeQuery();
                if (rs.next()) {
                    returnString = rs.getString("MenuName") + "->" + returnString;
                    theID = rs.getString("ParentID");
                    flag = true;
                }
            } while (flag);
            returnString = returnString.substring(0,returnString.length()-2);
        } 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 "当前位置："+returnString;
    }

    /**
     * 得到当前栏目的第一个子栏目的文章。
     * 如果当前栏目没有子栏目，那么显示当前栏目的文章。
     */
    public ArrayList getFirChdMenuItems(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 top " +pageSize + " * from DN_Item "
                         +" where (id not in (select top " + pageSize*(toPage-1)
                         +" id from DN_Item where ParentID=?"