CODEWITHSUNDEEP
javaspring-bootspring-jdbcjdbctemplate
Galet
Galet

Reputation: 6269

Spring boot Jdbctemplate returns the output in specified format

I am using Spring boot for my application. Using jdbctemplate to run MySQL queries.

query = "Select * from users";

List<Map<String, Object>> response = jdbcTemplate.queryForList(query);

Current output:

[
        {
            "id": 1,
            "firstname": "Sam",
            "address": "US"
        },
        {
            "id": 2,
            "firstname": "Alex",
            "address": "US"
        }
]

I want to return like below output using jdbctemplate. Is there any method in jdbctemplate to return like below output?

Expected output:

[
        [
            "id"
            "firstname"
            "address"
        ],
        [
            1,
            "Sam",
            "US"
        ],
        [
            2,
            "Alex",
            "US"
        ]
]

Upvotes: 2

Views: 1233

Answers (1)

M A
M A

Reputation: 72844

You can leverage the ResultSetExtractor to map the ResultSet into the desired response structure. The columns can be retrieved from the ResultSet's metadata: rs.getMetadata().

ResultSetExtractor<List<List<Object>>> resultSetExtractor = new ResultSetExtractor<>() {

    @Override
    public List<List<Object>> extractData(ResultSet rs) throws SQLException, DataAccessException {
        List<List<Object>> result = new ArrayList<>();
        List<Object> columnNames = new ArrayList<>();
        result.add(columnNames);

        ResultSetMetaData rsmd = rs.getMetaData();
        int columnCount = rsmd.getColumnCount();
        for (int col = 1; col <= columnCount; col++) {
            String columnName = rsmd.getColumnName(col);
            columnNames.add(columnName);
        }

        while (rs.next()) {
            List<Object> row = new ArrayList<>();
            result.add(row);
            for (int col = 1; col <= columnCount; col++) {
                Object value = rs.getObject(col);
                row.add(value);
            }
        }
        return result;
    }
};
return jdbcTemplate.query("Select * from user", resultSetExtractor);

Upvotes: 6

Related Questions