2011/6/26 功能菜单模块分析_数据库操作层

2011/6/26 功能菜单模块分析_数据库操作层

1.BSMenuDBMang 类继承了 BSDBBase 类

方法一：得到功能菜单

    public ArrayList<MenuPojo> getMenuList(String where) throws Exception {
        ArrayList<MenuPojo> menuList = new ArrayList<MenuPojo>();
        StringBuffer strSQL = _getMenuSelectSQL(where);
        CachedRowSet rs = this.sqlHelper.queryCachedBySql(strSQL.toString());
        if (rs != null) {
            while (rs.next()) {
                menuList.add(this._setOneMenuPojo(rs));
            }
            rs.close();
        }
        return menuList;
    }

里面调用了另一个很重要的方法 _getMenuSelectSQL

    // 取得应用信息的SQL语句
    private StringBuffer _getMenuSelectSQL(String where) {
        StringBuffer strSQL = new StringBuffer();
        strSQL.append("select ");
        strSQL.append("t.MENU_ID,");
        strSQL.append("t.MENU_NAME,");
        strSQL.append("t.MENU_DESC,");
        strSQL.append("t.MENU_CLASS,");
        strSQL.append("t.MENU_STYLE as MENU_STYLE_ID,");
        // strSQL.append("t2.COL_VALUE_NAME as MENU_STYLE,");
        strSQL.append("t.MENU_STATE,");
        strSQL.append("t.JSFUN,");
        strSQL.append("t.IMGNAME,");
        strSQL.append("t.TOPAGE,");
        strSQL.append("t.ISOPEN,");
        strSQL.append("t.TOP_FLAG,");
        strSQL.append("t1.SEQ,");
        strSQL.append("t2.MENU_ID AS P_MENU_ID,");
        strSQL.append("t2.MENU_NAME AS P_MENU_NAME,");
        strSQL.append("(select count(*) from T_MENU_R tt where tt.P_MENU_ID = t.MENU_ID) as CHLD_NUM ");
        strSQL.append(" from T_MENU t, T_MENU_R t1, T_MENU t2 ");
        strSQL.append(" where ");
        strSQL.append("t1.MENU_ID(+) = t.MENU_ID ");
        strSQL.append("and t1.P_MENU_ID = t2.MENU_ID(+) ");
        if (!where.trim().equals("")) {
            strSQL.append(where);
        }
        System.out.println(strSQL);
        return strSQL;
    }

私有的方法，用于拼接sql语句，最终生成的sql语句如下所示：真的很长啊。。。里面的(+)貌似是左右联接，并且要注意，参数where是放在最后的那个select语句中的

select t.MENU_ID,t.MENU_NAME,t.MENU_DESC,t.MENU_CLASS,t.MENU_STYLE as MENU_STYLE_ID,t.MENU_STATE,t.JSFUN,t.IMGNAME,t.TOPAGE,t.ISOPEN,t.TOP_FLAG,t1.SEQ,t2.MENU_ID AS P_MENU_ID,t2.MENU_NAME AS P_MENU_NAME,(select count(*) from T_MENU_R tt where tt.P_MENU_ID = t.MENU_ID) as CHLD_NUM  from T_MENU t, T_MENU_R t1, T_MENU t2  where t1.MENU_ID(+) = t.MENU_ID and t1.P_MENU_ID = t2.MENU_ID(+)  and t.MENU_ID not in (select MENU_ID from T_MENU_R)
select t.MENU_ID,t.MENU_NAME,t.MENU_DESC,t.MENU_CLASS,t.MENU_STYLE as MENU_STYLE_ID,t.MENU_STATE,t.JSFUN,t.IMGNAME,t.TOPAGE,t.ISOPEN,t.TOP_FLAG,t1.SEQ,t2.MENU_ID AS P_MENU_ID,t2.MENU_NAME AS P_MENU_NAME,(select count(*) from T_MENU_R tt where tt.P_MENU_ID = t.MENU_ID) as CHLD_NUM  from T_MENU t, T_MENU_R t1, T_MENU t2  where t1.MENU_ID(+) = t.MENU_ID and t1.P_MENU_ID = t2.MENU_ID(+)  and t.MENU_ID in (select MENU_ID from T_MENU_R where P_MENU_ID='AA743723E31BAD81ED97A8F0DFDB0A3A')
select t.MENU_ID,t.MENU_NAME,t.MENU_DESC,t.MENU_CLASS,t.MENU_STYLE as MENU_STYLE_ID,t.MENU_STATE,t.JSFUN,t.IMGNAME,t.TOPAGE,t.ISOPEN,t.TOP_FLAG,t1.SEQ,t2.MENU_ID AS P_MENU_ID,t2.MENU_NAME AS P_MENU_NAME,(select count(*) from T_MENU_R tt where tt.P_MENU_ID = t.MENU_ID) as CHLD_NUM  from T_MENU t, T_MENU_R t1, T_MENU t2  where t1.MENU_ID(+) = t.MENU_ID and t1.P_MENU_ID = t2.MENU_ID(+)  and t.MENU_ID in (select MENU_ID from T_MENU_R where P_MENU_ID='WAITDO_SYS')

