imenuimpl.java

struts+spring+jdbc权限管理 菜单树实现

package com.hiiso.crm.common.authority.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.apache.log4j.Logger;

import com.hiiso.crm.common.authority.IMenu;
import com.hiiso.crm.common.exception.AuthorityException;
import com.hiiso.crm.common.jdbc.JdbcCallback;
import com.hiiso.crm.common.jdbc.JdbcTemplate;
import com.hiiso.crm.common.jdbc.NextValue;
import com.hiiso.crm.common.jdbc.PreparedStatementSetter;
import com.hiiso.crm.common.pojo.Menu;

/*************************************************************************
* SYSTEM:       基金CRM系统
* SUBSYS:       CRM权限管理模块
* DESCRIPTION:  CRM菜单管理接口实现
* AUTHOR:       YAOYI
* CREATE DATE:  2008/10/30
* COPYRIGHT:    (c)Copyright 2008 HISUN Corporation. All rights reserved.
* VERSION:      V1.0G
* EDIT HISTORY:
*************************************************************************/

public class IMenuImpl implements IMenu {
	
	static  Logger  logger  =  Logger.getLogger("crm.operator");
	private DataSource dataSource;
	private Connection conn;
	private NextValue nextValue;
	private List list;
	private List list1;
	private List list2;
	private String seq = "";

	public DataSource getDataSource() {
		return dataSource;
	}

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	
	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public NextValue getNextValue() {
		return nextValue;
	}

	public void setNextValue(NextValue nextValue) {
		this.nextValue = nextValue;
	}
	//添加菜单
	public void addMenu(final Menu menu) throws AuthorityException {
		try{
			conn = dataSource.getConnection();
		}catch(Exception ex){
			throw new AuthorityException("获取连接失败！");
		}
		JdbcTemplate tmp = new JdbcTemplate(conn);
		String sql ="insert into TBL_SYSMENU(CMENUID,CMENUNAME,CMENUDESC,CMENUHREF,CMENUIMAGE,CPARENTID,IORDERID) ";
		sql += "values(?,?,?,?,?,?,?)";
		logger.info(IMenuImpl.class.getName()+"sql语句操作"+sql);
		tmp.save(sql, new PreparedStatementSetter(){
			public void setter(PreparedStatement pstmt) throws SQLException {
							pstmt.setString(1, menu.getMenuId());
							pstmt.setString(2, menu.getMenuName());
							if(menu.getDesc() != null){
								pstmt.setString(3, menu.getDesc());
							}else{
								pstmt.setString(3, "");
							}
							pstmt.setString(4, menu.getHttpRef());
							pstmt.setString(5, "");
							pstmt.setString(6, menu.getParentId());
							if(menu.getOrderId() != null && !menu.getOrderId().equals("")){
								pstmt.setInt(7, Integer.parseInt(menu.getOrderId()));
							}else{
								pstmt.setInt(7, 1);
							}
			}
		});
	}
	
