Does any JDBC driver supports LOAD DATA INFILE sql command?

11,695

Solution 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.

Solution 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.

Solution 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:

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.

Solution 4

"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.

Share:
11,695

Related videos on Youtube

Umesh K
Author by

Umesh K

#SOreadytohelp

Updated on September 15, 2022

Comments

  • Umesh K
    Umesh K over 1 year

    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.

    • a_horse_with_no_name
      a_horse_with_no_name
      You can use load data infile through JDBC (the file must be located on the server!) - why don't you simply try it?
    • Umesh K
      Umesh K
      @a_horse_with_no_name thanks a lot for the input. I read that LOAD DATA INFILE command is not supported by JDBC drivers so I asked this question?
  • Umesh K
    Umesh K almost 11 years
    Hi thanks a lot for the input. I suspect there JDBC driver does not support LOAD DATA INFILE sql command. Thats why I asked this question. Please guide.
  • DaveH
    DaveH almost 11 years
    OK - thanks for clearing that up. But I'm guessing that it's mySql specific though? The OP needs something that'll work across multiple databases so, unless all the vendors Data Load utilities work thru their JDBC drivers, he's still going to run into trouble.
  • a_horse_with_no_name
    a_horse_with_no_name almost 11 years
    Yes it is MySQL specific. Some DBMS support this through SQL statements (e.g. COPY for Postgres), some don't have a SQL statement for bulk loading, only external command line programs.
  • Umesh K
    Umesh K almost 11 years
    Thanks a lot for the answer. I know this code works on MYSQL but I want it to run on Sybase, Oracle, DB2 and I doubt it will run on these dbs. Since you are a database expert please guide.
  • a_horse_with_no_name
    a_horse_with_no_name almost 11 years
    LOAD DATA is a MySQL specific feature. It is not available on any other DBMS. If you want a cross-platform way to do that, you will have to parse the file yourself and generate "plain" INSERT statements for each row.
  • Umesh K
    Umesh K almost 11 years
    I thought the same I will have to parse file anyways thanks a lot for the guidance.
  • Barry Kelly
    Barry Kelly over 4 years
    This is not true; data loaded via LOAD DATA INFILE is replicated. I have the existence proof of running 7 years in production using the feature with replication :) The data is not very well validated, for sure (e.g. invalid dates -> the infamous 0000-00-00), but the insert itself happens in the context of whatever transaction the connection has started.