java how to parse selected sql row to get values to xml

Question

I need to get table data to make xml file and sign it. XML file must be like:

<invoice credate="" invtype="" turnoverdate="">
   <customers>
       <customer custname="" custadr="" custbin="">
       </customer>
   </customers>
   <products curr="" totalExciseAmount="0" totalNdsAmount="0" totalPriceWithTax="200" totalPriceWithoutTax="200" totalTurnoverSize="200">
                <product descr="aa" ndsAmount="0" priceWithTax="100" priceWithoutTax="100" turnoverSize="100"/>
                <product descr="bb" ndsAmount="0" priceWithTax="100" priceWithoutTax="100" turnoverSize="100"/>
   </products>
   <sellers>
       <seller sellname="" selladr="" sellbin="">
       </seller>
   </sellers>
</invoice>

So i have a problem with selecting data from table.

As u can see invoice includes information about customer, seller, products, products' details.

One invoice can include one product or more than one.

For example, 3 invoices, selected rows from table:

1row: INVTYPE1 CREDATE1 TRNVRDATE1 CUSTNAME1 CUSTADR1 CUSTBIN1 SELLNAME1 SELLADR1 SELLBIN1 PRODCURR1 proddescr1 prodndsam1 prodprWtax1 prodprWOtax1 prodtrnvrsize1
2row: INVTYPE1 CREDATE1 TRNVRDATE1 CUSTNAME1 CUSTADR1 CUSTBIN1 SELLNAME1 SELLADR1 SELLBIN1 PRODCURR1 proddescr2 prodndsam2 prodprWtax2 prodprWOtax2 prodtrnvrsize2
3row: INVTYPE2 CREDATE2 TRNVRDATE2 CUSTNAME2 CUSTADR2 CUSTBIN2 SELLNAME2 SELLADR2 SELLBIN2 PRODCURR2 proddescr1 prodndsam1 prodprWtax1 prodprWOtax1 prodtrnvrsize1
4row: INVTYPE3 CREDATE3 TRNVRDATE3 CUSTNAME3 CUSTADR3 CUSTBIN3 SELLNAME3 SELLADR3 SELLBIN3 PRODCURR3 proddescr1 prodndsam1 prodprWtax1 prodprWOtax1 prodtrnvrsize1

When i use this code to set precreated arrays' values:

int i=0;
while (rows.next()) {
System.out.println("Invoice "+(i+1)+" :");
crdate[i] = rows.getDate("credate");
invtype[i] = rows.getString("invtype");
turnoverdate[i] = rows.getDate("turnoverdate");
custname[i] = rows.getString("custname");
custadr[i] = rows.getString("custadr");
custbin[i] = rows.getString("custbin");
curr[i] = rows.getString("curr");
ndsAmount[i] = rows.getFloat("ndsAmount");
priceWithTax[i] = rows.getFloat("priceWithTax");
priceWithoutTax[i] = rows.getFloat("priceWithoutTax");
turnoverSize[i] = rows.getFloat("turnoverSize");
sellname[i] = rows.getString("sellname");
selladr[i] = rows.getString("selladr");
sellbin[i] = rows.getString("sellbin");
i++;
}

I have output like this:

Invoice 1:

INVTYPE1 CREDATE1 TRNVRDATE1 CUSTNAME1 CUSTADR1 CUSTBIN1 SELLNAME1 SELLADR1 SELLBIN1 PRODCURR1 proddescr1 prodndsam1 prodprWtax1 prodprWOtax1 prodtrnvrsize1

Invoice 2:

INVTYPE1 CREDATE1 TRNVRDATE1 CUSTNAME1 CUSTADR1 CUSTBIN1 SELLNAME1 SELLADR1 SELLBIN1 PRODCURR1 proddescr2 prodndsam2 prodprWtax2 prodprWOtax2 prodtrnvrsize2

Invoice 3:

INVTYPE2 CREDATE2 TRNVRDATE2 CUSTNAME2 CUSTADR2 CUSTBIN2 SELLNAME2 SELLADR2 SELLBIN2 PRODCURR2 proddescr1 prodndsam1 prodprWtax1 prodprWOtax1 prodtrnvrsize1

Invoice 4:

INVTYPE3 CREDATE3 TRNVRDATE3 CUSTNAME3 CUSTADR3 CUSTBIN3 SELLNAME3 SELLADR3 SELLBIN3 PRODCURR3 proddescr1 prodndsam1 prodprWtax1 prodprWOtax1 prodtrnvrsize1

But there are only 3 invoices, because 1st invoice has 2 product positions.So it must be like:

Invoice 1:

INVTYPE1 CREDATE1 TRNVRDATE1 CUSTNAME1 CUSTADR1 CUSTBIN1 SELLNAME1 SELLADR1 SELLBIN1 PRODCURR1 proddescr1 proddescr2 prodndsam1 prodndsam2 prodprWtax1 prodprWtax2 prodprWOtax1 prodprWOtax2 prodtrnvrsize1 prodtrnvrsize2

Invoice 2:

INVTYPE2 CREDATE2 TRNVRDATE2 CUSTNAME2 CUSTADR2 CUSTBIN2 SELLNAME2 SELLADR2 SELLBIN2 PRODCURR2 proddescr1 prodndsam1 prodprWtax1 prodprWOtax1 prodtrnvrsize1

Invoice 3:

INVTYPE3 CREDATE3 TRNVRDATE3 CUSTNAME3 CUSTADR3 CUSTBIN3 SELLNAME3 SELLADR3 SELLBIN3 PRODCURR3 proddescr1 prodndsam1 prodprWtax1 prodprWOtax1 prodtrnvrsize1

So can u help me with this problem. How to get multiple product position for 1 invoice? If 1 invoice has 1 product position its ok, xml file will be created normally. But if there are 2 or more product positions of 1 invoice , xml file will be created like there are 2 or more invoices because of 2 or more positions. Help me plz.

P.S. I cant use "db data to xml" converting libs, because i must add some information to xml which are not taken from db.

@A.Agarwal SQL Query: select o.invtype, o.curr, o.crdate, o.trnvrdate, o.custname, o.custadr, o.custbin, o.sellname, o.selladr, o.sellbin, d.descr, d.ndsamount, d.prWTax, d.prWOTax, d.trnvrsize from inv o, inv_dtl d where o.dep_id = d.dep_id and o.id= d.id — NovaCenturion
– NovaCenturion, Commented Jun 4, 2014 at 10:15

A. Agarwal · Accepted Answer · 2014-06-04 10:24:24Z

1

Modify your query to include o.id in select

 select o.id, o.invtype, o.curr, o.crdate, o.trnvrdate, o.custname, o.custadr, 
 o.custbin, o.sellname, o.selladr, o.sellbin, d.descr, d.ndsamount, d.prWTax, 
 d.prWOTax, d.trnvrsize from inv o, inv_dtl d 
 where o.dep_id = d.dep_id and o.id= d.id

And then modify your loop to deduplicate invoice using o.id while keeping the products.

long tempId = 0;
while (rows.next()) {
  if (tempId == rows.getLong("id")) {
    // add productN
  } else {
    // add invoice + product1
    tempId = rows.getLong("id");
  }
}

answered Jun 4, 2014 at 10:24

A. Agarwal

4154 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

java how to parse selected sql row to get values to xml

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related