DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

SBOMs are essential to circumventing software supply chain attacks, and they provide visibility into various software components.

Related

  • Querydsl vs. JPA Criteria, Part 6: Upgrade Guide To Spring Boot 3.2 for Spring Data JPA and Querydsl Project
  • A Guide to Enhanced Debugging and Record-Keeping
  • Upgrade Guide To Spring Boot 3.0 for Spring Data JPA and Querydsl
  • How To Build Self-Hosted RSS Feed Reader Using Spring Boot and Redis

Trending

  • *You* Can Shape Trend Reports: Join DZone's Data Engineering Research
  • Serverless Machine Learning: Running AI Models Without Managing Infrastructure
  • Jakarta EE 11 and the Road Ahead With Jakarta EE 12
  • How to Embed SAP Analytics Cloud (SAC) Stories Into Fiori Launchpad for Real-Time Insights
  1. DZone
  2. Coding
  3. Frameworks
  4. JDBC: Emulating a Sequence

JDBC: Emulating a Sequence

See how to emulate a sequence.

By 
Michal Jastak user avatar
Michal Jastak
·
Aug. 21, 19 · Tutorial
Likes (1)
Comment
Save
Tweet
Share
10.0K Views

Join the DZone community and get the full member experience.

Join For Free

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 ... });
More details:
  • 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!

Spring Framework Test case Prepared statement Database Commit (data management) Hibernate POST (HTTP) Framework

Published at DZone with permission of Michal Jastak, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Querydsl vs. JPA Criteria, Part 6: Upgrade Guide To Spring Boot 3.2 for Spring Data JPA and Querydsl Project
  • A Guide to Enhanced Debugging and Record-Keeping
  • Upgrade Guide To Spring Boot 3.0 for Spring Data JPA and Querydsl
  • How To Build Self-Hosted RSS Feed Reader Using Spring Boot and Redis

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: