JDBC代码实现之第八版

前言

由JDBC代码实现之第七版：批量执行DML语句，结合批量插入员工数据案例，了解了批量更新的实现与应用。本篇依然是与插入数据有关，但探讨的是先后插入多张表的情况。其中需要应用到一个技术细节叫返回自动主键。比如， 在我们执行一个insert语句时，是用序列自动生成了主键ID (不同的数据库生成ID的方式有所不同，Oracle用序列生成ID，其他数据是自增长，只要你在数据库上，设置一个AUTO_INCREMENT属性，它就自增长，自动生成了ID，反正不管怎么样，这个数据库的ID是可以自动生成的)。在某些业务当中，我们刚刚执行完一个insert以后，我就希望知道刚才的这个ID是什么，这种场景可能会需要获取这个生成的ID。

1.通过业务场景理解技术本质

• 现有这样一个业务，我要注册一个账号，在注册页面上有账号，密码等注册信息，当用户输入完数据以后，点注册这个时候，就提交了数据，把数据提交给了服务器，服务器接收到数据以后，最终通过JDBC技术把数据存到数据库里，存到user表里，那user表里肯定有它自己的主键ID，user的id。假如现在注册的是一个游戏账号，注册账号的时候，这个游戏公司为了吸引你，新用户送英雄，送了3个英雄，当然，这3个英雄不是无限使用的，可以用一个礼拜，或者用一个月，过一个月回收了，你再买，就这个意思。
  
• 那这个英雄也是一份数据，这份数据也得存储到数据库里，还得有另外一张表，用户的英雄表，user_hero，用户有几个英雄都存到这张表里，当然user_hero表也有它的id，那显然user表和user_hero表，这两个表的关系是一对多，某一个用户可以有多个英雄，那么如果两张表示一对多的关系，它们的这个关系就体现在字段id上，比如创建一个员工表，员工表里有个部门id，那么部门和员工就是一对多，一个部门有多个员工，我们在员工表里也存了部门ID。
• 那么一对多的两张表儿，我们习惯于，管这个1叫主表，管多的叫从表，在当前业务当中，user是主表，user_hero是从表。那一对多的关系，是在从表中体现的，从表中需要增加一个字段外键来存储主表的主键，比如部门表是主表，员工表是从表，员工表里存储了部门ID， 如果是用户与英雄的关系，则在user_hero表里也有一个hero的ID，它还要存储一个user的id，即uid。

2.具体业务逻辑

1. 先增加用户数据：获取账号密码等数据存入user表。
2. 再增加用户的英雄数据：通过user表主键ID，将对应用户的英雄数据存入user_hero表。

• 根据业务逻辑，用户和英雄是一对多的关系，英雄表里要存入用户数据的主键。在向user_hero表插入数据前，首先需要获取插入user表时生成的那个主键ID，这种场景就是，刚刚插入完数据以后，我们就需要立刻得到那个数据的ID。即有的时候，我们的业务是先后插入多张表，因为有关联关系，就得获取某张表的主键ID值。
• 那么如果刚刚插入一条数据，ID用序列生成，如何立刻得到这个ID：
• 方案一：先执行insert，再通过已知的条件参数查询ID值。因为插入用户表的数据账号是已知的，所以在插入完成后，可以通过账号反查ID。

  insert into user values (seq.nextval,?,?)
  select * from user where username=?

• 方案二：先记录ID，再应用ID执行insert。因为ID用序列生成，在执行insert之前，先查下序列的值是多少，在执行insert的时候，把事先查询好的ID，直接作为insert的参数传进来。

  select seq.nextval from dual -〉id
  insert into user values (id,?,?)

• 以上两种方式，都得额外的执行一个查询SQL，第一种方式是执行insert以后查一遍，第二种方式是没执行insert前，先查一遍，虽然说都能解决问题，但是并不太好，效率都不是很高。有一种效率更高效的方案：让PS对象返回自动生成的序列ID。因为PS它负责和数据库打交道，向数据库发送执行SQL时，在执行SQL的时候，有能力和数据库得到刚刚生成的那个序列的ID，即我们可以让PS对象给我们返回生成的ID是几，但它默认不去记录ID，不返回，需要让它返回，它才返回。具体如何实现，下面将通过案例(比如用户user和英雄user_hero，或者部门depts和员工emps)去探究技术的本质。

3.代码分析与实现

准备工作：

部门和员工的关系是一对多，先增加部门，再给部门增加一个员工，准备部门表depts和员工表emps，员工表已经存在。创建部门表depts，并为其添加序列和默认数据。部门表比较简单，第一个是部门ID，第二个字段是名字，第3个字段是部门地址。

	create table depts (
		deptno number(8)			primary key,
		dname varchar(20),
		loc varchar(100)
	);
	
	create sequence depts_seq;
	
	insert into depts values(depts_seq.nextval,'销售部','北京');
	insert into depts values(depts_seq.nextval,'市场部','上海');
	insert into depts values(depts_seq.nextval,'开发部','广州');
	commit;
	select * from depts;

