Home » Java » Core Java » JDBC – Emulating a sequence

About Michal Jastak

Michal Jastak
Michał is a Chief Technology Officer in Java Division of AIS.PL, company developing mostly Web Applications of different kind, usually e-Government related.

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:

1
2
3
4
5
public interface Sequences {
 
    int nextValue(String sequenceName) throws SQLException;
 
}

and the implementation of this API in the following form:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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:

01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
@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:

1
2
3
4
5
import org.hibernate.Session;
...
 
entityManager.unwrap(Session.class)
                      .doReturningWork(connection -> { ... code derived from my example ... });

Few links for the dessert:

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

Published on Java Code Geeks with permission by Michal Jastak, partner at our JCG program. See the original article here: JDBC – Emulating a sequence

Opinions expressed by Java Code Geeks contributors are their own.

(0 rating, 0 votes)
You need to be a registered member to rate this.
Start the discussion Views Tweet it!
Do you want to know how to develop your skillset to become a Java Rockstar?
Subscribe to our newsletter to start Rocking right now!
To get you started we give you our best selling eBooks for FREE!
1. JPA Mini Book
2. JVM Troubleshooting Guide
3. JUnit Tutorial for Unit Testing
4. Java Annotations Tutorial
5. Java Interview Questions
6. Spring Interview Questions
7. Android UI Design
and many more ....
I agree to the Terms and Privacy Policy
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments