employeedaoimpl.java

固定资产管理系统包括一个基本的部分

package com.shenlong.assetmanage.daoImplSQLserver;

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

import com.shenlong.assetmanage.dao.EmployeeDao;
import com.shenlong.assetmanage.dbhelp.ConnectionFactory;
import com.shenlong.assetmanage.domain.Employee;



public class EmployeeDaoImpl implements EmployeeDao {
	//－－－－－－增加员工信息－－－－－－－－－
	public int saveNewEmp (Employee emp) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int update = 0;

		try {
			con = ConnectionFactory.getConnection();
			con.setAutoCommit(false);
			String sql = "INSERT INTO employee (emp_name, emp_duty, emp_memo, emp_state) " +
					"VALUES (?,?,?,?)";
			ps = con.prepareStatement(sql);
			ps.setString(1, emp.getName());
			ps.setString(2, emp.getDuty());
			ps.setString(3, emp.getMemo());
			ps.setString(4, "1");
			update = ps.executeUpdate();
			con.commit();
		} catch(SQLException e) {
			con.rollback();
			e.printStackTrace();			
		} finally {			
			ps.close();
			con.close();
		}
		return update;
	}
	//－－－－－－－－－－－根据员工的姓名查询进行查询－－－－－
	public int getEmployeeCountByName(String empName) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int count = 0;
		try {
			con = ConnectionFactory.getConnection();			

			String sql = "SELECT count(emp_id) FROM employee " +
					"WHERE emp_state = 1";
			
			if(!empName.equalsIgnoreCase("*")){
				sql += " AND emp_name like ?";
			}
			ps = con.prepareStatement(sql);
			if(!empName.equalsIgnoreCase("*")) {		
				ps.setString(1, "%" + empName + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {
				count = rs.getInt(1);
			}
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return count;
	}
	//－－－－－－－－－修改员工的信息－－－－－－－－－－－－－－－
	public int modifyEmployee(Employee employee) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		int row = 0;
		try{
			con = ConnectionFactory.getConnection();
			String sql = "UPDATE employee SET emp_name=?, emp_duty=?, emp_memo=? " +
					"WHERE emp_id=?";
			ps = con.prepareStatement(sql);
			ps.setString(1, employee.getName());
			ps.setString(2, employee.getDuty());
			ps.setString(3, employee.getMemo());
			ps.setInt(4, employee.getId());
			row = ps.executeUpdate();
		} finally {
			ps.close();
			con.close();			
		}	
		return row;
	}
	//－－－－－－－－－－删除员工(首先查询是否拥有固定资产)－－－－－－－－－－－－
	public int deleteEmployeeById(int id) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int row = 0;
		int count=0;
		try {
			con = ConnectionFactory.getConnection();
			String sql = "SELECT count(asset_id)FROM assets  " +
			"WHERE asset_emp_id=?";
			ps = con.prepareStatement(sql);
			ps.setInt(1, id);
            rs=ps.executeQuery();
            while(rs.next()){
            	count=rs.getInt(1);
            }

		 } finally {
			rs.close();
			ps.close();
			con.close();
		}
		if(count==0){
		try {
				con = ConnectionFactory.getConnection();
				String sql = "UPDATE employee SET emp_state=0 " +
				"WHERE emp_id=?";
				ps = con.prepareStatement(sql);
				ps.setInt(1, id);
				row=ps.executeUpdate();
			} finally {
				ps.close();
				con.close();
			}
		
		}
		return row;
	}
	
	//－－－－根据姓名进行排序查询所有的员工－－－－－－－－－－－
	public List<Employee> getAllEmployees() throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<Employee> employees = new ArrayList<Employee>();
		try {
			con = ConnectionFactory.getConnection();
			String sql = "SELECT emp_id, emp_name FROM employee " +
					"WHERE emp_state=1 " +
					"ORDER BY emp_name";
			ps = con.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()){
				Employee employee = new Employee();
				employee.setId(rs.getInt("emp_id"));
				employee.setName(rs.getString("emp_name"));
				employees.add(employee);
			}
		}finally{
			rs.close();
			ps.close();
			con.close();
		}
		return employees;
	}
	//-----------------------------－－－－－－－－
	
	public Employee queryEmployeeById(int id) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs=null;
		Employee employee=null;
	
		try {
			con = ConnectionFactory.getConnection();
			String sql = "select *from employee  " +
					"WHERE emp_id=? ";
			ps = con.prepareStatement(sql);
			ps.setInt(1,id);
			 rs = ps.executeQuery();
			while (rs.next()) {				
				 employee = new Employee(
						rs.getInt("emp_id"), rs.getString("emp_name"), rs.getString("emp_duty"),
						(rs.getString("emp_memo")), "1"
					);
			}
			
		} finally {
			ps.close();
			con.close();
		}	
		return employee;
	
   }
	//
	public List<Employee> queryEmployeeByName(String empName, int page, int total) throws SQLException {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int pagesize=5;
		List<Employee> employees = new ArrayList<Employee>();
		int rowStart = (page - 1) * pagesize + 1;
		int rowEnd = page * pagesize;
		rowEnd = (rowEnd >= total) ? 0 : total - rowEnd;
		try {
			con = ConnectionFactory.getConnection();
			
			String sql1 = "SELECT emp_id, emp_name, emp_duty, emp_memo FROM employee " +
						"WHERE emp_id NOT IN (SELECT TOP " + (rowStart - 1) + " emp_id FROM employee ";

			String sql2 = "ORDER BY emp_id) " +
						"AND emp_id NOT IN (SELECT TOP " + rowEnd + " emp_id FROM employee ";
			String sql3 = "ORDER BY emp_id DESC) ";
												
			String sql4 = "ORDER BY emp_id "; 
			String sql = null;
			if(!empName.equalsIgnoreCase("*")) {
				sql = sql1 + "WHERE emp_state=1 AND emp_name LIKE ? " +
					sql2 + "WHERE emp_state=1 AND emp_name LIKE ? " +
					sql3 + "AND emp_state=1 AND emp_name LIKE ? " +
					sql4;
			} else {
				sql = sql1 + "WHERE emp_state=1 " +
					sql2 + "WHERE emp_state=1 " +
					sql3 + "AND emp_state=1 " +
					sql4;
			}
			ps = con.prepareStatement(sql);
			if(!empName.equalsIgnoreCase("*")) {	
				ps.setString(1, "%" + empName + "%");
				ps.setString(2, "%" + empName + "%");
				ps.setString(3, "%" + empName + "%");
			}
			rs = ps.executeQuery();
			while (rs.next()) {				
				Employee employee = new Employee(
						rs.getInt("emp_id"), rs.getString("emp_name"), rs.getString("emp_duty"),
						rs.getString("emp_memo"), "1"
					);
				employees.add(employee);
			}		
		} finally {
			rs.close();
			ps.close();
			con.close();
		}
		return employees;
	}
	
	
	
}