java，mysql使用prepareStatement插入多个表 [英] java,mysql insert into multiple tables using preparedStatement
问题描述
好吧，因为我之前做的太复杂了，所以要重新措辞这个问题.
Ok, going to reword this question as i made it a bit too complicated before.
我想吃这个:
con.setAutoCommit(false);
String tempforbatch;
Statement stmt = con.createStatement();
for (int i = 0; i < tables.length; i++) {
tempforbatch = "INSERT INTO " + tables[i] + " VALUES ('" + values[i] + "')";
stmt.addBatch(tempforbatch);
}
stmt.executeBatch();
仅使用以下命令即可将其转换为完全相同的内容:
and turn it into something exactly the same, only using:
PreparedStatement stmt = con.prepareStatement("INSERT INTO table_name VALUES (?, ?)");
其中"table_name"可以根据每次循环中的表[i]进行替换.
where 'table_name' can be replaced based on what tables[i] is each time through the loop.
到目前为止，我已经尝试了我能想到的所有可能方式，所有结果都相同，或者对sql无效，或者每次都丢失了除最后一次INSERT之外的所有内容.
i have tried every possible way i can think of so far and all come out the same, either invalid for sql, or simply missing all but the last INSERT each time.
最后，我想做一个循环，将所有插入的内容分批处理，我可能不知道可能是100个，也许是1000个.在任何情况下，我都希望它循环并批处理所有插入，然后在全部完成后执行批处理.另一种方法是仅自己执行每个插入，这首先消除了使用批处理功能的原因.
In the end i want to make a loop that will batch up all inserts, perhaps 100, maybe 1000 i dont know. in any case i want it to loop and batch all inserts and then execute the batch once all are made. The other way is to just execute each and every single insert by itself which sorta kills the reason of having the batch function in the first place.
如果只有一种方法可以做到这一点:
If only there was a way to do this:
PreparedStatement stmt = con.prepareStatement("INSERT INTO ? VALUES (?, ?)");
...loop code here....
stmt.setTable(1, "table1");
stmt.setString(2, "value1");
stmt.setString(3, "value2");
stmt.addBatch();
...end loop here....
stmt.executeBatch();
但是，我们都知道.setTable不存在:(如果存在，则可以将其放入循环中，并为循环中的每次通过赋予不同的表值.这种循环现在可以正常工作了都放在同一张桌子上.
however as we all know .setTable does not exist :( if there were then it could be placed into a loop and given a different table value for each pass through the loop. This type of loop will work fine right now provided its all to the same table.
推荐答案
Judging by other examples (such as PreparedStatement.addBatch in java has any restrictions?) you can't use just one call to executeBatch when you make multiple calls to prepareStatement. Each call to prepareStatement, although it can have multiple calls to addBatch, must have its own call to executeBatch.
因此，为了最小化Java和MySQL之间的通信，我将考虑在MySQL中使用存储过程.和往常一样，在进行优化之前，我会以简单的方式尝试它，然后得出结论，实际上需要更复杂的方式(存储过程).
Therefore, to minimize the communication between Java and MySQL, I would consider using a stored procedure in MySQL. And as usual with optimizations, I would try it the simple way before I conclude that the more complex way (the stored procedure) is actually needed.
我还看到其他人在使用executeBatch时关闭了自动提交.
I also see that other people turn off autocommit when they use executeBatch.
这篇关于java，mysql使用prepareStatement插入多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
