How to implement RowMapper in jdbc template

0
votes

I have a query that It gives the following output:

State  CST QTY
aa      0   10
aa      1   20
aa      2   30
aa      3   40
ac      0   10
ac      1   20
ac      2   30
ac      3   40

I am using in spring mvc with jdbc template: While Implementing RowMapper I am getting confused which data Structure need to use and how to map the each row to get the following output following EXPECTED O/P:

STATE CST0 CST CST2 CST3 
aa      10  20  30   40
ac      10  20  30   40

Can any one help me?

2
javaspringspring-mvcjdbcspring-jdbc
This is a table pivot issue, you can do this in sql query. - Blank
without changing query its not possible? - ASR
Not if you persist on using a RowMapper... But why make it more complex as needed, the database is very capable of doing that and is probably more efficient that transferring a lot of data to your java application and transform it with your application. - M. Deinum
I have no option to modify query, I should do it with java only - ASR

2 Answers

1
votes

If you are using the Google Guava library, you can use a new collection called Multimap. A multimap is mapping from unique keys to a collection of values : Map<K, List<V>> or Map<K, Set<V>>.

Instead of using a RowMapper, I would rather use the interface RowCallbackHandler which is a similar interface for processing resultset :

public class MyRowMapper implements RowCallbackHandler{

   private Multimap<String, Integer> stateByQuantity = new ArrayListMultimap();

    void processRow(ResultSet rs) throws SQLException {
      String state = rs.getString("State");
      Integer qty = rs.getInt("QTY");
      stateByQuantity.put(state, qty);          
    }
}
0
votes

I have used the following snippet for my requirement :

List<Map<String, Object>> resultMapList= new ArrayList<Map<String, Object>>();

      Map<String, List<Map<String, Integer>>>  listMap= new TreeMap<String, List<Map<String, Integer>>>();

      for(Map<String, Object> mapList:resultMapList){
          List< Map<String, Integer>> ListMapPair= new ArrayList<Map<String, Integer>>();
          Map<String, Integer> entry= new HashMap<String, Integer>();
      }

      Map<String, List<Object>> m= new HashMap<String, List<Object>>();

      List<Object> list= new ArrayList<Object>();
      list.add(1);
      list.add(2);
      list.add(3);
      m.put("a", list);

      List<Object> list1= new ArrayList<Object>();
      list1.add(4);
      list1.add(5);
      list1.add(6);
      m.put("b", list1);

      m.put("c", list);

      System.out.println(m);

Output would be like :

{a=[1, 2, 3], b=[4, 5, 6], c=[1, 2, 3]}