userdboperation.java

java高级引用管理综合朝鲜经java高级引用管理综合朝鲜经java高级引用管理综合朝鲜经java高级引用管理综合朝鲜经

package com.login;

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

public class UserDBOperation {

    private Connection conn;
	
	public UserDBOperation()
	{
		try{
			conn=DatabaseConn.getConnection();
		}catch(Exception e){
			
		}
		
	}
	
	public UserInforBean getUser(String person_name,String pass)throws Exception
	{
		String strSql="select * from person where person_name = ? && pwd = ? ";
		PreparedStatement stmt=conn.prepareStatement(strSql);
		stmt.setString(1,person_name);
		stmt.setString(2,pass);
		ResultSet rset=stmt.executeQuery();
		//conn.commit();
		UserInforBean user=null;
		if(rset.next())
		{
			String pid=rset.getString(1);//id
			String name=rset.getString(2);//人员名称
			String pwd=rset.getString(3);//密码
			String duty=rset.getString(4);//职务
			String dept=rset.getString(5);//部门
			String asses_dept=rset.getString(6);//协管部门
			String role=rset.getString(7);//角色	   
		   //
			user=new UserInforBean();
		    user.setPid(pid);
		    user.setPerson_name(name);
		    user.setPwd(pwd);
		    user.setDuty(duty);
		    user.setDept(dept);
		    user.setAsses_dept(asses_dept);
		    user.setRole(role);
		}
		rset.close();
		stmt.close();
		conn.close();
		return user;
	}
	
	public UserInforBean getUser(String id)throws Exception
	{
		String strSql="select * from person where pid = ? ";
		PreparedStatement stmt=conn.prepareStatement(strSql);
		stmt.setString(1,id);
		ResultSet rset=stmt.executeQuery();
		//conn.commit();
		UserInforBean user=null;
		if(rset.next())
		{
			String pid=rset.getString(1);//id
			String name=rset.getString(2);//人员名称
			String pwd=rset.getString(3);//密码
			String duty=rset.getString(4);//职务
			String dept=rset.getString(5);//部门
			String asses_dept=rset.getString(6);//协管部门
			String role=rset.getString(7);//角色	   
		   //
			user=new UserInforBean();
		    user.setPid(pid);
		    user.setPerson_name(name);
		    user.setPwd(pwd);
		    user.setDuty(duty);
		    user.setDept(dept);
		    user.setAsses_dept(asses_dept);
		    user.setRole(role);
		}
		rset.close();
		stmt.close();
		conn.close();
		return user;
	}
	
	public Collection getUsers()throws Exception
	{
		Collection ret = new ArrayList();
		Statement stmt=conn.createStatement();
		String strSql="select * from person order by role ";
		ResultSet rset=stmt.executeQuery(strSql);
		//conn.commit();
		while(rset.next())
		{
			String pid=rset.getString(1);//id
			String name=rset.getString(2);//人员名称
			String pwd=rset.getString(3);//密码
			String duty=rset.getString(4);//职务
			String dept=rset.getString(5);//部门
			String asses_dept=rset.getString(6);//协管部门
			String role=rset.getString(7);//角色	   
		   //
			UserInforBean user=new UserInforBean();
		    user.setPid(pid);
		    user.setPerson_name(name);
		    user.setPwd(pwd);
		    user.setDuty(duty);
		    user.setDept(dept);
		    user.setAsses_dept(asses_dept);
		    user.setRole(role);
		  
		    ret.add(user);
			
		}
		
		rset.close();
		stmt.close();
		conn.close();
		return ret;
	}
	
	public Collection getAssesUsers()throws Exception
	{
		Collection ret = new ArrayList();
		Statement stmt=conn.createStatement();
		String strSql="select * from person where asses_dept != \"\"  ";
		ResultSet rset=stmt.executeQuery(strSql);
		//conn.commit();
		while(rset.next())
		{
			String pid=rset.getString(1);//id
			String name=rset.getString(2);//人员名称
			String pwd=rset.getString(3);//密码
			String duty=rset.getString(4);//职务
			String dept=rset.getString(5);//部门
			String asses_dept=rset.getString(6);//协管部门
			String role=rset.getString(7);//角色	   
		   //
			UserInforBean user=new UserInforBean();
		    user.setPid(pid);
		    user.setPerson_name(name);
		    user.setPwd(pwd);
		    user.setDuty(duty);
		    user.setDept(dept);
		    user.setAsses_dept(asses_dept);
		    user.setRole(role);
		  
		    ret.add(user);
			
		}
		
		rset.close();
		stmt.close();
		conn.close();
		return ret;
	}
	
