BLOB 字段使用方法

@Override
public List queryRobot(Map map) {
String sql = " select t.ROBOT_ID," +
" t.ROBOT_PICTURE," +
" t.ROBOT_NAME," +
" t.STATUS," +
" t.WELCOME_MESSAGE," +
" t.UNANSWERABLE_MESSAGE " +
" from DDM_SERVICE_MERCHANT_ROBOT t where t.MERCHANT_ID = ? ";
List<Map<String, Object>> list = new ArrayList();
this.getJdbcTemplate().query(sql,new Object[]{StringTools.obj2String(map.get(“merchantId”))}, new RowCallbackHandler() {
@Override
public void processRow(ResultSet rs) throws SQLException {
Map<String, Object> mapRobot = new HashMap<String, Object>();
mapRobot.put(“ROBOT_ID”, rs.getString(“ROBOT_ID”));
mapRobot.put(“ROBOT_PICTURE”, rs.getString(“ROBOT_PICTURE”));
mapRobot.put(“ROBOT_NAME”, rs.getString(“ROBOT_NAME”));
mapRobot.put(“STATUS”, rs.getString(“STATUS”));
Blob getBlod = rs.getBlob(“WELCOME_MESSAGE”);
try {
if (!org.springframework.util.StringUtils.isEmpty(getBlod)){
mapRobot.put(“WELCOME_MESSAGE”, new String(getBlod.getBytes((long)1, (int)getBlod.length()),“UTF-8”));
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
Blob getBlod2 = rs.getBlob(“UNANSWERABLE_MESSAGE”);
try {
if (!org.springframework.util.StringUtils.isEmpty(getBlod2)){
mapRobot.put(“UNANSWERABLE_MESSAGE”, new String(getBlod2.getBytes((long)1, (int)getBlod2.length()),“UTF-8”));
}
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
list.add(mapRobot);
}
});
return list;
}

@Override
public int addMessage(Map map) throws UnsupportedEncodingException, SQLException {
    Blob blobName = new SerialBlob(StringTools.obj2String(map.get("message_name")).getBytes("UTF-8"));//String 转 blob
    Blob blobContent = new SerialBlob(StringTools.obj2String(map.get("message_content")).getBytes("UTF-8"));
    String sql = "insert into DDM_SERVICE_MESSAGE(MESSAGE_ID,MESSAGE_NAME,MESSAGE_PHONE,MESSAGE_CONTENT,MERCHANT_ID) values (SEQ_DDM_MESSAGE_ID.NEXTVAL,?,?,?,?)";
    return this.getJdbcTemplate().execute(sql, new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
        @Override
        protected void setValues(PreparedStatement preparedStatement, LobCreator lobCreator) throws SQLException, DataAccessException {
            preparedStatement.setBlob(1, blobName.getBinaryStream());
            preparedStatement.setString(2, StringTools.obj2String(map.get("message_phone")));
            preparedStatement.setBlob(3, blobContent.getBinaryStream());
            preparedStatement.setString(4, StringTools.obj2String(map.get("merchantId")));
        }
    });
}










@Override
public int updateRobot(Map map) throws UnsupportedEncodingException, SQLException {
    String countSql = "select t.ROBOT_ID from DDM_SERVICE_MERCHANT_ROBOT t where t.MERCHANT_ID = ?";
    List<? extends Map> list = this.getJdbcTemplate().queryForList(countSql, new Object[]{StringTools.obj2String(map.get("merchantId"))});
    Blob welcomeBlob = new SerialBlob(StringTools.obj2String(map.get("welcome_message")).getBytes("UTF-8"));
    Blob unanswerableBlob = new SerialBlob(StringTools.obj2String(map.get("unanswerable_message")).getBytes("UTF-8"));
    String sql = "";
    if (list.size()>0){
        sql = "update DDM_SERVICE_MERCHANT_ROBOT t set t.ROBOT_PICTURE = ?,t.WELCOME_MESSAGE = ?,t.UNANSWERABLE_MESSAGE = ?,t.STATUS = ?,t.ROBOT_NAME = ? where t.MERCHANT_ID = ?";
        return this.getJdbcTemplate().execute(sql, new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
            @Override
            protected void setValues(PreparedStatement preparedStatement, LobCreator lobCreator) throws SQLException, DataAccessException {
                preparedStatement.setString(1, StringTools.obj2String(map.get("robot_picture")));
                preparedStatement.setBlob(2, welcomeBlob.getBinaryStream());
                preparedStatement.setBlob(3, unanswerableBlob.getBinaryStream());
                preparedStatement.setString(4,StringTools.obj2String(map.get("status")));
                preparedStatement.setString(5,StringTools.obj2String(map.get("robotName")));
                preparedStatement.setString(6,StringTools.obj2String(map.get("merchantId")));
            }
        });
    }else{
        sql = "insert into DDM_SERVICE_MERCHANT_ROBOT(ROBOT_ID,MERCHANT_ID,ROBOT_PICTURE,WELCOME_MESSAGE,UNANSWERABLE_MESSAGE,STATUS,ROBOT_NAME) values (SEQ_DDM_SERVICE_MERCHANT_ROBOT.NEXTVAL,?,?,?,?,?,?)";
        return this.getJdbcTemplate().execute(sql, new AbstractLobCreatingPreparedStatementCallback(lobHandler) {
            @Override
            protected void setValues(PreparedStatement preparedStatement, LobCreator lobCreator) throws SQLException, DataAccessException {
                preparedStatement.setString(1, StringTools.obj2String(map.get("merchantId")));
                preparedStatement.setString(2, StringTools.obj2String(map.get("robot_picture")));
                preparedStatement.setBlob(3, welcomeBlob.getBinaryStream());
                preparedStatement.setBlob(4, unanswerableBlob.getBinaryStream());
                preparedStatement.setString(5, StringTools.obj2String(map.get("status")));
                preparedStatement.setString(6,StringTools.obj2String(map.get("robotName")));
            }
        });
    }
}