java - java将每条记录存储到json数组

Question

我无法将每个结果集记录添加到 json 数组中。我从三个表中获取了数据，我对 jsonarray1 中 table1 record1 的结果和 jsonarray2 中 table2 record1 的结果很感兴趣，依此类推。最后将组合记录添加到 JSON 对象中，这是一个完整的记录。所以问题是当我打印 JSON 对象时，为每条记录提供准确的数据。现在我正在向 JSON 数组添加一条记录。但是当我打印 json 数组时，它首先显示记录准确，但是当进入下一个时，它会打印第二条记录两次第三次三次。但我想要第一、第二、第三等等

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
            }
        ]
    }

]

}

Answer 1

正如我在代码中看到的，您已在 while 循环之外声明了“customersAll”和“finalobj1”，该循环正在迭代结果集“rs1”，即查询“queryforCustomers”。

您的代码有什么问题:您已在 while 循环之外声明了“customersAll”，这是正确的，但您不应该在循环外部声明“finalobj1”，因为您正在迭代结果集“rs1”，您每次在“customersAll”对象中添加相同的对象。

您可以像下面这样修改您的代码并检查

 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
          .
          .
     }