如何查询 List<String>在 Jdbc 模板中? [英] How to query for a List<String> in JdbcTemplate?
问题描述
我正在使用 Spring 的 JdbcTemplate
并运行这样的查询:
SELECT COLNAME FROM TABLEA GROUP BY COLNAME
没有传递命名参数,但是列名 COLNAME
将由用户传递.
问题
有没有办法使用占位符,例如
?
用于列名?例如SELECT ?来自 TABLEA GROUP BY ?
如果我想简单地运行上面的查询并得到一个
List
什么是最好的方法?
目前我在做:
List
有没有办法拥有占位符,比如?对于列名?例如 SELECT ?来自 TABLEA GROUP BY ?
使用动态查询如下:
String queryString = "SELECT "+ colName+ " FROM TABLEA GROUP BY "+ colName;
<块引用>
如果我想简单地运行上面的查询并获得一个列表,最好的方法是什么?
List数据 = getJdbcTemplate().query(query, new RowMapper(){公共字符串 mapRow(ResultSet rs, int rowNum)抛出 SQLException {返回 rs.getString(1);}});
要停止 SQL 注入,请检查 colName 中的非单词字符:
Pattern pattern = Pattern.compile("\\W");if(pattern.matcher(str).find()){//作为无效的列名抛出异常}
I'm using Spring's JdbcTemplate
and running a query like this:
SELECT COLNAME FROM TABLEA GROUP BY COLNAME
There are no named parameters being passed, however, column name, COLNAME
, will be passed by the user.
Questions
Is there a way to have placeholders, like
?
for column names? For exampleSELECT ? FROM TABLEA GROUP BY ?
If I want to simply run the above query and get a
List<String>
what is the best way?
Currently I'm doing:
List<Map<String, Object>> data = getJdbcTemplate().queryForList(query);
for (Map m : data) {
System.out.println(m.get("COLNAME"));
}
Is there a way to have placeholders, like ? for column names? For example SELECT ? FROM TABLEA GROUP BY ?
Use dynamic query as below:
String queryString = "SELECT "+ colName+ " FROM TABLEA GROUP BY "+ colName;
If I want to simply run the above query and get a List what is the best way?
List<String> data = getJdbcTemplate().query(query, new RowMapper<String>(){
public String mapRow(ResultSet rs, int rowNum)
throws SQLException {
return rs.getString(1);
}
});
EDIT: To Stop SQL Injection, check for non word characters in the colName as :
Pattern pattern = Pattern.compile("\\W");
if(pattern.matcher(str).find()){
//throw exception as invalid column name
}
这篇关于如何查询 List<String>在 Jdbc 模板中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!