jdbc连接数据库步骤_JDBC –模拟序列

jdbc连接数据库步骤

也许我们每个人在程序员的生命中至少遇到过一次这个问题-如何模拟数据库序列？ 在下面，您可能会发现我对该问题解决方案的一些了解。

假设我们有一个接口定义了用于返回整数序列的所需API：

 public interface Sequences { 
    int nextValue(String sequenceName) throws SQLException;
 }

并以以下形式实现此API：

 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)); "[%d] - commit" , threadId));

                connection.commit();

            }

        }

    }
 }

您必须原谅我两件事：) –我添加println的用法是为了产生一些视觉反馈；)并且缺少详细的解释说明此解决方案的工作原理；)我只想提一下线索是准备好的语句的处理方式创建并处理结果集：updateRow / moveToInsertRow / insertRow用法；)(有关详细信息，请参见本文底部的链接)。

我编写了简单的测试用例来观察和验证此代码，例如：

 @Autowired
 private Sequences sequences;
 private Callable<Integer> callable() {

    return () -> {

        System.out.println(String.format( "[%d] - starting" , Thread.currentThread().getId())); "[%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());

    }
 }

运行上述代码时，输​​出将如下所示(括号中为线程的ID)：[16] –开始[18] –开始[17] –开始[17] –选择要更新[17] –下一个值：1[17] –提交[18] –选择要更新结果1-1[18] –下一个值：2[18] –提交[16] –选择要更新[16] –下一个值：3[16] –提交结果2 – 2结果3 – 3

这段代码仅用于演示目的：) –如果您想在项目中执行类似的操作，则可能更希望将其用于ex。 Spring Framework的@Transactional批注，而不是手动的事务处理，甚至JPA都将这项工作委托给JDBC。 例如，在Hibernate中，您可以这样进行操作：

 import org.hibernate.Session;
 ...
 entityManager.unwrap(Session. class )

                      .doReturningWork(connection -> { ... code derived from my example ... });

甜点的几个链接：

• 更新ResultSet对象(JDBC)中的行
• 在ResultSet对象(JDBC)中插入行
• 声明式事务管理和使用@Transactional (Spring Framework)
• ReturningWork(JPA，Hibernate)

…，我差点忘了;)– GitHub存储库保存了我所有的这篇文章的代码经验

翻译自: https://www.javacodegeeks.com/2019/08/jdbc-emulating-sequence.html

jdbc连接数据库步骤