oracle 获取多个序列值,一次性获取多个oracle序列值问题

假设场景：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 list;

public Test(String name,List 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 list = Collections.synchronizedList(new ArrayList());

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