java 并发插入数据到oracle 博客分类: javaoracle java并发多线程迁移数据
程序员文章站
2024-03-22 17:10:58
...
各位技术大牛,请教java 多线程问题
由于项目需要,需要从oracle中dept将表记录迁移到dept2中,dept和dept2表结构一样。
设想:
查询:
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from dept");
通过 rs.last();得到记录的总个数,然后通过这个总个数,多线程实现迁移所有的记录。
插入:
PreparedStatement pstmt = null;
String psql = "insert into dept2 values(?,?,?)";
pstmt = conn.prepareStatement(psql);
其中的index为rs结果集的索引值,当index=1,表示rs.next()的第一个值
rs.absolute(index);
pstmt.setInt(1, rs.getInt(1));
pstmt.setString(2, rs.getString(2));
pstmt.setString(3, rs.getString(3));
pstmt.addBatch();
本人对多线程不是很了解,多次尝试,没能实现多线程迁移数据,希望有高手指点。谢谢!
public class ConcurrentTestPreparedStmt { public static int totalRowNum; public static int index = 1; public static boolean isFinish; public static void main(String[] args) throws Exception { Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@192.168.5.201:1521:orcl"; String user = "scott"; String password = "tiger"; String sql = "select * from dept"; Connection conn = DriverManager.getConnection(url, user, password); /** * 读取 */ Statement stmt = conn.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery(sql); /** * 写入 * 使用批处理 */ PreparedStatement pstmt = null; String psql = "insert into testpstmt values(?,?,?)"; pstmt = conn.prepareStatement(psql); //rs.next 只要为true,说明表中记录数 rs.last(); totalRowNum = rs.getRow(); int i = 0; //问题所在,当 i<2时,表示两个线程,执行时,往目的表中插入了同样一行记录。 while (i < 1 && !isFinish) { TaskThread t = new ConcurrentTestPreparedStmt().new TaskThread( pstmt, rs); new Thread(t).start(); i++; } //如果没完成,则主线程等待 while (!isFinish) { Thread.sleep(500); } pstmt.executeBatch(); conn.commit(); System.out.println("SUCCESS"); } public synchronized int incrementIndex() { return index++; } public synchronized int getExecuteNum() { return index; } /** * 并发迁移类 * @author ttan */ class TaskThread implements Runnable { PreparedStatement pstmt = null; ResultSet rs = null; public TaskThread(PreparedStatement pstmt, ResultSet rs) { this.rs = rs; this.pstmt = pstmt; } @Override public void run() { int index = incrementIndex(); //指定到具体的行数 try { while (index <= totalRowNum) { System.out.println("index = " + index); rs.absolute(index); pstmt.setInt(1, rs.getInt(1)); pstmt.setString(2, rs.getString(2)); pstmt.setString(3, rs.getString(3)); pstmt.addBatch(); index = incrementIndex(); } if (getExecuteNum() > totalRowNum) { isFinish = true; return; } } catch (SQLException e) { e.printStackTrace(); } } } }