I'm using springs jdbctemplate and running a query like below:
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 example SELECT ? 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"));
This question is related to
java
jdbc
jdbctemplate
spring-jdbc
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
}
You can't use placeholders for column names, table names, data type names, or basically anything that isn't data.
Use following code
List data = getJdbcTemplate().queryForList(query,String.class)
To populate a List of String, you need not use custom row mapper. Implement it using queryForList.
List<String>data=jdbcTemplate.queryForList(query,String.class)
Source: Stackoverflow.com