Converting the Database details to JSON object

Question

I have a table with has the columns namely

recordID, recordName , titleFeild, titleIDMap, titleId, titleStartDate, titleEndDate, languageId

Now I have convert the data from above columns to the JSON object data which looks like below

{
  "recordId" :10,
  "recordName" : "RECORDS",
  "records" : [ {
    "titleField" : 1,
    "titleIDMap" : null,
    "titleId" : 500,
    "titleStartDate" : "2013-12-22T00:00:00.000+0000",
    "titleEndDate" : "2013-12-03T00:00:00.000+0000",
    "languageId" : 20
  }]
}

Please note that records is an array of columns ( titleFeild,titleIDMap,titleId,titleStartDate,titleEndDate,languageId)

The code so far I have developed is

    List<Object[]> objList = dao.getStatus();
    Integer result = null;
     JSONObject jsonData = new JSONObject();
     JSONArray jsonDataArray = new JSONArray();
    if(objList!=null && objList.size()>10000)
        {
            for (Object[] nameObj : objList) {

                jsonData.put("", nameObj.get(arg0) );


            }
        }

How do I construct the JSON Object from the columns data ?

A JSON array ([]) is a Java List, and a JSON "object" ({}) is a Java Map. Construct the two Maps and the List to match the desired structure and then "serialize" to JSON. — Hot Licks
– Hot Licks, Commented Jan 14, 2014 at 12:35

StoopidDonut · Accepted Answer · 2014-01-14 12:22:10Z

You can easily achieve this with google-gson library. In simple terms you would have to create a couple of Pojos (with reference to another containin a list of references).

Consider RecordID and RecordName as Meta Data.

Create a pojo representing this information:

public class DbMetaPojo {

    private int recordID;
    private String recordName;
    private List<Record> records;

    public List<Record> getRecords() {
        return records;
    }

    public void setRecords(List<Record> records) {
        this.records = records;
    }

    public String getRecordName() {
        return recordName;
    }

    public void setRecordName(String recordName) {
        this.recordName = recordName;
    }

    public int getRecordID() {
        return recordID;
    }

    public void setRecordID(int recordID) {
        this.recordID = recordID;
    }

}

Create another pojo with the actual Record fields:

public class Record {

    public int getTitleFeild() {
        return titleFeild;
    }

    public void setTitleFeild(int i) {
        this.titleFeild = i;
    }

    public String getTitleIDMap() {
        return titleIDMap;
    }

    public void setTitleIDMap(String titleIDMap) {
        this.titleIDMap = titleIDMap;
    }

    public int getTitleId() {
        return titleId;
    }

    public void setTitleId(int titleId) {
        this.titleId = titleId;
    }

    public String getTitleStartDate() {
        return titleStartDate;
    }

    public void setTitleStartDate(String titleStartDate) {
        this.titleStartDate = titleStartDate;
    }

    public String getTitleEndDate() {
        return titleEndDate;
    }

    public void setTitleEndDate(String titleEndDate) {
        this.titleEndDate = titleEndDate;
    }

    public int getLanguageId() {
        return languageId;
    }

    public void setLanguageId(int languageId) {
        this.languageId = languageId;
    }

    private int titleFeild;
    private String titleIDMap;
    private int titleId;
    private String titleStartDate;
    private String titleEndDate;
    private int languageId;

}

Now just a method to populate your POJOs with the relevant data (replace the hardcoding logic with your data retrieve):

public static void main(String... main) {
        DbMetaPojo obj = new DbMetaPojo();

        obj.setRecordID(10);
        obj.setRecordName("RECORDS");

        Record record = new Record();

        record.setLanguageId(20);
        record.setTitleEndDate("2013-12-22T00:00:00.000+0000");
        record.setTitleFeild(1);
        record.setTitleId(500);
        record.setTitleIDMap("SOME NULL");
        record.setTitleStartDate("2013-12-22T00:00:00.000+0000");

        List<Record> list = new ArrayList<Record>();
        list.add(record);
        obj.setRecords(list);

        Gson gson = new Gson();

        String json = gson.toJson(obj);

        System.out.println(json);
    }

Output is your formed JSON:

{
    "recordID": 10,
    "recordName": "RECORDS",
    "records": [
        {
            "titleFeild": 1,
            "titleIDMap": "SOME NULL",
            "titleId": 500,
            "titleStartDate": "2013-12-22T00:00:00.000+0000",
            "titleEndDate": "2013-12-22T00:00:00.000+0000",
            "languageId": 20
        }
    ]
}

EDIT:

To align to your code, you might want to do something like:

List<Object> objList = dao.getStatus();
        List<DbMetaPojo> metaList = new ArrayList<DbMetaPojo> ();
        if (objList != null && objList.size() > 10000) {
            for (Object nameObj : objList) {
                DbMetaPojo meta = new DbMetaPojo();
                meta.setRecordID(nameObj[0]);
                meta.setRecordName(nameObj[0]);
                ...
                ...
                ...

                metaList.add(meta);
            }
        }

List<Object> objList = dao.getStatus(); DbMetaPojo obj = new DbMetaPojo(); if(objList!=null && objList.size()>10000) { for (Object nameObj : objList) { obj.setRecordID(nameObj[0]); obj.setRecordName(nameObj[0]); ; } } is this correct ?
@AmitSharad Doesn't seem so, you are looping over the entire object list and assigning [0]th index value to DbMetaPojo every time. For the Proof of Concept, drop your loop and try with just the 0th index of elements and create the json with it (as I've done above). Maybe later your can create an array of DbMetaPojos and work with it.
@AmitSharad Edited the answer to show how you can align it to your code

Vinayak Pingale · Accepted Answer · 2014-01-14 10:15:12Z

First of all what you have to do is retrieve the data from the columns of the table using your DAO and calling a Function from DAOIMPL which in turn will return the list of data(POJO probably).

Create a map like this which will contain your key value pair for example recordid and value, recordname and value

Map<String,Object> objMap = new HashMap<String,Object>();
    objMap.put("recordId", Record.getId());
    objMap.put("recordName",Record.getName());
    // Now here is the deal create another hashmap here whose key will be records "the key for your second array" 
    //Put the values in this second hashmap as instructed above and put it as a key value pair.
    ........
    .......
    .......
    JSONObject JsonObject = JSONObject.fromObject(objMap);//This will create JSON object out of your hashmap.
    objJSONList.add(JsonObject);
}


StringBuffer jsonBuffer = new StringBuffer();
    jsonBuffer.append("{\"data\": {");
    jsonBuffer.append(objJSONList.tostring());
    jsonBuffer.append("}");
    //jsonBuffer.append(",\"total\":"+ objJSONList.size());// TOTAL Optional
    //jsonBuffer.append(",\"success\":true}");//SUCCESS message if using in callback Optional

Tugrul · Accepted Answer · 2014-01-14 10:16:10Z

1

Create an object which has your attribues. (recordID, recordName , titleFeild, titleIDMap, titleId, titleStartDate, titleEndDate, languageId)

Get data from dao and convert it to json. It will looks like what you want.

Gson gson = new Gson();

    // convert java object to JSON format,
    // and returned as JSON formatted string
    String json = gson.toJson(obj);

answered Jan 14, 2014 at 10:16

Tugrul

1,8084 gold badges27 silver badges40 bronze badges

Comments

user3192244 · Accepted Answer · 2014-01-14 23:07:09Z

I think your dao.getStatus() should return a List with Map keys and values. Your key would be column name and value would be content.

List<Map<String,Object>> objList = dao.getStatus();
if(objList!=null && objList.size()>10000){
   for(Map<String,Object> row : objList) {
       Iterator<String> keyList = row.keySet().iterator();
       while(keyList.hasNext()){
          String key = keyList.next();
          jsonData.put(key, row.get(key));
       }
   }
}

For the records array you need to build it while iterating table columns. Combining above code with building records array would be something like this..

    String[] group = {"titleField","titleIDMap","titleId","titleStartDate","titleEndDate","languageId"};
    List<String> recordGroup = Arrays.asList(group);
    Map<Object, JSONArray> records = new HashMap<Object,JSONArray>();
    List<Map<String,Object>> objList = dao.getStatus();
    JSONObject jsonData = new JSONObject();
    if(objList!=null && objList.size()>10000){
       for(Map<String,Object> row : objList) {
           int columnCount = 0;
           Iterator<String> keyList = row.keySet().iterator();

           while(keyList.hasNext()){
              String key = keyList.next();

              if(recordGroup.contains(key)){
                  Object recordId = row.get("recordId");
                  JSONArray recordArray = new JSONArray();
                  if(records.containsKey(recordId)){
                      recordArray = records.get(recordId);
                      JSONObject jsonObj = null;
                      if(columnCount >= recordGroup.size()){
                         jsonObj = new JSONObject();
                         recordarray.add(jsonObj);
                         columnCount = 0; 
                      }
                      else {
                         jsonObj = (JSONObject) recordArray.get(recordArray.size()-1);
                      }
                      jsonObj.put(key, row.get(key));
                      columnCount++;
                  }
                  else {
                      JSONObject jsonObj = new JSONObject();
                      jsonObj.put(key, row.get(key));
                      recordArray.add(jsonObj);
                      records.put(recordId, recordArray);
                  }
                  jsonData.put("records", records.get(recordId));
              }
              else {
                  jsonData.put(key, row.get(key));
              }
           }
       }
    }

Collectives™ on Stack Overflow

Converting the Database details to JSON object

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related