有没有使用嵌套while循环实现此程序的不同方法？ [英] Is there a different way of implementing this program without using nested while loop?

问题描述

目前我的程序工作正常，但是我如何在不使用嵌套while循环的情况下实现这个程序（一个while循环在另一个循环中）。这是一种孩子的编程方式，我的办公室同事不希望我写像这样的代码。所以有不同的方式来实现这个程序或实现上述代码中看到的while循环的正确方法??

Presently my program is working properly, but how do i implement this program without using nested while loop(one while loop within another while loop).This is a kids way of programming and my office colleague doesn't want me to write code like this.So is there a different way for implementing this program or a proper way of implementing the while loops seen in the above code??

这是我的当前代码：

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 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();
            String strSql2 = "SELECT  * FROM snomed_descriptiondata WHERE conceptid =? AND active=1  ";
            oPrStmtSelect = oRoot.con.prepareStatement(strSql2);
            String sql = "INSERT INTO snomedinfo_data (refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid) VALUES( ?, ?, ?,?,?,?,?,?,?,?)";
            oPrStmt = oRoot.con.prepareStatement(sql);
            while (oRsSelect.next()) //first while loop
            {
                snomedcode = Root.TrimString(oRsSelect.getString("id"));

                oPrStmtSelect.setString(1, snomedcode);

                oRs = oPrStmtSelect.executeQuery();

                while (oRs.next()) //second while loop
                {
                    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"));


                    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();

    }


}

注意：导入过程也正常，但有点慢。我已经尝试使用conceptid列的索引。

NOTE:Also the import process is working but is a little slow.I have already tried using an index for the conceptid column .

推荐答案

由于 INSERT 语句中使用的所有数据都来自您的'SELECT'语句，因此在您的Java应用程序和数据库之间进行额外的往返是没有意义的。在一个SQL语句中执行所有操作将为您提供最佳性能。

Since all data used in the INSERT statement comes from your 'SELECT' statements there is no point in taking the extra round-trip between your java app and the database. Executing everything in one SQL statement will give you the best performance.

您的SQL语句应该是这样的

Your SQL statement should be like this

INSERT INTO snomedinfo_data (refid,id,effectivetime,active,moduleid,conceptid,languagecode,typeid,term,caseSignificanceid)
SELECT d.refid, d.id, d.effectivetime, d.active, d.moduleid, d.conceptid, d.languagecode, d.typeid, d.term, d.caseSignificanceid
FROM snomed_descriptiondata d
JOIN snomed_conceptdata c ON c.id = d.conceptid AND c.active = 1 AND d.active = 1

你的java代码可以归结为这个

And your java code can be boiled down to this

try {
    oRoot = Root.createDbConnection(null);
    String sql = "INSERT INTO snomedinfo_data...";
    oPrStmt = oRoot.con.prepareStatement(sql);
    oPrStmt.executeUpdate();
}

这篇关于有没有使用嵌套while循环实现此程序的不同方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！