其中还调用了另一个很重要的方法：queryCachedBySql

    /**
     * <p>
     * 方法名称: queryCachedBySql
     * </p>
     * <p>
     * 方法功能描述:根据传入的SQL语句进行查询，将结果集封装到一个CachedRowSet中返回
     * </p>
     * <p>
     * 输入参数描述: String sql 查询类的SQL语句
     * </p>
     * <p>
     * 输出参数描述: ResultSet 结果数据集
     * </p>
     */
    public CachedRowSet queryCachedBySql(String sql) throws Exception {
        return this.queryCachedBySql(this.dbName, sql);
    }

    /**
     * <p>
     * 方法名称: queryCachedBySql
     * </p>
     * <p>
     * 方法功能描述:根据传入的SQL语句进行查询，将结果集封装到一个CachedRowSet中返回
     * </p>
     * <p>
     * 输入参数描述: String dbName 数据库名 String sql 查询类的SQL语句
     * </p>
     * <p>
     * 输出参数描述: ResultSet 结果数据集
     * </p>
     */
    public CachedRowSet queryCachedBySql(String dbName, String sql)
            throws Exception {
        CachedRowSet rst = new CachedRowSetImpl();
        ResultSet rs = null;
        try {
            if (this.sqlFacade == null) {
                this.sqlFacade = new SqlFacade(dbName);
            }
            rs = this.sqlFacade.executeQuery(sql);
            if (rs != null) {
                rst.populate(rs);
            }
        } catch (Exception exSql) {
            Debug.outInfo("dbName:" + dbName + " Sql:" + sql);
            if (rs != null) {
                close(rs);
            }
            if (rst != null) {
                close(rst);
            }
            Debug.outError("查询操作出错", exSql);
            throw exSql;
        } finally {
            if (rs != null) {
                rs.close();
            }
        }
        return rst;
    }

通过sqlhelper可以很方便的进行数据库的操作，而不用自己去连接数据库等等操作，也就是说，BSDBBse类对数据库操作进行了封装和优化，使其更加安全，更易于使用

方法二：得到一个功能菜单

    public MenuPojo getOneMenu(String where) throws Exception {
        MenuPojo onePojo = null;
        StringBuffer strSQL = _getMenuSelectSQL(where);
        if (!where.trim().equals("")) {
            strSQL.append(where);
        }
        CachedRowSet rs = this.sqlHelper.queryCachedBySql(strSQL.toString());
        if (rs != null && rs.next()) {
            onePojo = (this._setOneMenuPojo(rs));
            rs.close();
        }
        return onePojo;
    }

这个方法中也调用了那个sql语句拼接的方法，同时还调用了另一个很重要的方法 _setOneMenuPojo

    // 设置一个菜单实体
    private MenuPojo _setOneMenuPojo(CachedRowSet rs) throws Exception {
        MenuPojo oneMenu = new MenuPojo();
        if (rs.getString("MENU_ID") != null) {
            oneMenu.setMenuId(rs.getString("MENU_ID"));
        }
        if (rs.getString("MENU_NAME") != null) {
            oneMenu.setMenuName(rs.getString("MENU_NAME"));
        }
        if (rs.getString("MENU_DESC") != null) {
            oneMenu.setMenuDes(rs.getString("MENU_DESC"));
        }
        if (rs.getString("MENU_CLASS") != null) {
            oneMenu.setMenuClass(rs.getInt("MENU_CLASS"));
        }
        if (rs.getString("MENU_STATE") != null) {
            oneMenu.setState(rs.getInt("MENU_STATE"));
        }
        if (rs.getString("MENU_STYLE_ID") != null) {
            // oneMenu.setType(this.staticDB.getOneStatic("", rs
            // .getString("MENU_STYLE")));
            oneMenu.getMenuType().setStaticId(rs.getString("MENU_STYLE_ID"));
        }
        if (rs.getString("JSFUN") != null) {
            oneMenu.setJsfun(rs.getString("JSFUN").replaceAll("´", "'"));
        }
        if (rs.getString("IMGNAME") != null) {
            oneMenu.setImgName(rs.getString("IMGNAME"));
        }
        if (rs.getString("ISOPEN") != null) {
            oneMenu.setIsOpen(rs.getInt("ISOPEN"));
        }
        if (rs.getString("TOP_FLAG") != null) {
            oneMenu.setTopFlag(rs.getInt("TOP_FLAG"));
        }
        if (rs.getString("SEQ") != null) {
            oneMenu.setSeq(rs.getInt("SEQ"));
        }
        // 上级ID
        if (rs.getString("P_MENU_ID") != null) {
            oneMenu.setPmenuId(rs.getString("P_MENU_ID"));
        }
        if (rs.getString("P_MENU_NAME") != null) {
            oneMenu.setPmenuName(rs.getString("P_MENU_NAME"));
        }
        if (rs.getString("TOPAGE") != null) {
            oneMenu.setToPage(rs.getString("TOPAGE"));
        }
        if (rs.getString("CHLD_NUM") != null) {
            oneMenu.setCount(rs.getInt("CHLD_NUM"));
        }

        oneMenu.setChlMenuNum(rs.getInt("CHLD_NUM"));
        return oneMenu;
    }

意思就是首先从数据库中查询取得一个CachedRowSet ，但是只要其一个记录，然后将得到的这一个记录中的内容赋值给一个Pojo，这样就得到了一个具有其相应数据的

Menu实体对象，然后就可以将它放置到页面中进行显示