JDBC - Emulating a sequence

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 ... });

Few links for the dessert:

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

... and I almost forgot ;) - GitHub repository holding all my code expriments for this post

---

Follow-ups:

 

This article has been republished on DZone's Database Zone (08/21/2019).