5

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.

Umesh K
  • 13,436
  • 25
  • 87
  • 129
  • 1
    You can use `load data infile` through JDBC (the file must be located on the server!) - why don't you simply try it? –  Jun 26 '13 at 07:24
  • 1
    @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 Jun 26 '13 at 08:02
  • Where did you read that? What happened when you tried? –  Jun 26 '13 at 08:09
  • Hey @Umesh - I hope you didn't take that from my answer below. I was only _guessing_ that the JDBC driver wouldn't support it. – DaveH Jun 26 '13 at 08:11
  • 1
    @DaveHowes I did not take from your answers I also read the same in many places so I asked question here that JDBC driver supports LOAD DATA INFILE – Umesh K Jun 26 '13 at 08:13
  • Believe me. It **does** work. See my answer for a quick (and dirty) test program. –  Jun 26 '13 at 08:17

4 Answers4

8

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.

  • 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. – Umesh K Jun 26 '13 at 08:28
  • 1
    `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. –  Jun 26 '13 at 08:29
  • I thought the same I will have to parse file anyways thanks a lot for the guidance. – Umesh K Jun 26 '13 at 08:47
4

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.

DaveH
  • 7,187
  • 5
  • 32
  • 53
  • 1
    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. – DaveH Jun 26 '13 at 08:20
  • 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. –  Jun 26 '13 at 08:27
1

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.

AllTooSir
  • 48,828
  • 16
  • 130
  • 164
  • 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. – Umesh K Jun 26 '13 at 07:58
1

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

Blind TeamKiller
  • 51
  • 4
  • 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. – Barry Kelly Oct 08 '19 at 09:10