最近项目同事中,想更新db2的大字段内容,抽空写了点代码实现这一潜在需求点.
1.db2可直接进行更新大字段
package com.yyy.dailycode.enjoycoding.insertblob; import java.io.BufferedOutputStream; import java.io.InputStream; import java.io.OutputStream; import java.io.UnsupportedEncodingException; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import org.apache.log4j.Logger; import com.yyy.dailycode.db.db2.Db2Unit; /** * @类名: TestUpdateBlob * @描述: 测试更新db2数据库大字段 * @作者: 杨文胜 * @生成时间: 2017年12月26日 下午7:34:27 * @修改人: * @修改时间: **/ public class TestUpdateBlob { private static Logger log = Logger.getLogger(TestUpdateBlob.class); private static char[] mChars = "0123456789ABCDEF".toCharArray(); public static void main(String[] args) { // new TestUpdateBlob().insertBlob(); new TestUpdateBlob().selectBlob(); } public void selectBlob(){ // String selectSql = "select hex(POSTING_MASK) POSTING_MASK,POSTING_MASK AA from gldm where key_1='0039002010149900001'"; String selectSql = "select distinct POSTING_MASK AA from gldm"; OutputStream os = null; BufferedOutputStream bos =null; InputStream is = null; ResultSet rs = null; PreparedStatement pstm = null; Connection con = null; try { con = Db2Unit.getConnection(); ResultSet set = Db2Unit.query(selectSql); while (set.next()) { // String posting_mask = set.getString("POSTING_MASK"); String aa = set.getString("AA"); System.out.println(aa); System.out.println(aa.length()); } } catch (Exception e) { log.error("插入数据失败!", e); } finally { try { if(bos != null){ bos.close(); } if(is != null){ is.close(); } if(os != null){ os.close(); } if(rs != null){ rs.close(); } if(pstm!=null){ pstm.close(); } if(con != null){ con.close(); } Db2Unit.closeConnection(); } catch (Exception e) { log.error("关闭数据库链接失败", e); } } } public static String stringToHexString1(String s){ StringBuffer sb = new StringBuffer(); byte[] b = s.getBytes(); for(int i =0; i< b.length;i++){ sb.append(mChars[(b[i] & 0xFF) >> 4]); sb.append(mChars[b[i] & 0x0F]); // sb.append(' '); } System.out.println(sb.toString().trim()); return sb.toString(); } public static String stringToHexString(String s){ String str = ""; for(int i =0; i< s.length();i++){ int ch =(int)s.charAt(i); String s4 = Integer.toHexString(ch); str = str + s4; } return str; } /** * @生成时间: 2016年6月15日 下午2:04:40 * @方法说明: 插入大字段测试 * @参数: * @返回值: * @异常: * **/ public void insertBlob(){ // 建库语句 // -- Create table // create table TEMP_TEST_TABLESPACE_SIZE // ( // id_ VARCHAR(10), // bytes_ BLOB // ) // ; String updateSql = "update TEMP_TEST_TABLESPACE_SIZE set BYTES_ = ? where id_= ?"; OutputStream os = null; BufferedOutputStream bos =null; InputStream is = null; ResultSet rs = null; PreparedStatement pstm = null; Connection con = null; try { con = Db2Unit.getConnection(); pstm = con.prepareStatement(updateSql); pstm.setBytes(1, "HHHHHHHHHHHHHHHHHH".getBytes()); pstm.setString(2, "300"); pstm.executeUpdate(); } catch (Exception e) { log.error("插入数据失败!", e); } finally { try { if(bos != null){ bos.close(); } if(is != null){ is.close(); } if(os != null){ os.close(); } if(rs != null){ rs.close(); } if(pstm!=null){ pstm.close(); } if(con != null){ con.close(); } } catch (Exception e) { log.error("关闭数据库链接失败", e); } } } }
2.oracle数据库的大字段,只能先插入,然后更新public void insertBlob(){ // 建库语句 // -- Create table // create table TEMP_TEST_TABLESPACE_SIZE // ( // id_ VARCHAR2(10), // bytes_ BLOB // ) // tablespace BPMSTS_DATA // pctfree 10 // initrans 1 // maxtrans 255 // storage // ( // initial 64K // next 1M // minextents 1 // maxextents unlimited // ); String sql = "insert into TEMP_TEST_TABLESPACE_SIZE(id_, BYTES_) values('100',empty_blob())"; OutputStream os = null; BufferedOutputStream bos =null; InputStream is = null; ResultSet rs = null; Statement pstm = null; Connection con = null; try { con = OracleUnit.getConnection(); con.setAutoCommit(false); pstm = con.createStatement(); pstm.executeUpdate(sql); sql = "select BYTES_ from TEMP_TEST_TABLESPACE_SIZE where id_='100' for update"; rs = pstm.executeQuery(sql); if(rs.next()){ oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BYTES_"); os = blob.getBinaryOutputStream(); bos = new BufferedOutputStream(os); is = new FileInputStream("d:\\temp\\aa.zip"); int i=0; while((i=is.read()) != -1){ bos.write(i); } os.flush(); os.close(); con.commit(); con.setAutoCommit(true); } } catch (Exception e) { log.error("插入数据失败!", e); } finally { try { if(bos != null){ bos.close(); } if(is != null){ is.close(); } if(os != null){ os.close(); } if(rs != null){ rs.close(); } if(pstm!=null){ pstm.close(); } if(con != null){ con.close(); } } catch (Exception e) { log.error("关闭数据库链接失败", e); } } }