JdbcTemplate简化数据库操作

Posted on | Edited on | In | Views:
Symbols count in article: 5.8k | Reading time ≈ 5 mins.

普片来说，后端服务系统，大部分都是需要和数据库打交道的。对于简单的系统，可能数据库操作的代码编写时间就占据了整个系统开发时间的40%。频繁的数据表到对象的映射编码，或对象到数据表映射编码，大多数都是繁琐、低效的低等工作。

所以市面上都有这些问题的解决方案，如：hibernate、mybatis和JdbcTemplate等，hibernate效率低、笨重，mybatis的编写风格不太喜欢，所以笔者要说的是轻量、简洁的JdbcTemplate。

数据库操作大多数为增删改查操作，下面来讲解这些操作的编写和其中的坑。

insert

个人习惯，每个表都用long类型记录创建时间和修改时间，所以insert操作基本可以用一下模板来写：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public boolean insert(Po po) {
        try {
            long curTime = System.currentTimeMillis();
            po.setCreateTime(curTime);
            po.setUpdateTime(curTime);

            SimpleJdbcInsert insert = new SimpleJdbcInsert(jdbcTemplate).withTableName(tableName);
            SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(po);

            return insert.execute(parameterSource) > 0;
        } catch (DuplicateKeyException e) {
            // TODO 重复插入的的处理，可以返回true，也可以抛异常，按业务需求来定
            return true;
        } catch (Exception e) {
            // TODO 打印日志并抛异常即可
            throw new RuntimeException();
        }
    }

insert.execute方法，无论是因为是主键重复，还是unique key重复，都会抛DuplicateKeyException异常，所以只需要catch这个异常，就可以处理重复插入的处理。
补充方法指定自增id字段 usingGeneratedKeyColumns("id")，并使用insert.executeAndReturnKey来代替insert.execute方法，可以获取数据库自增的id。

重点来说一下BeanPropertySqlParameterSource，它会自动将对象的属性映射到数据表的字段中，底层原理并不是简单的属性转换，而是对象和表字段匹配。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
public List<Object> matchInParameterValuesWithInsertColumns(SqlParameterSource parameterSource) {
	List<Object> values = new ArrayList<>();
	//去掉大小写敏感获得的map
	Map<String, String> caseInsensitiveParameterNames = SqlParameterSourceUtils.extractCaseInsensitiveParameterNames(parameterSource);
	for (String column : this.tableColumns) {
        //属性名直接匹配字段名
		if (parameterSource.hasValue(column)) {
			values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, column));
		}
		else {
            //属性名和字段全小写名匹配
			String lowerCaseName = column.toLowerCase();
			if (parameterSource.hasValue(lowerCaseName)) {
				values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, lowerCaseName));
			}
			else {
                //属性名匹配字段名的下划线格式
				String propertyName = JdbcUtils.convertUnderscoreNameToPropertyName(column);
				if (parameterSource.hasValue(propertyName)) {
					values.add(SqlParameterSourceUtils.getTypedValue(parameterSource, propertyName));
				}
				else {
                    //属性小写名和字段小写名匹配
					if (caseInsensitiveParameterNames.containsKey(lowerCaseName)) {
						values.add(SqlParameterSourceUtils.getTypedValue(
								parameterSource, caseInsensitiveParameterNames.get(lowerCaseName)));
					}
					else {
                        //没有匹配到
						values.add(null);
					}
				}
			}
		}
	}
	return values;
}

在实验中，Po的createTime属性对应的数据表的create_time，我修改为createtime，也是没有问题的。另外，如果对象中多一个属性，并不会导致没匹配到而报错。

query

数据查询可能是数据操作中用的最多的，可以用以下模板来写：

  1. 对象查询：
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    public Po query(long id) {

        String sql = "SELECT * from " + tableName + " where id=?";

        try {
            Po po = jdbcTemplate.queryForObject(sql,
                                                new BeanPropertyRowMapper<Po>(Po.class), id);

            return po;
        } catch (EmptyResultDataAccessException e) {
            //TODO 查询数据库没记录的情况，可以返回空，也可以抛异常
        }

        return null;
    }

queryForObject方法有一个新人经常会踩到的坑，就是如果记录没找到，不是返回null，而是抛异常EmptyResultDataAccessException，所以这个需要自己的业务需求来对这个异常做对应的处理。

重点说明一下BeanPropertyRowMapper，它会自动将数据表字段映射到对象的属性中。底层实现中使用的是对象的setter方法设置，所以如果没写setter方法是映射不过去的。BeanPropertyRowMapper会初始化属性到字段的映射，如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
protected void initialize(Class<T> mappedClass) {
    this.mappedClass = mappedClass;
    this.mappedFields = new HashMap<>();
    this.mappedProperties = new HashSet<>();
    //获取setter方法的属性
    PropertyDescriptor[] pds = BeanUtils.getPropertyDescriptors(mappedClass);
    for (PropertyDescriptor pd : pds) {
        if (pd.getWriteMethod() != null) {
            //属性名小写映射
            this.mappedFields.put(lowerCaseName(pd.getName()), pd);
            //下划线映射
            String underscoredName = underscoreName(pd.getName());
            if (!lowerCaseName(pd.getName()).equals(underscoredName)) {
                this.mappedFields.put(underscoredName, pd);
            }
            this.mappedProperties.add(pd.getName());
        }
    }
}

mapRow方法会将字段内容写入到对象属性中，这个方法写了如下内容：

1
2
3
String column = JdbcUtils.lookupColumnName(rsmd, index);
String field = lowerCaseName(StringUtils.delete(column, " "));
PropertyDescriptor pd = (this.mappedFields != null ? this.mappedFields.get(field) : null);

通过之前初始化的mappedFields，就可以将字段关联到属性了。

  1. 基本类型：
    如果查询的是基本类型，则将查询操作修改为下面的方法即可：

    1
    Long res = jdbcTemplate.queryForObject(sql, Long.class, id);

  2. 列表查询：

    1
    List<Long> res = jdbcTemplate.queryForList(sql, Long.class, id);

update

更新操作就简单了，笔者一般会使用updateTime作为乐观锁，所以可以使用以下模板：

1
2
3
4
5
6
7
public boolean update(long id, int num, long updateTime) {

    String sql = "UPDATE " + tableName
        + " set num=num+?,update_time=unix_timestamp(now())*1000 where id=? and update_time=?";

    return jdbcTemplate.update(sql, num, id, updateTime) > 0;
}

如果更新比较多属性，甚至是整个对象都更新的，那么可以使用NamedParameterJdbcTemplate来操作，如下：

1
2
3
4
String sql = "UPDATE " + tableName
            + " set num=num+ :num,update_time=unix_timestamp(now())*1000 where id= :id and update_time= :update_time";
SqlParameterSource parameterSource = new BeanPropertySqlParameterSource(po);
return namedParameterJdbcTemplate.update(sql, parameterSource) > 0;

delete

删除操作与更新类似，模板如下：

1
2
3
4
5
6
7
public boolean delete(long id) {

    String sql = "DELETE FROM " + tableName
        + " WHERE id = ?";

    return jdbcTemplate.update(sql, id) > 0;
}

以上，就可以高效、高性能，且可以自己灵活掌控SQL的编写代码，如果您有更好的做法，也可以在下方留言。