Does any JDBC driver support the sql command of the LOAD DATA INFILE command?

Question

Hi, I would like to create a table via JDBC for several databases such as DB2, Sybase, MySQL, etc. Now I need to create this table using the text file say data.txt, which contains data separated by spaces. For instance,

CustName OrderNo PhoneNo XYZ 230 123456789 ABC 450 879641238 

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

I found that there is a LOAD DATA INFILE sql command. Does any JDBC driver support this command? If not what should be the best effective quick approach to solving this problem.

Please guide. Thanks in advance.

Answer 1

The following will work through JDBC. Please note that in order to use LOAD DATA INFILE you need superuser privilege. You do not need to 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 file system! If you are using a local file, then obviously it is based on the client file system.

Answer 2

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

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

• Parsing the input file and using SQL INSERT statements via JDBC connection
• Write down several different database-dependent scripts, determine which dbms you are using, and execute the correct one using Runtime.exec

If you have no compelling reasons for performance, I would choose option 1.

Answer 3

I believe LOAD DATA INFILE faster than parsing a file and pasting records using Java. You can execute a query for LOAD DATA INFILE via JDBC. According to 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 must be on the server. You can try both approaches, register the time that each of them consumes.

Answer 4

" Load local access data " works with the MySQL JDBC driver, there are some problems with this.

When using "load data infile" or "load data local infile", the added WILL NOT entries will be added to the bin log, which means that if you use replication, the entries inserted in the "load data infile" will not be transferred to the slave server (s) , transactions will not be recorded in the inserted records, and that is why information about the downloaded data is much faster than the standard insert and due to the lack of verification of the inserted data.