JDBC: Emulating a Sequence

Each of us has probably encountered this problem at least once in our programming life — How do I emulate a database sequence? Below, you may find my variation of this problem's solution.

Suppose that we have an interface defining the desired API for returning a sequence of integer numbers:

public interface Sequences {

    int nextValue(String sequenceName) throws SQLException;

}

and the implementation of this API in the following form:

class SequencesService implements Sequences {

    private static final String SQL_QUERY =
        "SELECT SEQ_NAME, SEQ_VALUE FROM SEQUENCE WHERE SEQ_NAME = ? FOR UPDATE";

    private final DataSource dataSource;

    SequencesService(final DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Override
public int nextValue(final String sequenceName) throws SQLException {
        final long threadId = Thread.currentThread().getId();

        try (final Connection connection = dataSource.getConnection()) {
            connection.setAutoCommit(false);
            try (final PreparedStatement statement =
                     connection.prepareStatement(
                         SQL_QUERY, TYPE_SCROLL_SENSITIVE, CONCUR_UPDATABLE)) {
                statement.setString(1, sequenceName);
                try (final ResultSet resultSet = statement.executeQuery()) {
                    System.out.println(
                        String.format("[%d] - select for update", threadId));
                    int nextValue = 1;
                    if (resultSet.next()) {
                        nextValue = 1 + resultSet.getInt(2);
                        resultSet.updateInt(2, nextValue);
                        resultSet.updateRow();
                    } else {
                        resultSet.moveToInsertRow();
                        resultSet.updateString(1, sequenceName);
                        resultSet.updateInt(2, nextValue);
                        resultSet.insertRow();
                    }
                    System.out.println(
                        String.format("[%d] - next val: %d", threadId, nextValue));
                    return nextValue;
                }
            } finally {
                System.out.println(String.format("[%d] - commit", threadId));
                connection.commit();
            }
        }
    }

}

You have to forgive me for two things :) — the println usage, which I added for generating some visual feedback...and a lack of detailed explanation on how this solution works. I'll just mention that the clue is the way a prepared statement is created and the result set handling: updateRow/moveToInsertRow/insertRow usage (see the links at the bottom of this post for the details).

I wrote a simple test case to observe and verify this code. Something like:

@Autowired
private Sequences sequences;

private Callable<Integer> callable() {
    return () -> {
        System.out.println(String.format("[%d] - starting", Thread.currentThread().getId()));
        return  sequences.nextValue("My Sequence");
    };
}

@Test
public void test() throws Exception {
    final ExecutorService executor = Executors.newFixedThreadPool(3);
    final CompletionService<Integer> completion = new ExecutorCompletionService<>(executor);

    for (int i = 0; i < 3; i++) {
        completion.submit(callable());
    }

    for (int completed = 1; completed <= 3; completed++) {
        final Future<Integer> result = completion.take();
        System.out.println(String.format("Result %d - %d", completed, result.get()));
        assertEquals(Integer.valueOf(completed), result.get());
    }
}

When run, the output will be something like this (threads' IDs in the brackets):
[16] - starting
[18] - starting
[17] - starting
[17] - select for update
[17] - next val: 1
[17] - commit
[18] - select for update
Result 1 - 1
[18] - next val: 2
[18] - commit
[16] - select for update
[16] - next val: 3
[16] - commit
Result 2 - 2
Result 3 - 3

This code is just for demonstration purposes. If you want to do something similar in your project, it's probable that you would rather use it for Spring Framework's @Transactional annotation instead of manual transactions handling or even JPA delegating this work to JDBC. For example, in Hibernate, you may do it somehow like this:

import org.hibernate.Session;
...

entityManager.unwrap(Session.class)
                      .doReturningWork(connection -> { ... code derived from my example ... });

• Updating Rows in ResultSet Objects (JDBC)
• Inserting Rows in ResultSet Objects (JDBC)
• Declarative transaction management and Using @Transactional (Spring Framework)
• ReturningWork (JPA, Hibernate)

Here is the GitHub repository holding all my code experiments for this post.

Thanks for reading!