代码分析：

代码结构：

为了演示这样一个场景，先增加一个部门，再给此部门，增加一个员工，然后获得生成的部门ID，供员工表使用，这里要执行两个insert，
而且这两个insert是一个完整的业务，所以要保证是一个事务，只创建一个连接对象。首先搭建好基本的代码结构：创建连接，并进行手动事务管理。

/**
 * 先增加一个部门，再给此部门增加一个员工.
 * 要点：增加部门后如何获得生成的部门ID
 */
@Test
public void test() {
	Connection conn = null;
	try {
		conn = DBUtil.getConnection();
		conn.setAutoCommit(false);
		
		conn.commit();
	} catch (SQLException e) {
		e.printStackTrace();
		DBUtil.rollback(conn);
	} finally {
		DBUtil.close(conn);
	}
}

模拟参数：

在写具体的业务逻辑之前，还需要准备好业务所需的参数，而正式项目中的参数一定是通过页面传过来的，这里对数据进行模拟，其中部门id由序列自动生成，不用模拟。假设页面传入的其他数据，部门名称是 测试部，部门地址是 杭州，员工是 八戒，job是 取经，mgr是 0，还有入职日期，工资，提成等数据：

	//假设页面传入的部门数据是
	String dname = "测试部";
	String loc = "杭州";
	//假设页面传入的员工数据是
	String ename = "";
	String job = "";
	int mgr = 0;
	Date hiredate = new Date(System.currentTimeMillis());
	double sal = 5000.0;
	double comm = 1000;

业务实现

代码结构与参数准备完以后，要先插入部门，再插入员工，进行多次插入，但这个多次插入与批量插入是不同的，不能通过批量的方式操作，因为增加部门和增加员工的sql结构是不一样的，增加部门传入的是部门的参数，增加员工传入的是员工的参数，让PS对象又记部门参数，又记员工参数，混在一起它就乱了，而批量增加有个前提，必须是一个sql，PS对象只能针对一个SQL，记录相似的数据，批量发送过去，如果是两个sql或者更多，都是不允许的。那既然不能批量就单个执行，就两条数据只执行两次：

1. 增加部门

• 编写sql：部门表一共是3列，其中部门的id用序列生成，所以只需要两个条件，部门的名字和部门地址。sql实现：

  String sql = “insert into depts values(depts_seq.nextval,?,?)”;

• 发送sql：要执行sql，首先需要创建PS对象，调用其中的preparedStatement方法，并将sql传入即可：

  PreparedStatement，PreparedStatement ps = conn.preparedStatement(sql);，

  但其实，这个preparedStatement，还有很多重载的方法，其中有一个方法，它是带有两个参数的，如果需要这个对象返回自动生成的ID，就需要加第二个参数，而第二个参数是一个String数组，数组里面写的是字段名，你希望这个对象给你返回哪几个字段，就把它的字段名写上，它就会按照这个顺序给你返回。之所以第二个参数被设成一个数组，是因为在设计，创建表的时候，那么大部分的表，它都是一个主键，但也有的表是联合主键，可能是多个字段合在一起形成一个主键，这叫联合主键。那有可能联合主键都是自动生成的，所以这个主键未必是一个字段，是考虑更多的情况，第二个参数被设成数组。但是对我们来讲，绝大部分的都是一个主键，数组里一般都是写一个主键的ID，即主键的字段名deptno。这样，当我们sql执行完以后，就可以去跟这个PS对象，去要它所记录的字段的值。

  PreparedStatement ps = conn.prepareStatement(sql, new String[] {“deptno”});

• 执行sql：执行sql之前，首先需要给sql中的两个条件参数赋值，并调用PS对象的executeUpdate()方法执行这个sql，执行完以后，这个PS对象就已经记录下来自动生成的主键ID字段的值。

  ps.setString(1, dname);
  ps.setString(2, loc);
  ps.executeUpdate();

