Considerations for using JDBC Load Data InFile to import Data

Today, we tested JDBC Connection to use Load Data InFile to import Data to the Data table. The Java program is as follows:

Public class LoadDataTest {@ Test public void test_loadData () throws Exception {Connection conn = null; Statement stmt = null; try {conn = DBUtils. fetchConnection (); stmt = conn. createStatement (); String SQL = "load data infile 'C:/test_key_value.txt 'into table test_key_value fields terminated ', 'enabledby' \ ''lines terminated by' \ r \ N' "; boolean result = stmt.exe cute (SQL); System. out. println ("Load execution result:" + result);} finally {DBUtils. freeConnection (); DBUtils. closeQuietly (stmt); DBUtils. closeDataSource ();}}}

However, MySQL syntax exceptions are always reported:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 2at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)at java.lang.reflect.Constructor.newInstance(Constructor.java:513)at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)at com.mysql.jdbc.Util.getInstance(Util.java:386)at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1990)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2151)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2619)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2569)at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:813)at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:656)at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)at com.alipay.mbill.loaddata.LoadDataTest.test_loadData(LoadDataTest.java:31)at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)at java.lang.reflect.Method.invoke(Method.java:597)at org.junit.internal.runners.TestMethod.invoke(TestMethod.java:59)at org.junit.internal.runners.MethodRoadie.runTestMethod(MethodRoadie.java:98)at org.junit.internal.runners.MethodRoadie$2.run(MethodRoadie.java:79)at org.junit.internal.runners.MethodRoadie.runBeforesThenTestThenAfters(MethodRoadie.java:87)at org.junit.internal.runners.MethodRoadie.runTest(MethodRoadie.java:77)at org.junit.internal.runners.MethodRoadie.run(MethodRoadie.java:42)at org.junit.internal.runners.JUnit4ClassRunner.invokeTestMethod(JUnit4ClassRunner.java:88)at org.junit.internal.runners.JUnit4ClassRunner.runMethods(JUnit4ClassRunner.java:51)at org.junit.internal.runners.JUnit4ClassRunner$1.run(JUnit4ClassRunner.java:44)at org.junit.internal.runners.ClassRoadie.runUnprotected(ClassRoadie.java:27)at org.junit.internal.runners.ClassRoadie.runProtected(ClassRoadie.java:37)at org.junit.internal.runners.JUnit4ClassRunner.run(JUnit4ClassRunner.java:42)at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:46)at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

However, I got this statement in the MySQL command line, but it was right. I found a large circle on the Internet and did not find the result. So I Debug it myself and found that the SQL content is as follows:

load data infile 'c:/test_key_value.txt' into table test_key_value fields terminated by ',' enclosed by ''' lines terminated by ''

I am X, and even the SQL content is disconnected. Of course, that's not the case. It's totally different from the SQL content of the command line. It suddenly becomes very clear that the escape characters were not added to the Java SQL, change the SQL statement as follows to pass the test:

String sql = "load data infile 'c:/test_key_value.txt' into table test_key_value fields terminated by ',' enclosed by '\\'' lines terminated by '\\r\\n'";

In this way, the final SQL content is: load data infile 'C:/test_key_value.txt 'into table test_key_value fields terminated ', 'enabledby' \ ''lines terminated by' \ r \ N'

 

The following table structure and test file are attached:

CREATE TABLE `test_key_value` (  `id` bigint(20) NOT NULL AUTO_INCREMENT,  `key` varchar(32) CHARACTER SET latin1 DEFAULT NULL,  `value` varchar(128) CHARACTER SET latin1 DEFAULT NULL,  `gmt_create` timestamp NULL DEFAULT NULL,  `gmt_modify` timestamp NULL DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=gbk

Test file content:

'1','KEY01','Value01','2012-06-08 15:50:30','2012-06-08 16:50:30''2','KEY02','Value02','2012-06-08 15:50:30','2012-06-08 16:50:30''3','KEY03','Value03','2012-06-08 15:50:30','2012-06-08 16:50:30''4','KEY04','Value04','2012-06-08 15:50:30','2012-06-08 16:50:30''5','KEY05','Value05','2012-06-08 15:50:30','2012-06-08 16:50:30'

 

 

Based on the official MySQL documentation, we can continue to improve the test cases and Load some data:

Public class LoadDataTest {/*** full import, including the auto-incrementing field */@ Test public void test_loadData_ALL () throws Exception {Connection conn = null; Statement stmt = null; try {conn = DBUtils. fetchConnection (); stmt = conn. createStatement (); String SQL = "load data infile 'C:/test_key_value.txt 'replace into table test_key_value character set GBK fields terminated ', 'enabledby' \ ''lines terminated by' \ r \ N' "; boolean result = stmt.exe cute (SQL); System. out. println ("Load execution result:" + result);} finally {DBUtils. freeConnection (); DBUtils. closeQuietly (stmt); DBUtils. closeDataSource () ;}}/*** partially imported. The auto-increment Field automatically adds 1 */@ Test public void test_loadData_PART () throws Exception {Connection conn = null; statement stmt = null; try {conn = DBUtils. fetchConnection (); stmt = conn. createStatement (); String SQL = "load data infile 'C:/test_key_value_02.txt 'replace into table test_key_value character set GBK fields terminated ', 'enabledby' \ ''lines terminated by' \ r \ n' ('key', 'value', 'gmt _ create', 'gmt _ modify ') "; boolean result = stmt.exe cute (SQL); System. out. println ("Load execution result:" + result);} finally {DBUtils. freeConnection (); DBUtils. closeQuietly (stmt); DBUtils. closeDataSource ();}}}