java - 如何在不使用 INSERT INTO SELECT 语句的情况下实现该程序以导入表?
问题描述
目前导入/插入过程运行良好。但是我不想为插入和选择编写一个查询,而是编写一个单独的查询来从“snomed_descriptiondata”表中选择,并编写一个单独的查询来插入到“snomedinfo”_data 表中。
我当前的代码:
package Snomed.Snomed;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
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;
int count = 0;
final int batchSize = 1000;
try{
oRoot = Root.createDbConnection(null);
strSql = "SELECT id FROM snomed_conceptdata WHERE active=1 ";
oPrStmt2 = oRoot.con.prepareStatement(strSql);
oRsSelect = oPrStmt2.executeQuery();
String sql = "INSERT INTO snomedinfo_data (refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid)SELECT refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid from snomed_descriptiondata WHERE conceptid =? AND active=1" ;
oPrStmtSelect = oRoot.con.prepareStatement(sql);
while (oRsSelect.next()) {
snomedcode = Root.TrimString(oRsSelect.getString("id"));
//String sql = "INSERT INTO snomedinfo_data (refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid)SELECT refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid from snomed_descriptiondata WHERE conceptid =? AND active=1" ;
//oPrStmtSelect = oRoot.con.prepareStatement(sql);
oPrStmtSelect.setString(1,snomedcode);
oPrStmtSelect.executeUpdate();
}
//oPrStmtSelect.executeBatch();
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();
}
}
注意:如何在不使用嵌套 while 循环的情况下执行此操作?有人可以为我提供符合我的程序的解决方案吗?
解决方案
IN
您可以(并且应该)在子句中包含 concept-id-selecting 查询:
INSERT INTO snomedinfo_data (refid, id, effectivetime, active, moduleid, conceptid,
languagecode, typeid, term, caseSignificanceid)
SELECT refid, id, effectivetime, active, moduleid, conceptid,
languagecode, typeid, term, caseSignificanceid
FROM snomed_descriptiondata
WHERE active = 1 AND conceptid IN
(SELECT cd.id FROM snomed_conceptdata cd WHERE cd.active = 1)
这样,您应该能够在一个语句中完成所有操作,这将比 JDBC 驱动程序逐行(又名慢速)处理相同的数据快几个数量级。
推荐阅读
- python - 在 python docker 映像上安装 simstring (SWIG)
- javascript - Facebook Graph API - 获取用户共享的帖子覆盖率
- javascript - 爬取javascript表但认为是异常请求
- regex - 排除正则表达式中特殊字符 # 和 # 之后的所有字符的匹配
- node.js - 使用 Axios POST 时未定义的结果
- spring - Hibernate Envers:如何捕获谁删除了审计表中的实体
- r - 如何在R中按列对表格进行排序?
- python - psycopg2:离线安装不起作用,导入错误
- powershell - 在powershell中格式化输出以记录文件
- python - Geodjango 使用 LayerMapping 和 OGR 导入 Shapefile