Converting the Database details to JSON object

问题:

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 ?

回答1:

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);
            }
        }

回答2:

1. 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

回答3:

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);

回答4:

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));
              }
           }
       }
    }