JDBCTempalte RowMapper to read jsonb column in postgres table

Question

I am reading the Postgres table using the JDBCTemplate, which has the below schema:

orderid|order_name|features|extra_features|

orderid: integer
order_name: text
features: josnb
extra_features: jsonb

Order DTO:

public class Order{
  private Integer orderid;
  private String orderName;
  List<Features> features;
  List<ExtraFeatures> extraFeatures;
       ....

   getter & setter   
}

Feature DTO:

public class Features{
  private String featureName:
  private String featureValuel
}

Now, while executing the SELECT * FROM public.orders query, I am writing the rowMapper like below: jdbcTemplate.query("SELECT * FROM public.orders", new OrderRowMapper())

RowMapper

public class OrderRowMapper implements RowMapper<Order>{
  @Override
  public Order mapRow(ResultSet rs, int rowNum) throws SQLException{
     Order order = new Order();
     order.setOrderid(rs.getInt("orderid"));
     order.setFeatures()// how to read jsonbcolumn?
  }
}

I am able to set all the values except the jsonb column, I don't know how to implement RowMapper for that, please help.

Did you try `ObjectMapper mapper = new ObjectMapper(); List features = objectMapper.readValue(rs.getString("features"), new TypeReference>(){});` — Kavithakaran Kanapathippillai, Jul 09 '20 at 12:08

score 2 · Accepted Answer · answered Jul 09 '20 at 12:33


public class OrderRowMapper implements RowMapper<Order>{
  private ObjectMapper mapper = new ObjectMapper();

  @Override
  public Order mapRow(ResultSet rs, int rowNum) throws SQLException{

     List<Features> features = objectMapper
          .readValue(rs.getString("features"), 
                     new TypeReference<List<Features>>(){});
     Order order = new Order();
     order.setOrderid(rs.getInt("orderid"));
     order.setFeatures()// how to read jsonbcolumn?
  }
}

JDBCTempalte RowMapper to read jsonb column in postgres table

1 Answers1