sqlservermenudao.java~17~

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

    public ArrayList getSearchItems(String key, 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=?"
                         +" and IsUse=1 order by ID DESC)) and ParentID=? "
                         +" and IsUse=1 order by ID desc";
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            MenuDTO chdmenuDTO = new MenuDTO();
            chdmenuDTO = this.getFirChdMenuDTO(menuid);
            if (chdmenuDTO != null) {
                pstm.setString(1, chdmenuDTO.getID());
                pstm.setString(2, chdmenuDTO.getID());
            }else {
                pstm.setString(1, menuid);
                pstm.setString(2, menuid);
            }

            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 MenuDTO getFirChdMenuDTO(String menuid) throws BaseException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        MenuDTO menuDTO = null;
        String sql = "select top 1 * from DN_Menu where ParentID=? and IsUse=1";
        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 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 top " + pageSize + " * from DN_Item "
                         +" where (id not in (select top " + pageSize*(toPage-1)
                         +" id from DN_Item where ParentID = '" + menuid
                         + "' and IsUse=1 order by ID DESC)) "
                         +" and IsUse=1 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 ArrayList getItemList(String menuid, int topNum) throws BaseException {
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        ArrayList itemList = new ArrayList();
        try {
            String sql = "";
            if (topNum != 0) {
                sql = "select top " + topNum + " * from DN_Item "
                      +" where ParentID = '" + menuid + "' order by ID DESC";
            }else{
                sql = "select * from DN_Item "
                      +" where 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 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);

            MenuDTO chdmenuDTO = new MenuDTO();
            chdmenuDTO = this.getFirChdMenuDTO(menuid);
            if (chdmenuDTO != null) {
                pstm.setString(1, chdmenuDTO.getID());
            }else {
                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;
    }

    public int getItemsNum(String key) throws BaseException{
        String sql="select count(*) from DN_Item where DnetName like '%" + key + "%'";
        Connection conn = null;
        PreparedStatement pstm = null;
        ResultSet rs = null;
        int i=0;
        try {
            //连接数据库
            conn = DBHelper.getConnection();
            pstm = conn.prepareStatement(sql);
            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;
    }

}