How to rewrite jdbcTemplate query using JDK8 features?

Question

Here's the code. Let's say there are many large images in the db so I want to keep only one in memory at a time. How to write this using JDK 8 features like lambda and streams?

I started using those but it kept failing ( from https://spring.io/guides/gs/relational-data-access/ and www.jooq.org/java-8-and-sql) and using ResultSetExtractor is working as intendent, but is there a way to do it without ResultSetExtractor?

 jdbcTemplate.query( 
            "select id,image,mimetype from images",
            new ResultSetExtractor(){  
                @Override  
                public List extractData(ResultSet rs) throws SQLException, DataAccessException {  
                        while(rs.next()){  
                           createThumbnail( new ImageHolder(rs.getInt("id"), rs.getBytes("image"), rs.getString("mimetype") ));
                        }  
                        return null;
                    }  
            }
    );

This is nice looking stream/lambda version which works but holds too many things in memory and gives OOME sooner or later.

 jdbcTemplate.query( 
             "select id,image,mimetype from images",
             (rs,rowNum)->new ImageHolder(rs.getInt("id"), rs.getBytes("image"),rs.getString("mimetype"))
    ).stream().forEach(   
                   imageHolder -> createThumbnail(imageHolder)
    ) );

This just loads all the rows before it starts to "streaming" them.

score 2 · Answer 1 · answered Jul 28 '16 at 10:00

2

A solution could be to move the generation of the thumbnail in the first lambda, translating directly your first example.

jdbcTemplate.query( 
         "select id,image,mimetype from images",
         (rs,rowNum)-> {
             createThumbnail(new ImageHolder(
                rs.getInt("id"),
                rs.getBytes("image"),
                rs.getString("mimetype") ));
         })
)

To have a structure similar to the one you tried to achieve the query method should return a stream so you don't have to collect everything before streaming.

answered Jul 28 '16 at 10:00

carlo-colombo

136
5

Yes, good solution, missed this one. I'll have test if it actually works ;) Somehow I kept believing that query is returning stream(able) thing, but obvisouly it is not. – ljack Jul 28 '16 at 11:16
ops, `Caused by: org.postgresql.util.PSQLException: Ran out of memory retrieving query results.` – ljack Jul 28 '16 at 11:46

score 1 · Answer 2 · edited May 23 '17 at 12:14

You could limit your JDBC Statement.setFetchSize() to something more reasonable than the default (see also some hints here).

An alternative is to manually paginate as follows:

int size = 10;
jdbcTemplate.query(

    // This query creates chunks of 10 and shows the low/high id value in each chunk
    "select id1, id2 "
  + "from ( "
  + "  select "
  + "    id AS id1, "
  + "    lead(id, ?) over (order by id) AS id2, "
  + "    row_number() over (order by id) rn from images) i "
  + "  from images "
  + ") i    
  + "where rn % ? = 0",
    new Object[] { size, size },
    rs1 -> {
        while (rs1.next())
            jdbcTemplate.query(
                // This query fetches only images for each chunk
                "select id, image, mimetype from images "
              + "where id >= ? and id < coalesce(?, 2147483647)",
                new Object[] { rs1.getInt(1), rs1.getInt(2) },
                rs2 -> {
                    createThumbnail(new ImageHolder(
                        rs2.getInt("id"), 
                        rs2.getBytes("image"), 
                        rs2.getString("mimetype") 
                    ));
                    return null;
                }
            );

        return null;
    }
);

How to rewrite jdbcTemplate query using JDK8 features?

2 Answers2