Java 使用Prepared Statements设置表名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1208442/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Prepared Statements to set Table Name
提问by Brandon
I'm trying to use prepared statements to set a table name to select data from, but I keep getting an error when I execute the query.
我正在尝试使用准备好的语句来设置表名以从中选择数据，但是在执行查询时我不断收到错误消息。
The error and sample code is displayed below.
错误和示例代码如下所示。
[Microsoft][ODBC Microsoft Access Driver] Parameter 'Pa_RaM000' specified where a table name is required.
private String query1 = "SELECT plantID, edrman, plant, vaxnode FROM [?]"; //?=date
public Execute(String reportDate){
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection conn = DriverManager.getConnection(Display.DB_MERC);
PreparedStatement st = conn.prepareStatement(query1);
st.setString(1, reportDate);
ResultSet rs = st.executeQuery();
Any thoughts on what might be causing this?
关于可能导致这种情况的任何想法？
采纳答案by camickr
A table name can't be used as a parameter. It must be hard coded. So you can do something like:
表名不能用作参数。它必须是硬编码的。因此，您可以执行以下操作：
private String query1 = "SELECT plantID, edrman, plant, vaxnode FROM [" + reportDate + "?]";
回答by Pierre
I'm not sure you can use a PreparedStatement to specify the name of the table, just the value of some fields. Anyway, you could try the same query but, without the brackets:
我不确定您是否可以使用 PreparedStatement 来指定表的名称，只是某些字段的值。无论如何，您可以尝试相同的查询，但没有括号：
"SELECT plantID, edrman, plant, vaxnode FROM ?"
回答by Anoop
String table="pass";
String st="select * from " + table + " ";
PreparedStatement ps=con.prepareStatement(st);
ResultSet rs = ps.executeQuery();
回答by Damien Allison
As a number of people have said, you can't use a statement parameter for a table name, only for variables as part of the condition.
正如许多人所说，您不能将语句参数用于表名，只能用于作为条件一部分的变量。
Based on the fact you have a variable table name with (at least) two table names, perhaps it would be best to create a method which takes the entity you are storing and returns a prepared statement.
基于您有一个带有（至少）两个表名的可变表名的事实，也许最好创建一个方法来获取您正在存储的实体并返回一个准备好的语句。
PreparedStatement p = createStatement(table);
回答by mypetlion
This is technically possible with a workaround, but very bad practice.
这在技术上是可行的，但有一种解决方法，但做法非常糟糕。
String sql = "IF ? = 99\n";
sql += "SELECT * FROM first_table\n";
sql += "ELSE\n";
sql += "SELECT * FROM second_table";
PreparedStatement ps = con.prepareStatement(sql);
And then when you want to select from first_table you set the parameter with
然后当你想从 first_table 中选择时，你设置参数
ps.setInt(1, 99);
Or if not, you set it to something else.
或者，如果没有，您将其设置为其他内容。
回答by Tanvir Singh
This might help:
这可能有帮助：
public ResultSet getSomething(String tableName) {
PreparedStatement ps = conn.prepareStatement("select * from \`"+tableName+"\`");
ResultSet rs = ps.executeQuery();
}
回答by bebbo
If you need a solution which is not vulnerable to SQL injection, you have to duplicate the query for all tables you need:
如果您需要一个不易受到 SQL 注入攻击的解决方案，您必须为您需要的所有表复制查询：
final static String QUERIES = {
"SELECT x FROM Table1 x WHERE a=:a AND b=:b AND ...",
"SELECT x FROM Table2 x WHERE a=:a AND b=:b AND ...",
"SELECT x FROM Table3 x WHERE a=:a AND b=:b AND ...",
...
};
And yes: the queries are duplicates and only the table name differs.
是的：查询是重复的，只有表名不同。
Now you simply select the query that fits your table, e.g. like
现在您只需选择适合您的表格的查询，例如
...
PreparedStatement st = conn.prepareStatement(QUERIES[index]);
...
You can use this approach wich JPA, Hibernate, whatever...
您可以使用这种方法，即 JPA、Hibernate 等等...
If you want a more verbose approach consider using an enum like
如果您想要更详细的方法，请考虑使用枚举
enum AQuery {
Table1("SELECT x FROM Table1 x WHERE a=:a AND b=:b AND ..."),
Table2("SELECT x FROM Table2 x WHERE a=:a AND b=:b AND ..."),
Table3("SELECT x FROM Table3 x WHERE a=:a AND b=:b AND ..."),
...
private final String query;
AQuery(final String query) {
this.query = query;
}
public String getQuery() {
return query;
}
}
Now use the either an index
现在使用任一索引
String sql = AQuery.values()[index].getQuery();
PreparedStatement st = conn.prepareStatement(sql);
...
Or use a table name
或者使用表名
String sql = AQuery.valueOf("Table1").getQuery();
PreparedStatement st = conn.prepareStatement(sql);
...
