positiondaoimpl.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 com.dao.PositionDao;
import com.domain.Position;
import com.domain.Staff;
import com.util.JdbcUtil;

public class PositionDaoImpl implements PositionDao {
	
	private String SELECT_POSITION = "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 = ? ";
	private String SELECT_POSITION_INFO = "select positionname,positionId,introduction from position where positionName != '自动化系统'";
	private String INSEARCH_STAFF = "insert into position (positionId,parentId,positionName,introduction,actualBeginTime,actualEndTime) values (positions.nextval,?,?,?,?,?)";
	private String UPDATE_POSITION = "update position set positionName = ?,introduction = ?,actualBeginTime = ?, actualEndTime = ? where positionId = ?";
	private String MOVE_POSITION = "update position set parentId = ? where positionId = ?";

	//通过职位ID查询职位信息
	public List searchPositionById(int positionId) {
		
		Connection conn = JdbcUtil.getConnection();
		
		List<Staff> list = new ArrayList<Staff>();
		Position position = null;
		try {
			PreparedStatement ps = conn.prepareStatement(SELECT_POSITION);
			ps.setInt(1, positionId);
			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 searchPositionName() {
		
		Connection conn = JdbcUtil.getConnection();
	
		List<Position> list = new ArrayList<Position>();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(SELECT_POSITION_INFO);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				Position position = new Position();
				position.setPositionName(rs.getString("positionName"));
				position.setPositionId(rs.getInt("positionId"));
				position.setIntroduction(rs.getString("introduction"));
				list.add(position);
			}
			rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	//通过职位ID查询职位名称
	public String searchPositionNameByID(int positionId) {
		
		Connection conn = JdbcUtil.getConnection();
		String info = "select positionName from position where positionId = '"+positionId+"'";
		String positionName = null;
		
		try {
			PreparedStatement ps = conn.prepareStatement(info);
			ResultSet rs = ps.executeQuery();

			if(rs.next()){
				positionName = rs.getString("positionName");
			}
			rs.close();
			ps.close();			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return positionName;
	}
	
	public void addPosition(Position position) {
		
		Connection conn = JdbcUtil.getConnection();		
		PreparedStatement ps;
		
		try {
			ps = conn.prepareStatement(INSEARCH_STAFF);
			int parentId = position.getParentID();
			String positionName = position.getPositionName();
			String introduction = position.getIntroduction();
			String begin = position.getActualBeginTime();
			String end = position.getActualEndTime();
				
			ps.setInt(1, parentId);
			ps.setString(2, positionName);
			ps.setString(3, introduction);
			ps.setString(4, begin);
			ps.setString(5, end);
			
			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 deletePosition(int positionId) {
		Connection conn = JdbcUtil.getConnection();		
		PreparedStatement ps;
		String info = "delete from position where positionId = '"+positionId+"'";
		
		try {
			ps = conn.prepareStatement(info);
		
		ps.execute();
		ps.close();
		conn.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	//查询父ID
	public List searchPositionParentId() {
		
		Connection conn = JdbcUtil.getConnection();
		String info = "select parentId from position";
		List list = new ArrayList();
		
		try {
			PreparedStatement ps = conn.prepareStatement(info);
			ResultSet rs = ps.executeQuery();

			while(rs.next()){
				String parentId = rs.getString("PARENTID");
				list.add(parentId);
			}
			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 boolean isHasStaff(int positionId) {
		
		Connection conn = JdbcUtil.getConnection();
		String info = "select fullName from staff where staffid in (select staffId from staffposition where positionid in (select positionid from position where positionid = "+positionId+"))";
		boolean isHasStaff = false;
		PreparedStatement ps;
		
		try {
			ps = conn.prepareStatement(info);
			ResultSet rs = ps.executeQuery();
			
			if(rs.next()){
				isHasStaff = true;
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return isHasStaff;
	}

	public List searchIntroductionById(int positionId) {
		Connection conn = JdbcUtil.getConnection();
		String info = "select * from position where positionId = '"+positionId+"'";
		List list = new ArrayList();
		try {
			PreparedStatement ps = conn.prepareStatement(info);
			ResultSet rs = ps.executeQuery();

			while(rs.next()){
				Position position = new Position();
				position.setActualBeginTime(rs.getString("actualBeginTime"));
				position.setActualEndTime(rs.getString("actualendTime"));
				position.setIntroduction(rs.getString("introduction"));
				position.setPositionName(rs.getString("positionName"));
				position.setPositionId(positionId);
				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 updatePosition(Position position) {
		
		String positionName = position.getPositionName();
		String introduction = position.getIntroduction();
		String actualBeginTime = position.getActualBeginTime();
		String actualEndTime = position.getActualEndTime();
		int positionId = position.getPositionId();
		
		Connection conn = JdbcUtil.getConnection();
		
		PreparedStatement ps;
		
		try {
			ps = conn.prepareStatement(UPDATE_POSITION);
			ps.setString(1, positionName);
			ps.setString(2, introduction);
			ps.setString(3, actualBeginTime);
			ps.setString(4, actualEndTime);
			ps.setInt(5, positionId);
			
			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 movePosition(int positionId, int parentId) {
		
		Connection conn = JdbcUtil.getConnection();
		PreparedStatement ps;
		
		try {
			ps = conn.prepareStatement(MOVE_POSITION);
			ps.setInt(1, parentId);
			ps.setInt(2, positionId);
			
			ps.execute();
			ps.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}