Heim > Datenbank > MySQL-Tutorial > MySQL使用JDBC Load Data InFile导入数据注意事项_MySQL

MySQL使用JDBC Load Data InFile导入数据注意事项_MySQL

WBOY
Freigeben: 2016-06-01 13:48:56
Original
1446 Leute haben es durchsucht

bitsCN.com

今天测式JDBC Connection使用Load Data InFile往数据表中导入数据,Java程序如下:

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 by ',' enclosed by '/'' lines terminated by '/r/n'";            boolean result = stmt.execute(sql);            System.out.println("Load执行结果:" + result);        } finally {            DBUtils.freeConnection();            DBUtils.closeQuietly(stmt);            DBUtils.closeDataSource();        }    }}
Nach dem Login kopieren

可是一直报MySQL语法异常:

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 2	at 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)
Nach dem Login kopieren

可是把这条语句拿到MySQL命令行中,却是对的,在网上找了一大圈,也没有找到结果,于是自己Debug调试一下,发现SQL的内容如下:

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

我X,竟然SQL内容都断开了,那当然不行了,这完全跟在命令行的SQL内容不一样啊,顿时豁然开朗,原来在Java的SQL中少加了转义字符,把SQL改成如下,便可测试通过:

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'";
Nach dem Login kopieren

这样,最终的SQL内容才为:load data infile 'c:/test_key_value.txt' into table test_key_value fields terminated by ',' enclosed by '/'' lines terminated by '/r/n'

附上数据表结构和测试文件内容:

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
Nach dem Login kopieren

测试文件内容:

'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'
Nach dem Login kopieren
bitsCN.com
Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage