1、比如根据一条查询语句得到的结果集,循环该结果集,插入到另一个表中:
Connection con = null;
try {
//装载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
//打开连接
con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:class4","uswjx","uswjx");
} catch (Exception err) {
err.printStackTrace();
}
Statement st = null;
ResultSet rs = null;
try{
st = con.createStatement();
con.setAutoCommit(false);
for (int i = 0; i < set.length; i++) {//set为多组合套帐
int intCount = 3 ; //由于java的批处理最大支持32767条SQL语句,所以需要进行对SQL语句的数量进行控制。
目前约定 为10000条提交一次。
//修改删除条件为fdate 20090329
st.addBatch("delete from " + pub.yssSetPrefix(set[i]) + "HzJkMx where fszsh = 'H' and fjyfs = 'PT'");
String sql = "select * from uswjx where ........";
(loadConnection().createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_READ_ONLY)).executeQuery(sql);
while (rs.next()) {
String strSql = "insert into uswjx(.........) values(.........)";
st.addBatch(strSql);
intCount++;
if (intCount > 10000) {
st.executeBatch();
intCount = 0;
}
}
rs.getStatement().close();
st.executeBatch();
}
con.commit();
}catch(....){
throw new YssException("处理上海数据出错!", ex);
}finally{
if (rs1 != null)
try{
rs1.getStatement().close();
rs1 = null;
}
catch (Exception e){}
if (rs2 != null)
try{
rs2.getStatement().close();
rs2 = null;
}
catch (Exception e){}
if (rs3 != null)
try{
rs3.getStatement().close();
rs3 = null;
}
catch (Exception e){}
if (rs4 != null)
try{
rs4.getStatement().close();
rs4 = null;
}
catch (Exception e){}
if (st1 != null)
try{
st1.close();
}
catch (Exception e){}
if (st2 != null)
try{
st2.close();
}
catch (Exception e){}
try{
if (bTrans)
con.rollback();
}
catch (Exception e){}
try{
con.setAutoCommit(true);
}
catch (Exception e){}
}