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)
