在springboot中使用jdbcTemplate(5)

更新和批量更新在jdbc中也很常用，JdbcTemplate也是有支持的。

/**
     * 更新字段属性
     *
     * @param column
     */
    public void updateColumn(Config column) {
        log.info(column.toString());

        String sql = "update `config` " +
                "set name=?," +
                "filter=?," +
                "type=?," +
                "remark=? " +
                "where code=? and column=?";

        jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, column.getName());
                ps.setBoolean(2, column.getFilter());
                ps.setInt(3, column.getType());
                ps.setString(4, column.getRemark());
                ps.setString(5, column.getCode());
                ps.setString(6, column.getColumn());

            }
        });
    }

    /**
     * 批量更新字段order
     *
     * @param columns
     */
    public void batchUpdate(List<Config> columns) {
        String sql = "update `config` " +
                "set c_order=? " +
                "where code=? and column=?";

        jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, columns.get(i).getCOrder());
                ps.setString(2, columns.get(i).getCode());
                ps.setString(3, columns.get(i).getColumn());
            }

            @Override
            public int getBatchSize() {
                return columns.size();
            }
        });
    }

 有时候我们需要在insert数据后获取mysql的自增主键，这个可以有

public int insertTemplateVersion(TemplateVersion templateVersion) {
        String sql = "insert into `template_version`(temp_code)" +
                " values(?)";
        log.info(sql);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator(){

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1,templateVersion.getTempCode());
                return ps;
            }
        },keyHolder);

        return keyHolder.getKey().intValue();
    }

常见需求的特殊处理 

有时候我们想update一条记录，但是又不想更新表中的所有字段，这个时候replace into和上面的update方法都不好用了，只能采用下面的动态SQL方法

/**
* {
    "uid": "mysql://hw-node4:3306/fill/api_config",
    "entities":"[\"a1\",\"a2\",\"a3\"]",
    "properties": "{\"k\":\"v\"}",
    "documentation": "这是一张API配置表"
}
*最终生成的SQL：update metadata set entities='["a1","a2","a3"]',documentation='这是一张API配置表',properties='{"k":"v"}'where uid='mysql://hw-node4:3306/fill/api_config'
*/
@Override
    public void updateMetadata(String obj) {
        String uid = (String) JSONPath.read(obj, "$.uid");
        JSONObject jsonObject = JSON.parseObject(obj);
        StringBuilder sqlBuilder = new StringBuilder("update metadata set ");
        for (Map.Entry<String, Object> kv : jsonObject.entrySet()) {
            Object key = kv.getKey();
            Object value = kv.getValue();
            if (!key.equals("uid") && value != null && !((String) value).trim().equals("")) {
                sqlBuilder.append(key).append("=").append("'").append(value).append("'").append(",");
            }
        }
        sqlBuilder.deleteCharAt(sqlBuilder.length()-1);
        sqlBuilder.append(" where uid=").append("'").append(uid).append("'");
        metadataMapper.update(sqlBuilder.toString());
    }