简单参数化查询出错 - Java/ SQL
根据我之前与方法设计有关的问题之一，建议我实施我的 SQL 查询是参数化查询，而不是简单的字符串。
我以前从未使用过参数化查询，因此我决定从简单的开始，采用以下 Select 语句：
String select = "SELECT * FROM ? ";
PreparedStatement ps = connection.prepareStatement(select);
ps.setString(1, "person");
这给了我以下错误："[SQLITE_ERROR] SQL 错误或丢失数据库 (靠近“？”：语法错误）“
然后我尝试了一个具有附加条件的修改版本；
String select = "SELECT id FROM person WHERE name = ? ";
PreparedStatement ps = connection.prepareStatement(select);
ps.setString(1, "Yui");
这个版本工作正常，在我的第一个示例中，我是否错过了参数化查询的要点，或者我是否错误地构建了它们？
谢谢！
Following on from one of my previous questions to do with method design I was advised to implemented my SQL queries as a parameterized query as opposed to a simple string.
I've never used parameterized queries before so I decided to start with something simple, take the following Select statement:
String select = "SELECT * FROM ? ";
PreparedStatement ps = connection.prepareStatement(select);
ps.setString(1, "person");
This gives me the following error: "[SQLITE_ERROR] SQL error or missing database (near "?": syntax error)"
I then tried a modified version which has additional criteria;
String select = "SELECT id FROM person WHERE name = ? ";
PreparedStatement ps = connection.prepareStatement(select);
ps.setString(1, "Yui");
This version works fine, in the my first example am I missing the point of parameterized queries or am I constructing them incorrectly?
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
简单来说，SQL 绑定不能绑定表，只能绑定 where 子句值。这有一些与“编译”准备好的 SQL 语句相关的幕后技术原因。一般来说，参数化查询的目的是通过防止 SQL 注入来使 SQL 更加安全，它还有一个副作用，即使查询更加“模块化”，但还没有达到能够动态设置表名的程度（因为假设您已经知道桌子会是什么）。
Simply put, SQL binds can't bind tables, only where clause values. There are some under-the-hood technical reasons for this related to "compiling" prepared SQL statements. In general, parameterized queries was designed to make SQL more secure by preventing SQL injection and it had a side benefit of making queries more "modular" as well but not to the extent of being able to dynamically set a table name (since it's assumed you already know what the table is going to be).
如果您想要 PERSON 表中的所有行，您应该执行以下操作：
变量绑定不会像上面提到的其他那样动态绑定表名称。
如果您将表名作为变量输入到您的方法中，则可以构造整个查询，如下所示：
参数化查询用于查询字段名称 - 而不是表名称！
If you want all rows from PERSON table, here is what you should do:
Variable binding does not dynamically bind table names as others mentioned above.
If you have the table name coming in to your method as a variable, you may construct the whole query as below:
Parameterized queries are for querying field names - not the table name!
准备好的语句仍然是 SQL，需要使用适当的 where 子句来构造；即 x = y。它们的优点之一是它们在第一次看到时由 RDMS 进行解析，而不是每次发送时进行解析，这加快了具有不同绑定值的同一查询的后续执行速度。
Prepared statements are still SQL and need to be constructed with the appropriate where clause; i.e. where x = y. One of their advantages is they are parsed by the RDMS when first seen, rather than every time they are sent, which speeds up subsequent executions of the same query with different bind values.