popedomdao.java

该项为帮助银行工作人员处理银行业务

package com.bluedot.bank.framework.dao;

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 com.bluedot.bank.framework.sql.MySqlInfo;
import com.bluedot.bank.framework.web.actionform.PopedomBean;
import com.bluedot.bank.framework.web.actionform.PopedomFromBean;

public class PopedomDao {
	public PopedomBean select(String username) {
		PopedomBean popedomBean = new PopedomBean();
		popedomBean.setUsername(username);
		List<String> list = new ArrayList<String>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select role_range from popedom where role_name=(select role_name from roles where role_id=(select role_id from users where username=?))");
			statement.setString(1, username);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("role_range"));
			}
			popedomBean.setHoldSystem(list);
			popedomBean.setLackSystem(filtrateRange(list));
			rs.close();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return popedomBean;
	}
	
	public PopedomBean selec(String username) {
		PopedomBean popedomBean = new PopedomBean();
		popedomBean.setUsername(username);
		List<String> list = new ArrayList<String>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select role_range from popedom where role_name=?");
			statement.setString(1, username);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("role_range"));
			}
			popedomBean.setHoldSystem(list);
			popedomBean.setLackSystem(filtrateRange(list));
			rs.close();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return popedomBean;
	}

	public List<String> selectFirst(String id) {
		List<String> list = new ArrayList<String>();
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select * from popedom where role_name=(select role_name from roles where role_id=?)");
			statement.setString(1, id);
			ResultSet rs = statement.executeQuery();
			while (rs.next()) {
				list.add(rs.getString("role_range"));

			}
			rs.close();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}
		return list;
	}

	public List<String> filtrateRange(List<String> p) {

		List<String> info = new ArrayList<String>();
		info.add("帐户转帐管理系统");
		info.add("权限管理系统");
		info.add("交易记录管理系统");
		info.add("客户管理系统");
		info.add("帐户管理系统");
		info.add("ATM管理系统");

		info.removeAll(p);
		return info;
	}

	public void addrole(String name, List<String> role_name) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {

			for (int i = 0; i < role_name.size(); i++) {
				PreparedStatement statement2 = connection
						.prepareStatement("insert into popedom(role_name,role_range)values(?,?)");
				statement2.setString(1, name);
				statement2.setString(2, role_name.get(i));
				statement2.execute();
				statement2.close();
			}
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}

	}

	public void addRole(String name, List<String> role_name) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select role_name from roles r inner join users u on r.role_id=u.role_id where u.username=?");
			statement.setString(1, name);
			ResultSet rs = statement.executeQuery();
			rs.next();
			String rolename = rs.getString("role_name");
			for (int i = 0; i < role_name.size(); i++) {
				PreparedStatement statement2 = connection
						.prepareStatement("insert into popedom(role_name,role_range)values(?,?)");
				statement2.setString(1, rolename);
				statement2.setString(2, role_name.get(i));
				statement2.execute();
				statement2.close();
			}
			rs.close();

			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}

	}

	public void del(String name, List<String> role_name) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {
			PreparedStatement statement = connection
					.prepareStatement("select role_name from roles r inner join users u on r.role_id=u.role_id where u.username=?");
			statement.setString(1, name);
			ResultSet rs = statement.executeQuery();
			rs.next();
			String rolename = rs.getString("role_name");
			for (int i = 0; i < role_name.size(); i++) {
				PreparedStatement statement2 = connection
						.prepareStatement("delete from popedom where role_name=? and role_range=?");
				statement2.setString(1, rolename);
				statement2.setString(2, role_name.get(i));
				statement2.execute();
			}
			rs.close();
			statement.close();
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}

	}
	
	public void dele(String name, List<String> role_name) {
		Connection connection = null;
		connection = new MySqlInfo().getConnection();
		try {

			for (int i = 0; i < role_name.size(); i++) {
				PreparedStatement statement2 = connection
						.prepareStatement("delete from popedom where role_name=? and role_range=?");
				statement2.setString(1, name);
				statement2.setString(2, role_name.get(i));
				statement2.execute();
				statement2.close();
			}
			
			connection.commit();
		} catch (SQLException e) {
			new MySqlInfo().rollback(connection);
			throw new RuntimeException(e);
		} finally {
			new MySqlInfo().closeCon(connection);
		}

	}

}