java - How to handle repeated value in jdbc while joining multiple tables -
following jdbc code fetch data multiple tables
public set<bill> fetch(long billno) { system.out.println(" billproductdetailsbusiness fetch()"); status = "success"; set st = new hashset(); con = connectionfactory.getconnection(); try { string query = "select distinct bpd.product_id,bpd.bill_no," + "bpd.qty, bpd.unit_price up,bpd.selling_price sp," + "bpd.discount_percent dp, " + "bd.*,cu.*,pr_dl.*, " + " colors.color,pmodeld.model_no, ptype.product_type, " + "sizes.size, pbrand.brand,suppd.name " + "from bill_product_details bpd " + "left join bill_details bd " + "on bd.bill_no=bpd.bill_no " + "left join product_details pr_dl " + "on bpd.product_id=pr_dl.barcode " + "left join " + "colors " + "on pr_dl.color_id=colors.color_id " + "left join " + "product_model_details pmodeld " + "on pr_dl.model_id=pmodeld.model_no_id " + "left join " + " product_brand pbrand " + "on pr_dl.brand_id=pbrand.id " + "left join " + "product_types ptype " + "on pr_dl.product_type_id=ptype.product_type_id " + "left join " + "sizes " + "on pr_dl.size_id=sizes.size_id " + "left join " + "supplier_details suppd " + " on pr_dl.supplier_id=suppd.id " + "left join customer_details cu " + "on bd.customer_id=cu.id " + "where bpd.bill_no=? "; ps = con.preparestatement(query); ps.setlong(1, billno); system.out.println("before execution"); res = ps.executequery(); bill b; productdetailsaction pb = null; while (res.next()) { b = new bill(); long product_id = res.getlong("product_id"); int qty = res.getint("qty"); float unit_price_s = res.getfloat("up"); float sell_price_s = res.getfloat("sp"); int dis_per_s = res.getint("dp"); long bill_no = res.getlong("bill_no"); long customer_id = res.getlong("customer_id"); float sub_total = res.getfloat("sub_total"); float vat = res.getfloat("vat"); float total = res.getfloat("total"); string payment_type = res.getstring("payment_type"); timestamp add_date = res.gettimestamp("add_date"); long bar_code = res.getlong("barcode"); string color = res.getstring("color"); string model_no = res.getstring("model_no"); string brand = res.getstring("brand"); string product_type = res.getstring("product_type"); string size = res.getstring("size"); string supplier_name = res.getstring("name"); long quntity = res.getlong("quntity"); float unit_price = res.getlong("unit_price"); float selling_price = res.getlong("selling_price"); int discount_percent = res.getint("discount_percent"); long id = res.getlong("id"); string name = res.getstring("name"); string address = res.getstring("address"); string mobno = res.getstring("mobno"); date dob = res.getdate("dob"); date anniversery = res.getdate("anniversery"); timestamp adddate = res.gettimestamp("add_date"); //setting bill bean system.out.println(bill_no + " " + sub_total); b.getbilldetails().setbillno(bill_no); b.getbilldetails().setsubtotal(sub_total); b.getbilldetails().setvat(vat); b.getbilldetails().settotal(total); b.getbilldetails().setpaymenttype(payment_type); simpledateformat date_format = new simpledateformat("dd-mmm-yy hh:mm aaa"); string pur_date = date_format.format(add_date); b.getbilldetails().setdispdate(pur_date); b.getcustomerdetails().setcustomername(name); b.getcustomerdetails().setaddress(address); b.getcustomerdetails().setmobno(mobno); pb = new productdetailsaction(); pb.setbarcode(bar_code); pb.setproducttype(product_type); pb.setproductsize(size); pb.setquantity(qty); pb.setunitprice(unit_price_s); pb.setsellingprice(sell_price_s); pb.settotalsellingprice(sell_price_s * qty); pb.setdiscountpercentage(dis_per_s); b.getproductdetails().add(pb); st.add(b); } con.close(); } catch (sqlexception s) { status = "failure"; system.out.println("sql code not execute." + s); } { try { if (con != null) { con.close(); } } catch (sqlexception sqe) { status = "failure"; system.out.println("sqlexception " + sqe); } } system.out.println("returning billproductdetailsbusiness fetch() "); return st; } bill.java
public class bill { private customeraction customerdetails = new customeraction(); private set<productdetailsaction> productdetails = new hashset<productdetailsaction>(0); private billaction billdetails = new billaction(); //getter , setter } i fetching data multiple table , showing in jsp page. code fetching proper data. problem if productdetails more 1 repeating customerdetails , billdetails as productdetails there.
i want 1 customerdetails 1 billdetails , 1 or more productdetails available in database
how resolve this.
i believe issue joining tables data sql, means database returning multiple rows:
customer1 bill1 product1 customer1 bill1 product2 etc there few ways solve this. simplest, depending on if have enough memory keep data in memory, store bill in hashmap, , iterate through rows, check see if bill in hashmap. if is, add product product list in bill, otherwise, create new bill object , add map.
then when display on page, display:
bill1 - product1 - product2 etc. hope helps.
Comments
Post a Comment