如何在SELECT COUNT查询中将表名传递给Prepared Statement? [英] How to pass table name to a Prepared Statement in a SELECT COUNT query?
本文介绍了如何在SELECT COUNT查询中将表名传递给Prepared Statement?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
所以我有以下方法可以正常工作:
So I have this following method which works just fine:
static void getCount(final String url, final String username, final String password) throws SQLException {
final Connection connection = DriverManager.getConnection(url, username, password);
final String query = "SELECT COUNT(*) FROM app_user";
final PreparedStatement preparedStatement = connection.prepareStatement(query);
final ResultSet resultSet = preparedStatement.executeQuery();
resultSet.next();
System.out.println(resultSet.getInt(1));
resultSet.close();
preparedStatement.close();
connection.close();
}
但是当我尝试时:
static void foobar(final String url, final String username, final String password, final String tablename) throws SQLException {
final Connection connection = DriverManager.getConnection(url, username, password);
final String query = "SELECT COUNT(*) FROM ? ";
final PreparedStatement preparedStatement = connection.prepareStatement(query);
preparedStatement.setString(1, tablename);
final ResultSet resultSet = preparedStatement.executeQuery();
resultSet.next();
System.out.println(resultSet.getInt(1));
resultSet.close();
preparedStatement.close();
connection.close();
}
我得到:
Exception in thread "main" 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 ''app_user'' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
我在做什么错了?
推荐答案
您只能绑定PreparedStatement
中的值,不能绑定语法元素或对象名(在这种情况下为表名).您将不得不求助于字符串操作:
You can only bind values in a PreparedStatement
, not syntactic elements or object names (in this case, the table name). You'll have to resort to string manipulation:
final String query = String.format("SELECT COUNT(*) FROM %s", tablename);
final PreparedStatement preparedStatement = connection.prepareStatement(query);
final ResultSet resultSet = preparedStatement.executeQuery();
请注意,此查询中没有占位符,因此使用PreparedStatement
相对于普通的Statement
是否真的有任何优势是令人怀疑的.
Note that there are no placeholders in this query, so it's questionable whether there's really any advantage in using a PreparedStatement
as opposed to a plain old Statement
.
这篇关于如何在SELECT COUNT查询中将表名传递给Prepared Statement?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文