java - 如何加快以下 JDBC 插入/导入过程？

Question

所以基本上我有 2 个表调用snomed_conceptdata（454772 行）和
'snomed_descriptiondata' 调用（1383698 行）。根据这段代码，目前我正在尝试将记录插入一个名为 的表中snomedinfo_data，该表正在工作，但插入/导入过程发生在哪里慢慢地。我怀疑这是因为嵌套的 while 循环在处理时花费了太长时间。

是否有其他方法可以做到这一点，以便导入/插入过程可以快速发生。

包 Snomed.Snomed;

import java.sql.PreparedStatement;
import java.sql.ResultSet;

import catalog.Root;

public class Snomedinfo {
    public void snomedinfoinsert()
    {
    Root oRoot = null;
    ResultSet oRsSelect = null;
    PreparedStatement oPrStmt = null;
    PreparedStatement oPrStmt2 = null;
    PreparedStatement oPrStmtSelect = null;
    String strSql = null;
    String strSql2 = null;
    String snomedcode=null;
    ResultSet oRs = null;
    String refid = null;
    String id = null;
    String effectivetime = null;
    String active = null;
    String moduleid = null;
    String conceptid = null;
    String languagecode = null;
    String typeid = null;
    String term = null;
    String caseSignificanceid = null;
    try{
    oRoot = Root.createDbConnection(null);

    strSql = "SELECT  id FROM snomed_conceptdata WHERE active=1 ";
    oPrStmt2 = oRoot.con.prepareStatement(strSql);
    oRsSelect = oPrStmt2.executeQuery();
    while (oRsSelect.next()) {
        snomedcode = Root.TrimString(oRsSelect.getString("id"));

        strSql2 = "SELECT  * FROM snomed_descriptiondata WHERE conceptid =? AND active=1  ";
        oPrStmtSelect = oRoot.con.prepareStatement(strSql2);
        oPrStmtSelect.setString(1,snomedcode);
        oRs =oPrStmtSelect.executeQuery();
        while (oRs.next()) {
            refid = Root.TrimString(oRs.getString("refid")); 
            id = Root.TrimString(oRs.getString("id"));
            effectivetime = Root.TrimString(oRs.getString("effectivetime"));
            active = Root.TrimString(oRs.getString("active"));
            moduleid = Root.TrimString(oRs.getString("moduleid"));
            conceptid = Root.TrimString(oRs.getString("conceptid"));
            languagecode = Root.TrimString(oRs.getString("languagecode"));
            typeid = Root.TrimString(oRs.getString("typeid"));
            term = Root.TrimString(oRs.getString("term"));
            caseSignificanceid = Root.TrimString(oRs.getString("caseSignificanceid"));

            String sql = "INSERT INTO snomedinfo_data (refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid) VALUES( ?, ?, ?,?,?,?,?,?,?,?)";
            oPrStmt = oRoot.con.prepareStatement(sql);
            oPrStmt.setString(1, refid);
            oPrStmt.setString(2, id);
            oPrStmt.setString(3, effectivetime);
            oPrStmt.setString(4, active);
            oPrStmt.setString(5, moduleid);
            oPrStmt.setString(6, conceptid);
            oPrStmt.setString(7, languagecode);
            oPrStmt.setString(8, typeid );
            oPrStmt.setString(9, term);
            oPrStmt.setString(10, caseSignificanceid);
             oPrStmt.executeUpdate();

        }

    }
    System.out.println("done");
    }

    catch (Exception e) {
        e.printStackTrace();

    }

    finally {

        oRsSelect = Root.EcwCloseResultSet(oRsSelect);
        oRs = Root.EcwCloseResultSet(oRs);
        oPrStmt = Root.EcwClosePreparedStatement(oPrStmt);
        oPrStmt = Root.EcwClosePreparedStatement(oPrStmt2);
        oPrStmt = Root.EcwClosePreparedStatement(oPrStmtSelect);
        oRoot = Root.closeDbConnection(null, oRoot);
    }
}
    public static void main(String args[] ) throws Exception 
      { 


      Snomedinfo a = new Snomedinfo();
      a .snomedinfoinsert();

      }


}

标签： javamysqljdbc

Answer 1

您可以使用SQL 插入到选择查询中

INSERT INTO snomedinfo_data 
SELECT refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid FROM snomed_descriptiondata 
WHERE conceptid =? AND active=1  

它将合并您的第二个和第三个查询，这样您就可以避免不必要的迭代。