假设场景:A与B表是一对多关系,现在多个A和多个B进行批量入库。
A的主键用某个序列JOB_SEQUENCE.nextval,B表的外键字段存A表的主键。
如果A和B在同一个事务保存里,应该怎么操作呢?
之前的做法是:
public static int newMeasurementJobId(IntHolder jobId) {
Connection conn = getConnection();
if (conn == null) {
logger.error("newMeasurementJobId:failed to get DBConnection");
return 0;
}
Statement stmt = null;
ResultSet rset = null;
try {
String sql = "select PM_JOB_SEQUENCE.nextval job_id from dual";
stmt = conn.createStatement();
rset = stmt.executeQuery(sql);
if (rset.next()) {
jobId.value = rset.getInt("job_id");
}
return 1;
} catch (SQLException e) {
return 2;
} finally {
closeDataBaseConnection(conn,stmt,rset);
}
}
然后for循环里调用newMeasurementJobId(IntHolder jobId)获取主键值,分别填充A和B表模型Bean的相应字段。
这个做法,有个问题是会大量open/close 数据库的IO,频繁操作对数据库的影响是非常大的。
若用UUID做主键是没有这个问题的,因为都是本地生成的。
那么有没有什么办法一次性获取大量的序列值呢。
有个非常绝妙的办法:
select PM_JOB_SEQUENCE.nextval job_id from (select 1 from all_objects where rownum <= 10)
其中all_objects为系统表,基本上都有几十万条数据,如果不够用,可以重复执行几次。
这样就可以一次获取10条递增的唯一序列值,且不用担心并发问题,oracle数据库已经帮你规避。
可以写个简单的测试程序,测试一下并发的场景:
假设有10个程序,同时获取同一个序列的5个值,看看是不是会有重复值呢?
Test线程类:
class Test implements Runnable {
private String name;
private List<String> list;
public Test(String name,List<String> list){
this.name = name;
this.list = list;
}
public void run() {
String sql = "select PM_JOB_SEQUENCE.nextval job_id from (select 1 from all_objects where rownum <= 5)";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
list.add(name+"_"+rs.getInt("job_id"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
rs.close();
pstmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
测试方法:
public void test() throws Exception{
List<String> list = Collections.synchronizedList(new ArrayList<String>());
for (int i = 0; i < 10; i++) {
Test test = new Test("Thread"+String.valueOf(i), list);
Thread t = new Thread(test);
t.start();
t.join();
}
for(String val : list){
System.out.println(val);
}
System.out.println("===========================");
Collections.sort(list);
for(String value : list){
System.out.println(value);
}
}
运行结果:
Thread0_62443290
Thread0_62443291
Thread0_62443292
Thread0_62443293
Thread0_62443294
Thread1_62443295
Thread1_62443296
Thread1_62443297
Thread1_62443298
Thread1_62443299
Thread2_62443300
Thread2_62443301
Thread2_62443302
Thread2_62443303
Thread2_62443304
Thread3_62443305
Thread3_62443306
Thread3_62443307
Thread3_62443308
Thread3_62443309
Thread4_62443310
Thread4_62443311
Thread4_62443312
Thread4_62443313
Thread4_62443314
Thread5_62443315
Thread5_62443316
Thread5_62443317
Thread5_62443318
Thread5_62443319
Thread6_62443320
Thread6_62443321
Thread6_62443322
Thread6_62443323
Thread6_62443324
Thread7_62443325
Thread7_62443326
Thread7_62443327
Thread7_62443328
Thread7_62443329
Thread8_62443330
Thread8_62443331
Thread8_62443332
Thread8_62443333
Thread8_62443334
Thread9_62443335
Thread9_62443336
Thread9_62443337
Thread9_62443338
Thread9_62443339
===========================
Thread0_62443290
Thread0_62443291
Thread0_62443292
Thread0_62443293
Thread0_62443294
Thread1_62443295
Thread1_62443296
Thread1_62443297
Thread1_62443298
Thread1_62443299
Thread2_62443300
Thread2_62443301
Thread2_62443302
Thread2_62443303
Thread2_62443304
Thread3_62443305
Thread3_62443306
Thread3_62443307
Thread3_62443308
Thread3_62443309
Thread4_62443310
Thread4_62443311
Thread4_62443312
Thread4_62443313
Thread4_62443314
Thread5_62443315
Thread5_62443316
Thread5_62443317
Thread5_62443318
Thread5_62443319
Thread6_62443320
Thread6_62443321
Thread6_62443322
Thread6_62443323
Thread6_62443324
Thread7_62443325
Thread7_62443326
Thread7_62443327
Thread7_62443328
Thread7_62443329
Thread8_62443330
Thread8_62443331
Thread8_62443332
Thread8_62443333
Thread8_62443334
Thread9_62443335
Thread9_62443336
Thread9_62443337
Thread9_62443338
Thread9_62443339