usermanager.java

分销资源计划是管理企业的分销网络的系统

package com.beifengkd.drp.sysmgr;
import java.sql.*;
import com.beifengkd.drp.util.*;
import java.util.*;
public class UserManager {
	private static UserManager instance = new UserManager();
	
	//私有的构造方法
	private UserManager() {
		
	}
	
	public static UserManager getInstance() {
		return instance;
	}
	
	/**
	 * 根据id添加用户
	 * @param user
	 */
	public void addUser(User user) {
		String sql = "insert into t_user(user_id, user_name, password, contact_tel, email, create_date) " + 
					  "values(?, ?, ?, ?, ?, ?)";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getUserId());
			pstmt.setString(2, user.getUserName());
			pstmt.setString(3, user.getPassword());
			pstmt.setString(4, user.getContactTel());
			pstmt.setString(5,user.getEmail());
			pstmt.setTimestamp(6, new Timestamp(user.getCreateDate().getTime()));
			pstmt.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	/**
	 * 根据用户id删除
	 * @param userId
	 */
	public void deleteUser(String userId) {
		String sql = "delete from t_user where user_id=? ";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userId);
			pstmt.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	
	/**
	 * 根据id查询用户
	 * @param userId
	 * @return
	 */
	public User findUserById(String userId) {
		String sql = "select * from t_user where user_id=?";
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		User user = null;
		try {
			conn = DB.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userId);
			rs = pstmt.executeQuery();
			if(rs.next()) {
				user = new User();
				user.setUserId(rs.getString("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setPassword(rs.getString("password"));
				user.setContactTel(rs.getString("contact_tel"));
				user.setEmail(rs.getString("email"));
				user.setCreateDate(rs.getTimestamp("create_date"));
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(pstmt);
			DB.close(conn);
		}
		return user;
	}
	/**
	 * 用数组实现删除 sql = select from t_user where user_id in ('b001', 'b002', ...)
	 * @param userIds
	 */
	public void deleteUser(String[] userIds) {
		StringBuffer sbStr = new StringBuffer();
		for(int i=0; i<userIds.length; i++) {
			sbStr.append("'")
				 .append(userIds[i])
				 .append("',");
		}
		String sql = "delete from t_user where user_id in (" + sbStr.substring(0, sbStr.length()-1) + ")";
System.out.println("sql= " + sql);		
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = DB.getConn();
			stmt = conn.createStatement();
			stmt.executeUpdate(sql);
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(stmt);
			DB.close(conn);
		}
	}
	
	/**
	 * 修改用户
	 * @param user
	 */
	public void modifyUser(User user) {
		String sql = "update t_user set user_name=?, password=?, contact_tel=?, email=? where user_id=? ";
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getUserName());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getContactTel());
			pstmt.setString(4, user.getEmail());
			pstmt.setString(5, user.getUserId());
			pstmt.executeUpdate();
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(pstmt);
			DB.close(conn);
		}
	}
	
	/**
	 * 分页查询
	 * @param pageNo 当前页
	 * @param pageSize 每页显示多少条
	 * @return pageModel
	 */
	public PageModel findAllUser(int pageNo, int pageSize) {
		String sql = "select * from t_user where user_id <> 'root' order by user_id " +
			"limit " + (pageNo - 1)*pageSize + ", " + pageSize;
//System.out.println("sql=" + sql);
		PageModel pageModel = null;
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = DB.getConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			List userList = new ArrayList();
			
			while(rs.next()) {
				User user = new User();
				user.setUserId(rs.getString("user_id"));
				user.setUserName(rs.getString("user_name"));
				user.setPassword(rs.getString("password"));
				user.setContactTel(rs.getString("contact_tel"));
				user.setEmail(rs.getString("email"));
				user.setCreateDate(rs.getTimestamp("create_date"));
				userList.add(user);
			}
			//取得所有记录
			int totalRecords = getTotalRecords(conn);
			pageModel = new PageModel();
			pageModel.setPageNo(pageNo);
			pageModel.setPageSize(pageSize);
			pageModel.setList(userList);
			pageModel.setTotalRecord(totalRecords);
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return pageModel;
	}
	
	/**
	 * 取得所有记录
	 * @param conn
	 * @return 所有记录totalRecords
	 */
	private int getTotalRecords(Connection conn) {
		String sql = "select count(*) from t_user where user_id <> 'root' ";
//System.out.println("sql=" + sql);
		int totalRecords = 0;
		Statement stmt = null;
		ResultSet rs = null;
		try {
			conn = DB.getConn();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()) {
				totalRecords = rs.getInt(1);
			}
		} catch(SQLException e) {
			e.printStackTrace();
		} finally {
			DB.close(rs);
			DB.close(stmt);
			DB.close(conn);
		}
		return totalRecords;
	}
	
	public User login(String userId, String password) {
		User user = findUserById(userId);
		if(user == null) {
			throw new UserNotFoundException("用户代码[" + userId + "]不存在");
		}
		if(!user.getPassword().equals(password)) {
			throw new PasswordNotCorrectException("密码不正确");
		}
		return user;
	}
}