userdao.java

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

package dao;

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

import bo.*;

public class UserDAO {
	/**
	 * 根据用户名称和密码查询用户信息
	 * 
	 * @param userName
	 * @param password
	 * @return
	 */
	public UserGR selectUserByNP(String userName, String password) {

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select user.*,groups.*,role.* from user,groups,role,user_group,user_role where (user.userId=user_role.userId)and(user.userId=user_group.userId) and (role.roleId=user_role.roleId and groups.groupId=user_group.groupId) and userName=? and password=?";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, userName);
			pstmt.setString(2, password);
			rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
				// 读到数据，生成实体类
				UserGR user = new UserGR();
				user.setUserId(rs.getInt("userId"));
				user.setUserName(rs.getString("userName"));
				user.setPassword(rs.getString("password"));
				user.setParentName(rs.getString("parentName"));
				user.setArea(rs.getString("area"));
				user.setRealName(rs.getString("realName"));
				user.setPhone(rs.getString("phone"));
				user.setEmail(rs.getString("email"));
				user.setCreateTime(rs.getString("createTime"));
				user.setMasterName(rs.getString("masterName"));
				user.setSchoolName(rs.getString("schoolName"));
				user.setGroupId(rs.getInt("groupId"));
				user.setGroupName(rs.getString("groupName"));
				user.setRoleId(rs.getInt("roleId"));
				user.setRoleName(rs.getString("roleName"));
				return user;
			}

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

