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屋!