L
L
leksoQA2017-07-14 10:49:25
Java
leksoQA, 2017-07-14 10:49:25

Why is only the first line written to JSON when reading rows from a SQL query?

Good afternoon.
As a result of SQL request I receive three lines with results. Next, using JDBC and the json-simple library, I write the results to JSON. I can't understand why three ITEMs with the same data are written to the JSON in the ITEMS object. If you try to output to the console without writing to json, then the data for the item-s is different, as it should be.
Method code:

public String getTenderData() {
        JSONObject dataTender = new JSONObject();
        JSONArray dataItems = new JSONArray();
        JSONObject dataItem = new JSONObject();
        JSONObject result = new JSONObject();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = this.getConnect();
            if(connection != null) {
                statement = connection.createStatement();
                resultSet = statement.executeQuery(QUERY);
                while (resultSet.next()) {
                     //Extracting data from the database and writing to JSON
                    //Tender-----------------------------------------------
                    dataTender.put("numberDocument", resultSet.getString("Ном.тендера"));
                    dataTender.put("typeProcedure", resultSet.getString("Тип процедуры"));
                    dataTender.put("status", resultSet.getString("Статус"));
                    dataTender.put("description", resultSet.getString("Описание"));
                    dataTender.put("expectedSum", resultSet.getString("Сумма"));
                    dataTender.put("yearPlan", resultSet.getString("Годовой план"));
                    dataTender.put("enquirePeriodStart", resultSet.getString("НПВопр"));
                    dataTender.put("enquirePeriodEnd", resultSet.getString("ОПВопр"));
                    dataTender.put("tenderPeriodStart", resultSet.getString("НТенд"));
                    dataTender.put("tenderPeriodEnd", resultSet.getString("ОТенд"));
                    //Items------------------------------------------------
                    dataItem.put("numberRequest", resultSet.getString("Ном.заявки"));
                    dataItem.put("cpv", resultSet.getString("CPV"));
                    dataItem.put("codeBudget", resultSet.getString("Код бюджета"));
                    dataItem.put("name", resultSet.getString("Наименование"));
                    dataItem.put("count", resultSet.getString("Количество"));
                    dataItems.add(dataItem);
                    //Result
                    result.put("dataTender", dataTender);
                    result.put("dataItems", dataItems);
               }
                closeConnect();
                if(connection == null) System.out.println("...Connection Close");
                resultSet.close();
                statement.close();
                statement = null;
                resultSet = null;
            } else System.out.println("Error: No active Connection");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result.toJSONString();

As a result, I get this JSON:
{
  "dataTender": {
    "yearPlan": "30000.0100",
    "typeProcedure": "Допорогова закупівля",
    "enquirePeriodStart": "2017-07-14",
    "tenderPeriodEnd": "2017-06-05",
    "expectedSum": "10000.0000",
    "description": "[TEST: 26.06.2017 в 13:22] Тестовий тендер",
    "enquirePeriodEnd": "2017-06-08",
    "numberDocument": "1755",
    "tenderPeriodStart": "2017-06-08",
    "status": "Согласованный документ"
  },
  "dataItems": [
    {
      "cpv": "31210000-1",
      "name": "Запобіжник  10А керамічний",
      "count": "5.000000",
      "numberRequest": "17140",
      "codeBudget": "1.23.01.0001"
    },
    {
      "cpv": "31210000-1",
      "name": "Запобіжник  10А керамічний",
      "count": "5.000000",
      "numberRequest": "17140",
      "codeBudget": "1.23.01.0001"
    },
    {
      "cpv": "31210000-1",
      "name": "Запобіжник  10А керамічний",
      "count": "5.000000",
      "numberRequest": "17140",
      "codeBudget": "1.23.01.0001"
    }
  ]
}

Answer the question

In order to leave comments, you need to log in

1 answer(s)
R
roswell, 2017-07-14
@leksoQA

The dataItem declaration needs to be moved to the body of the loop.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question