• 获取主键：

  • sql执行完，PS对象就已经获记录了这个自动生成的主键ID，那想要获取到这个主键，需要调用PS对象的方法，getGenerateKeys()，是个复数，因为有可能主键是联合主键，是多个字段，目前是一个。所以它给我们返回一个结果集，这个结果集当中只有一条数据。我们只执行了一个insert，一个insert，它只能返回一行数据，所以结果集中只有一行数据，那这一行数据中有几列数据，这是由我们告诉它返回几个字段决定的，数组里写几个字段，它就返回几列。现在只有一个字段deptno，所以就返回一列。所以当前的场景，它的结果集中只包含了一行一列数据，就一行，那想从中取值也没有必要遍历，直接rs.next();，指针移动下一行就可以了，next一次以后，这个结果集的指针就指向唯一的这一行，就从这一行中取出这一列，int deptno = rs.getInt(1);，
  • 这里需要注意的是，一般从结果集中取值，在get的时候，可以写这个字段的序号，也可以写字段的名字，在执行DQL时，建议都写字段名，但是这里必须写字段的序号，因为我们调用getGenerateKeys()方法，所得到的结果集，它里面没有字段名，只有这个序号，这里是比较特殊的。所以， rs.getInt(1);，这里的key是整数，代表第一列。那为什么这种场景下的结果集，只能通过字段的序号获取值，不可以写字段名，这个估计是底层，它在实现这个逻辑的时候，有什么难言之隐，不好实现，所以就有这样一个规则，因为底层的实现，不是由我们去实现的，我们也不知道有哪些困难，确实，如果想自己写一个类似于jdbc这样的，一个东西的话，你想实现这个jdbc接口的话，其实挺麻烦的，很难的，它肯定有很多各种各样的困难，估计这个情况下，有解决不了的问题，所以，这里只能是这样特意记一下。

• 代码示例：经过以上步骤，就可以得到部门ID，在插入员工数据时，就可以以此除员工的部门ID作为条件插入员工表。

  //从PS中获取生成的主键
  //结果集当中包含1行1列
  ResultSet rs = ps.getGeneratedKeys();
  rs.next();
  //这种场景下的结果，只能通过字段的序号获取值。
  int deptno = rs.getInt(1);

2. 增加员工：

• 编写sql：员工表里，一共有8个字段，有6个是模拟从页面获取的，还有2个，一个是员工id，自动生成，另外一个是部门id，是我们插入部门时才知道的。因为之前增加部门是的变量叫sql，这里有所区别，改一下叫sql2：

  String sql2 = “insert into emps values(emps_seq.nextval,?,?,?,?,?,?,?)”;

• 发送执行sql：想要执行这个sql2，还得创建一个PS对象，调用其中的preparedStatement方法发送给数据库编译成计划，然后还需要给sql2中的每一个问号赋值，最后通过PS对象中的executeUpdate()方法执行sql2。
• 代码示例：

  //增加一个员工
  String sql2 = “insert into emps values(emps_seq.nextval,?,?,?,?,?,?,?)”;
  PreparedStatement ps2 = conn.prepareStatement(sql2);
  ps2.setString(1, ename);
  ps2.setString(2, job);
  ps2.setInt(3, mgr);
  ps2.setDate(4, hiredate);
  ps2.setDouble(5, sal);
  ps2.setDouble(6, comm);
  ps2.setInt(7, deptno);
  ps2.executeUpdate();

完整代码：

/**
 * 先增加一个部门，再给此部门增加一个员工.
 * 要点：增加部门后如何获得生成的部门ID
 */
@Test
public void test() {
	//假设页面传入的部门数据是
	String dname = "测试部";
	String loc = "杭州";
	//假设页面传入的员工数据是
	String ename = "";
	String job = "";
	int mgr = 0;
	Date hiredate = new Date(System.currentTimeMillis());
	double sal = 5000.0;
	double comm = 1000;
	
	Connection conn = null;
	try {
		conn = DBUtil.getConnection();
		conn.setAutoCommit(false);
		
		//增加一个部门
		String sql = "insert into depts values(depts_seq.nextval,?,?)";
		//参数2是一个数组，用来告诉PS，需要它返回哪些字段。
		PreparedStatement ps = conn.prepareStatement(sql, new String[] {"deptno"});
		ps.setString(1, dname);
		ps.setString(2, loc);
		ps.executeUpdate();
		
		//从PS中获取生成的主键
		//结果集当中包含1行1列
		ResultSet rs = ps.getGeneratedKeys();
		rs.next();
		//这种场景下的结果，只能通过字段的序号获取值。
		int deptno = rs.getInt(1);
		
		//增加一个员工
		String sql2 = "insert into emps values(emps_seq.nextval,?,?,?,?,?,?,?)";
		PreparedStatement ps2 = conn.prepareStatement(sql2);
		ps2.setString(1, ename);
		ps2.setString(2, job);
		ps2.setInt(3, mgr);
		ps2.setDate(4, hiredate);
		ps2.setDouble(5, sal);
		ps2.setDouble(6, comm);
		ps2.setInt(7, deptno);
		ps2.executeUpdate();
		
		conn.commit();
	} catch (SQLException e) {
		e.printStackTrace();
		DBUtil.rollback(conn);
	} finally {
		DBUtil.close(conn);
	}
}

4.验证

执行程序，查询数据库是否插入成功：

select * from depts;
select * from emps order by empno desc;

先查部门，部门里面多了一个测试部，id为4，然后，再查员工，员工里多了一条数据，id是129，ename是八戒，部门id为4。

5.总结

参考文献(References)

文中如有侵权行为，请联系me。。。。。。。。。。。。。
文中的错误，理解不到位的地方在所难免，也请指教！在成长过程中，也将继续不断完善，不作为专业文章。不喜勿喷。