JDBC – Emulating a sequence

Michal JastakAugust 9th, 2019Last Updated: August 7th, 2019

0 99 3 minutes read

Probably each of us encountered this problem at least once in the programmer’s life – how to 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 two things :) – the println usage, which I added for generating some visual feedback ;) and a lack of detailed explanation how this solution works ;) I’ll just mention that the clue is the way 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 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 above code, 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 will rather use for ex. 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 ... });