如何查询 List<String>在 Jdbc 模板中? [英] How to query for a List&lt;String&gt; in JdbcTemplate?

查看:75
本文介绍了如何查询 List<String>在 Jdbc 模板中?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在使用 Spring 的 JdbcTemplate 并运行这样的查询:

SELECT COLNAME FROM TABLEA GROUP BY COLNAME

没有传递命名参数,但是列名 COLNAME 将由用户传递.

问题

  1. 有没有办法使用占位符,例如 ? 用于列名?例如 SELECT ?来自 TABLEA GROUP BY ?

  2. 如果我想简单地运行上面的查询并得到一个 List 什么是最好的方法?

目前我在做:

List>数据 = getJdbcTemplate().queryForList(query);对于(地图 m:数据){System.out.println(m.get(COLNAME"));}

解决方案

有没有办法拥有占位符,比如?对于列名?例如 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

  1. Is there a way to have placeholders, like ? for column names? For example SELECT ? FROM TABLEA GROUP BY ?

  2. 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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