Convert Object Containing Repeated Fields To Json

I have two table country and city in mysql dababase and i make a query to return records like that as List : 1,france,1,paris 1,france,2,marseille 1,france,3,lion ....

Solution 1:

You can use Google's Gson in this case :

publicStringgetJSONFromResultSet(ResultSet rs, String key) {
    Map json = newHashMap();
    List list = newArrayList();
    if (rs != null) {
        try {
            ResultSetMetaData mData = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> columns = newHashMap<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);
    }
    returnnewGson().toJson(json);
}

Update: You can call getJSONFromResultSet method like below :

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

Solution 2:

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

publicclassCountry{

    Integer idLvl1;
    String  nameLvl1;

    public Country(Integer idLvl1, String  nameLvl1) {
    }

    List<City> cities;

}

publicclassCity{

    Integer idLvl2;
    String  nameLvl2;

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

publicclassMyDTOConverter{

    publicstatic 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 existif (!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.

Solution 3:

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