actiondao.java

包括所有开发文档以及数据库文件

package dao;

import java.sql.*;
import java.util.Vector; //import java.util.Vector;

import bo.*;

public class ActionDAO {

	/**
	 * 根据角色查询权限
	 * 
	 * @param roleId
	 * @return
	 */

	public Vector selectActionByRole(String roleId) {

		Vector<Action> vResult = new Vector<Action>(1, 1);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from action where actionId in (select actionId from role_action where roleId=?)";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, roleId);
			rs = pstmt.executeQuery();
			int i = 0;
			while (rs != null && rs.next() && i < 20) {
				// 读到数据，生成实体类
				Action action = new Action();
				action.setActionId(rs.getInt("actionId"));
				action.setActionName(rs.getString("actionName"));
				action.setActionDescription(rs.getString("actionDescription"));
				vResult.add(action);
				i++;
			}

			rs.close();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return vResult;
	}

	/**
	 * 根据角色查询总权限
	 * 
	 * @param roleId
	 * @return
	 */
	public Vector selectAllActionByRole(String roleId) {

		Vector<Action> vResult = new Vector<Action>(1, 1);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select distinct action.* from action,group_action,role_action where (action.actionId=group_action.actionId and groupId = (select groupId from group_role where roleId=?)) or (action.actionId=role_action.actionId and roleId=?)order by actionId asc;";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, roleId);
			pstmt.setString(2, roleId);
			rs = pstmt.executeQuery();
			int i = 0;
			while (rs != null && rs.next() && i < 20) {
				// 读到数据，生成实体类
				Action action = new Action();
				action.setActionId(rs.getInt("actionId"));
				action.setActionName(rs.getString("actionName"));
				action.setActionDescription(rs.getString("actionDescription"));
				vResult.add(action);
				i++;
			}

			rs.close();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return vResult;
	}

	/**
	 * 修改角色权限
	 * 
	 * @param roleId
	 * @param actionId
	 */
	public void saveRoleAction(String roleId, String actionId) {

		Connection con = null;
		PreparedStatement pstmt = null;
		// ResultSet rs = null;
		String sql = "insert into role_action(roleId,actionId) values(?,?);";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, roleId);
			pstmt.setString(2, actionId);
			pstmt.executeUpdate();

			// rs.close();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void deleteRoleAction(String roleId) {

		Connection con = null;
		PreparedStatement pstmt = null;
		// ResultSet rs = null;
		String sql = "delete from role_action where roleid=?;";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, roleId);
			pstmt.executeUpdate();

			// rs.close();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 查询组权限
	 * 
	 * @param groupId
	 * @return
	 */
	public Vector selectActionInGroup(String groupId) {

		Vector<Action> vResult = new Vector<Action>(1, 1);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select *from action where actionId in(select actionId from group_action where groupId=?);";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, groupId);
			rs = pstmt.executeQuery();
			int i = 0;
			while (rs != null && rs.next() && i < 20) {
				// 读到数据，生成实体类
				Action action = new Action();
				action.setActionId(rs.getInt("actionId"));
				action.setActionName(rs.getString("actionName"));
				action.setActionDescription(rs.getString("actionDescription"));
				vResult.add(action);
				i++;
			}

			rs.close();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return vResult;
	}

	/**
	 * 查询组总权限
	 * 
	 * @param roleId
	 * @return
	 */
	public Vector selectAllGroupAction(String roleId) {

		Vector<Action> vResult = new Vector<Action>(1, 1);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select distinct action.* from action,group_action,role_action where action.actionId=group_action.actionId and groupId = (select groupId from group_role where roleId=?) or (action.actionId=role_action.actionId and roleId=?)order by actionId asc;";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, roleId);
			pstmt.setString(2, roleId);
			rs = pstmt.executeQuery();
			int i = 0;
			while (rs != null && rs.next() && i < 20) {
				// 读到数据，生成实体类
				Action action = new Action();
				action.setActionId(rs.getInt("actionId"));
				action.setActionName(rs.getString("actionName"));
				action.setActionDescription(rs.getString("actionDescription"));
				vResult.add(action);
				i++;
			}

			rs.close();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return vResult;
	}

	/**
	 * 保存组权限
	 * 
	 * @param groupId
	 * @param actionId
	 */
	public void saveGroupAction(String groupId, String actionId) {
		Connection con = null;
		PreparedStatement pstmt = null;
		// ResultSet rs = null;
		String sql = "insert into group_action(groupId,actionId) values(?,?);";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, groupId);
			pstmt.setString(2, actionId);
			pstmt.executeUpdate();

			// rs.close();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	/**
	 * 删除组总权限
	 * 
	 * @param groupId
	 */
	public void deleteGroupAction(String groupId) {

		Connection con = null;
		PreparedStatement pstmt = null;
		// ResultSet rs = null;
		String sql = "delete from group_action where groupId=?;";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, groupId);
			pstmt.executeUpdate();

			// rs.close();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
}