0%

NamedParameterJdbcTemplete使用方法总结

最近修复SQL注入的问题,单当遇到in操作的时候,直接使用?占位符是行不通的,因此进行查询发现NamedParameterJDBCTemplate能够修复这种问题,因此在这里记录下NamedParameterJDBCTemplate的使用方式,常规NamedParameterJDBCTemplate提供execute方法、query及queryForXXX方法、update和batchUpdate方法

支持类

SqlParameterSource简介

SqlParameterSource命名参数设置 1. MapSqlParameterSource 使用java.util.Map封装使用 2. BeanPropertySqlParameterSource封装了JavaBean对象,通过对象传递参数 3. EmptySqlParameterSource空的,一般用作占位

RowMapper简介

实现sql查询结果和对象之间的转换,可自己实现或者系统实现,主要的实现类: 1. SingleColumnRowMappersql查询结果为单列数据,如List<String>、List<Integer>、String、Integer 2. BeanPropertyRowMapper,sql 结果匹配到对象List<JavaBean>,JavaBean

updateXXX方法使用

Map作为参数

API:int update(String sql,Map<String,Object> map)

1
2
3
4
5
Map<String,Obejct> paramMap = new HashMap<String,Object>();
paramMap.put("id","1");
paramMap.put("name","1");

template.update("INSERT INTO TEMP_TABLE VALUES(:id,:name)",paramMap);

BeanPropertySqlParameterSource作为参数

API:int update(String sql, BeanPropertySqlParameterSource paramSource)

1
2
3
4
public class Student{
private String name;
private int age;
}

1
2
3
4
Student st = new Student();
st.setName("张三");
st.setAge(10);
template.update("INSERT INTO STUDENT VALUES(:name,:age)",new BeanPropertySqlParameterSource(st));

MapSqlParameterSource作为参数

API:int update(String sql,MapSqlParameterSource paramSource)

1
2
MapSqlParameterSource paramSource = new MapSqlParameterSource().addValue("name","张三").addValue("age",10);
template.update("INSERT INTO STUDENT VALUES (:name,:age)",paramSource);

查询

单行单列数据

API:public T queryForObject(String sql,Map<String,Object> paramMap,Class requiredType) API:public T queryForObject(String sql,SqlParameterSource paramSource,Class requiredType)

1
Integer tempInt =  template.queryForObject("select count(*) from student ",new HashMap<String,Object>(),Integer.class);
1
String name = template.queryForObject("select name from student limit 1",EmptySqlParameterSource.INSTANCE,String.class);

多行数据查询

API:public<T> List<T>queryForList(String sql,Map<String,Object> paramMap,Class<T> elementType) API: public < T> List< T> queryForList(String sql, SqlParameterSource paramSource, Class< T> elementType)

1
List< String> namelist = template.queryForList("select name from student", new HashMap<>(), String.class);

单行数据查询

API:public < T> T queryForObject(String sql, Map< String, ?> paramMap, RowMapper< T>rowMapper) API:public < T> T queryForObject(String sql, SqlParameterSource paramSource, RowMapper< T> rowMapper)

1
2
3
4
Student  stu = template.queryForObject(
"select * from student limit 1", new HashMap<>(), new BeanPropertyRowMapper<Student>(Student.class));
//BeanPropertyRowMapper会把下划线转化为驼峰属性
//结果对象可比实际返回字段多或者少
这两个API也可以使用SingleColumnRowMapper返回单行单列数据
1
2
String name = template.queryForObject(
"select name from student limit 1", EmptySqlParameterSource.INSTANCE, new SingleColumnRowMapper<>(String.class));

单行数据(Map)

API:public Map< String, Object> queryForMap(String sql, Map< String, ?> paramMap) API:public Map< String, Object> queryForMap(String sql, SqlParameterSource paramSource)

1
Map< String, Object> studentMap = template.queryForMap("select * from student limit 1", new HashMap<>());

多行数据

API:public < T> List< T> query(String sql, Map< String, ?> paramMap, RowMapper< T> rowMapper) API:public < T> List< T> query(String sql, SqlParameterSource paramSource, RowMapper< T> rowMapper) API:public < T> List< T> query(String sql, RowMapper< T> rowMapper)

1
2
3
4
List< Student> studentList = template.query(
"select * from student",
new BeanPropertyRowMapper<>(Student.class)
);

多行数据(Map)

API:public List< Map< String, Object>> queryForList(String sql, Map< String, ?> paramMap) API:public List< Map< String, Object>> queryForList(String sql, SqlParameterSource paramSource)

1
List<Map<String, Object>> mapList = template.queryForList("select * from student", new HashMap<>());

应用

SQL注入中in问题的解决

1
2
3
4
5
6
7
8
9
10
11
12
13
14
public void queryListByMapSqlParameterSource(List<String> userids){
String sql = "select * from user_table u where u.user_id in (:userids )";

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("userids", userids);

List<Map<String,Object>> list = namedJdbcTemplate.queryForList(sql, parameters);

for (Map<String, Object> map : list) {
System.out.println("-------------------");
System.out.println(map.get("user_id"));
System.out.println(map.get("user_name"));
}
}

本文引自NamedParameterJdbcTemplate常用方法总结 本文引自springjdbc-NamedParameterJdbcTemplate处理in查询