How to convert jdbc template query result to JSON Array in java?

Question

I am converting software from Delphi to Spring boot. I have lots of queries in an existing SQL Server database, and I want to display them.

For every query, I have to create entity class, that override row mapper and create entity class.

public class PregledTroskova {
int RbrStavke;
int RbrNaloga;
String Konto;
String SifKomint;
float Duguje;
float Potrazuje;

public PregledTroskova() {
}

public int getRbrStavke() {
    return RbrStavke;
}

public void setRbrStavke(int rbrStavke) {
    RbrStavke = rbrStavke;
}

public int getRbrNaloga() {
    return RbrNaloga;
}

public void setRbrNaloga(int rbrNaloga) {
    RbrNaloga = rbrNaloga;
}

public String getKonto() {
    return Konto;
}

public void setKonto(String konto) {
    Konto = konto;
}

public String getSifKomint() {
    return SifKomint;
}

public void setSifKomint(String sifKomint) {
    SifKomint = sifKomint;
}

public float getDuguje() {
    return Duguje;
}

public void setDuguje(float duguje) {
    Duguje = duguje;
}

public float getPotrazuje() {
    return Potrazuje;
}

public void setPotrazuje(float potrazuje) {
    Potrazuje = potrazuje;
}
}

Then after that I have to write one more class for mapper

class PregledTroskovaRowMapper implements RowMapper<PregledTroskova> {
@Override
public PregledTroskova mapRow(ResultSet resultSet, int i) throws SQLException {
    PregledTroskova pregledTroskova = new PregledTroskova();
    pregledTroskova.setDuguje(resultSet.getFloat("Duguje"));
    pregledTroskova.setPotrazuje(resultSet.getFloat("Potrazuje"));
    pregledTroskova.setRbrStavke(resultSet.getInt("RbrStavke"));
    pregledTroskova.setRbrNaloga(resultSet.getInt("RbrNaloga"));
    pregledTroskova.setKonto(resultSet.getString("Konto"));
    pregledTroskova.setSifKomint(resultSet.getString("SifKomint"));
    return pregledTroskova;
}
}

Is there any way to just generate json list/array of object where property is result set name value for all rows from select?

Assuming you are using @RestController, why don't you send List<Object> to client ? Here object is referred as RowMapper entity. — abhi3232
– abhi3232, Commented Jul 2, 2018 at 23:55
@abhi3232 but how to fetch values and put assign them to object? — Mihajlo Racic
– Mihajlo Racic, Commented Jul 3, 2018 at 0:52
That means instead of creating DTOs for every table you want directly return all column value mappings ? Is my understanding correct ? — abhi3232
– abhi3232, Commented Jul 3, 2018 at 2:13

Thoomas · Accepted Answer · 2018-07-03 06:17:27Z

2

If you don't want to create an entity class for every query, you can return a List<Map<String, Object>> which is a list of all rows, each row is represented by a Map (column / value).

Example :

final List<Map<String, Object>> rows = jdbcTemplate.queryForList(yourQuery);
for (final Map<String, Object> row : rows) {
    // do something like : row.get("yourField");
}

answered Jul 3, 2018 at 6:17

Thoomas

2,3883 gold badges26 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Badreddine Leghrib · Accepted Answer · 2018-10-08 18:05:30Z

Example :

 String sql = "select * from mytable";
   result=  jdbcTemplate.query(sql, new AnyObjectMapper());

Mapper (generic) :

class AnyObjectMapper implements RowMapper<Map<String, Object>> {

    public AnyObjectMapper() {
        // TODO Auto-generated constructor stub
    }

    @Override
    public Map<String, Object> mapRow(ResultSet rs, int rowNum) throws SQLException {

        ResultSetMetaData rsMeta = rs.getMetaData();
        int colCount = rsMeta.getColumnCount();

        Map<String, Object> columns = new HashMap<String, Object>();
        for (int i = 1; i <= colCount; i++) {

            columns.put(rsMeta.getColumnLabel(i), rs.getObject(i));
        }
        return columns;

    }
}

Collectives™ on Stack Overflow

How to convert jdbc template query result to JSON Array in java?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related