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实体对象，然后就可以将它放置到页面中进行显示
979
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
