一、什么是NamedParameterJdbcTemplate
NamedParameterJdbcTemplate类拓展了JdbcTemplate类,对JdbcTemplate类进行了封装从而支持**具名参数特性**。
什么是具名参数?
SQL 按名称(以冒号开头)⽽不是按位置进⾏指定。
例如:
- 传统的jdbcTemplate的SQL语句定义,通过问号进行占位,如下:
private final String QUERY_SQL = "INSERT INTO T_USER(username,password) VALUES(?,?)";
- NamedParameterJdbcTemplate的吗具名参数定义SQL语句:
private final String QUERY_SQL = "INSERT INTO T_USER(username,password) VALUES(:username,:password)";
NamedParameterJdbcTemplate主要提供以下三类方法:execute方法、query及queryForXXX方法、update及batchUpdate方法。
二、准备工作
1、创建工程
2、添加Maven依赖
<!-- jdbc依赖-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- 连接数据库依赖-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- lombok依赖-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
另外:jdbc依赖也可以换成
<!-- jdbcTemplate 依赖-->
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.0.9.RELEASE</version>
</dependency>
3、yaml配置
#MySql8.0
spring:
datasource:
driverClassName: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/数据库名?useSSL=false&characterEncoding=utf-8&useUnicode=true&serverTimezone=Asia/Shanghai
username: 数据库用户名
password: 数据库密码
4、数据库结构
注意:数据库的id主键设置成自动递增
,所以添加的时候不用考虑id
5、JavaBean实体
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TUser implements Serializable {
private Integer id;
private String username;
private String password;
}
三、实际使用
1.支持的类
(1)SqlParameterSource
可以使用SqlParameterSource实现具名参数,默认实现有 :
- MapSqlParameterSource,只是封装了java.util.Map
- BeanPropertySqlParameterSource封装了一个JavaBean对象,通过JavaBean对象属性设置具名参数值
- EmptySqlParameterSource 一个空的SqlParameterSource ,占位使用
(2)RowMapper
这个接口为了实现sql查询结果和对象间的转换
,可以自己实现,也可以使用系统实现,主要实现类有:
- SingleColumnRowMapper,sql结果为一个单列的数据,如List , List,String,Integer等
- BeanPropertyRowMapper,sql结果匹配到对象 List< XxxVO> , XxxVO
2、插入/修改/删除数据,使用update方法
(1)Map作为参数
API: int update(String sql, Map<String, ?> paramMap)
@Autowired
private NamedParameterJdbcTemplate template;
//添加数据
Map<String,Object> map = new HashMap<>();
map.put("username","刘亦菲");
map.put("password","4983ghh");
template.update("INSERT INTO T_USER(username,password) VALUES(:username,:password)",map);
//修改数据
Map<String,Object> map = new HashMap<>();
map.put("username","刘诗诗");
map.put("password","ewgg");
map.put("id",4);
template.update("UPDATE T_USER SET USERNAME = :username,PASSWORD = :password WHERE ID = :id",map);
// 删除数据
Map<String,Object> map = new HashMap<>();
map.put("id",4);
template.update("DELETE FROM T_USER WHERE ID = :id",map);
(2)BeanPropertySqlParameterSource作为参数
API: int update(String sql, SqlParameterSource paramSource)
- 创建参数DTO
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ConditionDTO {
private String password;
private String username;
}
- 给DTO属性传入数据并作为参数传入
ConditionDTO conditionDTO = new ConditionDTO();
conditionDTO.setUsername("成龙");
conditionDTO.setPassword("2432tgh");
BeanPropertySqlParameterSource beanParam = new BeanPropertySqlParameterSource(conditionDTO);
template.update("INSERT INTO T_USER(username,password) VALUES(:username,:password)",beanParam);
注意:用BeanPropertySqlParameterSource作为参数的时候,可以将参数DTO属性存值单独出来一个方法,这样可以解耦,代码维护相对轻松。如下:
private ConditionDTO getConditionDTO() {
ConditionDTO conditionDTO = new ConditionDTO();
conditionDTO.setUsername("成龙");
conditionDTO.setPassword("2432tgh");
return conditionDTO;
}
BeanPropertySqlParameterSource beanParam = new BeanPropertySqlParameterSource(getConditionDTO());
template.update("INSERT INTO T_USER(username,password) VALUES(:username,:password)",beanParam);
(3)MapSqlParameterSource 作为参数
API: int update(String sql, SqlParameterSource paramSource)
MapSqlParameterSource mapSql = new MapSqlParameterSource();
mapSql.addValue("username","李连杰")
.addValue("password","huewrgowrei");
template.update("INSERT INTO T_USER(username,password) VALUES(:username,:password)",mapSql);
3、查询
(1)返回单行单列数据
注意:单行单列不是单独的一行或者单独的一列数据,单列单行是只有一个数据,如下:
API: public < T > T queryForObject(String sql, Map<String, ?> paramMap, Class<T> requiredType)
API: public < T > T queryForObject(String sql, SqlParameterSource paramSource, Class<T> requiredType)
Integer count = template.queryForObject(
"SELECT COUNT(*) FROM T_USER", new HashMap<>(), Integer.class);
使用EmptySqlParameterSource:
String username = template.queryForObject( "SELECT USERNAME FROM T_USER WHERE ID = 4", EmptySqlParameterSource.INSTANCE, String.class);
(2)返回 (多行)单列 数据
多行单列形式:
API: public < T> List< T> queryForList(String sql, Map<String, ?> paramMap, Class< T > elementType)
API: public < T> List< T> queryForList(String sql, SqlParameterSource paramSource, Class< T> elementType)
List< String> nameList = template.queryForList("SELECT USERNAME FROM T_USER", new HashMap<>(), String.class);
nameList.forEach(System.out::println);
控制台打印结果:
(3)返回单行数据
解释:数据库中的一行数据对应的就是java中的一个bean实体
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)
//BeanPropertyRowMapper会把下划线转化为驼峰属性
TUser user = template.queryForObject(
"SELECT * FROM T_USER LIMIT 1", new HashMap<>(), new BeanPropertyRowMapper<TUser>(TUser.class));
System.out.println("查询出来的user是:"+user);
(4)返回Map形式的单行数据
API: public Map< String, Object> queryForMap(String sql, Map< String, ?> paramMap)
API: public Map< String, Object> queryForMap(String sql, SqlParameterSource paramSource)
@RequestMapping("/querySingleTwo")
public Map testQueryForObjectTwo() {
Map< String, Object> userMap = template.queryForMap("SELECT * FROM T_USER LIMIT 1", new HashMap<>());
System.out.println(userMap);
return userMap;
}
postman返回结果:
(5)返回多行数据
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)
@RequestMapping("/querySingleTwo")
public List< TUser> testQueryForObjectTwo() {
List< TUser> userList = template.query(
"SELECT * FROM T_USER",
new BeanPropertyRowMapper<>(TUser.class)
);
return userList;
}
postman返回结果:
[
{
"id": 1,
"username": "admin",
"password": "admin"
},
{
"id": 2,
"username": "abc",
"password": "123"
},
{
"id": 3,
"username": "abcd",
"password": "1234"
},
{
"id": 4,
"username": "abcde",
"password": "12345"
},
{
"id": 109,
"username": "刘亦菲",
"password": "4983ghh"
},
{
"id": 110,
"username": "刘亦菲",
"password": "4983ghh"
},
{
"id": 111,
"username": "成龙",
"password": "2432tgh"
},
{
"id": 112,
"username": "李连杰",
"password": "huewrgowrei"
}
]
(6)返回多行数据(Map)
API: public List< Map< String, Object>> queryForList(String sql, Map< String, ?> paramMap)
API: public List< Map< String, Object>> queryForList(String sql, SqlParameterSource paramSource)
@RequestMapping("/querySingleTwo")
public List<Map<String, Object>> testQueryForObjectTwo() {
List<Map<String, Object>> mapList = template.queryForList(
"SELECT * FROM T_USER", new HashMap<>());
return mapList;
}
postman返回结果:
[
{
"id": 1,
"username": "admin",
"password": "admin"
},
{
"id": 2,
"username": "abc",
"password": "123"
},
{
"id": 3,
"username": "abcd",
"password": "1234"
},
{
"id": 4,
"username": "abcde",
"password": "12345"
},
{
"id": 109,
"username": "刘亦菲",
"password": "4983ghh"
},
{
"id": 110,
"username": "刘亦菲",
"password": "4983ghh"
},
{
"id": 111,
"username": "成龙",
"password": "2432tgh"
},
{
"id": 112,
"username": "李连杰",
"password": "huewrgowrei"
}
]
4、获取新增的主键
NamedParameterJdbcTemplate还新增了KeyHolder类,使⽤它我们可以获得主键,类似Mybatis中的useGeneratedKeys。
代码示例:
@RequestMapping("/querySingleTwo")
public int testQueryForObjectTwo() {
String sql = "INSERT INTO T_USER(username,password) VALUES(:username,:password)";
ConditionDTO conditionDTO = new ConditionDTO("r43g", "呼呼");
SqlParameterSource sqlParameterSource = new BeanPropertySqlParameterSource(conditionDTO);
KeyHolder keyHolder = new GeneratedKeyHolder();
template.update(sql, sqlParameterSource, keyHolder);
int k = keyHolder.getKey().intValue();
System.out.println("主键值是:"+k);
return k;
}
返回结果就是新增的主键。
5、批量操作 batchUpdate
方法源码:
(1)批量添加
SqlParameterSource[] 参数为例
List<TUser> list = new ArrayList<>();
TUser tUser1 = new TUser();
tUser1.setPassword("t7493857vd");
tUser1.setUsername("给会儿");
TUser tUser2 = new TUser();
tUser2.setPassword("erwghrthey");
tUser2.setUsername("国瑞");
list.add(tUser1);
list.add(tUser2);
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list.toArray());
template.batchUpdate("INSERT INTO T_USER(username,password) VALUES(:username,:password)",batch);
(2)批量更新
List<TUser> list = new ArrayList<>();
TUser tUser1 = new TUser();
tUser1.setPassword("eryg");
tUser1.setUsername("个人谈话人");
tUser1.setId(109);
TUser tUser2 = new TUser();
tUser2.setPassword("我二哥");
tUser2.setUsername("个羊肉汤");
tUser1.setId(110);
TUser tUser3 = new TUser();
tUser3.setPassword("gerhr6");
tUser3.setUsername("反倒是规范");
tUser3.setId(111);
list.add(tUser1);
list.add(tUser2);
list.add(tUser3);
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list.toArray());
template.batchUpdate("UPDATE T_USER SET USERNAME = :username,PASSWORD = :password WHERE ID = :id",batch);
(3)批量删除
List<TUser> list = new ArrayList<>();
TUser tUser1 = new TUser();
tUser1.setId(109);
TUser tUser2 = new TUser();
tUser2.setId(112);
TUser tUser3 = new TUser();
tUser3.setId(113);
list.add(tUser1);
list.add(tUser2);
list.add(tUser3);
SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(list.toArray());
template.batchUpdate("DELETE FROM T_USER WHERE ID = :id",batch);