Oracle 自动增量功能:触发器还是 11.2 中的 Oracle JDBC CallableStatement? [英] Oracle Autoincrement Functionality: Triggers or Oracle JDBC CallableStatement in 11.2?
问题描述
当您需要使用 JDBC 检索新生成的键时，在 Oracle (11.2) 中实现自动增量功能的最佳方法(就插入性能而言)是什么?
Which is the best way (in terms of insert performance) to implement autoincrement functionality in Oracle (11.2) when you need to retrieved the newly generated key using JDBC?
我知道 Oracle 12 中有标识列，但我现在坚持使用 11.2.
I know there are identity columns in Oracle 12, but I'm stuck with 11.2 right now.
像许多其他人一样，我没有运气让 JDBC getGeneratedKeys() 与 Oracle 一起工作.我最终在我的 Oracle (11.2) 数据库中有触发器，它的作用类似于 MySQL 自动增量函数，并从表特定序列中插入 NextVal 作为其主键，只要有插入到该表中.这使得获取新插入的密钥变得困难，但我最终进行了第二次查询以获取新生成的密钥.
Like many others, I have had no luck in getting the JDBC getGeneratedKeys() to work with Oracle. I ended up having trigger in my Oracle (11.2) database that acts like a MySQL autoincrement function and inserts the NextVal from a table specific sequence to act as its primary key whenever there is an insert into that table. This made getting the newly inserted key difficult though, I ended up doing a 2nd query to get the newly generated key.
最近，我发现了带有返回值的 CallableStatement，并且我看到了如何使用这些返回值来完成 1 次调用的所有操作.
More recently I discovered CallableStatement with return values and I see how these could be used to do everything with 1 call.
当您还需要新生成的密钥时，这最后一种方法通常是一种更快的插入方式，还是我缺少更好的选择?
Is this last method generally a faster way of doing inserts when you also need that newly generated key or is there a better option for me that I am missing?
推荐答案
我在我的迷你基准测试中有一个有趣的结果，并决定分享它.
I've got interesing result in my mini-benchmark and decide to share it.
测试代码:
import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.Assert;
import org.springframework.util.StopWatch;
import java.sql.*;
public class TriggerPerformanceTest {
private static final int STEPS_COUNT = 1000;
public static void main(String[] args) throws SQLException {
final Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@host:1521/oradev", "user", "pass");
prepare(connection);
final StopWatch stopWatch = new StopWatch("mini-bench");
testTrigger(connection, stopWatch);
testSequence(connection, stopWatch);
testSeparateCalls(connection, stopWatch);
JdbcUtils.closeConnection(connection);
System.out.println(stopWatch.prettyPrint());
}
private static void testTrigger(Connection connection, StopWatch stopWatch) throws SQLException {
final PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test_table_trigger (text) VALUES (?)", new String[]{"ID"});
stopWatch.start("with trigger");
for (int i = 0; i < STEPS_COUNT; i++) {
preparedStatement.setString(1, "test");
preparedStatement.executeUpdate();
final ResultSet resultSet = preparedStatement.getGeneratedKeys();
final boolean next = resultSet.next();
Assert.state(next, "Expected not empty result set with generated keys");
final long id = resultSet.getLong(1);
Assert.state(id > 0, "Expected generated key value");
JdbcUtils.closeResultSet(resultSet);
}
stopWatch.stop();
JdbcUtils.closeStatement(preparedStatement);
}
private static void testSequence(Connection connection, StopWatch stopWatch) throws SQLException {
final PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test_table_sequence (id, text) VALUES (sq_test2.NEXTVAL, ?)", new String[]{"ID"});
stopWatch.start("without trigger");
for (int i = 0; i < STEPS_COUNT; i++) {
preparedStatement.setString(1, "test");
preparedStatement.executeUpdate();
final ResultSet resultSet = preparedStatement.getGeneratedKeys();
final boolean next = resultSet.next();
Assert.state(next, "Expected not empty result set with generated keys");
final long id = resultSet.getLong(1);
Assert.state(id > 0, "Expected generated key value");
JdbcUtils.closeResultSet(resultSet);
}
stopWatch.stop();
JdbcUtils.closeStatement(preparedStatement);
}
private static void testSeparateCalls(Connection connection, StopWatch stopWatch) throws SQLException {
final PreparedStatement preparedStatementSeq = connection.prepareStatement("SELECT sq_test3.NEXTVAL FROM dual");
final PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test_table_generated (id, text) VALUES (?, ?)");
stopWatch.start("separate calls");
for (int i = 0; i < STEPS_COUNT; i++) {
final ResultSet resultSet = preparedStatementSeq.executeQuery();
resultSet.next();
final long id = resultSet.getLong(1);
JdbcUtils.closeResultSet(resultSet);
preparedStatement.setLong(1, id);
preparedStatement.setString(2, "test");
preparedStatement.executeUpdate();
}
stopWatch.stop();
JdbcUtils.closeStatement(preparedStatementSeq);
JdbcUtils.closeStatement(preparedStatement);
}
private static void prepare(Connection connection) throws SQLException {
Statement statement = connection.createStatement();
try {
statement.execute("DROP TABLE test_table_sequence");
statement.execute("DROP TABLE test_table_trigger");
statement.execute("DROP TABLE test_table_generated");
statement.execute("DROP SEQUENCE sq_test1");
statement.execute("DROP SEQUENCE sq_test2");
statement.execute("DROP SEQUENCE sq_test3");
} catch (SQLException sqle) {
//ignore
}
try {
statement.execute("CREATE TABLE test_table_sequence (id NUMBER, text VARCHAR2(10))");
statement.execute("CREATE TABLE test_table_trigger (id NUMBER, text VARCHAR2(10))");
statement.execute("CREATE TABLE test_table_generated (id NUMBER, text VARCHAR2(10))");
statement.execute("CREATE SEQUENCE sq_test1 START WITH 1 INCREMENT BY 1 CACHE 20");
statement.execute("CREATE SEQUENCE sq_test2 START WITH 1 INCREMENT BY 1 CACHE 20");
statement.execute("CREATE SEQUENCE sq_test3 START WITH 1 INCREMENT BY 1 CACHE 20");
statement.execute("CREATE OR REPLACE TRIGGER trg_increment BEFORE INSERT ON test_table_trigger FOR EACH ROW
" +
"BEGIN
" +
" SELECT sq_test1.NEXTVAL INTO :new.id FROM dual;
" +
"END;");
} catch (SQLException sqle) {
sqle.printStackTrace();
}
try {
statement.execute("TRUNCATE TABLE test_table_sequence");
statement.execute("TRUNCATE TABLE test_table_trigger");
statement.execute("TRUNCATE TABLE test_table_generated");
} catch (SQLException sqle) {
sqle.printStackTrace();
}
}
}
输出:
StopWatch 'mini-bench': running time (millis) = 27430
-----------------------------------------
ms % Task name
-----------------------------------------
09214 034% with trigger
08916 033% without trigger
09300 034% separate calls
结论:差异很小......考虑到它.
The conclusion: the difference quite small... to take it into consideration.
附注.专用 Oracle 11.2.0.4，LAN 1Gb/s，Java 1.7.0_65.
PS. Dedicated Oracle 11.2.0.4, LAN 1Gb/s, Java 1.7.0_65.
这篇关于Oracle 自动增量功能:触发器还是 11.2 中的 Oracle JDBC CallableStatement?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
