JDBC中利用主键返回和Batch插入多条数据

3 向Emp表中插入一个团队成员

向Emp表中插入一个团队成员，该团队的成员信息如图-1所示。

图-1

从图-1可以看出tom为这个团队的管理者，其他三位员工的管理者ID(mgr) 都为1，而1是管理者tom的员工编号（empno）。

要求向Emp表插入以上四个员工的信息。职员marry、terry、jim的管理者ID(mgr)为刚刚插入Emp表的管理者tom的员工编号（empno）的数据。另外，Emp表的主键列empno的数据通过序列emp_seq获得。

DAO:

package homework;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import day01.DBUtil2;

public class TestDAO {
	public boolean update(List<Emp> list){
		Connection conn = null;
		try {
			conn = DBUtil2.getConnection();
			conn.setAutoCommit(false);//手动提交
			String sql = "INSERT INTO emp VALUES (emp_seq.nextval,"
					+ "?,?,?,?,?,?,?)";
			PreparedStatement ps = 
					conn.prepareStatement(sql,new String[]{"empno"});
			for(Emp e : list){
				if(e.getEname().equals("tom")){
					ps.setString(1, e.getEname());
					ps.setString(2, e.getJob());
					ps.setObject(3, e.getMgr());
					ps.setDate(4, e.getHiredate());
					ps.setObject(5, e.getSal());
					ps.setObject(6, e.getComm());
					ps.setObject(7, e.getDeptno());
					break;
				}
			}
			int flag = ps.executeUpdate();
			if(flag == 0){
				ps.close();
				throw new RuntimeException("访问数据库异常");
			}
			//获取该empno
			ResultSet rs = ps.getGeneratedKeys();
			rs.next();
			int empno = rs.getInt(1);
			rs.close();
			//处理剩下的三位员工
			String sql2 = "INSERT INTO emp VALUES (emp_seq.nextval,"
					+ "?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql2);
			for(Emp e: list){
				if(!e.getEname().equals("tom")){//不是tom
					ps.setString(1, e.getEname());
					ps.setString(2, e.getJob());
					ps.setObject(3, empno);
					ps.setDate(4, e.getHiredate());
					ps.setObject(5, e.getSal());
					ps.setObject(6, e.getComm());
					ps.setObject(7, e.getDeptno());
					ps.addBatch();
				}
			}
			ps.executeBatch();
			ps.clearBatch();
			ps.close();
			conn.commit();//手动提交
		} catch (SQLException e) {
			e.printStackTrace();
			try {
				conn.rollback();
				System.out.println("回滚");
				return false;
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			DBUtil2.closeConnection();
		}
		return true;
	}
	/**
	 * 注意:ps.setInt(3, empno)或ps.setDouble(3, empno)
	 * 针对传入的参数类型均为基本类型int 或 double,若实体类中该属性为null,
	 * 则在使用prepareStatement时要改为ps.setObject(3, empno);
	 * 否则会报空指针异常
	 */
}

实体类：

package homework;
import java.io.Serializable;
import java.sql.Date;
public class Emp implements Serializable{
	private static final long serialVersionUID = 1L;
	private Integer empno;
	private String ename;
	private String job;
	private Integer mgr;
	private Date hiredate;
	private Double sal;
	private Double comm;
	private Integer deptno;
	public Emp() {
	}
	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 Integer getMgr() {
		return mgr;
	}
	public void setMgr(Integer mgr) {
		this.mgr = mgr;
	}
	
	public Date getHiredate() {
		return hiredate;
	}
	public void setHiredate(Date hiredate) {
		this.hiredate = hiredate;
	}
	
	public Double getSal() {
		return sal;
	}
	public void setSal(Double sal) {
		this.sal = sal;
	}

	public Double getComm() {
		return comm;
	}
	public void setComm(Double comm) {
		this.comm = comm;
	}
	public Integer getDeptno() {
		return deptno;
	}
	public void setDeptno(Integer deptno) {
		this.deptno = deptno;
	}
	@Override
	public String toString() {
		return "Emp [empno=" + empno + ", ename=" + ename + ", job=" + job + ", mgr=" + mgr + ", hiredate=" + hiredate
				+ ", sal=" + sal + ", comm=" + comm + ", deptno=" + deptno + "]";
	}
}

测试类：

package homework;

import java.sql.Date;
import java.util.ArrayList;
import java.util.List;
import org.junit.Test;
public class MyUserService {
	
	@Test
	public void test1(){
		List<Emp> list = new ArrayList<Emp>();
		Emp e1 = new Emp();
		e1.setEname("tom");
		e1.setJob("manager");
		e1.setMgr(7839);
		e1.setHiredate(Date.valueOf("2017-04-01"));
		e1.setSal(5000.0);
		e1.setComm(300.0);
		e1.setDeptno(30);
		
		Emp e2 = new Emp();
		e2.setEname("marry");
		e2.setJob("clerk");
		e2.setHiredate(Date.valueOf("2017-04-02"));
		e2.setSal(3000.0);
		e2.setDeptno(30);
		
		Emp e3 = new Emp();
		e3.setEname("tery");
		e3.setJob("salesman");
		e3.setHiredate(Date.valueOf("2017-04-03"));
		e3.setSal(2500.0);
		e3.setComm(200.0);
		e3.setDeptno(30);
		
		Emp e4 = new Emp();
		e4.setEname("jim");
		e4.setJob("salesman");
		e4.setHiredate(Date.valueOf("2017-04-04"));
		e4.setSal(2500.0);
		e4.setComm(200.0);
		e4.setDeptno(30);
		
		TestDAO dao = new TestDAO();
		list.add(e1);
		list.add(e2);
		list.add(e3);
		list.add(e4);
		
//		for(Emp e : list){
//			System.out.println(e);
//		}
		
		boolean flag = dao.update(list);
		if(flag){
			System.out.println("ok");
		}else{
			System.out.println("false");
		}
	}
}