之外执行它
我在Java程序中使用了Select命令,并将其值存储在结果集中。现在,在Resultset中循环时,我想使用一个选择命令,该命令将选择结果集的前5行并插入其他表。第二次,它应选择下一5行并插入表中。这是第三次。
Statement s = connection.createStatement();
s.executeQuery("Select * from table1");
ResultSet res = s.getResultSet();
while(res.next()){
// here i want to select the first 5 lines of the result set and insert in the second table
}
Statement s = connection.createStatement();
s.executeQuery("Select * from table1");
ResultSet res = s.getResultSet();
while(res.next()){
// here i want to select the first 5 lines of the result set and insert in the second table
while(res.next() && (res.getRow()%5) !=0){
//select from this table
//call insert method(what selected)
}
}
我建议使用 LIMIT
和使用准备安排更改查询。类似:
SELECT * FROM table1 LIMIT ?,?
这有几个优点:
- 您并没有一次拍摄所有东西 - 如果您在桌子中要处理很多行,有时可能会带来性能。
- 您可以在每个批次中更改要获取的元素数量
因此,您的代码看起来像这样:
PreparedStatement ps = null;
ResultSet rs = null;
final int FETCH_LIMIT = 5; //number of elements to fetch per batch
final int BATCH_LIMIT = 3; //number of batches you would want
int currentRows = 0;
try{
ps = connection.prepareStatement("SELECT * FROM table1 LIMIT ?,?");
for(int currentBatch = 0; currentBatch < BATCH_LIMIT; currentBatch++){
ps.clearParameters();
ps.setInt(1, currentRows);
ps.setInt(2, currentRows + FETCH_LIMIT);
try{
rs = ps.executeQuery();
while(rs.next()){
// do your work
}
}catch(Exception exe){
//manage exception
}finally{
//manage resultset
}
currentRows += FETCH_LIMIT;
}
}catch(Exception exe){
//Handle your exception
}
finally{
//Manage your resources
}
请添加一个falg并使用它
int i=0;
while(res.next() && i< 5){
//select from this table
//call insert method(what selected)
i++;
}
在While循环内部动态创建另一个插入查询,并在while while loop