roledaoimpl.java

用jdbc写的一个存储过程的代码

package com.bluedot.persist;

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

import com.bluedot.common.JdbcUtil;
import com.bluedot.domain.Fun;
import com.bluedot.domain.Menu;
import com.bluedot.domain.Role;
import com.bluedot.exceptions.ApplicationException;
import com.bluedot.exceptions.DataAccessException;

public class RoleDaoImpl implements RoleDao {

	@Override
	public Role findRoleById(int id) {
		Connection conn = null;
		Role role = null;
		try {
			conn = JdbcUtil.getConnection();
			String sql = "select * from oa_roles where id=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, id);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				role = new Role();
				role.setId(rs.getInt("id"));
				role.setName(rs.getString("name"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ApplicationException();
		} finally {
			JdbcUtil.closeConnection(conn);
		}
		return role;
	}

	@Override
	public Role findRoleWithFunsAndMenusById(int id) {
		Connection conn = null;
		Role role = findRoleById(id);
		try {
			conn = JdbcUtil.getConnection();

			if (role != null) {
				String sql = "select * from oa_funs f,oa_roles_funs rf where f.id=rf.fun_id and rf.role_id=?";
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, role.getId());

				ResultSet rs = ps.executeQuery();
				while (rs.next()) {
					Fun fun = new Fun();
					fun.setId(rs.getInt("id"));
					fun.setName(rs.getString("name"));
					fun.setUri(rs.getString("uri"));

					role.getFuns().add(fun);
				}

				String sql2 = "select * from oa_menus m,oa_roles_menus rm where m.id=rm.menu_id and rm.role_id=? order by m.id";
				PreparedStatement ps2 = conn.prepareStatement(sql2);
				ps2.setInt(1, id);

				ResultSet rs2 = ps2.executeQuery();
				while (rs2.next()) {
					Menu m = new Menu();
					m.setId(rs2.getInt("id"));
					m.setName(rs2.getString("name"));
					m.setUri(rs2.getString("uri"));
					m.setFatherId(rs2.getInt("father_id"));

					role.getMenus().add(m);
				}

			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ApplicationException();
		} finally {
			JdbcUtil.closeConnection(conn);
		}
		return role;
	}

	@Override
	public void insertRole(String roleName) {
		Connection conn = null;
		try {
			conn = JdbcUtil.getConnection();
			String sql = "insert into oa_roles values(roles_seq.nextval,?)";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, roleName);

			ps.execute();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ApplicationException();
		} finally {
			JdbcUtil.closeConnection(conn);
		}
	}

	@Override
	public List<Role> findAllRoles() {
		Connection conn = null;
		List<Role> list = new ArrayList<Role>();
		try {
			conn = JdbcUtil.getConnection();
			String sql = "select * from oa_roles";
			PreparedStatement ps = conn.prepareStatement(sql);

			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				int roleId = rs.getInt("id");
				Role role = findRoleWithFunsAndMenusById(roleId);

				list.add(role);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ApplicationException();
		} finally {
			JdbcUtil.closeConnection(conn);
		}
		return list;
	}

	@Override
	public void updateRoleAndFuns(int roleId, String[] funIds) {
		Connection conn = null;
		try {
			conn = JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			String sql = "delete from oa_roles_funs where role_id=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, roleId);

			ps.execute();

			String sql2 = "insert into oa_roles_funs values(?,?)";
			if (funIds != null) {
				for (int i = 0; i < funIds.length; i++) {
					PreparedStatement ps2 = conn.prepareStatement(sql2);
					ps2.setInt(1, roleId);
					ps2.setString(2, funIds[i]);

					ps2.execute();
				}
			}

			conn.commit();
		} catch (SQLException e) {
			JdbcUtil.rollbackConnection(conn);
			e.printStackTrace();
			throw new DataAccessException();
		} catch (Exception e) {
			JdbcUtil.rollbackConnection(conn);
			e.printStackTrace();
			throw new ApplicationException();
		} finally {
			JdbcUtil.closeConnection(conn);
		}
	}

	@Override
	public Set<Integer> findMenusIdsFromFunsAndMenus(String[] funIds) {
		Connection conn = null;
		Set<Integer> list = new HashSet<Integer>();
		try {
			conn = JdbcUtil.getConnection();
			String sql = "select menu_id from oa_funs_menus where fun_id=?";

			if (funIds != null) {
				for (int i = 0; i < funIds.length; i++) {
					PreparedStatement ps = conn.prepareStatement(sql);
					ps.setString(1, funIds[i]);

					ResultSet rs = ps.executeQuery();
					while (rs.next()) {
						list.add(rs.getInt(1));
					}
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new DataAccessException();
		} catch (Exception e) {
			e.printStackTrace();
			throw new ApplicationException();
		} finally {
			JdbcUtil.closeConnection(conn);
		}
		return list;
	}

	@Override
	public void updateRoleAndMenus(int roleId, Set<Integer> menuIds) {
		Connection conn = null;
		try {
			conn = JdbcUtil.getConnection();
			conn.setAutoCommit(false);
			String sql = "delete from oa_roles_menus where role_id=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, roleId);

			ps.execute();

			String sql2 = "insert into oa_roles_menus values(?,?)";
			if (menuIds != null) {

				Iterator<Integer> it = menuIds.iterator();
				while (it.hasNext()) {
					PreparedStatement ps2 = conn.prepareStatement(sql2);
					ps2.setInt(1, roleId);
					ps2.setInt(2, it.next());
					ps2.execute();
				}
			}

			conn.commit();
		} catch (SQLException e) {
			JdbcUtil.rollbackConnection(conn);
			e.printStackTrace();
			throw new DataAccessException();
		} catch (Exception e) {
			JdbcUtil.rollbackConnection(conn);
			e.printStackTrace();
			throw new ApplicationException();
		} finally {
			JdbcUtil.closeConnection(conn);
		}
	}
}