java 并发插入数据到oracle

发布时间：2020-08-02 07:31:24 作者：qojpeojop
来源：网络阅读：625

各位技术大牛，请教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();
}
}
}
}

java企业级通用权限安全框架源码获取【下载地址】

java 并发插入数据到oracle

相关阅读