java，mysql使用preparedStatement插入多个表

Ok, going to reword this question as i made it a bit too complicated before.

好吧,要改写这个问题,因为我之前有点太复杂了。

i want to take this:

我想拿这个:

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 (?, ?)");

where 'table_name' can be replaced based on what tables[i] is each time through the loop.

其中'table_name'可以根据每次循环中的表[i]进行替换。

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.

我已经尝试了到目前为止我能想到的所有可能的方式,并且都是相同的,对于sql来说无效,或者只是每次都丢失除了最后一次INSERT之外的所有内容。

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.

最后我想制作一个循环,将批量所有插入,也许100,也许1000我不知道。在任何情况下,我希望它循环和批处理所有插入,然后一旦完成所有执行批处理。另一种方法是单独执行每个单独的插入,这有点排除了首先具有批处理功能的原因。

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();

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.

然而,正如我们都知道的那样.setTable不存在:(如果有的话,它可以被置于一个循环中,并为每次通过循环给出一个不同的表值。这种类型的循环现在可以正常工作,只要它全部到同桌。

1 个解决方案

#1

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中的PreparedStatement.addBatch有什么限制吗?)当你多次调用prepareStatement时,你不能只使用一次executeBatch调用。每次调用prepareStatement,虽然它可以多次调用addBatch,但必须有自己的executeBatch调用。

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.

因此,为了最大限度地减少Java和MySQL之间的通信,我会考虑在MySQL中使用存储过程。和通常的优化一样,在我得出实际需要更复杂的方式(存储过程)之前,我会以简单的方式尝试。

I also see that other people turn off autocommit when they use executeBatch.

我还看到其他人在使用e​​xecuteBatch时关闭了自动提交。