Handling large ResultSet with jdbcTemplate

Problem I have now is that the stored procedure called by the jdbcTemplate returns large amount of records, million records, which make our java query method very slow.

My java query method does the paging of the results so it is very slow. How can I improve this without altering the DB stored procedure. That is, to query for specific rows only so the java method would not have the burden of processing million records to do the paging.

public PageModel<Map<String, String>> query(String sql, final int offset, final int limit) throws ReportException {
    try {
        return jdbcTemplate.query(sql, new ResultSetExtractor<PageModel<Map<String, String>>>() {
            @Override
            public PageModel<Map<String, String>> extractData(ResultSet rs)
                    throws SQLException, DataAccessException {
                long startTime = System.nanoTime();
                PageModel<Map<String, String>> pageModel  = new PageModel<Map<String,String>>();
                List<Map<String, String>> list = new ArrayList<Map<String,String>>();
                int rows = 0;
                // skip rows
                for (int i=0; i<offset&&rs.next(); i++) {
                    rows++;
                }
                // get rows
                for (int i=0; i<limit&&rs.next(); i++) {
                    Map<String, String> map = new HashMap<String, String>();
                    ResultSetMetaData metadata = rs.getMetaData();
                    int count = metadata.getColumnCount();
                    for (int j=1; j<=count; j++) {
                        map.put(metadata.getColumnName(j), rs.getString(j));
                    }
                    list.add(map);
                    rows++;
                }
                // iterate remaining rows to get total rows
                while (rs.next())
                    rows++;
                pageModel.setOffset(offset);
                pageModel.setLimit(limit);
                pageModel.setData(list);
                pageModel.setTotal(rows);
                long endTime = System.nanoTime();
                long duration = endTime - startTime;
                System.out.println("Query took: " + duration);
                return pageModel;
            }
        });
    } catch (DataAccessException e) {
        throw new ReportException(e);
    }
}

Answers


How can I improve this without altering the DB stored procedure.

There is no any solution in your case, only altering SP


Need Your Help

Programmatically importing an existing project into Eclipse

java eclipse import eclipse-plugin

I am trying to importing the project to eclipse through programmatically. I dont want to use UI mode.

Problem with D3D & COM

c++ com visual-c++ direct3d iunknown

all the D3D interfaces are derived from COM's IUnknown interface, so I though I'd take an easy route for releasing D3D objects and use something like this:

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.