		return null;
	}

	/**
	 * 根据用户父级查询用户信息
	 * 
	 * @param userName
	 * @param password
	 * @return
	 */
	public Vector selectUserByParentName(String parentName) {

		Vector<UserGR> vResult = new Vector<UserGR>(1, 1);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select user.*,groups.*,role.* from user,groups,role,user_group,user_role where (user.userId=user_role.userId)and(user.userId=user_group.userId) and (role.roleId=user_role.roleId and groups.groupId=user_group.groupId) and parentName=?";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, parentName);
			rs = pstmt.executeQuery();
			int i = 0;
			while (rs != null && rs.next() && i < 20) {
				// 读到数据，生成实体类
				UserGR user = new UserGR();
				user.setUserId(rs.getInt("userId"));
				user.setUserName(rs.getString("userName"));
				user.setPassword(rs.getString("password"));
				user.setParentName(rs.getString("parentName"));
				user.setArea(rs.getString("area"));
				user.setRealName(rs.getString("realName"));
				user.setPhone(rs.getString("phone"));
				user.setEmail(rs.getString("email"));
				user.setCreateTime(rs.getString("createTime"));
				user.setMasterName(rs.getString("masterName"));
				user.setSchoolName(rs.getString("schoolName"));
				user.setGroupId(rs.getInt("groupId"));
				user.setGroupName(rs.getString("groupName"));
				user.setRoleId(rs.getInt("roleId"));
				user.setRoleName(rs.getString("roleName"));
				vResult.add(user);
				i++;
			}

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

		return vResult;
	}

	public Vector selectUserByParentName1(String parentName) {

		Vector<UserGR> vResult = new Vector<UserGR>(1, 1);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select user.*,groups.*,role.* from user,groups,role,user_group,user_role where (user.userId=user_role.userId)and(user.userId=user_group.userId) and (role.roleId=user_role.roleId and groups.groupId=user_group.groupId) and schoolName in (select schoolName from school where SchoolId in(select schoolId from school_user where userId=(select userId from user where userName=?)))";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, parentName);
			rs = pstmt.executeQuery();
			int i = 0;
			while (rs != null && rs.next() && i < 20) {
				// 读到数据，生成实体类
				UserGR user = new UserGR();
				user.setUserId(rs.getInt("userId"));
				user.setUserName(rs.getString("userName"));
				user.setPassword(rs.getString("password"));
				user.setParentName(rs.getString("parentName"));
				user.setArea(rs.getString("area"));
				user.setRealName(rs.getString("realName"));
				user.setPhone(rs.getString("phone"));
				user.setEmail(rs.getString("email"));
				user.setCreateTime(rs.getString("createTime"));
				user.setMasterName(rs.getString("masterName"));
				user.setSchoolName(rs.getString("schoolName"));
				user.setGroupId(rs.getInt("groupId"));
				user.setGroupName(rs.getString("groupName"));
				user.setRoleId(rs.getInt("roleId"));
				user.setRoleName(rs.getString("roleName"));
				vResult.add(user);
				i++;
			}

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

		return vResult;
	}

	/**
	 * 根据用户名称查询用户信息
	 * 
	 * @param userName
	 * @param password
	 * @return
	 */
	public User selectUserByName(String userName) {

		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from user where userName=?;";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, userName);
			rs = pstmt.executeQuery();
			if (rs != null && rs.next()) {
				// 读到数据，生成实体类
				User user = new User();
				user.setUserId(rs.getInt("userId"));
				user.setUserName(rs.getString("userName"));
				user.setPassword(rs.getString("password"));
				user.setParentName(rs.getString("parentName"));
				user.setArea(rs.getString("area"));
				user.setRealName(rs.getString("realName"));
				user.setPhone(rs.getString("phone"));
				user.setEmail(rs.getString("email"));
				user.setCreateTime(rs.getString("createTime"));
				user.setMasterName(rs.getString("masterName"));
				user.setSchoolName(rs.getString("schoolName"));
				return user;
			}

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

		return null;
	}

	/**
	 * 增加用户
	 * 
	 * @param insertUser
	 * @return
	 */
	public User InsertUser(User user) {

		Connection con = null;
		PreparedStatement pstmt = null;

		String sql = "insert into user(userName,password,parentName,area,realName,phone,email,createTime,masterName,schoolName) values (?,?,?,?,?,?,?,?,?,?)";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, user.getUserName());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getParentName());
			pstmt.setString(4, user.getArea());
			pstmt.setString(5, user.getRealName());
			pstmt.setString(6, user.getPhone());
			pstmt.setString(7, user.getEmail());
			pstmt.setString(8, user.getCreateTime());
			pstmt.setString(9, user.getMasterName());
			pstmt.setString(10, user.getSchoolName());
			pstmt.executeUpdate();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	/**
	 * 增加用户角色
	 * 
	 * @param insertUser
	 * @return
	 */
	public User InsertUserRole(int userId, int roleId) {

		Connection con = null;
		PreparedStatement pstmt = null;

		String sql = "insert into user_role(userId,roleId) values (?,?)";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, userId);
			pstmt.setInt(2, roleId);

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

		return null;
	}

	/**
	 * 增加用户组
	 * 
	 * @param insertUser
	 * @return
	 */
	public User InsertUserGroup(int userId, int groupId) {

		Connection con = null;
		PreparedStatement pstmt = null;

		String sql = "insert into user_group(groupId,userId) values (?,?)";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, groupId);
			pstmt.setInt(2, userId);

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

		return null;
	}

	/**
	 * 查询用户角色
	 * 
	 * @param roleId
	 * @return
	 */
	public Vector selectUserByRole(String roleId) {

		Vector<User> vResult = new Vector<User>(1, 1);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from user where userId in (select userId from user_role 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) {
				// 读到数据，生成实体类
				User user = new User();
				user.setUserId(rs.getInt("userId"));
				user.setUserName(rs.getString("userName"));
				user.setPassword(rs.getString("password"));
				user.setParentName(rs.getString("parentName"));
				user.setArea(rs.getString("area"));
				user.setRealName(rs.getString("realName"));
				user.setPhone(rs.getString("phone"));
				user.setEmail(rs.getString("email"));
				user.setCreateTime(rs.getString("createTime"));
				user.setMasterName(rs.getString("masterName"));
				user.setSchoolName(rs.getString("schoolName"));
				vResult.add(user);
				i++;
			}

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

		return vResult;
	}

	/**
	 * 查询组用户
	 * 
	 * @param groupId
	 * @return
	 */
	public Vector selectUserByGroup(String groupId) {

		Vector<User> vResult = new Vector<User>(1, 1);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "select * from user where userId in (select userId from user_group 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) {
				// 读到数据，生成实体类
				User user = new User();
				user.setUserId(rs.getInt("userId"));
				user.setUserName(rs.getString("userName"));
				user.setPassword(rs.getString("password"));
				user.setParentName(rs.getString("parentName"));
				user.setArea(rs.getString("area"));
				user.setRealName(rs.getString("realName"));
				user.setPhone(rs.getString("phone"));
				user.setEmail(rs.getString("email"));
				user.setCreateTime(rs.getString("createTime"));
				user.setMasterName(rs.getString("masterName"));
				user.setSchoolName(rs.getString("schoolName"));
				vResult.add(user);
				i++;
			}

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

		return vResult;
	}

	/**
	 * 增加用户角色
	 * 
	 * @param insertUser
	 * @return
	 */
	public User DeleteUser(int userId) {

		Connection con = null;
		PreparedStatement pstmt = null;

		String sql = "delete from user where userId=?";

		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, userId);

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

		return null;
	}

	/**
	 * 修改个人信息
	 * 
	 * @param insertUser
	 * @return
	 */
	public User upDateUserInfo(int userId, String userName, String area,
			String realName, String phone, String email) {

		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = "update user set userName=?,area=?,realName=?,phone=?,email=? where userId=?";
		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, userName);
			pstmt.setString(2, area);
			pstmt.setString(3, realName);
			pstmt.setString(4, phone);
			pstmt.setString(5, email);
			pstmt.setInt(6, userId);
			pstmt.executeUpdate();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

	/**
	 * 修改个人密码
	 * 
	 * @param insertUser
	 * @return
	 */
	public User upDateUserPassword(int userId, String password) {

		Connection con = null;
		PreparedStatement pstmt = null;
		String sql = "update user set password=? where userId=?";
		try {

			con = DB.getConnection();
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, password);
			pstmt.setInt(2, userId);
			pstmt.executeUpdate();
			pstmt.close();
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return null;
	}

}