也许我们每个人在程序员的生命中至少遇到过一次这个问题- 如何模拟数据库序列? 在下面,您可以找到我对这个问题的解决方案的变形。
假设我们有一个接口定义了所需的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,休眠)
…,我差点忘了;)– GitHub存储库保存了我所有的这篇文章的代码经验
翻译自: https://www.javacodegeeks.com/2019/08/jdbc-emulating-sequence.html