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("访问数据库异常");}//获取该empnoResultSet 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")){//不是tomps.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;}@Overridepublic 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 {@Testpublic 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");}}}