今天动手写课程设计,JDBC某处用到PreparedStatement,最初想将表名、字段名、字段值都作为参数用?代替,可是实践之后发现行不通。
最初想这样写:
preparedStatement = connection.prepareStatement("select * from ? where ?=?");
preparedStatement.setString(1, tableName);
preparedStatement.setString(2, colName);
preparedStatement.setString(3, value);
resultSet = preparedStatement.executeQuery();
后来发现,如果这样写会抛异常:
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 ''department' where 'Did'='2'' at line 1
注意看此处的SQL语句变为:
select * from 'department' where 'Did' = '2'
可以发现,PreparedStatement为占位符?的两边自动加上引号’,这样会使得SQL语句不可执行。
解决方法:只能通过拼接SQL字符串
可将上述语句改写为:
preparedStatement = connection.prepareStatement("select * from " + tableName + " where " + colName +"=?");
preparedStatement.setString(1, value);
resultSet = preparedStatement.executeQuery();
即可成功执行
总结:
PreparedStatement只能用来为可以加引号’的参数(如参数值)设置动态参数,即用?占位,不可用于表名、字段名等。