-3
\$\begingroup\$

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:I am allowed to use while loop but not in a nested way.And for your kind information the two tables 'snomed_conceptdata' and 'snomed_descriptiondata' has more than 1300000 rows of data present in them .

\$\endgroup\$

closed as unclear what you're asking by Heslacher, Stephen Rauch, Ludisposed, Martin R, t3chb0t Aug 20 '18 at 12:06

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    \$\begingroup\$ Your question is missing context about what your code should do. In its current form its off-topic. \$\endgroup\$ – Heslacher Aug 20 '18 at 11:18
  • \$\begingroup\$ The current question title states your concerns about the code. The site standard is for the title to simply state the task accomplished by the code. Please see How do I ask a good question? for examples, and revise the title accordingly. \$\endgroup\$ – Martin R Aug 20 '18 at 11:46
  • \$\begingroup\$ The while loops are the last thing I would worry about in your code. \$\endgroup\$ – t3chb0t Aug 20 '18 at 12:09
1
\$\begingroup\$

Do you have to use Java? In SQL:

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

This uses no while loops if that's the criteria.

In general, you want to do as few SQL/Java transfers of data as possible. Your original code did three. This does none.

The key observation is that if you want to find every entry in a table that corresponds with an entry in another table, you should do a join rather than two separate queries. This is where your nested while loop was. You were joining in your Java rather than in SQL.

And truly, you don't need to bring any of the data back to Java. It's quite possible to insert from a select in SQL.

If this is too much data, you might want to LIMIT how many rows you do at once. So do ten thousand at a time or whatever.

Test the SELECT before you do the INSERT to make sure that it returns the right data.

\$\endgroup\$

Not the answer you're looking for? Browse other questions tagged or ask your own question.