CODEWITHSUNDEEP
javamysqldatabasejdbc
Umesh Kacha
Umesh Kacha

Reputation: 13666

Does any JDBC driver supports LOAD DATA INFILE sql command?

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.

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.

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.

Please guide. Thanks in advance.

Upvotes: 5

Views: 12762

Answers (4)

Blind TeamKiller
Blind TeamKiller

Reputation: 61

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

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.

Upvotes: 1

user330315
user330315

Reputation:

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

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.

Upvotes: 8

DaveH
DaveH

Reputation: 7335

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

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

  1. Parse up the input file and use SQL INSERT statements over a JDBC connection
  2. Write a number of different, database dependent scripts, determine which dbms you are using and execute the correct one using Runtime.exec

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

Upvotes: 4

AllTooSir
AllTooSir

Reputation: 49362

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:

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

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

Upvotes: 1

Related Questions