storing every record to json array java

Question

I am unable to add every resultset record to json array. I have data fetched from three tables I am strong the result of table1 record1 in jsonarray1 and result of table2 record1 in jsonarray2 and so on. and finally adding a combined record to JSON object that is one complete record. so the problem is when I am printing a JSON object giving accurate data per record. Now I am adding a record to JSON array.But when I am printing json array it displays first to record accurate but when going to next it is printing the second record twice third three times.But I want first, second, third so on

String queryforCustomers="SELECT * FROM customers";
        String queryforCustomer="SELECT * FROM customers where id=?";
        String queryfortax_info="SELECT * FROM `customer_tax_info` where customer_id=?";
        String queryforcustomer_address="SELECT * from customer_address where customer_id=?";
        try
            {
             //for customer
                statement=connection.prepareStatement(queryforCustomers);
                rs1=statement.executeQuery();

                JSONObject customerobj=null;//result of customer information
                JSONObject finalobj1=new JSONObject();//result of every customer records
                JSONArray customersAll=new JSONArray(); //final result(all data)

                while(rs1.next())
                {

                    int id=rs1.getInt("id");
                    System.out.println("Id "+id );
                 // for adding kth element to araay
                                statement=connection.prepareStatement(queryforCustomer);
                                statement.setInt(1, id);
                                rs2=statement.executeQuery();
                                JSONArray arraycustomer=null;
                                arraycustomer=Convertor.convertResultSetIntoJSON(rs2);
                                //System.out.println("Customer Array: "+arraycustomer);
                                rs2.close();
                                statement.close();

                                        statement=connection.prepareStatement(queryfortax_info);
                                        statement.setInt(1, id);
                                        rs2=statement.executeQuery();
                                        JSONArray arrayCustomer_tax_info=null;
                                        arrayCustomer_tax_info=Convertor.convertResultSetIntoJSON(rs2);
                                        //System.out.println("Customer TAx: "+arrayCustomer_tax_info);
                                        rs2.close();
                                        statement.close();


                                statement=connection.prepareStatement(queryforcustomer_address);
                                statement.setInt(1, id);
                                rs3=statement.executeQuery();
                                JSONArray arrayCustomer_address=null;
                                arrayCustomer_address=Convertor.convertResultSetIntoJSON(rs3);
                               // System.out.println("Customer Address: "+arrayCustomer_address);
                                rs3.close();
                                statement.close();
                                finalobj1.put("customer_address",arrayCustomer_address);
                                finalobj1.put("customer_tax_info",arrayCustomer_tax_info);
                                finalobj1.put("customers", arraycustomer);
                                customersAll.put(finalobj1);
                                System.out.println("Final 1 "+finalobj1); 
                                System.out.println("Final "+customersAll.toString());              
                }
                System.out.println("Final "+customersAll.toString()); 
            }
        catch(Exception e)
            {
                e.printStackTrace();

            }




Output {
"customers": [
    {
        "customer_address": [
            {
                "zip": "171004",
                "country": "India",
                "address": "V.PO Chadwal Distt Kathua, Teh Hiranagar  Jammu, Jammu and Kashmir in",
                "as_ship": 1,
                "city": "Shimla",
                "created": "2017-10-10 12:59:45.0"

            }
        ],
        "customer_tax_info": [
            {
                "payment_term": "123",
                "created": "2017-10-10 12:59:45.0",
                "tax_reg_no": "1235",
                "id": 1,
                "customer_id": 1,
                "pan": ""
            }
        ],
        "customers": [
            {
                "website": "",
                "user_id": 1,
                "created": "2017-10-10 12:59:45.0",
                "company_name": "Trinity",
                "mobile": "8872406723",
                "last_name": "Thakur",
                "id": 1,
                "first_name": "Aneh",
                "status": 0
            }
        ]
    },
        {
        "customer_address": [
            {
                "zip": "171004",
                "country": "India",
                "address": "V.PO Chadwal Distt Kathua, Teh Hiranagar  Jammu, Jammu and Kashmir in",
                "as_ship": 1,
                "city": "Shimla",
                "created": "2017-10-10 12:59:45.0"

            }
        ],
        "customer_tax_info": [
            {
                "payment_term": "123",
                "created": "2017-10-10 12:59:45.0",
                "tax_reg_no": "1235",
                "id": 1,
                "customer_id": 1,
                "pan": ""
            }
        ],
        "customers": [
            {
                "website": "",
                "user_id": 1,
                "created": "2017-10-10 12:59:45.0",
                "company_name": "Trinity",
                "mobile": "8872406723",
                "last_name": "Thakur",
                "id": 1,
                "first_name": "Aneh",
                "status": 0
            }
        ]
    },
        {
        "customer_address": [
            {
                "zip": "171004",
                "country": "India",
                "address": "V.PO Chadwal Distt Kathua, Teh Hiranagar  Jammu, Jammu and Kashmir in",
                "as_ship": 1,
                "city": "Shimla",
                "created": "2017-10-10 12:59:45.0"

            }
        ],
        "customer_tax_info": [
            {
                "payment_term": "123",
                "created": "2017-10-10 12:59:45.0",
                "tax_reg_no": "1235",
                "id": 1,
                "customer_id": 1,
                "pan": ""
            }
        ],
        "customers": [
            {
                "website": "",
                "user_id": 1,
                "created": "2017-10-10 12:59:45.0",
                "company_name": "Trinity",
                "mobile": "8872406723",
                "last_name": "Thakur",
                "id": 1,
                "first_name": "Aneh",
                "status": 0
            }
        ]
    }

]

}

shivam · Accepted Answer · 2017-11-11 08:17:29Z

1

As i can see in your code you have declared the "customersAll" and "finalobj1" out side of while loop which is iterating the resultset "rs1" i.e. query "queryforCustomers" .

what is wrong with your code : you have declared "customersAll" outside of while loop that is correct but you should not declare the "finalobj1" outside of loop as you are iterating the result set "rs1" you are adding same object each time in "customersAll" object.

Can you please modify your code like below and check

 JSONObject customerobj=null;//result of customer information
 JSONArray customersAll=new JSONArray(); //final result(all data)
 while(rs1.next())
     {
         JSONObject finalobj1=new JSONObject();
         //"finalobj1" moved to inside of while loop

          . //rest of your code
          .
          .
     }

answered Nov 11, 2017 at 8:17

shivam

4913 gold badges9 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

storing every record to json array java

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