userado.java

辅助办公系统,具有发布公告、站内邮箱、日程安排、日志查看等功能

package com.x3408.employees;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import com.x3408.database.CNProvider;

public class UserADO {

	public static String userType(String employeeID,String password){
		//当返回值为空时，表示数据库中不存在该用户
		Connection conn=CNProvider.getConnection();
		PreparedStatement pstat=null;
		ResultSet rst=null;
		String result=null;
		try{
			pstat=conn.prepareStatement("select Admin from Employees "
					+"where EmployeeID=? and PassWord=?");
			pstat.setString(1,employeeID);
			pstat.setString(2,password);
			rst=pstat.executeQuery();
			if(rst.next()){
				if(rst.getInt(1)==1)
					result="admin";
				else
					result="user";
			}
		}catch(SQLException e){			
				e.printStackTrace();
			}finally{
				CNProvider.release(rst,pstat,conn);
			}
		return result;
	}
	public static boolean employeeInsert(UserInfo userInfo){
		Connection conn=null;
		PreparedStatement pstat=null;
		if(!userInfo.isVaild()){
			return false;
		}
		conn=CNProvider.getConnection();
		try {
			pstat=conn.prepareStatement("insert Employees" +
					"(employeeID,name,sex,birthday,mobile,phone,department,leader,admin,salary,[position],password)values(?,?,?,?,?,?,?,?,?,?,?,?)");
			pstat.setString(1, userInfo.getEmployeeID());
			pstat.setString(2, userInfo.getName());
			pstat.setString(3, userInfo.getSex());
			pstat.setString(4, userInfo.getBirthday());
			pstat.setString(5, userInfo.getMobile());
			pstat.setString(6, userInfo.getPhone());
			pstat.setString(7, userInfo.getDepartment());
			pstat.setString(8, userInfo.getLeader());
			pstat.setBoolean(9, userInfo.getAdmin());
			pstat.setInt(10,userInfo.getSalary());
			pstat.setString(11, userInfo.getPosition());
			pstat.setString(12,userInfo.getPassword());
			pstat.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(pstat,conn);
		}
		return false;
	}
	public static boolean employeeUpdate(UserInfo userInfo){
		Connection conn=null;
		PreparedStatement pstat=null;
		UserInfo tempUserInfo=null;
		if(userInfo.getPassword()==null||"".equals(userInfo.getPassword())){
			tempUserInfo=UserADO.userQuery(userInfo.getEmployeeID());
			if(tempUserInfo!=null){
				userInfo.setPassword(tempUserInfo.getPassword());
			}
		}
		if(!userInfo.isVaild()){
			return false;
		}
		try {
			conn=CNProvider.getConnection();
			pstat=conn.prepareStatement("update employees set name=?,sex=?,birthday=?,mobile=?,phone=?," +
					"department=?,leader=?,admin=?,salary=?,[position]=?,password=? where employeeID=?");
			pstat.setString(1, userInfo.getName());
			pstat.setString(2, userInfo.getSex());
			pstat.setString(3, userInfo.getBirthday());
			pstat.setString(4, userInfo.getMobile());
			pstat.setString(5, userInfo.getPhone());
			pstat.setString(6, userInfo.getDepartment());
			pstat.setString(7, userInfo.getLeader());
			pstat.setBoolean(8, userInfo.getAdmin());
			pstat.setInt(9,userInfo.getSalary());
			pstat.setString(10, userInfo.getPosition());
			pstat.setString(11,userInfo.getPassword());
			pstat.setString(12, userInfo.getEmployeeID());
			if(pstat.executeUpdate()!=0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(pstat,conn);
		}
		return false;
	}
	public static boolean baseInfoUpdate(UserInfo pUserInfo){
		UserInfo userInfo=null;
		userInfo=UserADO.userQuery(pUserInfo.getEmployeeID());
		userInfo.setMobile(pUserInfo.getMobile());
		userInfo.setPhone(pUserInfo.getPhone());
		userInfo.setPassword(pUserInfo.getPassword());
		return UserADO.employeeUpdate(userInfo);
	}
	public static boolean employeeDel(String employeeID){
		Connection conn=CNProvider.getConnection();
		PreparedStatement pstat=null;
		try {
			pstat=conn.prepareStatement("delete from employees where employeeID=?");
			pstat.setString(1, employeeID);
			if(pstat.executeUpdate()!=0){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(pstat,conn);
		}
		return false;
	}
	public static boolean allEmployeeDel(){
		Connection conn=CNProvider.getConnection();
		PreparedStatement pstat=null;
		try {
			pstat=conn.prepareStatement("delete from employees");
			pstat.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(pstat,conn);
		}
		return false;
	}
	public static UserInfo userQuery(String employeeID){
		Connection conn=CNProvider.getConnection();
		PreparedStatement pstat=null;
		ResultSet rst=null;
		UserInfo userInfo;
		userInfo=null;
		if(employeeID==null){
			return null;
		}
		try {
			pstat=conn.prepareStatement("select * from employees where employeeID=?");
			pstat.setString(1,employeeID);
			rst=pstat.executeQuery();
			while(rst.next()){
				userInfo=new UserInfo(rst.getString("employeeID"),rst.getString("name"),rst.getString("sex"),
						rst.getString("birthday"),rst.getString("mobile"),rst.getString("phone"),rst.getString("department"),
						rst.getString("leader"),rst.getString("admin"),rst.getString("salary"),rst.getString("position"),rst.getString("password"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rst,pstat,conn);
		}
		return userInfo;
	}
	public static Vector allUserQuery(){
		Connection conn=CNProvider.getConnection();
		PreparedStatement pstat=null;
		ResultSet rst=null;
		Vector<UserInfo> userList=new Vector<UserInfo>();
		UserInfo userInfo=null;
		userInfo=null;
		try {
			pstat=conn.prepareStatement("select * from employees");
			rst=pstat.executeQuery();
			while(rst.next()){
				userInfo=new UserInfo(rst.getString("employeeID"),rst.getString("name"),rst.getString("sex"),
						rst.getString("birthday"),rst.getString("mobile"),rst.getString("phone"),rst.getString("department"),
						rst.getString("leader"),rst.getString("admin"),rst.getString("salary"),rst.getString("position"),rst.getString("password"));
				userList.addElement(userInfo);
			}
			return userList.size()<1?null:userList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rst,pstat,conn);
		}
		return null;
	}
	public static Vector userSearch(String type,String keyword){
		Connection conn=CNProvider.getConnection();
		PreparedStatement pstat=null;
		ResultSet rst=null;
		UserInfo userInfo=null;
		Vector<UserInfo> userList=new Vector<UserInfo>();
		keyword=keyword==null?"":keyword.trim();
		try {
			if("employeeID".equals(type)){
				pstat=conn.prepareStatement("select * from employees where employeeID=?");
			}else{
				pstat=conn.prepareStatement("select * from employees where name like ?");
				keyword="%"+keyword+"%";
			}
			pstat.setString(1, keyword);
			rst=pstat.executeQuery();
			while(rst.next()){
				userInfo=new UserInfo(rst.getString("employeeID"),rst.getString("name"),rst.getString("sex"),
						rst.getString("birthday"),rst.getString("mobile"),rst.getString("phone"),rst.getString("department"),
						rst.getString("leader"),rst.getString("admin"),rst.getString("salary"),rst.getString("position"),rst.getString("password"));
				userList.addElement(userInfo);
			}
			return userList.size()<1?null:userList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			CNProvider.release(rst,pstat,conn);
		}
		return null;
	}
}