如何在 Java 中创建动态准备好的语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9943429/
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
How to create a dynamic prepared statement in Java?
提问by Mnementh
I know using prepared statements helps to avoid sql-injection. My problem is, that a prepared statement is usually very static. I have a problem, where I build the where-clause of the sql-query at runtime, depending on user-input. Depending on which input-fields are filled I have to add the correspending statements to the where-clause. How can this be realized with prepared statements?
我知道使用准备好的语句有助于避免 sql 注入。我的问题是，准备好的语句通常是非常静态的。我有一个问题，我在运行时根据用户输入构建 sql-query 的 where 子句。根据填充的输入字段，我必须将相应的语句添加到 where 子句中。如何通过准备好的语句实现这一点？
回答by Jonathan
I guess you could dynamically build your prepared statements based on what columns they want to query, i.e. use a StringBuffer and a loop to build them based on the required columns.
我猜您可以根据他们想要查询的列动态构建准备好的语句，即使用 StringBuffer 和循环根据所需的列构建它们。
For efficiency you should keep these in some kind of in-memory lookup. So you'd end up with a Map or other collection of prepared statements where the retrieval key is the columns they're designed to query against.
为了提高效率，您应该将这些保留在某种内存中查找中。所以你最终会得到一个 Map 或其他准备好的语句集合，其中检索键是它们被设计用来查询的列。
回答by ChadNC
If you are building the where/and clause(s) in your query based upon what what options the user selected then, I'm guessing, that you already know what changes will have to be made to the query in order get the desired data. If that is the case then you could create method that takes in the option the user asked for as a parameter to the method, the method builds the query and returns that query.
如果您正在根据用户选择的选项在查询中构建 where/and 子句，我猜您已经知道必须对查询进行哪些更改才能获得所需的数据. 如果是这种情况，那么您可以创建将用户要求的选项作为方法参数的方法，该方法构建查询并返回该查询。
//something similar to the following
public String buildQuery(int option){
StringBuilder sb = new StringBuilder();
sb.append("select fields from table");
switch(option){
case 1:
//build query and append to sb
sb.append("where clause for option1");
case 2:
//build query and append to sb
sb.append("where clause for option2");
default:
// build query using default
sb.append("default where clause");
}
return sb.toString();
}
// create the stored procedure
PreparedStatement ps = conn.prepareStatement(buildQuery(2));
ResultSet rs = ps.executeQuery();
Now if you need to use specific user entered values in the query you can store them temporarily in a list or map and then set them.
现在，如果您需要在查询中使用特定的用户输入值，您可以将它们临时存储在列表或地图中，然后进行设置。
ps.setString(1,list.get(0));
ps.setString(2,list.get(1));
ResultSet rs = ps.executeQuery();
There's probbaly a more elegeant solution to the problem but we have an application that queries the database for records using the user's input to build the queries and it's been working fine for the last two years. Hope that helps.
这个问题可能有一个更优雅的解决方案，但我们有一个应用程序，它使用用户的输入来查询数据库中的记录以构建查询，并且在过去两年中一直运行良好。希望有帮助。
回答by Alpedar
If you only want the protection from sql-injection, constructing the query at runtime is OK, as long as you don't insert anything user sent into the query (but inserting clause if user sent at least something in field is ok).
如果您只想防止 sql 注入，则在运行时构建查询是可以的，只要您不将用户发送到查询中的任何内容插入（但如果用户至少在字段中发送了一些内容，则插入子句是可以的）。
Performance - I don't know. Maybe DB will cache prepared statements, so if you construct exactly same again, it will be faster, or it won't. I dont know how to find out.
性能 - 我不知道。也许 DB 会缓存准备好的语句，所以如果你再次构造完全相同的语句，它会更快，或者不会。我不知道怎么查。
回答by Carlos Gavidia-Calderon
If you're using Hibernate, Criteria QueriesAPI is a nice tool for avoiding String concatenation to build SQL Queries. With plain JDBC, you'll have to go with a StringBuffer as @Jhonatan said.
如果您使用的是 Hibernate，Criteria QueriesAPI 是一个很好的工具，可以避免使用字符串连接来构建 SQL 查询。使用普通的 JDBC，您必须使用 @Jhonatan 所说的 StringBuffer 。
回答by saravana
Declare one method and pass the userinput through that method. Then use setStringor setLongdepending on your input data type within the preparedstement object.
声明一个方法并通过该方法传递用户输入。然后在 Preparedstement 对象中使用setString或setLong取决于您的输入数据类型。
