Spring JDBC Template使用小记

文章目录

Spring JDBC Template

Spring JDBC Template

表结构

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int NOT NULL,
  `sex` tinyint DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

主要方法

查询

query
queryForList
queryForObject
queryForMap
queryForRowSet

queryForObject 返回单个基本类型、字符串

queryForObject()方法可直接返回对象，但只能是基本类型及其包装类，以及 String，不能返回自定义对象，比如 jdbcTemplate.queryForObject(sql, User.class, id)。结果集必须为一条，否则报错

public String getName(Integer id) {
	String sql = "SELECT name FROM `user` where id = ?";
	return jdbcTemplate.queryForObject(sql, String.class, id);
}

queryForObject 返回单个实体类

BeanPropertyRowMapper 可以自动映射实体类的字段与数据库的列，实体类的驼峰 <-> 数据库的下划线。结果集必须为一条，否则报错

public User getUser(Integer id) {
   String sql = "SELECT * FROM `user` where id = ?";
   return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), id);
}

queryForMap 返回单个Map

键为数据库的列名。结果集必须为一条，否则报错

public Map<String, Object> getUserForMap(Integer id) {
   String sql = "SELECT * FROM `user` where  id = ?";
   return jdbcTemplate.queryForMap(sql, id);
}

结果集为空或多条时的解决方案

queryForObject、queryForMap都要求结果集有且仅有一条数据，当结果集为空时，抛出异常EmptyResultDataAccessException；当返回多条时，抛出异常IncorrectResultSizeDataAccessException，当为空时可以采用捕获异常的方式处理，但当返回多条数据时，不建议使用此方法，应该使用唯一索引查询，或者使用limit 1。

/**
 * 为空时的解决办法
 */
public String getName(Integer id) {
	try {
		String sql = "SELECT name FROM `user` where id = ?";
		return jdbcTemplate.queryForObject(sql, String.class, id);
	} catch (EmptyResultDataAccessException e) {
		return null;
	}
}

queryForList 返回基本类型、字符串的List

public List<Integer> listAge() {
   String sql = "SELECT age FROM `user`";
   return jdbcTemplate.queryForList(sql, Integer.class);
}

query 返回对象的List

BeanPropertyRowMapper除了可以用于单个实体类，也可以作用于List。
以下方式，若结果集为空不会抛出异常。

public List<User> listUser() {
   String sql = "SELECT * FROM `user`";
   return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
}

批量查询 IN

可以使用 NamedParameterJdbcTemplate 完成 IN 查询。传统的 JdbcTemplate 的占位符是 ?，而 NamedParameterJdbcTemplate 可以给参数命名，同时还能将参数自动展开为适当数量的占位符。支持两种形式的参数：SqlParameterSource、Map：

// 参数为 SqlParameterSource
public List<User> listUser2() {
    Set<Integer> ids = new HashSet<>();
    ids.add(1);
    ids.add(2);
    String sql = "SELECT * FROM `user` where id in (:ids)";
    MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
    mapSqlParameterSource.addValue("ids", ids);
    
    return namedParameterJdbcTemplate.query(sql, mapSqlParameterSource, new BeanPropertyRowMapper<>(User.class));
}

// 参数为 Map
public List<User> listUser3() {
    Set<Integer> ids = new HashSet<>();
    ids.add(1);
    ids.add(2);
    String sql = "SELECT * FROM `user` where id in (:ids)";
    Map<String, Object> params = new HashMap<>();
    params.put("ids", ids);

    return namedParameterJdbcTemplate.query(sql, params, new BeanPropertyRowMapper<>(User.class));
}

插入、更新、删除

update
batchUpdate

插入

public int insert(User user) {
	String sql = "INSERT INTO `user`(name, age, sex, email) VALUE (?, ?, ?, ?)";
	return jdbcTemplate.update(sql, user.getName(), user.getAge(), user.getSex(), user.getEmail());
}

插入并返回自增的主键

用 KeyHolder 保存自增的主键，且 con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS) 第二个参数是必须的。

public Integer insertReturnId(User user) {
	KeyHolder keyHolder = new GeneratedKeyHolder();
	String sql = "INSERT INTO `user`(name, age, sex, email) VALUE (?, ?, ?, ?)";
	jdbcTemplate.update(con -> {
		PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
		ps.setString(1, user.getName());
		ps.setInt(2, user.getAge());
		ps.setByte(3, user.getSex());
		ps.setString(4, user.getEmail());
		return ps;
	}, keyHolder);
	Number key = keyHolder.getKey();
	return key.intValue();
}

使用PreparedStatement，且插入的基本类型的字段为null报错

比如说上面的 insertReturnId() 方法，当 age 或sex 为空时会抛出 NullPointerException，解决办法如下，使用 setNull()方法。

public Integer insertReturnId(User user) {
	KeyHolder keyHolder = new GeneratedKeyHolder();
	String sql = "INSERT INTO `user`(name, age, sex, email) VALUE (?, ?, ?, ?)";
	jdbcTemplate.update(con -> {
		PreparedStatement ps = con.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
		ps.setString(1, user.getName());
		Integer age = user.getAge();
		if (age == null) {
			// setNull
			ps.setNull(2, Types.INTEGER);
		} else {
			ps.setInt(2, age);
		}

		Byte sex = user.getSex();
		if (sex == null) {
			// setNull
			ps.setNull(3, Types.TINYINT);
		} else {
			ps.setInt(3, sex);
		}

		ps.setString(4, user.getEmail());
		return ps;
	}, keyHolder);
	Number key = keyHolder.getKey();
	return key.intValue();
}

批量插入

public void batchInsert(List<User> users) {
	String sql = "INSERT INTO `user`(name, age, sex, email) VALUE (?, ?, ?, ?)";
	// sql, 集合 一批处理多少条, ...
	jdbcTemplate.batchUpdate(sql, users, 10, (ps, user) -> {
		ps.setString(1, user.getName());
		Integer age = user.getAge();
		if (age == null) {
			ps.setNull(2, Types.INTEGER);
		} else {
			ps.setInt(2, age);
		}

		Byte sex = user.getSex();
		if (sex == null) {
			ps.setNull(3, Types.TINYINT);
		} else {
			ps.setInt(3, sex);
		}

		ps.setString(4, user.getEmail());
	});
}

更新、删除

更新、删除的用法与插入一样