• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Nested While Loops

I am building an xml file busing java/jsp.  The data is pulled from a database.  The database has two tables with a standard foriegn key relationship.  

Table A
PK id
data column1
data column2

Table B
PK id2
PK id  (FK) to table A  (this is not unique in this table, of course)
data column3
data column4

So the id column from Table A can have multilpe values from table B.

I want my xml document to have something along the lines of this structure, excluding xml tags.

<tag1 ...   <%=result.getString(data column1)%> />
<tag2...   <%=result.getString(data column 2)%> />
<tag3... <%=result.getString(data column 3)%> />
<tag4... <%=result.getString(data column 4)%> />
<tag3... <%=result.getString(data column 3)%> />
<tag4... <%=result.getString(data column 4)%> />

how do I set this up? In my current code, it repeats tags for data column1 and 2 for every column3 and 4.  Is there any way to do a nested while loop or something similar


<%@page contentType="text/xml" language="java" import="java.sql.*, java.io.*, java.util.*"%>
<?xml version="1.0" encoding="UTF-8" ?>
<batch>
    <controls>
<%
.......
    Connection conn = DriverManager.getConnection(url, user, password);
    java.sql.Statement stmt = conn.createStatement();
ResultSet result = stmt.executeQuery("SELECT * FROM Table_A ");
    
    while (result.next())
    {
%>
               <control local_id= "<%=result.getString("data_column1")%>">
                <link type= "in" ref= "<%=result.getString("data_column2")%>" />
<%
        String controlid = result.getString("data_column1");
        ResultSet result2 = stmt.executeQuery("SELECT * FROM psc_atpid_evidence WHERE CONTROL_ID = '" + controlid +"'");
        while (result2.next())
        {
%>
                <attr name= "mref" value = "<%=result2.getString("data_column3")%>"  />
                <attr name= "Method" value = "<%=result2.getString("data_column4")%>" />
           
<%
}
%>
 </control>
<%
}
conn.close();
%>
    </controls>
</batch>

Open in new window

0
MichelleLacy
Asked:
MichelleLacy
  • 10
  • 5
  • 5
1 Solution
 
CEHJCommented:
You need to do a join query (see below) and then iterate the result set, adding a child node where necessary
select 
A.column1,
A.column2,
B.column3,
B.column4
from
TableA A,
TableB B
where
B.id = A.id

Open in new window

0
 
MichelleLacyAuthor Commented:
thank you for getting back to me.   I have tried that, and as I mentioned the output looks like this...
<tag1 ...   <%=result.getString(data column1)%> />
<tag2...   <%=result.getString(data column 2)%> />
<tag3... <%=result.getString(data column 3a)%> />
<tag4... <%=result.getString(data column 4a)%> />

<tag1 ...   <%=result.getString(data column1)%> /> repeated
<tag2...   <%=result.getString(data column 2)%> /> repeated
<tag3... <%=result.getString(data column 3b)%> />
<tag4... <%=result.getString(data column 4b)%> />
what I want is ....
 <tag1 ...   <%=result.getString(data column1)%> />
<tag2...   <%=result.getString(data column 2)%> />
<tag3... <%=result.getString(data column 3a)%> />
<tag4... <%=result.getString(data column 4a)%> />
<tag3... <%=result.getString(data column 3b)%> />
<tag4... <%=result.getString(data column 4b)%> />


 
0
 
CEHJCommented:
In the join the key column will be repeated, but with different child node ids. You need to do something like

if (key >= last) {
      if (last != 0) {
            closeParentNode();
      }
      last = key;
      openParentNode();
      // iterate, extract and append child nodes
      
}
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
MichelleLacyAuthor Commented:
can you show me how your example syntax would fit into my code...

 ResultSet result = stmt.executeQuery("SELECT * FROM Table_A a, Table_B b WHERE a.id = b.id");
    while (result.next())
    {
%>
            <control local_id= "<%=result.getString("ID")%>">
                <link type= "in" ref= "<%=result.getString("DATA_COLUMN1")%>" />
                <link type= "out" ref= "<%=result.getString("DATA_COLUMN2")%>" />
                <attr name= "Name" value = "<%=result.getString("DATA_COLUMN3")%>" />
                <attr name= "Method" value = "<%=result.getString("DATA_COLUMN4")%>"  />
            </control>
<%
}
conn.close();
%>
 
