Big Data/Analytics Zone is brought to you in partnership with:

I'm a Software Developer with over a decade's worth of experience in the IT Industry. While primarily a Java Developer I've working in - among other things - everything from C to Visual Basic to JavaScript to Ruby to Objective-C to C#. I officially consider myself a member of the cult of Lisp which tends to colour the way I think about how code should be written Julian is a DZone MVB and is not an employee of DZone and has posted 11 posts at DZone. View Full User Profile

Adding Java 8 Lambda Goodness to JDBC

12.05.2013
| 13929 views |
  • submit to reddit

Data access, specifically SQL access from within Java, has never been nice. This is in large part due to the fact that the JDBC api has a lot of ceremony.

Java 7 vastly improved things with ARM blocks by taking away a lot of the ceremony around managing database objects such as Statements and ResultSets but fundamentally the code flow is still the same.

Java 8 Lambdas gives us a very nice tool for improving the flow of JDBC.

Out first attempt at improving things here is very simply to make it easy to work with ajava.sql.ResultSet.

Here we simply wrap the ResultSet iteration and then delegate it to Lambda function.

This is very similar in concept to Spring's JDBCTemplate.

NOTE: I've released All the code snippets you see here under an Apache 2.0 license on Github.

First we create a functional interface called ResultSetProcessor as follows:

@FunctionalInterface
public interface ResultSetProcessor {

    public void process(ResultSet resultSet, 
                        long currentRow) 
                        throws SQLException;

}

Very straightforward. This interface takes the ResultSet and the current row of theResultSet as a parameter.

Next we write a simple utility to which executes a query and then calls ourResultSetProcessor each time we iterate over the ResultSet:

public static void select(Connection connection, 
                          String sql, 
                          ResultSetProcessor processor, 
                          Object... params) {
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            int cnt = 0;
            for (Object param : params) {
                ps.setObject(++cnt, param));
            }
            try (ResultSet rs = ps.executeQuery()) {
                long rowCnt = 0;
                while (rs.next()) {
                    processor.process(rs, rowCnt++);
                }
            } catch (SQLException e) {
                throw new DataAccessException(e);
            }
        } catch (SQLException e) {
            throw new DataAccessException(e);
        }
}

Note I've wrapped the SQLException in my own unchecked DataAccessException.

Now when we write a query it's as simple as calling the select method with a connection and a query:
select(connection, "select * from MY_TABLE",(rs, cnt)-> {        
 	System.out.println(rs.getInt(1)+" "+cnt)
});

So that's great, but I think we can do more...

One of the nifty Lambda additions in Java is the new Streams API. This would allow us to add very powerful functionality with which to process a ResultSet.

Using the Streams API over a ResultSet however creates a bit more of a challenge than the simple select with Lambda in the previous example.

The way I decided to go about this is create my own Tuple type which represents a single row from a ResultSet.

My Tuple here is the relational version where a Tuple is a collection of elements where each element is identified by an attribute, basically a collection of key value pairs. In our case the Tuple is ordered in terms of the order of the columns in the ResultSet.

The code for the Tuple ended up being quite a bit so if you want to take a look, see the GitHub project in the resources at the end of the post.

Currently the Java 8 API provides the java.util.stream.StreamSupport object which provides a set of static methods for creating instances of java.util.stream.Stream. We can use this object to create an instance of a Stream.

But in order to create a Stream it needs an instance ofjava.util.stream.Spliterator. This is a specialised type for iterating and partitioning a sequence of elements, the Stream needs for handling operations in parallel.

Fortunately the Java 8 api also provides the java.util.stream.Spliterators class which can wrap existing Collection and enumeration types. One of those types being ajava.util.Iterator.

Now we wrap a query and ResultSet in an Iterator:

public class ResultSetIterator implements Iterator {

    private ResultSet rs;
    private PreparedStatement ps;
    private Connection connection;
    private String sql;

    public ResultSetIterator(Connection connection, String sql) {
        assert connection != null;
        assert sql != null;
        this.connection = connection;
        this.sql = sql;
    }

