systemuaerdao.java

自己制作的联通CRM,支持客户分类,管理,升级,积分管理等等..

			
				return user;
		}
		
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		try {
			if(rs!=null){
			rs.close();
			stmt.close();
			con.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	

	}
	return null;
	
}
	/**
	 * 根据用户名称查询信息
	 * @param suser
	 * @return
	 */
	public SystemuserBean searchUser(String suser){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		SystemuserBean user = null;
		
		String sql = "select nid,realname,realnametwo,loginname,username,password,roleid" +
				" from systemuserinfo where loginname='"+suser+"'";
		
		System.out.println(sql);
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			if(rs.next()){
					user = new SystemuserBean();
					user.setRealname(rs.getInt("realname"));
					user.setRealnametwo(rs.getInt("realnametwo"));
					user.setNid(rs.getInt("nid"));
					user.setUsername(rs.getString("username"));
					user.setPassword(rs.getString("password"));
					user.setRoleid(rs.getInt("roleid")); 
					user.setLoginname(rs.getInt("loginname"));
					return user;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
				rs.close();
				stmt.close();
				con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	

		}
		return null;
		
	}
	/**
	 * 修改用户信息
	 * @param bean
	 * @return
	 */
	public boolean updateUser(SystemuserBean bean){
		boolean flag=false;
		Connection con = null;
		Statement stmt = null; 
		StringBuffer sql = new StringBuffer();

		sql.append("update systemuserinfo set ");
		sql.append("username= '"+bean.getUsername()+"',realname= "+bean.getRealname()+",realnametwo= "+bean.getRealnametwo()+",roleid= "+bean.getRoleid()+" where nid="+bean.getNid());
		 
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			if(stmt.executeUpdate(sql.toString())>0){
				flag=true;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try { 
				stmt.close();
				con.close(); 
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
		}
		return flag; 
	}
	/**
	 * 修改密码
	 * @param bean
	 * @return
	 */
	public boolean updatePassword(SystemuserBean bean){
		boolean flag=false;
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		SystemuserBean user = null;
		StringBuffer sql = new StringBuffer();

		sql.append("update systemuserinfo set ");
		sql.append("password= '"+bean.getPassword()+"' where nid="+bean.getNid());
	
		try {
			con = DBConnection.getConnection();
			stmt = con.createStatement();
			if(stmt.executeUpdate(sql.toString())>0){
				flag=true;
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(rs!=null){
				rs.close();
				stmt.close();
				con.close();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
		}
		return flag;
	}
	/**
	 * 删除用户
	 * @param nid
	 * @return
	 */
	public boolean deleteUser(int nid){
		
		boolean flag= false;
		Connection conn =DBConnection.getConnection();
		Statement stmt = null; 
		String sql = "delete from systemuserinfo where nid="+nid;
		
		try {
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			stmt.execute(sql);
			flag = true;
		} catch (SQLException e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO 自动生成 catch 块
				e1.printStackTrace();
			}
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				conn.setAutoCommit(true);
				conn.close();							
				stmt.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return flag; 
	}
	   
	   /**
	    * 添加用户
	    * @param bean
	    * @return
	    */
	public boolean insertUser(SystemuserBean bean){ 
		boolean flag = false;
		StringBuffer sql = new StringBuffer();
		if(bean != null){
			sql.append("insert into systemuserinfo(");
			sql.append("nid,username,realname,realnametwo,loginname,password,roleid )values(");
			sql.append("SEQ_SYSTEMUSERINFO.Nextval,");
			sql.append("'"+bean.getUsername()+"',");
			
			sql.append(bean.getRealname()+",");
			sql.append(bean.getRealnametwo()+",");
			sql.append(bean.getLoginname()+",");
			
			sql.append("'"+bean.getPassword()+"',");
			sql.append(bean.getRoleid()+")"); 
			
		}
		Connection conn= null;
		Statement stmt = null; 
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			if(stmt.executeUpdate(sql.toString())>0){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			} 
		}
		return flag; 
	}
	/**
	 *显示用户列表
	 */
	public List<SystemuserBean> listGradeRule(String username,HttpServletRequest request) {
    	List<SystemuserBean> list = new ArrayList<SystemuserBean>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = ""; 
		//开始分页
		PageDAO pagedao = new PageDAO(request);
		pagedao.setPagesize(10);
		// 获取每页多少条
		int pagesize = pagedao.getPagesize();
		//获取总记录数
		sql = "select count(1) from systemuserinfo where 1=1 ";
		if (username != null && !username.trim().equals(""))
			sql = sql + " and username like '%" + username + "%'"; 
		//总记录数
		int rscount = 0; 
		try { 
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql); 
			while(rs.next()){
				rscount = rs.getInt(1);
			} 
			//设置总记录数
			pagedao.setRscount(rscount);
			//获取总页数
			int pagecount = pagedao.getPageCount();
			// 获取当前页
			int currentpage = pagedao.getCurrentPage();
			//构建工具栏
			String pagetool = pagedao.pagetool(PageDAO.BbsText);
			request.setAttribute("pagetool", pagetool);
			//计算起始行和结束行
			int pastart = (currentpage-1)*pagesize+1; 
			int paend = currentpage*pagesize;
			sql = "select * from ("; 
			sql = sql + "select s.*,rownum as myrow from systemuserinfo s where 1=1 ";
			if (username != null && !username.trim().equals(""))
				sql = sql + " and username like '%" + username + "%'"; 
			sql = sql + " and rownum <= " + paend;
			sql = sql + ")";
			sql = sql + " where myrow >= " + pastart+"order by realname";
//			System.out.println(sql+"              aaaaaaaaaaaaaaaaa");
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				SystemuserBean userBean = new SystemuserBean();
				userBean.setNid(rs.getInt("nid"));
				userBean.setRoleid(rs.getInt("roleid"));
				userBean.setRealname(rs.getInt("realname"));
				userBean.setUsername(rs.getString("username"));
				userBean.setRealnametwo(rs.getInt("realnametwo"));
				userBean.setLoginname(rs.getInt("loginname"));
				list.add(userBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		} 
		return list;
	}
	/**
	 * 显示除管理员与客户经理以外的全部用户列表
	 * @param username
	 * @param request
	 * @return
	 */
	public List<SystemuserBean> UserList() {

		List<SystemuserBean> list = new ArrayList<SystemuserBean>();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		String sql = "";
		sql = "select * from systemuserinfo where loginname>=10001 and loginname<=20000";		
		try {	
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();		
			rs = stmt.executeQuery(sql);
			
			while (rs.next()) {

				SystemuserBean userBean = new SystemuserBean();
				userBean.setNid(rs.getInt("nid"));
				userBean.setRoleid(rs.getInt("roleid"));
				userBean.setRealname(rs.getInt("realname"));
				userBean.setUsername(rs.getString("username"));
				userBean.setRealnametwo(rs.getInt("realnametwo"));
				userBean.setLoginname(rs.getInt("loginname"));
				list.add(userBean);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null)
					rs.close();
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	} 

}