Does any JDBC driver supports LOAD DATA INFILE sql command?

Question

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.

Answer 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

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.

Answer 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

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.