CODEWITHSUNDEEP
javajsonspringrestjackson
MeetJoeBlack
MeetJoeBlack

Reputation: 2924

Convert List<Object[]> to JSON

Hi guys!

Can someone helps me, how to convert my HQL query results to JSON with list of objects and get it with rest service.

Here is my service method which returns qyery result list: 

   @Override
    public List<Object[]> getAllDepartments() {
        List<Object[]> list;
        Query query =  entityManager.createQuery("SELECT dp.name, avg (wr.salary) FROM Worker wr join wr.department dp GROUP BY dp.name");
        list =  query.getResultList();
        return list;
    }

And my Spring RESTfull Controller:

@RequestMapping(value = "/all", method = RequestMethod.GET)
@ResponseBody
public JsonNode getAllDeps() {
    List<Object[]> list  = departmentService.getAllDepartments();
    ObjectMapper objectMapper = new ObjectMapper();
    Map<String, Integer> resultMap = new HashMap<String, Integer>(list.size());
    for (Object[] result : list)
        resultMap.put((String)result[0], ((Double)result[1]).intValue() );
    final JsonNode json = objectMapper.valueToTree(resultMap);
    return json;
}

Now my service responce me json with data in thats look : {"Security":1500,"Economical":1850,"IT":2000}

But i need in that(list of objects):

[{name:"Security",salary:"1500"},{name:"Economical",salary:1850},{name:"IT",salary:2000}]

Thanks for help.

Upvotes: 2

Views: 3984

Answers (2)

Joubert Anderson Vieira
Joubert Anderson Vieira

Reputation: 1

public JSONObject findOccurrByFileUUID(UUID filePefin) throws JSONException {

    StringBuilder strQuery = new StringBuilder();
    strQuery.append("SELECT c.\"name\" AS creditorname, d.\"name\" AS debtorname, concat(b2.\"type\",'-',b2.\"number\",'-',b2.parcel) AS docto, b2.uniquenumber, to_char(b.dateoccurr,'DD/MM/YYYY HH24:MI:SS') AS dateoccur, to_char(b.createddate, 'DD/MM/YYYY HH24:MI:SS') AS createddate, b.statusnegatived, l.username FROM billnegativedoccurr b \n" +
                    "LEFT JOIN billnegatived b2 ON b2.uuid = b.billnegativeduuid \n" +
                    "LEFT JOIN creditor c ON c.tenantowner_uuid = b2.tenant_uuid AND c.uuid = b2.creditor_uuid \n" +
                    "LEFT JOIN debtor d ON d.tenantowner_uuid = b2.tenant_uuid AND d.uuid = b2.debtor_uuid \n" +
                    "INNER JOIN login l ON l.uuid = b.useroccurr \n" +
                    "WHERE b.filepefinuuid = :filePefin");

    Query query = eM.createNativeQuery(strQuery.toString()); //no entity mapping
    query.setParameter("filePefin", filePefin);
    List<Object[]> queryList = query.getResultList();
    JSONArray array = new JSONArray();
    JSONObject obj = new JSONObject();

    for (Object[] result : queryList) {

        JSONObject object = new JSONObject();
        object.put("creditorName", result[0]);
        object.put("debtorName", result[1]);
        object.put("docto", result[2]);
        object.put("uniquenumber", result[3]);
        object.put("dateoccur", result[4]);
        object.put("createddate", result[5]);
        object.put("statusnegatived", result[6]);
        object.put("username", result[7]);

        array.put(object);
    }

    return obj.put("Values", array);
}

Upvotes: 0

Evgeni Dimitrov
Evgeni Dimitrov

Reputation: 22516

Just return the list from the controller method

@RequestMapping(value = "/all", method = RequestMethod.GET)
@ResponseBody
public List<Object[]> getAllDeps() {
    List<Object[]> list  = departmentService.getAllDepartments();
    return list;
}

The @ResponseBody annotation does the transformation for you.

Upvotes: 4

Related Questions