【SpringBoot初级篇】JdbcTemplate常用方法

• JdbcTemplate 查询
• JdbcTemplate 插入、更新、删除
• execute执行任意的SQL
• NamedParameterJdbcTemplate

JdbcTemplate 查询

1、查询记录条数

String sql = "select count(*) from tb_user";
int count = jdbcTemplate.queryForObject(sql, Integer.class);

2、按条件查询记录条数，通过?参数绑定

String sql = "select count(*) from tb_user where username = ?";
int count = jdbcTemplate.queryForObject(sql, Integer.class, "Tom");或者String sql = "select count(*) from tb_user where username = ?";
Object[] params = new Object[]{"Tom"};
int count = jdbcTemplate.queryForObject(sql, params, Integer.class);

3、查询多个字段，返回一个Map

String sql = "select user_id, username, age from tb_user where user_id = ?";
Object[] params = new Object[]{"46a8247fbffc46c3a591961351fa3277"};
Map resultMap = jdbcTemplate.queryForMap(sql, params);

4、查询多条数据，每条数据都以Map形式返回，整个结果是List>

String sql = "select user_id, username, age from tb_user where age = ?";
Object[] params = new Object[]{25};
List> resultMapList = jdbcTemplate.queryForList(sql, params);

5、查询单条数据并通过RowMapper映射成实体对象

String sql = "select user_id, username, mobile, create_time from tb_user where user_id = ?";
Object[] params = new Object[]{"0ff5771760c1483abaefa7a3e530ea6d"};
User user = jdbcTemplate.queryForObject(sql, params,new RowMapper() {@Overridepublic User mapRow(ResultSet rs, int rowNum) throws SQLException {// 在这里做字段到实体的映射User u = new User();u.setUserId(rs.getString("user_id"));u.setUsername(rs.getString("username"));u.setMobile(rs.getString("mobile"));u.setCreateTime(rs.getTimestamp("create_time"));return u;}});

6、查询对象集合

String sql = "select user_id, username, mobile, create_time from tb_user where username = ?";
Object[] params = new Object[]{"相同的用户名"};
List users = jdbcTemplate.query(sql, params,new RowMapper() {@Overridepublic User mapRow(ResultSet rs, int rowNum) throws SQLException {User u = new User();u.setUserId(rs.getString("user_id"));u.setUsername(rs.getString("username"));u.setMobile(rs.getString("mobile"));u.setCreateTime(rs.getTimestamp("create_time"));return u;}});

7、将字段到实体映射抽出来单独维护

通常在应用中，字段到实体的映射只需要编写一份来维护，因此前边的两例中的User映射可以单独抽出来成为一个UserRowMapper类：

public class UserRowMapper implements RowMapper {@Overridepublic User mapRow(ResultSet rs, int rowNum) throws SQLException {User u = new User();u.setUserId(rs.getString("user_id"));u.setUsername(rs.getString("username"));u.setMobile(rs.getString("mobile"));u.setCreateTime(rs.getTimestamp("create_time"));return u;}
}

然后在使用的时候创建就行了：

String sql = "select user_id, username, mobile, create_time from tb_user where username = ?";
List users = jdbcTemplate.query(sql, new Object[]{"相同的用户名"}, new UserRowMapper());

8、插入数据并返回生成的uuid主键

String sql = "insert into tb_user (username, mobile, create_time, age) values(?, ?, ?, ?)";// KeyHolder保存返回的数据，可以指定返回的列
KeyHolder keyHolder = new GeneratedKeyHolder();jdbcTemplate.update(new PreparedStatementCreator() {@Overridepublic PreparedStatement createPreparedStatement(Connection con) throws SQLException {// 指定返回的属性列名为user_idPreparedStatement ps = con.prepareStatement(sql, new String[]{"user_id"});// 插入的数据ps.setString(1, "新用户");ps.setString(2,"12345678");ps.setTimestamp(3, new Timestamp(System.currentTimeMillis()));ps.setInt(4, 28);return ps;}
}, keyHolder);// 输出生成的uuid
System.out.println(keyHolder.getKeys().get("user_id"));

JdbcTemplate 插入、更新、删除

在JdbcTemplate中，数据的插入、更新、删除操作都是通过update(...)来完成的。

1、插入单条数据

String sql = "insert into tb_user(user_id, username, mobile, create_time) values (?, ?, ?, ?)";
Object[] params = new Object[]{uuid(), "新用户", "134578546", new Date()};
int successCount = jdbcTemplate.update(sql, params);

2、批量插入

String sql = "insert into tb_user(user_id, username, mobile, create_time) values (?, ?, ?, ?)";
List params = new ArrayList<>();
params.add(new Object[]{uuid(), "新用户1", "134578546", new Date()});
params.add(new Object[]{uuid(), "新用户2", "134578546", new Date()});
int[] successCounts = jdbcTemplate.batchUpdate(sql, params);

3、更新单条数据

String sql = "update tb_user set username = ? where user_id = ?";
Object[] params = new Object[]{"更新用户名", "a7e7c5b4750945249d3da9c543f3fe43"};
int successCount = jdbcTemplate.update(sql, params);

4、批量更新

String sql = "update tb_user set username = ? where user_id = ?";
List params = new ArrayList<>();
params.add(new Object[]{"更新用户名1", "0ff5771760c1483abaefa7a3e530ea6d"});
params.add(new Object[]{"更新用户名2", "4e88b38660324334be61f0c9b2edd7f6"});
int[] successCounts = jdbcTemplate.batchUpdate(sql, params);

5、删除单条数据

String sql = "delete from tb_user where user_id = ?";
Object[] params = new Object[]{"a7e7c5b4750945249d3da9c543f3fe43"};
int successCount = jdbcTemplate.update(sql, params);

6、批量操作

public void removeByIds(String[] rids, Integer year) {String deleteSQL = "delete from nt_budget_income_plan where  ADMDIV_CODE = ? and year = ? and ID = ?";// 设置预编译参数jdbcTemplate.batchUpdate(deleteSQL, new BatchPreparedStatementSetter() {@Overridepublic void setValues(PreparedStatement ps, int i) throws SQLException {String rid = rids[i];ps.setString(1, SessionUtil.getSession().getAdmdivCode());ps.setInt(2, year);ps.setLong(3, Long.parseLong(rid));}@Overridepublic int getBatchSize() {return rids.length;}});
}

execute执行任意的SQL

可以通过execute(..)执行任何SQL，一般用来执行DDL语句

String sql = "create table new_table (id varchar(32), name varchar(100))";
jdbcTemplate.execute(sql);

NamedParameterJdbcTemplate

在使用JdbcTemplate的时候，如果要使用参数，那么需要通过在SQL中使用?做占位符。

NamedParameterJdbcTemplate提供了一个更加直观的方式来定义SQL中的占位符，即通过:变量名的形式。

可以通过Map或者BeanPropertySqlParameterSource传递参数值：

Map直接设置参数值；

String sql = "select count(*) from tb_user where username = :username and age = :age";
Map params = new HashMap<>(2);
params.put("username", "Michael");
params.put("age", 25);
Integer count = namedParameterJdbcTemplate.queryForObject(sql, params, Integer.class);

其他方法同JdbcTemplate类似