Convert object containing repeated fields to JSON

Question

I have two table country and city in mysql dababase and i make a query to return records like that as List<myDTO> :

1,france,1,paris
1,france,2,marseille
1,france,3,lion
....

MyDTO

public class MyDTO {

    public Integer idLvl1;
    public String  nameLvl1;
    public Integer idLvl2;
    public String  nameLvl2;

    public MyDTO(Integer idLvl1, String nameLvl1, Integer idLvl2, String nameLvl2) {
        this.idNiv1 = idLvl1;
        this.nomNiv1 = nameLvl1;
        this.idNiv2 = idLvl2;
        this.nomNiv2 = nameLvl2;
    }

How can i convert it to json object to avoid the repeating country :

[ 
  {"idNiv1" :1,"nameLvl1":"France","cities":[{"idNiv2":1,"nameLvl2":"paris"}]} 
  {"idNiv1" :1,"nameLvl1":"France","cities":[{"idNiv2":2,"nameLvl2":"marseille"}]} 
  {"idNiv1" :1,"nameLvl1":"France","cities":[{"idNiv2":3,"nameLvl2":"lion"}]} 
  ....
]

to

[ 
  {
     "idNiv1" :1,
     "nameLvl1":"France",
     "cities":[
                { "idNiv2":1,"nameLvl2":"paris" } ,
                { "idNiv2":2,"nameLvl2":"marseille" } ,
                { "idNiv2":3,"nameLvl2":"lion" }
              ]
  }
  ....
]

Answer 1

Create additional classes for country and city. Transform the flat structure to nested structure of country and cities as shown below:

public class Country {

    Integer idLvl1;
    String  nameLvl1;

    public Country(Integer idLvl1, String  nameLvl1) {
    }

    List<City> cities;

}

public class City {

    Integer idLvl2;
    String  nameLvl2;

    public City(Integer idLvl2, String  nameLvl2) {
    }
}

public class MyDTOConverter {

    public static Collection<Country> covert(List<MyDTO> dtos){
        Map<Integer, Country> countries = new LinkedHashMap<Integer, Country>();
        for (MyDTO myDTO : dtos) {
            //First adding the country if it doesn't exist
            if (!countries.containsKey(myDTO.idLvl1)){
                countries.put(myDTO.idLvl1, new Country(myDTO.idLvl1, myDTO.nameLvl1));
            }

            //Adding city in the existing country.
            countries.get(myDTO.idLvl1).cities.add(new City(myDTO.idLvl2, myDTO.nameLvl2));
        }

        return countries.values();
    }

}

The final Collection of Country will result is the desired JSON.

Answer 2

You can write Wrapper DTO on top of Your MyDTO and then use any available json libraries like Google's Gson to convert to required JSON format.

Regards, Sakumar

Answer 3

You can use Google's Gson in this case :

public String getJSONFromResultSet(ResultSet rs, String key) {
    Map json = new HashMap();
    List list = new ArrayList();
    if (rs != null) {
        try {
            ResultSetMetaData mData = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> columns = new HashMap<String, Object>();
                for (int columnIndex = 1; columnIndex <= mData.getColumnCount(); columnIndex++) {
                    if (rs.getString(mData.getColumnName(columnIndex)) != null) {
                        columns.put(mData.getColumnLabel(columnIndex),
                                rs.getString(mData.getColumnName(columnIndex)));
                    } else {
                        columns.put(mData.getColumnLabel(columnIndex), "");
                    }
                }
                list.add(columns);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        json.put(key, list);
    }
    return new Gson().toJson(json);
}

Update: You can call getJSONFromResultSet method like below :

Connection con = DBConnectionClass.myConnection();
        PreparedStatement ps = con.prepareStatement("SELECT * FROM Customer");
      //as an example consider a table named Customer in your DB.
        ResultSet rs = ps.executeQuery();
        System.out.println(getJSONFromResultSet(rs, "customer"));