Java, getting data from a Derby database and converting each row into a Json object

Question

I am tinkering with Java and Derby database, and wrote code to get all the rows from a derby database (netbeans integrated database) and create a JSON object from it.

try {
      PreparedStatement statement = this.con.prepareStatement("SELECT * FROM PURCHASEORDERS");
      ResultSet result = statement.executeQuery();
      JSONObject objectJson = new JSONObject();
      while(result.next()){
      for (int x=1;x<(result.getMetaData().getColumnCount())+1;x++)
      {
          if(result.getMetaData().getColumnType(x)==java.sql.Types.INTEGER)
          {objectJson.put(result.getMetaData().getColumnName(x), result.getInt(x));}
          if(result.getMetaData().getColumnType(x)== java.sql.Types.VARCHAR)
          {objectJson.put(result.getMetaData().getColumnName(x), result.getString(x));}
          if(result.getMetaData().getColumnType(x)== java.sql.Types.BOOLEAN)
          {objectJson.put(result.getMetaData().getColumnName(x), result.getBoolean(x));}
      }
      }
      System.out.println(objectJson);

Here is what the output is

{"STATUS":"ordered","DEPARTMENTCODE":"Enterfake","COMPLETEDSTATUS":true,"PURCHASEID":4,"DELIVERYATTENTION":"fake"}

which is the last row of the database. My question is why is it giving me only the last row? and How can I make it so that it gives me all the rows? Any Ideas?

Answer 1

If you put an object to JSONObject with same key, then the previous values with that key will get overwritten, not added (because a JSON object can not have multiple keys with same name).

To get it working, create a list and then add that list to your JSONObject