package com.neusoft.dao.impl;

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

import com.neusoft.dao.EmpDao;
import com.neusoft.po.Emp;
import com.neusoft.util.CommonUtil;
import com.neusoft.util.DBUtil;

public class EmpDaoImpl implements EmpDao {// 实现类

	private Connection con = null;
	private PreparedStatement pst = null;
	private ResultSet rs = null;

	// 全查询
	@Override
	public List<Emp> selectEmpAll() {
		List<Emp> list = new ArrayList<>();// 查询多行,所以准备容器
		String sql = "select * from emp order by empno";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			rs = pst.executeQuery();
			while (rs.next()) {
				Emp emp = new Emp();
				emp.setEmpno(rs.getInt("empno"));
				emp.setEname(rs.getString("ename"));
				emp.setJob(rs.getString("job"));
				emp.setHireDate(rs.getString("hiredate"));
				emp.setSal(rs.getDouble("sal"));
				emp.setDeptno(rs.getInt("deptno"));
				list.add(emp);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(rs, pst, con);
		}
		return list;
	}

	// 根据ID查询,返回受影响的
	public Emp selectEmpById(int empno) {
		Emp emp = null;
		String sql = "select * from emp where empno=?";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, empno);
			rs = pst.executeQuery();
			if (rs.next()) {
				emp = new Emp();
				emp.setEmpno(rs.getInt("empno"));
				emp.setEname(rs.getString("ename"));
				emp.setJob(rs.getString("job"));
				emp.setHireDate(rs.getString("hiredate"));
				emp.setSal(rs.getDouble("sal"));
				emp.setDeptno(rs.getInt("deptno"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(rs, pst, con);
		}
		return emp;
	}

	// 添加,返回受影响的行数
	public int insertEmp(Emp emp) {
		int result = 0;
		String sql = "insert into emp values(null,?,?,?,?,?)";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, emp.getEname());
			pst.setString(2, emp.getJob());
			pst.setString(3, CommonUtil.getCurDate());// 获取系统日期
			pst.setDouble(4, emp.getSal());
			pst.setInt(5, emp.getDeptno());
			result = pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(null, pst, con);
		}
		return result;
	}

	// 更新工资和职位
	public int updateEmp(Emp emp) {
		int result = 0;
		String sql = "update emp set job=?,sal=? where empno=?";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setString(1, emp.getJob());
			pst.setDouble(2, emp.getSal());
			pst.setInt(3, emp.getEmpno());
			result = pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(rs, pst, con);
		}
		return result;
	}

	// 删除
	public int deleteEmpById(int empno) {
		int result = 0;
		String sql = "delete from emp where empno=?";
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql);
			pst.setInt(1, empno);
			result = pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeAll(rs, pst, con);
		}
		return result;
	}
}

package com.neusoft.dao;

import java.util.List;

import com.neusoft.po.Emp;

public interface EmpDao {
	
	public List<Emp> selectEmpAll();//全查询
	public Emp selectEmpById(int empno);//根据主键查询
	public int insertEmp(Emp emp);//添加
	public int updateEmp(Emp emp);//修改
	public int deleteEmpById(int empno);//删除
}

package com.neusoft.po;

public class Emp {// 实体类,和数据库数据一一对应

	private Integer empno;
	private String ename;
	private String job;
	private String hireDate;
	private Double sal;
	private Integer deptno;

	@Override
	public String toString() {
		return this.empno + "\t" + this.ename + "\t" + this.job + "\t" + this.hireDate + "\t" + this.sal + "\t"
				+ this.deptno;
	}

	public Integer getEmpno() {
		return empno;
	}

	public void setEmpno(Integer empno) {
		this.empno = empno;
	}

	public String getEname() {
		return ename;
	}

	public void setEname(String ename) {
		this.ename = ename;
	}

	public String getJob() {
		return job;
	}

	public void setJob(String job) {
		this.job = job;
	}

	public String getHireDate() {
		return hireDate;
	}

	public void setHireDate(String hireDate) {
		this.hireDate = hireDate;
	}

	public Double getSal() {
		return sal;
	}

	public void setSal(Double sal) {
		this.sal = sal;
	}

	public Integer getDeptno() {
		return deptno;
	}

	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}

}

package com.neusoft.util;

import java.text.SimpleDateFormat;
import java.util.Date;

public class CommonUtil {// 共通的非JDBC的工具类类

	// 获取系统日期
	public static String getCurDate() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		return sdf.format(new Date());
	}
}

package com.neusoft.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBUtil {

	private static final String URL = "jdbc:mysql://localhost:3306/emp?characterEncoding=utf-8";
	private static final String DRIVER = "com.mysql.jdbc.Driver";
	private static final String USERNAME = "root";
	private static final String PASSWORD = "root";

	// 获取Connection连接
	public static Connection getConnection() {
		Connection con = null;
		try {
			Class.forName(DRIVER);
			con = DriverManager.getConnection(URL, USERNAME, PASSWORD);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return con;
	}

	// 关闭资源
	public static void closeAll(ResultSet rs, PreparedStatement pst, Connection con) {
		try {
			if (rs != null) {
				rs.close();
			}
			if (pst != null) {
				pst.close();
			}
			if (con != null) {
				con.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

package com.neusoft;

import java.util.List;

import com.neusoft.dao.EmpDao;
import com.neusoft.dao.impl.EmpDaoImpl;
import com.neusoft.po.Emp;

public class Test {

	public static void main(String[] args) {
	    EmpDao dao = new EmpDaoImpl();
	    //全查询
	    /*
	    List<Emp> list = dao.selectEmpAll();
	    if(list.size()==0){
	    	System.out.println("没有数据");
	    }
	    for(Emp emp : list){
	    	System.out.println(emp);
	    }
	    */
	    /*
	    //根据主键查询
	    Emp emp = dao.selectEmpById(10);
	    if(emp!=null){
	    	System.out.println(emp);
	    }else{
	    	System.out.println("查无此人");
	    }  
	    */
	    /*
	    Emp emp = new Emp();
	    emp.setEname("习大大");
	    emp.setJob("国家主席");
	    emp.setSal(9000.32);
	    emp.setDeptno(10);
	    int result = dao.insertEmp(emp);
	    if(result>0){
	       System.out.println("添加成功");
	    }else{
	    	System.out.println("添加失败");
	    }
	   */
	   /*
	    Emp emp = new Emp();
	    emp.setEmpno(75);
	    emp.setJob("国家主席1");
	    emp.setSal(9000.33);
	    int result = dao.updateEmp(emp);
	    if(result>0){
	       System.out.println("更新成功");
	    }else{
	    	System.out.println("更新失败");
	    }
	    */
	    
	    int result = dao.deleteEmpById(75);
	    if(result>0){
		       System.out.println("删除成功");
		}else{
		    	System.out.println("删除失败");
		}
		
	}
}