Java - 如何批量数据库插入和更新 [英] Java - how to batch database inserts and updates

问题描述

我想在一个PreparedStatement中批量处理多种类型的数据库调用。

PreparedStatement pstmt = connection.prepareStatement（？）;

可以 INSERT INTO MY_TABLE VALUES（1,2,3,4），也可以是 UPDATE MY_TABLE，SET MY_VAL ='1'WHERE MY_VAL IS NULL

或者我总是需要为我准备好的语句指定一个表和动作吗？

解决方案

Java不允许只添加？在preparedstatement字符串参数，因为它期望？

对于你的情况，你可能需要有2个准备的语句对象，并且在循环中，你可以使用一个决定谁打电话。所以它将是如下所示：

  PreparedStatement insertPstmt = connection.prepareStatement（INSERT INTO MY_TABLE VALUES（？，？ ，？））; 
 PreparedStatement updatePstmt = connection.prepareStatement（UPDATE MY_TABLE，SET MY_VAL =？WHERE MY_VAL IS NULL）; 
 
 While（< condition>）{
 If（< insert condition>）{
 //使用insert pstmt并添加批处理
} else {
 //使用update pstmt并添加批处理
} 
} 
 
 insertPstmt.executeBatch（）; 
 updatePstmt.executeBatch（）; 
  

如果你有任何插入，它依赖于更新，你可以相应地执行批处理。这将确保更新能正常工作。我想到首先执行插入，因为他们可能不依赖于更新。

I want to batch up multiple types of database calls in one PreparedStatement. Is this possible?

Is there anyway to do something like

PreparedStatement pstmt = connection.prepareStatement("?");

where the ? can either be INSERT INTO MY_TABLE VALUES(1,2,3,4) or it could be UPDATE MY_TABLE, SET MY_VAL='1' WHERE MY_VAL IS NULL

Or do I always need to specify a table and action for my prepared statement?

解决方案

Java will not allow you add only ? in preparedstatement string parameter, as it expects the ? for the place holder only for the parameters to the give SQL.

For your case, you may have to have 2 prepared statement objects, and in loop through, you can make a decision which one to call. So it would be something like below:

PreparedStatement insertPstmt = connection.prepareStatement("INSERT INTO MY_TABLE VALUES(?,?,?,?)");
PreparedStatement updatePstmt = connection.prepareStatement("UPDATE MY_TABLE, SET MY_VAL=? WHERE MY_VAL IS NULL");

While (<condition>) {
  If (<insert condition>) {
    // use insert pstmt and add batch
  } else {
    // use update pstmt and add batch
  }
}

insertPstmt.executeBatch(); 
updatePstmt.executeBatch();

if you have any insert , which has dependency on the update, you might execute the batches accordingly. This will make sure that the update will work correctly. I would think of executing insert first, as they might not depend on update.

这篇关于Java - 如何批量数据库插入和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！