	//删除菜单
	public void deleteMenu(final String menuId) throws AuthorityException {
		try{
			conn = dataSource.getConnection();
		}catch(Exception ex){
			throw new AuthorityException("获取连接失败！");
		}
		String sql = "delete from tbl_sysmenu t where t.CMENUID=?";
		JdbcTemplate tmp = new JdbcTemplate(conn);
		logger.info(IMenuImpl.class.getName()+"sql语句操作"+sql);
		tmp.delete(sql, new PreparedStatementSetter(){
			public void setter(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, menuId);	
				System.out.println("The MenuId is " + menuId);
			}
		});
	}
	//获取所有菜单
	synchronized public List getAllMenu() throws AuthorityException {
		list = new ArrayList();
		try{
			conn = dataSource.getConnection();
		}catch(Exception ex){
			throw new AuthorityException("获取连接失败！");
		}
		String sql = "select t.CMENUID,T.CMENUNAME,T.CMENUDESC,T.CMENUHREF,T.CPARENTID,T.IORDERID from tbl_sysmenu t";
		JdbcTemplate tmp = new JdbcTemplate(conn);
		logger.info(IMenuImpl.class.getName()+"sql语句操作"+sql);
		tmp.find(sql, new JdbcCallback(){
				public void doExecute(ResultSet rs) throws SQLException {
					while(rs.next()){
						Menu menu = new Menu();
						menu.setMenuId(rs.getString(1));
						menu.setMenuName(rs.getString(2));
						menu.setDesc(rs.getString(3));
						menu.setHttpRef(rs.getString(4));
						menu.setParentId(rs.getString(5));
						menu.setOrderId(rs.getString(6));
						list.add(menu);
					}
				}
		   	}
		);
		return list;
	}
	//根据权限获取菜单
	synchronized public List getMenu(final String roleId) throws AuthorityException {
		list1 = new ArrayList();
		String sql = "select t.CMENUID,T.CMENUNAME,T.CMENUDESC,T.CMENUHREF,T.CPARENTID,T.IORDERID from tbl_sysmenu t,tbl_rights t1 ";
		sql += "where t.CMENUID=t1.CMENUID and t1.CROLEID=?";
		JdbcTemplate tmp = new JdbcTemplate(conn);
		logger.info(IMenuImpl.class.getName()+"sql语句操作"+sql);
		tmp.find(sql, new JdbcCallback(){
				public void doExecute(ResultSet rs) throws SQLException {
					while(rs.next()){
						Menu menu = new Menu();
						menu.setMenuId(rs.getString(1));
						menu.setMenuName(rs.getString(2));
						menu.setDesc(rs.getString(3));
						menu.setHttpRef(rs.getString(4));
						menu.setParentId(rs.getString(5));
						menu.setOrderId(rs.getString(6));
						list1.add(menu);
					}
				}
		   	},
		   	new PreparedStatementSetter(){
				public void setter(PreparedStatement pstmt) throws SQLException {
					pstmt.setString(1, roleId);
				}	
		   	}
		);
		return list1;
	}
	//修改菜单
	public void editMenu(final Menu menu) throws AuthorityException {
		try{
			conn = dataSource.getConnection();
		}catch(Exception ex){
			throw new AuthorityException("获取连接失败！");
		}
		JdbcTemplate tmp = new JdbcTemplate(conn);
		String sql ="update tbl_sysmenu t set t.CMENUNAME=?,t.CMENUHREF=? where t.CMENUID=?";
		logger.info(IMenuImpl.class.getName()+"sql语句操作"+sql);
		tmp.update(sql, new PreparedStatementSetter(){
			public void setter(PreparedStatement pstmt) throws SQLException {
							pstmt.setString(1, menu.getMenuName());
							pstmt.setString(2, menu.getHttpRef());
							pstmt.setString(3, menu.getMenuId());
			}
		});		
	}

	//获取菜单编号
	synchronized public String getMenuSeq() throws AuthorityException {
		seq = "";
		try{
			conn = dataSource.getConnection();
		}catch(Exception ex){
			throw new AuthorityException("获取连接失败！");
		}
		JdbcTemplate tmp = new JdbcTemplate(conn);
		String sql = "select SEQ_MENU_ID.Nextval from dual";
		logger.info(IMenuImpl.class.getName()+"sql语句操作"+sql);
		tmp.find(sql, new JdbcCallback(){
			public void doExecute(ResultSet rs) throws SQLException {
				if(rs.next()){
					seq = rs.getString(1);
				}
			}	
		});
		return seq;
	}
	
	//获取子菜单
	public List getChildMenu(final String parentId) throws AuthorityException {
		String sql = "select t.CMENUID,T.CMENUNAME,T.CMENUDESC,T.CMENUHREF,T.CPARENTID,T.IORDERID from tbl_sysmenu t";
		sql += " where t.CPARENTID=?";
		JdbcTemplate tmp = new JdbcTemplate(conn);
		logger.info(IMenuImpl.class.getName()+"sql语句操作"+sql);
		tmp.find(sql, new JdbcCallback(){
				public void doExecute(ResultSet rs) throws SQLException {
					list2 = new ArrayList();
					while(rs.next()){
						Menu menu = new Menu();
						menu.setMenuId(rs.getString(1));
						menu.setMenuName(rs.getString(2));
						menu.setDesc(rs.getString(3));
						menu.setHttpRef(rs.getString(4));
						menu.setParentId(rs.getString(5));
						menu.setOrderId(rs.getString(6));
						list2.add(menu);
					}
				}
		   	},
		   	new PreparedStatementSetter(){
				public void setter(PreparedStatement pstmt) throws SQLException {
					pstmt.setString(1, parentId);
				}	
		   	}
		);
		return list2;
	}

	//删除权限表中数据
	public void deleteRights(final String menuId) throws AuthorityException {
		try{
			conn = dataSource.getConnection();
		}catch(Exception ex){
			throw new AuthorityException("获取连接失败！");
		}
		String sql = "delete from tbl_rights t where t.CMENUID=?";
		JdbcTemplate tmp = new JdbcTemplate(conn);
		logger.info(IMenuImpl.class.getName()+"sql语句操作"+sql);
		tmp.delete(sql, new PreparedStatementSetter(){
			public void setter(PreparedStatement pstmt) throws SQLException {
				pstmt.setString(1, menuId);	
			}
		});
	}
}