我正在用Java开发与MySQL db通信的应用程序,而在测试它时,我注意到在表中插入新行的代码引发了MySQLSyntaxError异常,因此我尝试使用MySQL Workbench执行相同的INSERT,而且有效。有问题的代码是这样的:
public static boolean aggiungiElem(String nome, GrafoGenerico g){ if(connessioneAperta()){ try{ String sqlCommandUser="SELECT USER()"; String sqlCommandInserim="INSERT INTO salvataggi VALUES ( ? , ? , DEFAULT , NULL );"; PreparedStatement sUser=conn.prepareStatement(sqlCommandUser); ResultSet risultatiUtente=sUser.executeQuery(); String utente = null; while(risultatiUtente.next()){ utente=risultatiUtente.getString(1); } sUser.close(); PreparedStatement sInserim=conn.prepareStatement(sqlCommandInserim); sInserim.setString(1, utente); sInserim.setString(2, nome); //sInserim.setObject(3,g); System.out.println(sInserim.toString()); sInserim.executeUpdate(sqlCommandInserim); sInserim.close(); return true; } catch(SQLException e){ e.printStackTrace(); return false; } } else return false; }
编辑:对不起,堆栈跟踪是:
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 '? , ? , DEFAULT , NULL )' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:407) at com.mysql.jdbc.Util.getInstance(Util.java:382) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3603) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3535) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1989) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2150) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2620) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1664) at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1583) at se.diag.control.clientspec.UtilsClientSQL.aggiungiElem(UtilsClientSQL.java:67) at se.diag.control.clientspec.UtilsClientSQLTest.testAggiungiElem(UtilsClientSQLTest.java:67) at se.diag.control.clientspec.UtilsClientSQLTest.testCaricaElem(UtilsClientSQLTest.java:99) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99) at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81) at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34) at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75) at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45) at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:66) at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35) at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42) at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34) at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52) at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:45) at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:460) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:673) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:386) at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:196)
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException:SQL语法有错误;检查与您的MySQL服务器版本相对应的手册,以获取在’?附近使用的正确语法。,?,DEFAULT,NULL)’在第1行
这些占位符?根本不应该出现在MySQL端。
?
看这里,
sInserim.executeUpdate(sqlCommandInserim);
您将原始SQL字符串传递进来,executeUpdate()而不是PreparedStatement使用设置值执行。
executeUpdate()
PreparedStatement
替换为
sInserim.executeUpdate();
本executeUpdate(sqlString)应使用的Statement唯一。
executeUpdate(sqlString)
Statement
与 具体问题 无关的 是,您应该PreparedStatement在finally块中关闭,以防止在发生异常情况时资源泄漏。这同样适用于Connection,Statement并且ResultSet顺便说一句。
finally
Connection
ResultSet