    public void init() {
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();

        } catch (SQLException e) {
            close();
            throw new DataAccessException(e);
        }
    }

    @Override
    public boolean hasNext() {
        if (ps == null) {
            init();
        }
        try {
            boolean hasMore = rs.next();
            if (!hasMore) {
                close();
            }
            return hasMore;
        } catch (SQLException e) {
            close();
            throw new DataAccessException(e);
        }

    }

    private void close() {
        try {
            rs.close();
            try {
                ps.close();
            } catch (SQLException e) {
                //nothing we can do here
            }
        } catch (SQLException e) {
            //nothing we can do here
        }
    }

    @Override
    public Tuple next() {
        try {
            return SQL.rowAsTuple(sql, rs);
        } catch (DataAccessException e) {
            close();
            throw e;
        }
    }
}

This class basically delegates the iterator methods to the underlying result set and then on the next() call transforms the current row in the ResultSet into my Tuple type.

And that's the basics done (This class will need a little bit more work though). All that's left is to wire it all together to make a Stream object. Note that due to the nature of a ResultSet it's not a good idea to try process them in parallel, so our stream cannot process in parallel.

public static Stream stream(final Connection connection, 
                                       final String sql, 
                                       final Object... parms) {
  return StreamSupport
                .stream(Spliterators.spliteratorUnknownSize(
                        new ResultSetIterator(connection, sql), 0), false);
}

Now it's straightforward to stream a query. In the usage example below I've got a table TEST_TABLE with an integer column TEST_ID which basically filters out all the non even numbers and then runs a count:

     long result = stream(connection, "select TEST_ID from TEST_TABLE")
                .filter((t) -> t.asInt("TEST_ID") % 2 == 0)
                .limit(100)
                .count();

And that's it! We now have a very powerful way of working with a ResultSet.

So all this code is available under an Apache 2.0 license on GitHub here. I've rather lamely dubbed the project "lambda tuples," and the purpose really is to experiment and see where you can take Java 8 and Relational DB access, so please download or feel free to contribute.


Published at DZone with permission of Julian Exenberger, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Comments

Lukas Eder replied on Sun, 2014/03/02 - 1:11pm

Nice exercise. Note that something quite similar is already available in jOOQ  (or Commons DbUtils , Spring JdbcTemplate) as indicated in this blog post here. Example:

DSL.using(c)
   .fetch(sql)
   .map(r -> new Schema(
       r.getValue("SCHEMA_NAME", String.class),
       r.getValue("IS_DEFAULT", boolean.class)
   ))
   .forEach(System.out::println);

Julian Exenberger replied on Mon, 2014/03/03 - 2:17am in response to: Lukas Eder

Yes, it was modelled on Spring's Hibernate template as I indicated in the blog.

The more interesting thing IMHO is actually using the streams around the ResultSet



Lukas Eder replied on Tue, 2014/03/04 - 5:37am in response to: Julian Exenberger

The more interesting thing IMHO is actually using the streams around the ResultSet

Yes. Interestingly, though, even Java 8 doesn't feature your ResultSetIterator yet. That has really been missing for so long in the JDK.

I have now implemented something similar to your suggestion in jOOλ. Other ways of writing SQL in Java 8 are documented here.

Jeremy Chone replied on Sun, 2014/03/30 - 12:42pm

Nice, but I think there is a problem with this design. 

Basically, if the user does a short-circuit action which would reduce the total number of item that need to be evaluated, then, the close never get called. 

So, if the n in the limit(n) where n < total ResultSet length, or if we do a findFirst on a result set with more than one, then the resources do not get close. 

I have tried to look at the Spliterators and Iterator APIs to see if there is a work around, but unfortunately I did not find any.


Julian Exenberger replied on Mon, 2014/03/31 - 7:48am in response to: Jeremy Chone

Yes I realised that (hence the comment about the fact that the class needs more work :))

My thinking about this was to create a decorator around the stream that would be able to call an appropriate close method when a termination operation is called..


Lukas Eder replied on Mon, 2014/03/31 - 8:09am in response to: Julian Exenberger

Do note that Stream also implements AutoCloseable, so parts of the close semantics is already dictated by that contract...

Jeremy Chone replied on Mon, 2014/03/31 - 12:51pm

@LukasEder, thanks for the note about the AutoClosable. Somehow I missed it. So, @JulianExenberger's decorator approach would be the right one, and just overriding the close should work. Then, the developer can do a try(Stream s = ...){ ... }  and every should be closed correctly.

Then, the second thing that bothered me in this implementation was that the resultSet.next() was called in the Iterator hasNext, which is a semantic miss-match and could be dangerous. Perhaps, with the decorator approach would allow us to have more control.

 

Btw, I am building a j8sql lib, which will be a fluid, progresssive, lightweight ORM lib, which will be Java8 based (will be Apache v2). I am also doing a j8mapper which will a type/object converter (map to object, object to map, value to value). @JulianExenberger ResultSet stream and Transformer code have been great inspiration for both of these projects, thanks Julian.

Feel free to follow me on G+ (https://plus.google.com/+JeremyChone) to know when those will be out.

Lukas Eder replied on Mon, 2014/03/31 - 1:12pm in response to: Jeremy Chone

Then, the second thing that bothered me in this implementation was that the resultSet.next() was called in the Iterator hasNext, which is a semantic miss-match and could be dangerous. 

We do this in jOOQ. Thousands of users have built stable applications on top of that approach (probably unknowingly).

Besides, we have experimented with alternatives, i.e. using the ResultSet.isLast() and similar methods. You simply won't get proper support in all SQL dialects / JDBC drivers that way. ResultSet.next() is really the best way to implement this from a compatibility perspective.

Btw, I am building a j8sql lib, which will be a fluid, progresssive, lightweight ORM lib, which will be Java8 based (will be Apache v2). I am also doing a j8mapper which will a type/object converter (map to object, object to map, value to value). 

Interesting. Do you have any prototypes publicly available? We'll certainly feature them on our blog. On the other hand, how about contributing to jOOQ and ModelMapper instead? :-)

Jeremy Chone replied on Mon, 2014/03/31 - 2:38pm

@LukasEder, thanks for your answers.

Ok, so, for the hasNext and ResultSet.next, thanks for sharing your experience, and I will take your word for it. As long as the Stream workflow never call hasNext twice of the same item, I guess we are fine.

On j8sql vs JOOQ. First, I have been looking at JOOQ, and it is very well done, I love the create.select...where... In fact, j8sql shares quite a bit with JOOQ, like the "sql centric," "Schema aware," and "fluid" approaches. I also share the "Lib over Framework" view (or Guice over Spring) that seems to be the JOOQ team view as well. However, while I understand some of the benefits of code gen, by experience, I am not a big fan of it. In j8sql, I want to take a more progressive approach, by allowing developers to write straight SQL or higher CRUD/ORM api and get untyped (i.e. Map) or fully type object. It's a different approach, but it is by no mean saying that JOOQ is not well done, in fact, JOOQ is definitely a great source of inspiration.

Btw, this morning, I just created a simple CloseableStream which deleguates everything to the stream owner and call an additional closeable. The cool thing is that CloseableStream can be nested, so, if I get the stream from the "Statement" object, it will close the ResultSet, but if I get it from the "Connection" object, it will close the result set and then the statement. So, as long the developer use try (Stream s ...){ } then, everything will get closed correctly.

I will put the j8sql code on github soon and do a ping on this post.

On ModelMapper, I did not know about this one and at a first glance it looks very good. Going to tweet and share it with my friends. If it meet my j8sql need, I will definitely scrap my j8mapper plan and use it. It's just that I have been burned many time with Apache Common Bean stuff and even the Jackson Mapper type of solution. Also, I think that Java8 with its method reference really simplifies this whole problem space, as Julian's Transformer code shows. But I also agree that doing flexible and extensible mapper is a different level of complexity. Anyway, I will definitely look at ModelMapper.

Lukas Eder replied on Tue, 2014/04/01 - 12:53am in response to: Jeremy Chone

Just a short note on this:

As long as the Stream workflow never call hasNext twice of the same item, I guess we are fine.

You should make sure to correctly implement all contracts of Iterator. This includes being able to call hasNext() several times before calling next(). It is quite easy to implement as can be seen in jOOQ's org.jooq.impl.CursorImpl.CursorIterator.


Anyway, we'll be looking forward to seeing your progress on j8sql! Good luck with your next steps

Jeremy Chone replied on Tue, 2014/04/01 - 10:18pm

Thanks a lot, this is where we are happy to have a boolean at three states.

Lukas Eder replied on Wed, 2014/04/02 - 2:19am in response to: Jeremy Chone

Thanks a lot, this is where we are happy to have a boolean at three states.

Yes, this neat trick comes in handy every now and then. But never leak that three-valued state from the implementation.

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.