0
 
objectsCommented:
can you post what the data in your table looks like. Think we have some code here that does similar

0
 
objectsCommented:
you sql appears incorrect for what you want. show me the sample data that generates the output above that u want and I'll sort out the query you need.

0
 
MichelleLacyAuthor Commented:
the output data example is attached.  Thanks
 
0
 
MichelleLacyAuthor Commented:
oops forgot to attach the file
experts-exchange.xls
0
 
CEHJCommented:
There's something a little odd about the data. e.g. values in columns DataColumn1 and DataColumn2 look suspiciously like values of the same type of entity, yet they are in the different tables. Values in DataColumn3 look like they should be columns
0
 
MichelleLacyAuthor Commented:
There is a typo in that document, DataColumn2 should say, from TableA, not B
Also DataColumn 3 is a description of the values in 4, they do belong together. I do not want to create a crosstabular table using Data Column3, it would not be very efficient for storing the data.
0
 
objectsCommented:
can u also post the expected output from that data

0
 
MichelleLacyAuthor Commented:
- <control local_id="PID02530">
  <link type="in" ref="N00766" />
  <link type="out" ref="N07540" />  
  <attr name="Pairwise_value" value="1.42" />
  <attr name="Profiles" value="1016.54" />
  </control>
- <control local_id="PID02531">
  <link type="in" ref="N00766" />
  <link type="out" ref="N080250" />  
  <attr name="Overall_score" value="1533.26" />
  <attr name="Pairwise_value" value="1.5" />
  <attr name="Profiles" value="1016.54" />  
  </control>
..... etc
0
 
CEHJCommented:
Also, can you make sure you post the actual query (made in your db environment) that get you the data you want?
0
 
MichelleLacyAuthor Commented:
SELECT * FROM Table_A a, Table_B b WHERE a.ID = b.ID
0
 
objectsCommented:
that doesn't look right will run it past our db guy and see what he says

0
 
MichelleLacyAuthor Commented:
This is a very simply query.  I am puzzled....
0
 
MichelleLacyAuthor Commented:
the lower case a and b are aliases for the tables in the FROM clause.  if I were to write it out, it would look like
 
SELECT * FROM Table_A , Table_B  WHERE Table_A.ID = Table_B.ID
0
 
CEHJCommented:
>>There is a typo in that document, DataColumn2 should say, from TableA, not B

If that's the case and values for the same entity *are* appearing from the same table, then your query cannot be as you have stated
0
 
objectsCommented:
we've looked at this and a nested query looks like the best way to go. Trying to do it in a single query would just overcomlicate it with any real gains.  I've outlined the required code below. Let me know if you have any problems or questions


rs = conn1.executeQuery("select PK, DataColumn1, DataColumn2 from TableA");
while (rs.next()) {
   Object pk = rs.getObject("PK");
   Object datacolumn1 = rs.getObject("DataColumn1");
   Object datacolumn1 = rs.getObject("DataColumn2");

   <control local_id="<%= pk %>">
     <link type="in" ref="<%= datacolumn1 %>" />
     <link type="out" ref="<%= datacolumn2 %>" />  

   PreparedStatement ps = conn.prepareStement("select DataColumn3, DataCOlumn4 from TableB where ID=?");
   ps.setObject(1, pk);
   ResultSet rs2 = ps.executeQuery();
   while (rs2.next()) {
      Object datacolumn3 = rs2.getObject("DataColumn3");
      Object datacolumn4 = rs2.getObject("DataColumn4");

 <attr name="<%= datacolumn3 %>" value="<%= datacolumn4 %>" />
     
   }
   rs2.close();

</control>


}


0
 
MichelleLacyAuthor Commented:
Thank you very much for persistence and hardwork.  The solution worked beautifully.  Nice work!!!
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 10
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now