使用准备的语句和变量绑定Order By在Java中使用JDBC驱动程序 [英] Using a prepared statement and variable bind Order By in Java with JDBC driver

问题描述

我使用

1. jdbcTemplate建立与mySQL DB的JDBC连线


2. 保护自己免受SQL注入攻击的语句


3. 需要接受来自用户的请求，对十几个不同列中的任何一列进行排序



4. 以下语句

1. jdbcTemplate to make JDBC connections to a mySQL DB
2. prepared statements to protect myself as much as possible from SQL injection attacks
3. in need to accept requests from the user to sort the data on any of a dozen different columns

4. the following statement

jdbcTemplate.query("SELECT * FROM TABLE1 ORDER BY ? ?", colName, sortOrder);

当然这不行，因为变量绑定不应该指定列名称，只是查询中表达式的参数值。

Of course this doesn't work, because the variable bindings aren't supposed to specify column names just parameter values for expressions in the query.

所以...人们如何解决这个问题？只是在Java代码中排序看起来像一个简单的解决方案，但因为我得到一个变量字符串的列排序，和一个变量告诉我的排序顺序....这是一个丑陋的数量的比较器条件盖。这似乎应该是一个共同的模式，解决它的常见问题...

So...how are people solving this issue? Just doing the sort in Java code seems like an easy solution, but since I'm getting a variable string for the column to sort on, and a variable telling me the sort order....that's an ugly number of comparator-conditions to cover. This seems like it should be a common problem with a common pattern to solve it...

推荐答案

占位符？只能用于参数值，但不能用于列和排序顺序方向。所以标准的做法是这样的。 此处是使用 String＃format（）或类似的操作，将您的列名称和顺序值附加到查询中。

Placeholders ? can only be used for parameter values but not with column and sort order directions. So the standard way to do this as is pointed e.g. here is to use String#format() or something similar to append your column name and order value to your query.

另一个选择是使用Spring Data JPA您可以将类型排序，它可以包含数据库排序所需的所有信息。

Another option is to use Spring Data JPA where you can give to your method as an argument an instance of type Sort which can contain all needed info for database to sort.

这篇关于使用准备的语句和变量绑定Order By在Java中使用JDBC驱动程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！