systemuaerdao.java

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

package com.jn0801.login.systemuser;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import com.jn0801.menuLink.RuleBean;
import com.jn0801.tools.DBConnection;
import com.jn0801.tools.PageDAO;

public class SystemuaerDAO {
	/**
	 * 显示系统用户列表
	 * @return
	 */
	public List<RuleBean> selectRule(){
		ArrayList<RuleBean> userlist = new ArrayList<RuleBean>();
		StringBuffer sql = new StringBuffer();
		
		sql.append("select * from roleinfo where nid > 1");
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql.toString());
			
			while (rs.next()){  ;
				RuleBean bean = new RuleBean();
				bean.setNid(rs.getLong("nid"));
				bean.setRolename(rs.getString("rolename"));
				bean.setPowerid(rs.getString("powerid"));
				
				userlist.add(bean);
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		
		return userlist;
		
	}
	/**
	 * 根据用户编号查询一条信息
	 * @param nid
	 * @return
	 */
	public List<SystemuserBean> selectUser(int nid){
		ArrayList<SystemuserBean> userlist = new ArrayList<SystemuserBean>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from systemuserinfo where nid = "+nid);
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql.toString());
			while (rs.next()){
				SystemuserBean bean = new SystemuserBean();
				bean.setNid(rs.getInt("nid"));
				bean.setRealname(rs.getInt("realname"));
				bean.setRoleid(rs.getInt("roleid"));
				bean.setUsername(rs.getString("username"));
				bean.setRealnametwo(rs.getInt("realnametwo"));
				bean.setLoginname(rs.getInt("loginname"));
				userlist.add(bean);
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return userlist;
	}
	/**
	 * 根据会员名称查询单条信息
	 * @param suser
	 * @return
	 */
	public List<SystemuserBean> selectname(String suser){
		ArrayList<SystemuserBean> userlist = new ArrayList<SystemuserBean>();
		StringBuffer sql = new StringBuffer();
		sql.append("select * from systemuserinfo where username like '%"+suser+"%'");
		
		System.out.println(sql);
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		
		
		try {
			conn = DBConnection.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql.toString());
			
			while (rs.next()){
				SystemuserBean bean = new SystemuserBean();
				bean.setNid(rs.getInt("nid"));
				bean.setRoleid(rs.getInt("roleid"));
				bean.setRealname(rs.getInt("realname"));
				bean.setUsername(rs.getString("username"));
				bean.setRealnametwo(rs.getInt("realnametwo"));
				bean.setLoginname(rs.getInt("loginname"));
				
				userlist.add(bean);
			}
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				rs.close();
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		
		return userlist;
		
	}
	
	
	/**
	 * 根据用户编号查询单条信息
	 * @param nid
	 * @return
	 */
public SystemuserBean searchuser(int nid) {
		
	    SystemuserBean deptBean = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select * from systemuserinfo where nid =" + nid);
		//System.out.println(sql.toString());
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn=DBConnection.getConnection();
			stmt= conn.createStatement();
			rs= stmt.executeQuery(sql.toString());
			
			while(rs.next()){
				
				deptBean = new SystemuserBean();
				deptBean.setNid(nid);
				deptBean.setUsername(rs.getString("username"));
				deptBean.setPassword(rs.getString("password"));
				deptBean.setRealname(rs.getInt("realname"));
				deptBean.setRoleid(rs.getInt("roleid"));
				deptBean.setRealnametwo(rs.getInt("realnametwo"));
				deptBean.setLoginname(rs.getInt("loginname"));
			}
				
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				conn.close();
				stmt.close();
				rs.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}

		return deptBean;
	}
	/**
	 * 查询用户所有信息
	 * @return
	 */
	public SystemuserBean sssuser() {
	
	    SystemuserBean deptBean = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select username from systemuserinfo");
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
	
		try {
			conn=DBConnection.getConnection();
			stmt= conn.createStatement();
			rs= stmt.executeQuery(sql.toString());
			
			while(rs.next()){
				
				deptBean = new SystemuserBean();
				deptBean.setUsername(rs.getString("username"));
				
			}
				
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				conn.close();
				stmt.close();
				rs.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
	
		return deptBean;
	}
	/**
	 * 查找大客户经理ID最大值
	 * @return
	 */
	public SystemuserBean serchrealname() {

	    SystemuserBean deptBean = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select max(realname) as xx from systemuserinfo  where roleid=2 ");
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn=DBConnection.getConnection();
			stmt= conn.createStatement();
			rs= stmt.executeQuery(sql.toString());
			
			while(rs.next()){
				
				deptBean = new SystemuserBean();
				deptBean.setRealname(rs.getInt("xx"));
				
			}
				
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				conn.close();
				stmt.close();
				rs.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}

		return deptBean;
	}
	/**
	 * 查找客户经理ID最大值	
	 * @return
	 */
	public SystemuserBean serchrealnametwo() {

	    SystemuserBean deptBean = null;
		StringBuffer sql = new StringBuffer();
		sql.append("select max(realnametwo) as xx from systemuserinfo  where roleid=3");
		
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;

		try {
			conn=DBConnection.getConnection();
			stmt= conn.createStatement();
			rs= stmt.executeQuery(sql.toString());
			
			while(rs.next()){
				
				deptBean = new SystemuserBean();
				deptBean.setRealname(rs.getInt("xx"));
				
			}
				
		} catch (SQLException e) {
			// TODO 自动生成 catch 块
			e.printStackTrace();
		}finally{
			try {
				conn.close();
				stmt.close();
				rs.close();
			} catch (SQLException e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}

		return deptBean;
	}
	/**
	 * 这是 判断修改的密码是否符合
	 * @param password
	 * @return
	 */
public SystemuserBean ispassword(String password){
	Connection con = null;
	Statement stmt = null;
	ResultSet rs = null;
	SystemuserBean user = null;
	
	String sql = "select nid,username,password,roleid" +
			" from systemuserinfo where password='"+password+"'";
	
	try {
		con = DBConnection.getConnection();
		stmt = con.createStatement();
		rs = stmt.executeQuery(sql);
		if(rs.next()){
				user = new SystemuserBean();
				user.setNid(rs.getInt("nid"));
				user.setUsername(rs.getString("username"));
				user.setPassword(rs.getString("password"));
				user.setRoleid(rs.getInt("roleid"));