spring jdbc 使用详解

Spring将替我们完成所有使用JDBC API进行开发的单调乏味的、底层细节处理工作。
操作JDBC时Spring可以帮我们做这些事情：
定义数据库连接参数，打开数据库连接，处理异常，关闭数据库连接
我们要做的事：sql，传参，使用正确的方法，处理结果。
下面按照功能介绍：

1.delete(批量删除)

    final String[] id=ids.split(",");
    jdbcTemplate.batchUpdate("DELETE FROM file WHERE id = ?",
        new BatchPreparedStatementSetter(){
             public void setValues(PreparedStatement ps, int i)throws SQLException   {
                    ps.setString(1, id[i]);
             }

             public int getBatchSize() {
                    return id.length;
             }
        });
   return true;

2.update

    private static final String UPDATE_MODALITY_SQL = "UPDATE study SET mods = ? WHERE id = ?";
    public int updateModalitys(String mods, Integer studyId) {
        return getJdbcTemplate().update(UPDATE_MODALITY_SQL, mods, studmodsyId);
    }

3.insert(批量插入)

        private static final String BATCH_SQL = "insert into sharding(instance_id,url,ctime,mtime)values (?,?,?,?)";
        public void batchInsert(List<ROIPlaneSharding> roiPlaneShardings) {

            this.viewerJdbcTemplate().batchUpdate(BATCH_ROI_PLANE_SHARDING_INSERT_SQL,
                new ROIPlaneShardingBatchPreparedStatementSetter(roiPlaneShardings));
        }
    private class ROIPlaneShardingBatchPreparedStatementSetter implements BatchPreparedStatementSetter {

        private List<ROIPlaneSharding> roiPlaneShardings;

        public ROIPlaneShardingBatchPreparedStatementSetter(List<ROIPlaneSharding> roiPlaneShardings) {
            this.roiPlaneShardings = roiPlaneShardings;
        }

        @Override
        public void setValues(PreparedStatement ps, int i) throws SQLException {
            ROIPlaneSharding sharding = roiPlaneShardings.get(i);
            ps.setInt(1, sharding.getInstanceId());
            ···
        }

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

    }

4.select

字段查找

private static final String SELECT_SQL = "SELECT instance_id,roiplane_id,url FROM sharding WHERE instance_id =:id";
public List<ROIPlaneSharding> getShardings(List<Integer> instanceIds, List<Integer> roiPlaneIds,Integer contouring) {

        MapSqlParameterSource sqlParameter = new MapSqlParameterSource();

        sqlParameter.addValue("id", id);
        return namedViewerJdbcTemplate().query(SELECT_SQL, sqlParameter, getRowMapper());
    }

    private RowMapper<sharding> getRowMapper() {
        return new RowMapper<ROIPlaneSharding>() {

            @Override
            public ROIPlaneSharding mapRow(ResultSet rs, int rowNum) throws SQLException {

                ROIPlaneSharding Sharding = new ROIPlaneSharding();

                roiPlaneSharding.setUrl(rs.getString(ROIPlaneSharding.urlCoulmn));
                ···
                return roiPlaneSharding;
            }
        };
    }

集合 in(:ids) 查找

private static final String SELECT_INSTANCEIDS = "select * from file where id in(:ids)";
    @Override
    public List<ResourceInfo> getFiles(List<Integer> fileIds) {
        if (null == fileIds || fileIds.isEmpty()) {
            return null;
        }

        MapSqlParameterSource parameters = new MapSqlParameterSource();
        parameters.addValue("ids", fileIds);
        return namedBaseJdbcTemplate().query(SELECT_INSTANCEIDS, parameters, getRowMapper());
    }

    protected RowMapper<ResourceInfo> getRowMapper() {
        return new RowMapper<ResourceInfo>() {
            @Override
            public ResourceInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
                ResourceInfo file = new ResourceInfo();
                file.setFileName(rs.getString(File.originalNameCoulmn));
                ···
                return file;
            }
        };
    }