How to query for a List in JdbcTemplate?

Question

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 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"));
}

score 111 · Answer 1 · edited Nov 04 '16 at 08:08

111

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)

edited Nov 04 '16 at 08:08

Sabyasachi Mishra

1,677
2
31
49

answered Nov 04 '16 at 07:25

Ashwinie

1,111
2
7
3

Yogendra Singh · Accepted Answer · 2012-11-13T01:50:12.593

23

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
          }

edited Nov 13 '12 at 01:50

answered Nov 13 '12 at 01:16

Yogendra Singh

33,927
6
63
73

1

In using dynamic query I'll be putting whatever the user enters into my dynamic query which is a scary part. – birdy Nov 13 '12 at 01:26
@birdy: You may want to check the string has only `word characters` by using a simple regex `\\w`. That will prevent any sql injection possibility. – Yogendra Singh Nov 13 '12 at 01:33
9

You should use prepared statements instead of doing this regex stuff. Try this: ```getJdbcTemplate().query("SELECT ? FROM TABLEA GROUP BY ?", new RowMapper(){ public String mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getString(1); } }, colName, colName);``` – Jean Feb 27 '15 at 03:06
how we will map the rows if we have more than one argument.. ie.. in the place of columnName – MS Ibrahim Jul 21 '15 at 12:59
With lambda you can use this: List data = getJdbcTemplate().query(query, (rs, rowNum) -> rs.getString(1)); – Fabio Araujo Mar 11 '20 at 13:48

score 23 · Answer 3 · answered Aug 04 '16 at 05:17

23

Use following code

List data = getJdbcTemplate().queryForList(query,String.class)

answered Aug 04 '16 at 05:17

Amit Sargar

271
2
5

This is working fine. Going forward, how can I pass values if query has *IN operator* to this ? Please assist! The values are List inside In operator – Karthik_S_A Dec 04 '19 at 09:46

score 1 · Answer 4 · answered Dec 12 '12 at 05:14

1

You can't use placeholders for column names, table names, data type names, or basically anything that isn't data.

answered Dec 12 '12 at 05:14

Jayamohan

12,734
2
27
41

How to query for a List in JdbcTemplate?

4 Answers4

Linked