	public Collection getloginDepts()throws Exception
	{
		Collection ret = new ArrayList();
		Statement stmt=conn.createStatement();
		String strSql="select distinct dept from person where dept != \"\" ";
		ResultSet rset=stmt.executeQuery(strSql);
		//conn.commit();
		while(rset.next())
		{
			String dept=rset.getString(1);//部门
		    ret.add(dept);
			
		}
		
		rset.close();
		stmt.close();
		conn.close();
		return ret;
	}
	
	public Collection getloginUsers(String dept)throws Exception
	{
		Collection ret = new ArrayList();
		String strSql="select person_name from person where dept = ? and role !=\"jszj\" ";
		PreparedStatement stmt=conn.prepareStatement(strSql);
		stmt.setString(1,dept);
		ResultSet rset=stmt.executeQuery();
		//conn.commit();
		while(rset.next())
		{
			String name=rset.getString(1);//人员名称
	        ret.add(name);
		}
		rset.close();
		stmt.close();
		conn.close();
		return ret;
	}
	
    public boolean deleteUser(String pid)throws Exception{

		PreparedStatement stm=conn.prepareStatement("DELETE FROM person WHERE pid=? ");
		stm.setString(1,pid);
	    try 
		{
			stm.executeUpdate();
			stm.close();
		    conn.close();
			return true;
		} 
		catch(Exception e) 
		{
			conn.rollback();
			stm.close();
		    conn.close();
			return false;
		}		
	}
    
    public String getPid() throws Exception{
    	return UUID.randomUUID().toString();
    }
    
    public boolean addUser(String name,String pwd,String duty,
    		String dept,String asses,String role)throws Exception{
    	//人员名称//密码//职务//部门//协管部门//角色	   
    	PreparedStatement stm=conn.prepareStatement("INSERT INTO person VALUES(?,?,?,?,?,?,?)");
	    stm.setString(1,this.getPid());
        stm.setString(2,name);
        stm.setString(3,pwd);
        stm.setString(4,duty);
        stm.setString(5,dept);
        stm.setString(6,asses);
        stm.setString(7,role);
	    try 
		{
			stm.executeUpdate();
			stm.close();
		    conn.close();
			return true;
		} 
		catch(Exception e) 
		{
			conn.rollback();
			stm.close();
		    conn.close();
			return false;
		}		
	}
    
   public boolean updateUser(String name,String pwd,String duty,
    		String dept,String asses,String role,String userid) throws Exception{
 	   PreparedStatement stm=conn.prepareStatement("UPDATE person SET person_name=? , pwd=? , duty=? , dept=? , asses_dept=? , role=? where pid=? ");
 	       
 	   stm.setString(1,name);
       stm.setString(2,pwd);
       stm.setString(3,duty);
       stm.setString(4,dept);
       stm.setString(5,asses);
       stm.setString(6,role);
       stm.setString(7,userid);
       try 
		{
			stm.executeUpdate();
			stm.close();
		    conn.close();
			return true;
		} 
		catch(Exception e) 
		{
			conn.rollback();
			stm.close();
		    conn.close();
			return false;
		}
 	}
   
   public boolean updateUser(String pwd,String userid) throws Exception{
	   PreparedStatement stm=conn.prepareStatement("UPDATE person SET pwd=? where pid=? ");
	       

      stm.setString(1,pwd);
      stm.setString(2,userid);
      try 
		{
			stm.executeUpdate();
			stm.close();
		    conn.close();
			return true;
		} 
		catch(Exception e) 
		{
			conn.rollback();
			stm.close();
		    conn.close();
			return false;
		}
	}
   
   public String getUserName(String dept)throws Exception
	{
		String strSql="select person_name from person where dept = ? ";
		PreparedStatement stmt=conn.prepareStatement(strSql);
		stmt.setString(1,dept);
		ResultSet rset=stmt.executeQuery();
		String name=null;
		//conn.commit();
		if(rset.next())
		{
			name=rset.getString(1);//人员名称	
		}
		rset.close();
		stmt.close();
		conn.close();
		return name;
	}
}