Is there a different way of implementing this program without using nested while loop?

Question

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??

This IS MY CURRENT 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();

    }


}

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

Answer 1

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.

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

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

Answer 2

If i am reading your code correctly you are reading from one table and adding to another and never use the data in your application. If this is a case then best case scenario is to write a db procedure that will transform your data and execute it from your app.

Answer 3

if I am correct you are using two queries for fetching data from db. use this nested query, it will save the database connection effort and nested while loop

SELECT * FROM snomed_descriptiondata WHERE conceptid in (SELECT id FROM snomed_conceptdata WHERE active=1) AND active=1