0

I am reading table from postgreSQL DB and populating all columns and its values in a json object.

One of the column in postgre is of type json. So the output has lot of escape characters. like below for key dummykeyname.

 {
        "XY": "900144",
        "id": 1,
        "date": 1556167980000,
        "type": "XX50",
        "dummykeyname": {
            "type": "json",
            "value": "{\"XXXX\": 14445.0, \"YYYY\": 94253.0}"
        }
 }

I want the output to look like 

  "value": "{"XXXX": 14445.0, "YYYY": 94253.0}"

Code i used is 

JSONArray entities = new JSONArray();

var rm = (RowMapper<?>) (ResultSet result, int rowNum) -> {

while (result.next()) {
    JSONObject entity = new JSONObject();
    ResultSetMetaData metadata = result.getMetaData();
    int columnCount = metadata.getColumnCount() + 1;
    IntStream.range(1, columnCount).forEach(nbr -> {
      try {
        entity.put(result.getMetaData().getColumnName(nbr), result.getObject(nbr));
      } catch (SQLException e) {
        LOGGER.error(e.getMessage());
      }
    });
    entities.add(entity);
  }
  return entities;
};

Library used:

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;

Please guide me where am i going wrong.

VIJ
  • 1,516
  • 1
  • 18
  • 34

1 Answers1

0

Take a different approach.

1) first create a pojo of the required columns 

  ex : if your table has 4 columns
         id, name, country, mobile create a class Employee and populate the class using rowmapper available in spring jdbc.

2) create a class EmployeeList, which has List , add each Employee objects created out of rowmapper to declared list.

3) use 

a) ObjectMapper mapper = new ObjectMapper();
b) mapper.setSerializationInclusion(JsonInclude.Include.NON_NULL);
c) mapper.writeValueAsString(EmployeeListobjects);
mrMaverick
  • 41
  • 1
  • 5
  • Thank you, i cannot use any ORM here. cos the table name is dynamic. i cannot hardcode any tablename or column name. – VIJ May 27 '19 at 11:25