JAVA – Add Objects to JSON from a join SQL query

Issue

I hope the title is well set…

I have an existing Java application (i cannot change all the application) which simply exposes db tables as JSON using gson library.

My problem is how to create a Json not only with the simple fields of the DB but with the linked Objects.

I have a table in this way

| objectid | plantid | other_fields |

Now the existing gson gives me simply the plantid as an integer, but i have a plants table

| plantid | plantname | other_fields |

I would like to have the gson with the Plant Object, not simply the plantid

This is how the application maps the Sql query to the Model

public ArrayList<ObjectsEntity> getObject(String objectId){
        ArrayList<ObjectsEntity> list = new ArrayList<>();
        ResultSet rs = null;
        try {
            Connection connLocal = connFactLocal.getConnection();
            String query = "select * from OBJECTS o left join PLANTS p on o.PLANTID = p.PLANTID "
                    + "left join OBJECTTYPES ot on o.OBJECTTYPEID = ot.OBJECTTYPEID";
            Statement stmt = connLocal.createStatement();
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                ObjectsEntity oe = new ObjectsEntity();
                oe.setOBJECTID(rs.getInt("OBJECTID"));
                oe.setPLANTID(rs.getInt("PLANTID"));
                oe.setOBJECTNAME(rs.getString("OBJECTNAME"));
                oe.setOBJECTTYPEID(rs.getInt("OBJECTTYPEID"));
                oe.setDESCRIPTION(rs.getString("DESCRIPTION"));
                oe.setISMONITORED(rs.getBoolean("ISMONITORED"));
                oe.setGROUP(rs.getString("GROUP"));
                list.add(oe);
            }
        } catch (SQLException e){
            logger.error("SQL Error:",e);
        }
        return list;
    }

ObjectsEntity.java is a simple mapper with the DB table with getters and setters
I’ve tried to change this mapper updating

int PLANTID

with

Plants plant

But then i don’t know how to set the Plants from the ResultSet of the query… any idea?

Solution

You need to populate Plants object using data from resultset and set that on ObjectsEntity . Using ObjectMapper you can convert ObjectsEntity to JSON String

    while (rs.next()) {
                    ObjectsEntity oe = new ObjectsEntity();
                    oe.setOBJECTID(rs.getInt("OBJECTID"));
                    Plants plants = new Plants();
                    plants.setId(rs.getInt("PLANTID"));
                      //Set other fields.
                    oe.setPLANTS(plants);
                    oe.setOBJECTNAME(rs.getString("OBJECTNAME"));
                    oe.setOBJECTTYPEID(rs.getInt("OBJECTTYPEID"));
                    oe.setDESCRIPTION(rs.getString("DESCRIPTION"));
                    oe.setISMONITORED(rs.getBoolean("ISMONITORED"));
                    oe.setGROUP(rs.getString("GROUP"));
                    list.add(oe);
                }

Answered By – MayurB

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply

(*) Required, Your email will not be published