是否有任何JDBC驱动程序支持LOAD DATA INFILE sql命令？

Hi I would like to create table through JDBC on multiple databases like DB2, Sybase, MySQL etc. Now I need to create this table using text file say data.txt which contains data space separated values. For e.g.

您好我想通过JDBC在DB2，Sybase，MySQL等多个数据库上创建表。现在我需要使用文本文件创建此表，例如data.txt，其中包含数据空间分隔值。对于例如

CustName OrderNo PhoneNo

XYZ      230     123456789
ABC      450     879641238

Now this data.txt contains thousands of records space separated values. I need to parse this file line by line using java io and execute sql insert queries for each records.

现在这个data.txt包含数千个记录空间分隔值。我需要使用java io逐行解析这个文件，并为每个记录执行sql insert查询。

I found there is LOAD DATA INFILE sql command. Does any JDBC driver supports this command? If not what should be the best efficient fast approach to solve this problem.

我发现有LOAD DATA INFILE sql命令。是否有任何JDBC驱动程序支持此命令？如果不是什么应该是解决这个问题的最有效的快速方法。

Please guide. Thanks in advance.

请指导。提前致谢。

4 个解决方案

#1

The following will work through JDBC. Note that to use LOAD DATA INFILE you need superuser privilege. Which you don't need for LOAD DATA LOCAL INFILE

以下内容将通过JDBC完成。请注意，要使用LOAD DATA INFILE，您需要超级用户权限。您不需要LOAD DATA LOCAL INFILE

Connection con = DriverManager.getConnection("jdbc:mysql://localhost/foobar", "root", "password");
Statement stmt = con.createStatement();
String sql = 
    "load data infile 'c:/temp/some_data.txt' \n" +
    "   replace \n" +
    "   into table prd \n" +
    "   columns terminated by '\\t' \n" +
    "   ignore 1 lines";
stmt.execute(sql);

If you use LOAD DATA INFILE the file location is based on the server's filesystem! If you use a local file, then obviously it's based on the client's filesystem.

如果使用LOAD DATA INFILE，文件位置基于服务器的文件系统！如果您使用本地文件，那么显然它基于客户端的文件系统。

#2

I think LOAD DATA INFILE is specific to mySql, and I doubt whether a JDBC driver would support it. Other databases will have similar ( but different ) utilities

我认为LOAD DATA INFILE特定于mySql，我怀疑JDBC驱动程序是否支持它。其他数据库将具有类似（但不同）的实用程序

If you want to do this is a database independent way I think you have two choices

如果你想这样做是一种独立于数据库的方式，我认为你有两个选择

Parse up the input file and use SQL INSERT statements over a JDBC connection
解析输入文件并通过JDBC连接使用SQL INSERT语句
Write a number of different, database dependent scripts, determine which dbms you are using and execute the correct one using Runtime.exec
编写许多不同的，依赖于数据库的脚本，确定您正在使用哪些dbms并使用Runtime.exec执行正确的dbms

Unless you have compelling performance reasons not to, I'd go for option 1.

除非你有令人信服的表现理由，否则我会选择选项1。

#3

I believe LOAD DATA INFILE is faster than parsing the file and inserting the records using Java . You can execute the query for load data infile through JDBC. As per this Oracle doc and MySql doc:

我相信LOAD DATA INFILE比解析文件和使用Java插入记录更快。您可以通过JDBC执行查询加载数据infile。根据这个Oracle doc和MySql doc：

The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed.

LOAD DATA INFILE语句以非常高的速度将文本文件中的行读入表中。

The file should be in server . You can try both the approaches, log the time each one of them consume.

该文件应该在服务器中。您可以尝试这两种方法，记录每个方法消耗的时间。

#4

"Load data local infile" does work with MySQL's JDBC driver, there are some issues with this.

“加载数据本地infile”确实可以与MySQL的JDBC驱动程序一起使用，这有一些问题。

When using "load data infile" or "load data local infile" the inserted records WILL NOT be added to the bin log, this means that if you are using replication the records inserted by "load data infile" will not be transferred to the slave server(s), the inserted records will not have any transactions record, and this is why load data infile is so much quicker than a standard insert and due to no validation on the inserted data.

当使用“加载数据infile”或“加载数据本地infile”时，插入的记录将不会被添加到bin日志中，这意味着如果使用复制，“load data infile”插入的记录将不会传输到slave服务器，插入的记录将没有任何事务记录，这就是为什么加载数据infile比标准插入快得多，并且由于没有对插入的数据进行验证。

#1