在带有 JDBC 驱动程序的 Java 中使用准备好的语句和变量绑定 Order By [英] 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...

推荐答案

Placeholders ? 只能用于参数值，不能用于列和排序方向.因此，执行此操作的标准方法是指出例如这里是使用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，您可以将类型为 Sort 可以包含数据库排序所需的所有信息.

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.

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