Creating JSON properly from SQL Resultset using Java

Question

I have a table as

Country       State

 USA           Texas
 USA           Alasca
 India         Delhi
 India         Bombay
 India         Madras
 Russia        Mosco
 Russia        Petersberg
 Germany       Berlin
 China         Beijing
 China         Tibet
 Turkey        Antioch

I need to show these items in two select boxes, Country and State. When USA is selected from Country select box, Texas and Alasca should be displayed in State select box. So I would need to create a Json object as

 [{Country:USA, State:{{statename:Texas},{statename:Alasca}}},
 {Country:India, State:{{statename:Delhi}, {statename:Bombay}, {statename:Madras}}},
  {Country:Germany, State:{{statename:Berlin}},
{Country:Russia, State:{{statename:Mosco},{statename:Petersberg}}},
{Country:China, State:{{statename:Beijing}, {statename:Tibet}}},
 {Country:turkey, State:{{statename:Antioch}}}
 ]

Currently, I use two separate sqls to get country and state separately. I use Java class Country as

public class Country implements Serializable {

String country;



public String getCountry() {
    return country;
}


public void setCountry(String country) {
    this.country = country;
}

 }

and I use DAO as

  public List<Country>  extractData(ResultSet rs) throws SQLException,
        DataAccessException {
    List<Country> list = new ArrayList<Country>();

    while (rs.next()) {
        Country obj = new Country();
        obj.setCountry(rs.getString(1).trim());

        list.add(obj);
    }

    return list;
   }

I get the object as :

[{country:USA}, {country:India}, {country:Germany}, {country:Russia}, {country:China}, {country:turkey}]

In the same way, I created class for state and code for extracting data, I get the states as

         [ {state:Texas} {state:Alasca}, {state:Delhi}, {state:Bombay},     {state:Madras}, {state:Berlin}, {state:Mosco}, {state:Petersberg} {state:Beijing}, {state:Tibet}, {state:Antioch}]

This can be shown in select boxes separately.

But I need the object in the following way to show them properly in select boxes.

  [{Country:USA, State:{{statename:Texas},{statename:Alasca}}},
 {Country:India, State:{{statename:Delhi}, {statename:Bombay}, {statename:Madras}}},
  {Country:Germany, State:{{statename:Berlin}},
{Country:Russia, State:{{statename:Mosco},{statename:Petersberg}}},
{Country:China, State:{{statename:Beijing}, {statename:Tibet}}},
 {Country:turkey, State:{{statename:Antioch}}}
 ]

I could create a java class as,

 class CountryState{
    string country;
    List <State> state;

    public String getCountry()
    {
        return country;
    }

    public void setCountry(String country){
        this.country=country;
    }

    public List<State> getState(){
        return state;
    }

    public void setState(List<State> state)
    {
     this.state.add(state);
     }
    }

    class State{
    String statename;

    public String getStatename()
    {
    return statename;
    }

    public void setStatename(String statename){
    this.statename=statename;
    }

I need to know how to store the values in result set to such an object and produce the desired JSON object.

score 0 · Answer 1 · answered Oct 08 '15 at 14:54

Create a pojo in JAVA for the data object that comes from database and the use a JSON converter library on that pojo like you could use GSON or Jackson.

Hint : You might have to change your query to group by country and you might need a Collection of states mapped to one country.

Creating JSON properly from SQL Resultset using Java

1 Answers1