Parameter index out of range (1 > number of parameters, which is 0).问题的解决

在做javaweb作业的时候遇到好几次这种异常,总结了下问题都是一个原因:

预处理语句的定义中给[?]周围加上了[']

比如如下语句就是个bug

private static final String DELETE_SQL = "DELETE FROM customer WHERE custName = '?'";

这样调用setString就会产生上述异常

pstmt.setString(1, "name");

应该改成

private static final String DELETE_SQL = "DELETE FROM customer WHERE custName = ?";

之前单纯以为只有'?'是错误的会暴bug,后来发现还有一种情况也是会的,下面的这句简单的搜索语句也是有bug的

private static final String QUERY_SQL ="SELECT * FROM customer WHERE `custName` LIKE '%?%'";

改为

private static final String QUERY_SQL ="SELECT * FROM customer WHERE `custName` LIKE %?%";

还是错误的,查看错误信息终于得到解决方法了
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 '%''% OR `email` LIKE %''% OR `phone` LIKE %''%' at line 1

可以猜测java对于SQL预处理中单引号(')是这样处理的,在setString的时候,把问号(?)关键词替换为用户定义的字符串,并给这个用户定义的字符串的左右加上('),这样的话对于上述搜索语句,只能这样设置了。

private static final String QUERY_SQL ="SELECT * FROM customer WHERE `custName` LIKE ?";

在setString的时候

pstmt.setString(1, "%搜索关键字%");

当然这样也可以

pstmt.setString(1, "%"+query+"%");

问题解决!



关于 McKelvin

a hacker who's interested in `music computing` and `network security`.
此条目发表在 Work 分类目录,贴了 , 标签。将固定链接加入收藏夹。