JDBC代码实现之第八版:获取自动生成的序列ID
前言
由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.具体业务逻辑
- 先增加用户数据:获取账号密码等数据存入user表。
- 再增加用户的英雄数据:通过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或者更多,都是不允许的。那既然不能批量就单个执行,就两条数据只执行两次:
- 增加部门
-
编写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接口的话,其实挺麻烦的,很难的,它肯定有很多各种各样的困难,估计这个情况下,有解决不了的问题,所以,这里只能是这样特意记一下。
- sql执行完,PS对象就已经获记录了这个自动生成的主键ID,那想要获取到这个主键,需要调用PS对象的方法,getGenerateKeys(),是个复数,因为有可能主键是联合主键,是多个字段,目前是一个。所以它给我们返回一个结果集,这个结果集当中只有一条数据。我们只执行了一个insert,一个insert,它只能返回一行数据,所以结果集中只有一行数据,那这一行数据中有几列数据,这是由我们告诉它返回几个字段决定的,数组里写几个字段,它就返回几列。现在只有一个字段deptno,所以就返回一列。所以当前的场景,它的结果集中只包含了一行一列数据,就一行,那想从中取值也没有必要遍历,直接rs.next();,指针移动下一行就可以了,next一次以后,这个结果集的指针就指向唯一的这一行,就从这一行中取出这一列,
-
代码示例:经过以上步骤,就可以得到部门ID,在插入员工数据时,就可以以此除员工的部门ID作为条件插入员工表。
//从PS中获取生成的主键
//结果集当中包含1行1列
ResultSet rs = ps.getGeneratedKeys();
rs.next();
//这种场景下的结果,只能通过字段的序号获取值。
int deptno = rs.getInt(1);
- 增加员工:
- 编写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。。。。。。。。。。。。。
文中的错误,理解不到位的地方在所难免,也请指教!在成长过程中,也将继续不断完善,不作为专业文章。不喜勿喷。