Using RowMapper to return Map<String,Map<String,List<Object>>>

Question

Each row in my DB is going to be like this

School  Class   StudentID   Name    Age
123      8        811       John    10
123      8        812       Smith   11
123      8        821       John    12
123      8        822       Smith   13
123      9        911       John    14
123      9        912       Smith   15
456      8        811       John    10
456      8        812       Smith   11

Now I want to return the above data like this

Map<String,Map<String,List<Object>>>

ie. Map<School,Map<Class,List<StudentID>>>

I am using Spring's JdbcTemplate with a RowMapper but all I can get is an List of all the rows. Since I wont be able to maintain my main Map in my RowMapper (can I have a static map inside the RowMapper class ?? but I do not want it to be static as this is a webservice and subsequent calls might get screwed.), I only have the option to get this Row list and then have my logic to create the Collection that I want.

I came to know about ResultSetExtractor but I could not find how to get this done using that.

Please let me know if there is any way I can get this done using a row mapper or some sort. Or my only way to solve this is my having to iterate the list again and get what I want.

EDIT: My query is a select query like this:

SELECT * from TABLENAME

and it actually takes 40 minutes to retrieve the ResultSet from Java.

Since the DB call is for filling the cache object which is done using CRON every 4 hours, I don't mind the time delay of 40 minutes since the actual REST calls, will be referring to the cache object. But still I want to do this as efficiently as possible.

Also please let me know if there is any other way I can handle this scenario. A normal jdbc call for every REST call wll be costly,since we will be getting 100's of requests per second.

I assume you have a DAO class for each of your entities? Why not let your DAO do the transformation of the List to the Map and return it to the outside world? — CKing
The jdbcTemplate call is inside the DAO, I want to know if this can be done using RowMapper. — v1shnu
So you basically want the JdbcTemplate to form the Map for you when you execute a query with it? I believe any solution you implement would eventually just be transforming a List to a Map internally so why not let your DAO do it? — CKing
yes, that's exactly what I want. But is there a way to do it or should I do it on my own from the List obtained from jdbcTemplate — v1shnu
I guess there should be a way to do it. But the question is what is your motivation behind doing it? — CKing

Alexander Alexander · Accepted Answer · 2017-02-03T15:11:45

RowMapper

You could implement a state-full RowMapper that solves your problem, but it is not really conform with the idea of the RowMapper which is thought to be stateless and reusable like stated in the JavaDoc:

... RowMapper objects are typically stateless and thus reusable ...

The RowMapperis used for row to object conversion, which means each row of the ResultSet is getting an own Object.

ResultSetExtractor

It is really the ResultSetExtractor that fits your task. The ResultSetExtractor is made for ResultSet to Object conversion as you intend to do.

Just implement filling the Map there by iterating the ResultSet and pass an instance of the ResultSetExtractor implementation to your JdbcTemplate.query(...) call.

Update 2017-02-09

While following the comment that an example using ResultSetExtractor would be useful, I stumbled upon the RowCallbackHandler interface. While both options allow you to realize the wanted behavior, the RowCallbackHandler handles the ResultSet iteration for you. It can be used via JdbcTemplate.query(...) call as well.

For examples of both RowCallbackHandler and ResultSetExtractor I recommend the following article Ram Satish: http://www.javarticles.com//2015/02/example-of-spring-callbacks-used-in-jdbctemplate.html

Using RowMapper to return Map<String,Map<String,List<Object>>>

1 Answers