在springboot中使用jdbcTemplate(8)NamedParameterJdbcTemplate

在springboot中使用jdbcTemplate

在springboot中使用jdbcTemplate(2)-多数据源

在springboot中使用jdbcTemplate(3)

在springboot中使用jdbcTemplate(4)

在springboot中使用jdbcTemplate(5)

在springboot中使用jdbcTemplate(6)

在springboot中使用jdbcTemplate(7)-hivejdbc

使用JdbcTemplate大部分都是很方便的，不过有一点比较例外，就是插入或更新数据的时候。

需要把对象转成Object[],之前的实现总觉得不那么优雅。

一、不优雅实现

槽点：需要手动把所有字段get出来，保证有序，是个功夫活

String sql = "replace into metadata(" +
                "uid," +
                "name," +
                "`database`," +
                "store_type," +
                "create_time," +
                "ddl_update_time," +
                "storage_location," +
                "storage_size," +
                "recent_sync_time," +
                "row_count," +
                "columns," +
                "tenant_id) values(?,?,?,?,?,?,?,?,?,?,?,?)";
        targetConn.update(sql,  metadataHive.getUid(),
                    metadataHive.getName(),
                    metadataHive.getDatabase(),
                    metadataHive.getStoreType(),
                    metadataHive.getCreateTime(),
                    metadataHive.getDdlUpdateTime(),
                    metadataHive.getStorageLocation(),
                    metadataHive.getStorageSize(),
                    metadataHive.getRecentSyncTime(),
                    metadataHive.getRowCount(),
                    columns,
                    metadataHive.getTenantId());

二、不优雅实现

槽点1：需要保证表和对象中的所有字段都更新，表字段和类属性一一对应并且有序，

槽点2：反射影响性能

public void save(Metadata metadata) {
        String sql = "insert into metadata " +
                "(uid," +
                "name," +
                "meta_type," +
                "system_type," +
                "entities," +
                "properties," +
                "documentation," +
                "stats," +
                "owner) " +
                "values(?,?,?,?,?,?,?,?,?)";
        Object[] objects = getArray(metadata, Metadata.class);
        jdbcTemplate.update(sql, objects);
    }

//虽然用反射避免了将对象的每个字段get一遍，但如果想更新部分字段时还是非常麻烦
public <T> Object[] getArray(T t, Class<T> clazz) {

        Field[] fields = clazz.getDeclaredFields();
        return Arrays.stream(fields)
                .filter(field -> !field.getName().equals("serialVersionUID"))
                .map(field -> {
                    field.setAccessible(true);
                    Object fieldVal = null;
                    try {
                        fieldVal = field.get(t);
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                    return fieldVal;
                }).toArray(Object[]::new);
    }

三、NamedParameterJdbcTemplate

NamedParameterJdbcTemplate是JdbcTemplate的plus版，对插入更新做了增强

//可以传入jdbcTemplate直接得到
NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

//SQL需要传具名参数
String sql = "replace into mysql(" +
                "uid," +
                "name," +
                "`database`," +
                "engine," +
                "row_format," +
                "row_count," +
                "storage_size," +
                "create_time," +
                "recent_sync_time," +
                "columns," +
                "tenant_id) " +
                "values(" +
                ":uid," +
                ":name," +
                ":database," +
                ":engine," +
                ":rowFormat," +
                ":rowCount," +
                ":storageSize," +
                ":createTime," +
                ":recentSyncTime," +
                ":columns," +
                ":tenantId)";

jdbcTemplate.update(sql,new BeanPropertySqlParameterSource(metadataObject));

除了需要稍微改变下SQL的书写方式，NamedParameterJdbcTemplate可以直接将对象存入数据库，属性和字段的映射全帮我们做完了

真的非常优秀！

update的实现

@Override
    public void updateSyncTime(String uid, String syncTime) {
        String sql="update metadata set recent_sync_time=:syncTime where uid=:uid";
        log.info(sql);
        MapSqlParameterSource sps=new MapSqlParameterSource();
        sps.addValue("uid",uid);
        sps.addValue("syncTime",syncTime);
        jdbcTemplate.update(sql,sps);
    }