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内部编码设置问题,会出现异常。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。