如何在不构建字符串的情况下使用JDBC进行扩展插入? [英] How do you do an extended insert using JDBC without building strings?

问题描述

我有一个解析日志文件并将大量数据插入数据库的应用程序.它是用Java编写的，并通过JDBC与MySQL数据库进行通讯.我尝试了不同的方式来插入数据，以针对我的特定用例找到最快的方式.目前看来效果最好的一种是发出扩展插入(例如，一个具有多行的插入)，如下所示:

I've got an application that parses log files and inserts a huge amount of data into database. It's written in Java and talks to a MySQL database over JDBC. I've experimented with different ways to insert the data to find the fastest for my particular use case. The one that currently seems to be the best performer is to issue an extended insert (e.g. a single insert with multiple rows), like this:

INSERT INTO the_table (col1, col2, ..., colN) VALUES
(v1, v2, v3, ..., vN),
(v1, v2, v3, ..., vN),
...,
(v1, v2, v3, ..., vN);

行数可以是数万.

我尝试使用预准备的语句，但是它的速度并不快，这可能是因为每个插入仍仍单独发送到DB，并且表需要锁定等等.在我尝试使用批处理功能之前，我曾处理过代码的同事，但效果也不尽人意.

I've tried using prepared statements, but it's nowhere near as fast, probably because each insert is still sent to the DB separately and the tables needs to be locked and whatnot. My colleague who worked on the code before me tried using batching, but that didn't perform well enough either.

问题是，使用扩展插入意味着据我所知我需要自己构建SQL字符串(因为行数是可变的)，这意味着我打开了各种SQL注入向量没有足够的智慧找到自己.必须有一个更好的方法来做到这一点.

The problem is that using extended inserts means that as far as I can tell I need to build the SQL string myself (since the number of rows is variable) and that means that I open up all sorts of SQL injection vectors that I'm no where intelligent enough to find myself. There's got to be a better way to do this.

很明显，我对插入的字符串进行了转义，但是仅使用了类似str.replace("\"", "\\\"");的字符串(对于'，?和\重复)，但是我确定这还不够.

Obviously I escape the strings I insert, but only with something like str.replace("\"", "\\\""); (repeated for ', ? and \), but I'm sure that isn't enough.

推荐答案

准备好的语句+批量插入:

prepared statements + batch insert:

PreparedStatement stmt = con.prepareStatement(
"INSERT INTO employees VALUES (?, ?)");

stmt.setInt(1, 101);
stmt.setString(2, "Paolo Rossi");
stmt.addBatch();

stmt.setInt(1, 102);
stmt.setString(2, "Franco Bianchi");
stmt.addBatch();

// as many as you want   
stmt.executeBatch();

这篇关于如何在不构建字符串的情况下使用JDBC进行扩展插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！