create table TESTBLOB ( NAME VARCHAR2(50) not null, CONTENT BLOB not null, ID NUMBER(8) not null )alter table TESTBLOB add constraint IDFORTEST primary key (ID);
public static void main(String[] args) { //创建测试用对象 City beijing = new City(); beijing.setName("北京"); beijing.setCode("010"); City shanghai = new City(); shanghai.setName("上海"); shanghai.setCode("020"); City tianjin = new City(); tianjin.setName("天津"); tianjin.setCode("021"); List<City> cityList = new ArrayList<City>(); cityList.add(beijing); cityList.add(shanghai); cityList.add(tianjin); TestObject obj = new TestObject(); obj.setName("yangsq"); obj.setPassword("111"); obj.setDate(new Date()); obj.setCityList(cityList); try{ //将对象存入blob字段 ByteArrayOutputStream byteOut=new ByteArrayOutputStream(); ObjectOutputStream outObj=new ObjectOutputStream(byteOut); outObj.writeObject(obj) ; final byte[] objbytes=byteOut.toByteArray(); Class.forName("oracle.jdbc.driver.OracleDriver"); Connection con = DriverManager.getConnection( "jdbc:oracle:thin:@***.***.***.***:1521:****", "yangsq", "yangsq"); con.setAutoCommit(false); Statement st = con.createStatement(); st.executeUpdate("insert into TESTBLOB (ID, NAME, CONTENT) values (1, 'test1', empty_blob())"); ResultSet rs = st.executeQuery("select CONTENT from TESTBLOB where ID=1 for update"); if (rs.next()) { BLOB blob = (BLOB) rs.getBlob("CONTENT"); OutputStream outStream = blob.getBinaryOutputStream(); outStream.write(objbytes, 0, objbytes.length); outStream.flush(); outStream.close(); } byteOut.close(); outObj.close(); con.commit(); //取出blob字段中的对象,并恢复 rs = st.executeQuery("select CONTENT from TESTBLOB where ID=1"); BLOB inblob = null; if (rs.next()) { inblob = (BLOB) rs.getBlob("CONTENT"); } InputStream is = inblob.getBinaryStream(); BufferedInputStream input = new BufferedInputStream(is); byte[] buff = new byte[inblob.getBufferSize()]; while(-1 != (input.read(buff, 0, buff.length))); ObjectInputStream in = new ObjectInputStream( new ByteArrayInputStream( buff)); TestObject w3 = (TestObject)in.readObject(); System.out.println(w3.getName()); System.out.println(w3.getPassword()); System.out.println(w3.getDate()); List<City> list = w3.getCityList(); for(City city : list){ System.out.println(city.getName() + " " + city.getCode()); } st.close(); con.close(); } catch (Exception ex) { ex.printStackTrace(); System.exit(1); } } }
代码的蓝色部分创建了要存储的对象。再看红色的对象写入部分,它首先把对象转化成二进制流的形式。对于blob字段,我们不能简单的在insert时插入,实际上,insert时,对于blob字段,只能先插入一个空的blob对象
empty_blob(),然后再进行"select CONTENT from TESTBLOB where ID=1 for update"对blob字段进行更新。返回后,我们只要把对象的二进制流写入即可。