staffdaoimpl.java

该系统可以实现邮件的收发

package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;
import com.dao.StaffDao;
import com.domain.Position;
import com.domain.Staff;
import com.util.JdbcUtil;

public class StaffDaoImpl implements StaffDao {
	
	private String INSEARCH_STAFF = "insert into staff (staffId,loginId,password,email,mobilePhone,address,age,sex,fullName) values (staffs.nextval,?,?,?,?,?,?,?,?)";
	private String UPDATE_SP = "update staffPosition set onwork = 0 where staffId = ?";
	private String UUDATE_ONWORK = "update staffPosition set onwork = 1 where staffId = ?";

	public Staff staffLogin(String name) {
		
		Connection conn = JdbcUtil.getConnection();
		String info = "select * from staff where loginId = '"+ name + "'";
		Staff staff = null;
		
		try {
			PreparedStatement ps = conn.prepareStatement(info);
			ResultSet rs = ps.executeQuery();
					
			if(rs.next()){
				staff = new Staff();
				staff.setStaffId(rs.getInt("staffId"));
				staff.setLoginId(rs.getString("loginId"));
				staff.setPassword(rs.getString("password"));
				staff.setEmail(rs.getString("email"));
				staff.setMobilePhone(rs.getString("mobilePhone"));
				staff.setAddress(rs.getString("address"));
				staff.setAge(rs.getInt("age"));
				staff.setSex(rs.getString("sex"));
				staff.setCheckinTime(rs.getDate("checkinTime"));
				staff.setFullName(rs.getString("fullName"));
			}
			rs.close();
			ps.close();			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return staff;
	}

	public List searchStaff(int name,String positionId) {
		
		Connection conn = JdbcUtil.getConnection();
		String info = "select s.staffId,s.fullname,s.mobilephone,s.age,s.sex,s.email,p.positionname from staff s, position p,staffposition sp where sp.positionid=p.positionid and " +
					  "s.staffid=sp.staffid and p.positionId = '"+positionId +"'"+"and sp.onwork = '"+name+"'";
		
		List<Staff> list = new ArrayList<Staff>();
		Position position = null;
		
		try {
			PreparedStatement ps = conn.prepareStatement(info);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				Staff staff = new Staff();
				staff.setStaffId(rs.getInt("staffId"));
				staff.setEmail(rs.getString("email"));
				staff.setMobilePhone(rs.getString("mobilephone"));
				staff.setAge(rs.getInt("age"));
				staff.setSex(rs.getString("sex"));
				staff.setFullName(rs.getString("fullName"));
				position = new Position();
				position.setPositionName(rs.getString("positionName"));
				staff.setPosition(position);
				list.add(staff);
			}
			rs.close();
			ps.close();			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public List searchStaffById(int id) {
		Connection conn = JdbcUtil.getConnection();
		String info = "select * from staffPosition where staffId = '"+ id + "'";
		Staff staff = null;
		Position position = null;
		List list = new Vector();
	
		try {
			PreparedStatement ps = conn.prepareStatement(info);
			ResultSet rs = ps.executeQuery();
					
			if(rs.next()){
				staff = new Staff();
				position = new Position();
				staff.setStaffId(rs.getInt("staffId"));
				position.setPositionId(rs.getInt("positionId"));
				
				list.add(staff);
				list.add(position);
			}
			rs.close();
			ps.close();			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public void addStaff(Staff staff) {
		
		Connection conn = JdbcUtil.getConnection();		
		PreparedStatement ps;
		
		
		String INSEARCH_STAFFPOSITION = "insert into staffPosition (staffId,positionId,onwork) values (staffs.currval,"+staff.getPosition().getPositionId()+",1)";

		try {
			ps = conn
				.prepareStatement(INSEARCH_STAFF);
			String loginId = staff.getLoginId();
			String password = staff.getPassword();
			String email = staff.getEmail();
			String mobilePhone = staff.getMobilePhone();
			String address = staff.getAddress();
			int age = staff.getAge();
			String sex = staff.getSex();
			String fullName = staff.getFullName();
			
			ps.setString(1, loginId);
			ps.setString(2, password);
			ps.setString(3, email);
			ps.setString(4, mobilePhone);
			ps.setString(5, address);
			ps.setInt(6, age);
			ps.setString(7, sex);
			ps.setString(8, fullName);
			ps.executeQuery();
			
			ps = conn.prepareStatement(INSEARCH_STAFFPOSITION);
			ps.executeQuery();
			conn.close();
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void leavePosition(String[] staffId) {
		
		Connection conn = JdbcUtil.getConnection();
		
		PreparedStatement ps;
		try {
			ps = conn.prepareStatement(UPDATE_SP);
			
			for(int i = 0;i < staffId.length;i++){
				ps.setString(1, staffId[i]);
				ps.execute();
			}
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void moveStaff(String[] staffId, String positionId) {
		
		Connection conn = JdbcUtil.getConnection();
		String info = "update staffPosition set positionId = "+positionId +"where staffId = ?";
		PreparedStatement ps;
		
		try {
			ps = conn.prepareStatement(info);
			
			for(int i = 0;i < staffId.length;i++){
				ps.setString(1, staffId[i]);
				ps.execute();
			}
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void backPos(String[] staffId) {
		
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps;
		
		try {
			ps = conn.prepareStatement(UUDATE_ONWORK);
			for(int i = 0;i < staffId.length;i++){
				ps.setString(1, staffId[i]);
				ps.execute();
			}
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}