Conversion of Hibernate Result to json

Question

I need help with the case of converting the Hibernate SQL result into json.

I reached the stage that is converting correct, but the same does not come with the defined name of the column, and I was needing the result to come in the json with the name of the respective column.

SQL:

select locationApiAcp as acp from Pro

Function:

public List<Map<String,Object>> listByNativeQuery(String query) {
    Query nativeQuery = manager.createNativeQuery(query);

    return nativeQuery.getResultList();
}

Calling for:

List<Map<String,Object>> listObj = local._runLibrary(cq);

    Gson gs = new Gson();

    String json = gs.toJson(listObj);
    System.out.println(json);

Final result not exiting with column name:

["/api/"]

---Edit--- A small solution that I found using a parallel connection to JPA. I don’t know if it would be the right one or could do with the jpa’s own connection.

Class for later conversion:

public class JsonApi {
private String status;
private List<HashMap<String, String>> rows = new ArrayList<>();

public void _addRow(HashMap<String, String> map) {
    this.rows.add(map);
}

public String getStatus() {
    return status;
}

public void setStatus(String status) {
    this.status = status;
}

public List<HashMap<String, String>> getRows() {
    return rows;
}

public void setRows(List<HashMap<String, String>> rows) {
    this.rows = rows;
}}

Processing of the Resultset:

this.json.setStatus("200");

while (this.rs.next()) {

    ResultSetMetaData rsmd = rs.getMetaData();
    HashMap<String, String> map = new HashMap<>();

    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
        String column = rsmd.getColumnLabel(i);
        map.put(column, this.rs.getString(column));
    }

    this.json._addRow(map);

}

Answer 1

I ran a little test with the code:

List<Map<String,Object>> listObj = new ArrayList<>();
Map<String,Object> map1 = new HashMap<>();
map1.put("f1", "v1");
map1.put("f2", 1L);
listObj.add(map1);
Map<String,Object> map2 = new HashMap<>();
map2.put("f1", 1.2D);
listObj.add(map2);

Gson gs = new Gson();
String json = gs.toJson(listObj);
System.out.println(json);

And I got the exit:

[{"F1":"v1","F2":1},{"F1":1.2}]

Therefore, serialization code correctly.

Something important that should be understood is that the generic type of the list (in this case: Map<String,Object>) is just a compile-time check, not a run-time guarantee.

In this case, the method getResultList does not specify the generic type. In fact, in general, when there is no JPA entity selected, Hibernate returns a array containing the values of the fields in the order in which they were selected in the query. Since you selected only one field, Hibernate is probably just returning a list where each element is a string with the field value.

Therefore, what is wrong in the code is that it assumes the wrong type in each item of the list resulting from the query.

If you need to return values as a map, or any other structure, you need to rearrange the list values so that it is compatible with the desired structure for JSON.

When making a query like this, Inspecione via debugging which values Hibernate actually returns and never assume typing without confirming before, after all native queries do not have the JPA entity mapping guarantees.