1、blob字段存取

1.1、存入blob字段

//sql="INSERT INTO "EMS"."D5000"."REPORT"("filename","update_time","file") VALUES(?,?,?)"
FileInputStream inputStream = new FileInputStream(file);
int length = inputStream.available();
Timestamp timestamp = new Timestamp(System.currentTimeMillis());
jdbcTemplate.execute(sql, new AbstractLobCreatingPreparedStatementCallback(new DefaultLobHandler()) {
    @Override
    protected void setValues(PreparedStatement pre, LobCreator lobCreator) throws SQLException, DataAccessException {
        pre.setString(1,fileName);
        pre.setTimestamp(2,timestamp);
        lobCreator.setBlobAsBinaryStream(pre,3, inputStream,length);
    }
});

1.2、取出blob字段，保存文件

// SELECT * FROM "EMS"."D5000"."REPORT" WHERE "id"=?
DefaultLobHandler lobHandler = new DefaultLobHandler();
FileChannel channel = new FileOutputStream("C:\\Users\\tk40q\\Desktop\\xx.docx").getChannel();
jdbcTemplate.query(sql, new Object[]{3}, new AbstractLobStreamingResultSetExtractor<Object>() {
    @Override
    protected void streamData(ResultSet resultSet) throws SQLException, IOException, DataAccessException {
        byte[] bytes = lobHandler.getBlobAsBytes(resultSet, 4);
        if (bytes!=null){
            channel.write(ByteBuffer.wrap(bytes));
        }
    }
});

2、查询

rowmapper使用

2.1、查询单条

// 查询结果是一个值  ，查询个数
Integer total = jdbcTemplate.queryForObject(sql, new Object[]{fileId}, Integer.class);
// 查询结果是一个对象
Student student=jdbcTemplate.queryForObject(sql, new Object[]{name}, new BeanPropertyRowMapper<>(Student.class));

2.2、查询多条

 List<Integer> query = jdbcTemplate.query(sql, new Object[]{stationName}, new RowMapper<Integer>() {
            @Override
            public Integer mapRow(ResultSet resultSet, int i) throws SQLException {
                return resultSet.getInt(1);
            }
    });

// 查询结果是多个对象
List<Student> list=jdbcTemplate.query(sql, new Object[]{name},new BeanPropertyRowMapper<>(Student.class));

2.3、使用rowmapper自定义封装

当属性和字段不对应的时候可以使用

 List<Report> result = jdbcTemplate.query(sql, new Object[]{flag}, (resultSet, i) -> {
            Report report = new Report();
            report.setId(resultSet.getInt(1));
            report.setFilename(resultSet.getString(2));
            report.setUpdateTime(resultSet.getTimestamp(3).toLocalDateTime());
            report.setNote(resultSet.getString(5));
            report.setFlag(resultSet.getInt(6));
            return report;
        });

2.4、查询范围 in

@Autowired
    private JdbcTemplate jdbcTemplate;
    @GetMapping("/queryUsersByIds")
    @ApiOperation(value = "查询用户列表")
    public List<User> queryUsersByIds() {
        String sql = "select * from t_user where id in (:ids);";
        // id 集合
        List<Integer> ids = new ArrayList<>();
        ids.add(3);
        ids.add(1);
        MapSqlParameterSource parameters = new MapSqlParameterSource();
        // 传参
        parameters.addValue("ids", ids);
        NamedParameterJdbcTemplate givenParamJdbcTemp = new NamedParameterJdbcTemplate(jdbcTemplate);
        List<User> data = givenParamJdbcTemp.query(sql, parameters, new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                // TODO Auto-generated method stub
                User user = new User();
                user.setId(rs.getLong("id"));
                user.setUserName(rs.getString("user_name"));
                user.setRemark(rs.getString("remark"));
                return user;
            }
        });
        return data;
    }

3、批量插入、更新、删除