Using a query inside RowMapper

Question

In java i would do something like below to iterate the resultset and form the query,

public Map<String, List<MODEL>> fun(){
Map<String, List<MODEL>> map = new TreeMap<String, List<MODEL>>();      
        LinkedHashSet<String> set = new LinkedHashSet<String>();

            String sql = "select distinct(column) from table where conditions orderby column ";
            ResultSet rslt = stmt.executeQuery(sql);
            while (rslt.next()) {
                al.add(rslt.getString(1));
            }
            for (String s : al) {           
                List<MODEL> list = new ArrayList<MODEL>();
                String sql2 = "select * from table where column="+s;
                ResultSet rslt2 = stmt.executeQuery(sql2);
                while (rslt2.next()) {
                    MODEL obj = new MODEL();
                    // set values to setters from resultset
                    list.add(obj);
                }
                map.put(s, list);
            }
            return map;
            }

the reason why i have used the separate query is , i am adding the distinct values to the map key and their corresponding values(as List) to the values of the map .NOTE (In the result it has duplicate values of the column1) , But i need to store them as map key and hence making it as unique . also i need all its associated values so populating a list

How can i achieve the same functionality using JdbcTemplate ,

Thanks in advance

Answer 1

As stated by multiple people your solution isn't efficient although it might work you are basically falling into the trap of the 1+N select problem. 1 query to retrieve some id and next for each id another query (hence 1+N select).

It would be better to just write a single query which retrieves everything in one go. Looking at your code you have the following 2 queries which, by the looks of it, operate on the same table.

String sql1 = "select distinct(column) from table where conditions orderby column ";
String sql2 = "select * from table where column="+s

Now you can either make the first query a subselect for the second query

String sql = "select * from table where column in (select distinct(column) from table where conditions) order by column";

However, again from the looks of it, it would also be possible (or maybe even easier) to just put the where clause in your second query.

String sql = "select * from table where conditions order by column";

You will now, possibly, get for your key (in the Map) get multiple values. You can do two things either use a ResultSetExtractor loop over the results yourself or use a RowCallbackHandler which does the iteration for you.

What you need to do is just iterate of the results (or let the JdbcTemplate do that for you) for you key column check the Map if a List is already there. If it is add a row if it isn't first create the List and add it to the result Map.

public Map<String, List<MODEL>> fun(){
    final Map<String, List<MODEL>> map = new TreeMap<String, List<MODEL>>();      

    String sql = "select * from table where conditions order by column";
    getJdbcTemplate().query(sql, new RowCallbackHandler() {
        public void processRow(ResultSet resultSet) throws SQLException {
            String key = rs.getString("column");
            List rows = map.get(key);
            if (rows == null) {
                rows = new new ArrayList<MODEL>();
                map.put(key, rows);
            }
             MODEL obj = new MODEL();
            // set values to setters from resultset
            rows.add(obj);
        }, "arguments"); 
    return map;
}

Another solution, which would only work if the 'column' is also part of the MODEL class. That way you could use a RowMapper and get a List of all MODEL objects and do the partitioning afterwards. You could use Google Guava to make it a little easier to do that or if you are on Java 8 you could use the new streams api for that.

Answer 2

As @geoand said, the best thing (whether in straight JDBC code or using JDBCTemplate) would be to do a single query with a Join.

If, however, as you mentioned in the comments, you are absolutely certain that the result set from the first query will always be small (doubtful), then you could store the values retrieved from the first query in a list, and then do a second query with an "in" clause, using the list as a parameter. This would result in two queries.

In any case, you really don't want to do a separate query for each item returned from the first query, as this will be quite slow, even for small numbers of items. But, if you feel that you must, then store the results of the first query in a list, and then issue another query for each item in the list.

TLDR; No, JDBCTemplate does not provide a mechanism for doing what you want, mostly because it's generally considered to be a canonical example of a programming anti-pattern.