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 '?,?,?,?)'

使用?占位符写MYSQL查询语句,执行报错

 1 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 1
 2     at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 3     at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
 4     at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 5     at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
 6     at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
 7     at com.mysql.jdbc.Util.getInstance(Util.java:386)
 8     at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
 9     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4190)
10     at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4122)
11     at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2570)
12     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731)
13     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812)
14     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2761)
15     at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
16     at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:732)
17     at com.eloancn.test.run.ConntectTest.insert(ConntectTest.java:67)
18     at com.eloancn.test.run.ConntectTest.main(ConntectTest.java:44)

涉及测试的两个方法:

方法一:

 1 public static void main(String[] args) {
 2         try {
 3             Class.forName("com.mysql.jdbc.Driver");
 4             System.out.println("加载成功");
 5         } catch (ClassNotFoundException e) {
 6             System.out.println(e);
 7             e.printStackTrace();
 8         }
 9         InputStream in = null;
10         try {
        //加载本地数据库链接数据
11 in = new FileInputStream("src/main/resources/jdbc.properties"); 12 } catch (FileNotFoundException e) { 13 e.printStackTrace(); 14 } 15 Properties pp = new Properties(); 16 try { 17 pp.load(in); 18 } catch (IOException e) { 19 e.printStackTrace(); 20 } 21 Object obj = null; 22 ArrayList<HashMap<String, String>> result = new ArrayList<HashMap<String, String>>(); 23 try { 24 Connection conn = DriverManager.getConnection(pp.getProperty("jdbc.url"), pp.getProperty("jdbc.username"),pp.getProperty("jdbc.password")); 25 conn.setAutoCommit(false); 26 String sql = "insert into d_important_loandatum_score (tenderId,photoId,datumId,showClient)" 27 + " values (?,?,?,?)"; 28 insert(conn, sql, new Object[] { 1, "3", 2, 3 }); 29 conn.commit(); 30 } catch (Exception e) { 31 e.printStackTrace(); 32 } 33 34 }

方法二:执行插入

 1 public static boolean insert(Connection conn, String sql, Object[] paras)throws Exception {
 2         PreparedStatement statement = null;
 3         String str = "";
 4         statement = conn.prepareStatement(sql);
 5         if (paras != null && paras.length != 0) {
 6             for (int i = 0; i < paras.length; i++) {
 7                 if (paras[i].getClass().getSimpleName().equals("Integer")) {
 8                     statement.setInt(i + 1, (Integer) paras[i]);
 9                     str+=paras[i];
10                 } else {
11                     statement.setString(i + 1, (String) paras[i]);
12                     str+=paras[i];
13                 }
14             }
15         }
16         boolean result = statement.execute(sql);
17 //        logger.info("sql:"+sql+" paras:"+str);
18         return result;
19     }

如上两个方法放入类中,执行报错。经过多次查找,最终确定是插入方法中标红部分编写错误,修改为:boolean result = statement.execute();

保存,重新执行,正常。

PS:同样使用preparement 预编译SQL时,查询,修改等调用方法时,最终执行都不能再次编译SQL,是MYSQL内部编码设置问题,会出现异常。

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。