oracle惠存图片和文本

oracle存入图片和文本
转贴java操作Oracle数据库中的Clob,Blob字段

说明1:首先所有的文件都是以二进制存储 ?????????2:二进制文件有.doc?.xls?.jpg ???????????文本文件有???.txt?.html?.xml ????先在oracle数据库中建一张表用与测试 ????create?table?CDL_TEST ??( ????SID??VARCHAR2(20)?not?null, ????IMG??BLOB,???????//存储二进制 ????DOC??CLOB,?????//存储文本 ????DATA?NUMBER ??) ??--?约束 ????alter?table?CDL_TEST ????add?constraint?CDL_SID?primary?key?(SID) ??????测试代码如下: ????package?DataBaseUtil; ??import?java.sql.*; ??import?java.util.Scanner; ??import?java.io.*; ??import?oracle.sql.BLOB; ????class?InitDB{ ???private?static?Connection?con=null; ???private?static?Statement?stmt=null; ???private?static?ResultSet?rs=null; ???InitDB(){ ????try{ ????Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();?? ????????String?url="jdbc:oracle:thin:@localhost:1521:ORCL";??//ORCL?是sid ????String?user="cdl";?? ????String?password="1";?? ????con=?(Connection)?DriverManager.getConnection(url,user,password); ????InitDB.setCon(con); ????}catch(Exception?e){ ?????e.printStackTrace(); ????} ???} ??? ???public?void?closCon(){ ????try{ ?????con.close(); ????}catch(Exception?e){ ?????e.printStackTrace(); ????} ???} ???public?void?stmt(){ ????try{ ?????con.close(); ????}catch(Exception?e){ ?????e.printStackTrace(); ????} ???} ???public?void?rs(){ ????try{ ?????con.close(); ????}catch(Exception?e){ ?????e.printStackTrace(); ????} ???} ?????public?static?Connection?getCon()?{ ????return?con; ???} ?????public?static?void?setCon(Connection?con)?{ ????InitDB.con?=?con; ???} ?????public?static?ResultSet?getRs()?{ ????return?rs; ???} ?????public?static?void?setRs(ResultSet?rs)?{ ????InitDB.rs?=?rs; ???} ?????public?static?Statement?getStmt()?{ ????return?stmt; ???} ?????public?static?void?setStmt(Statement?stmt)?{ ????InitDB.stmt?=?stmt; ???} ??} ????/* ??*?插入Blob数据?如:图片 ??*/??class?InsertBlobData{ ???private?ResultSet?rs=null; ???private?InitDB?idb=null; ???InsertBlobData(){ ?????idb=new?InitDB(); ???} ???public??void?insertBlob(String?sql1)?throws?SQLException{ ????Connection?con=idb.getCon(); ????try{ ?????con.setAutoCommit(false);//不设置自动提交 ?????BLOB?blob?=?null;?//插入空的Blob ?????PreparedStatement?pstmt?=?con.prepareStatement("insert?into?cdl_test(sid,img)?values(?,empty_blob())"); ?????pstmt.setString(1,"100");? ?????pstmt.executeUpdate();? ?????pstmt.close();? ?????rs=con.createStatement().executeQuery(sql1); ?????while(rs.next()){ ???????System.out.println("rs?length?is:"); ???????oracle.sql.BLOB??b=(oracle.sql.BLOB?)rs.getBlob("img"); ???????System.out.println("cloblength?is:"+b.getLength()); ???????File?f=new?File("d:\\img\\1.jpg"); ???????System.out.println("file?path?is:"+f.getAbsolutePath()); ???????BufferedInputStream?in=new?BufferedInputStream(new?FileInputStream(f));?? ???????BufferedOutputStream??out=new?BufferedOutputStream(b.getBinaryOutputStream());? ???????int???c;??? ???????while?((c=in.read())!=-1)???{??? ????????out.write(c);??? ???????}??? ???????in.close();??? ???????out.close(); ?????} ?????con.commit(); ????}catch(Exception?e){ ?????con.rollback();//出错回滚 ?????e.printStackTrace(); ????}? ???} ??} ??/* ??*?插入大文本如:1.txt ??*/??class?InsertClobData{ ???private?ResultSet?rs=null; ???private?InitDB?idb=null; ???InsertClobData(){ ?????idb=new?InitDB(); ???} ???public??void?insertClob(String?sql1)?throws?SQLException{ ????Connection?con=idb.getCon(); ????try{ ?????con.setAutoCommit(false);//不设置自动提交 ?????BLOB?blob?=?null;?//插入空的Clob ?????PreparedStatement?pstmt?=?con.prepareStatement("insert?into?cdl_test(sid,doc)?values(?,empty_clob())"); ?????pstmt.setString(1,"101");? ?????pstmt.executeUpdate();? ?????pstmt.close();? ?????rs=con.createStatement().executeQuery(sql1); ?????while(rs.next()){ ??????System.out.println("sdfasdfas"); ??????oracle.sql.CLOB??cb=(oracle.sql.CLOB)rs.getClob("doc"); ??????File?f=new?File("d:\\doc\\1.txt"); ??????System.out.println("file?path?is:"+f.getAbsolutePath()); ??????BufferedWriter?out?=?new?BufferedWriter(cb.getCharacterOutputStream()); ??????BufferedReader?in?=?new?BufferedReader(new?FileReader(f)); ??????int?c; ??????while?((c=in.read())!=-1)?{ ??????????out.write(c); ??????} ??????in.close();??? ??????out.close(); ?????} ?????con.commit(); ????}catch(Exception?e){ ?????con.rollback();//出错回滚 ?????e.printStackTrace(); ????}? ???} ??} ??/* ??*?读取图片 ??*/??class?ReadBlobData{ ???private?ResultSet?rs=null; ???private?InitDB?idb=null; ???ReadBlobData(){ ?????idb=new?InitDB(); ???} ???public?void?getBlob(String?sql2)?throws?SQLException{ ????Connection?con=idb.getCon(); ????con.setAutoCommit(false); ????try{ ?????System.out.println("sq2?is:"+sql2); ?????System.out.println("stmt?is:"+con); ?????rs=con.createStatement().executeQuery(sql2); ?????while(rs.next()){ ??????System.out.println("rs?length?is:"); ??????Blob?b=(Blob)rs.getBlob("img"); ??????File?f=new?File("D:\\saveimg\\1.jpg"); ??????FileOutputStream?fos=new?FileOutputStream(f); ??????InputStream?is=b.getBinaryStream();//读出数据后转换为二进制流 ??????byte[]?data=new?byte[1024]; ??????while(is.read(data)!=-1){ ???????fos.write(data); ??????} ??????fos.close(); ??????is.close(); ?????} ????con.commit();//正式提交 ????}catch(Exception?e){ ?????e.printStackTrace(); ????}finally{ ?????//rs.close(); ????} ???} ??} ??/* ??*?读取大文本 ??*/??class?ReadClobData{ ???private?ResultSet?rs=null; ???private?InitDB?idb=null; ???ReadClobData(){ ?????idb=new?InitDB(); ???} ???public?void?getClob(String?sql2)?throws?SQLException{ ????Connection?con=idb.getCon(); ????try{ ?????con.setAutoCommit(false);//不设置自动提交 ?????System.out.println("sq2?is:"+sql2); ?????rs=con.createStatement().executeQuery(sql2); ?????while(rs.next()){ ??????oracle.sql.CLOB?clob=(oracle.sql.CLOB)rs.getClob("doc"); ??????File?f=new?File("d:\\savedoc\\1.txt"); ??????BufferedReader?in?=?new?BufferedReader(clob.getCharacterStream()); ??????//setCharacterStream()方法，可用于将CLOB字段与字节流相关联， ???????????????BufferedWriter?out?=?new?BufferedWriter(new?FileWriter(f)); ???????????????int?c; ???????????????while?((c=in.read())!=-1)?{ ????????????????????out.write(c); ????????????????} ??????out.close(); ??????in.close(); ?????} ????con.commit();//正式提交 ????rs.close(); ????}catch(Exception?e){ ?????e.printStackTrace(); ?????con.rollback(); ????} ???} ??} ??public?class?TestBlob?{ ???public?static?void?main(String?[]args){ ????String?sql1="select?*?from?cdl_test??for?update";//悲观锁锁定需更新的行 ????String?sql2="select?*?from?cdl_test"; ????System.out.println("\t\t\t欢迎使用:"); ????System.out.println("1:插入图片"); ????System.out.println("2:插入文本"); ????System.out.println("3:读取图片"); ????System.out.println("4:读取文本"); ????System.out.println("5:退出"); ????System.out.println("请选择:") ????while(true){ ?????try{ ??????Scanner?sc=new?Scanner(System.in); ??????int?i=sc.nextInt(); ??????System.out.println("sss:"+i); ??????switch(i){ ???????case?1: ????????InsertBlobData?isd=new?InsertBlobData(); ????????isd.insertBlob(sql1);?break; ???????case?2: ????????InsertClobData?icd=new?InsertClobData(); ????????icd.insertClob(sql1);?break; ???????case?3: ????????ReadBlobData?rb=new?ReadBlobData(); ????????rb.getBlob(sql2);?break; ???????case?4: ????????ReadClobData?rc=new?ReadClobData(); ????????rc.getClob(sql2);?break; ???????case?5: ????????System.exit(0); ??????} ?????}catch(Exception?e){ ??????e.printStackTrace(); ?????} ????} ???} ??}??