Oracle preparestatement中addBatch方法抛异常的问题:不支持此特性

最新推荐文章于 2022-09-15 17:41:06 发布

原创最新推荐文章于 2022-09-15 17:41:06 发布 · 置顶 · 3.9k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #JDBC #prepareStatement #addBatxh

数据库专栏收录该内容

7 篇文章

订阅专栏

本文探讨了在Java中使用PreparedStatement的addBatch()方法时遇到的问题，并解释了该方法不接受参数的原因。同时介绍了如何正确地使用addBatch()方法进行批量SQL操作，以及使用Statement作为替代方案。

对于preparestatement.addBatch()方法使用报异常的地方原因是不能使用prepareStatement接口去实现addBatch()多个sql语句，addBatch()方法不接受参数的输入（即使传入的参数也无法执行，没有重写这个方法因此不支持），你可以查看一下官方文档API，大概意思是说：addBatch()只是使用适当的setXXX()方法将设置的绑定参数添加到批处理中，（对于CallbleStatement或OracleCallableStatement对象也是一样）

For prepared statements, update batching is used to batch multiple executions of the same statement with different sets of bind parameters. For a PreparedStatement or OraclePreparedStatement object, the addBatch() method takes no input–it simply adds the operation to the batch using the bind parameters last set by the appropriate setXXX() methods. (This is also true for CallableStatement or OracleCallableStatement objects, but remember that in the Oracle implementation of standard update batching, you will probably see no performance improvement in batching callable statements.)

For example (again assuming a Connection instance conn):

…
PreparedStatement pstmt =
conn.prepareStatement(“INSERT INTO employees VALUES(?, ?)”);

pstmt.setInt(1, 2000);
pstmt.setString(2, “Milo Mumford”);
pstmt.addBatch();

pstmt.setInt(1, 3000);
pstmt.setString(2, “Sulu Simpson”);
pstmt.addBatch();
…

At this point, two operations are in the batch.

Because a batch is associated with a single prepared statement object, you can batch only repeated executions of a single prepared statement, as in this example.

可以使用createStatement来实现这个功能，但性能就不好说了。。。。
…
Statement stmt = conn.createStatement();

stmt.addBatch(“INSERT INTO emp VALUES(1000, ‘Joe Jones’)”);
stmt.addBatch(“INSERT INTO dept VALUES(260, ‘Sales’)”);
stmt.addBatch(“INSERT INTO emp_dept VALUES(1000, 260)”);
…