oracle本身支持对大数据类型的各种处理,但是平常大家可能用的不多,其中clob(一般用于大类型的字符型的存取)和blob(一般用于大类型的二进制类型的存取)我想应该就是用的比较少的。所以我写下这篇博文,主要是对这两个数据类型的文件进行的操作,如,保存这类文件进入数据库,读取这类文件到本地磁盘。
本来按照老习惯是该先上效果图的,可惜我的机器现在巨卡,10g跑起来基本让我泪流满面,所以我就偷下懒,图就给省略了 -_-!
第一步,新建一个java项目(为啥是java项目而不是web呢,主要因为它便于测试,出效果也快...)orclTest
第二步,新建一个数据库连接的类,我这里是com.test.InitDB.java,代码如下:
- classInitDB{
- privatestaticConnectioncon=null;
- privatestaticStatementstmt=null;
- privatestaticResultSetrs=null;
- //链接oracle数据库
- InitDB()
- {
- try
- {
- Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
- Stringurl="jdbc:oracle:thin:@localhost:1521:YYSMID";
- Stringuser="zhangsan";
- Stringpassword="Lc123456";
- con=(Connection)DriverManager.getConnection(url,user,password);
- InitDB.setCon(con);
- }
- catch(Exceptione)
- {
- e.printStackTrace();
- }
- }
- publicvoidclosCon()
- {
- try
- {
- con.close();
- }
- catch(Exceptione)
- {
- e.printStackTrace();
- }
- }
- publicvoidstmt()
- {
- try
- {
- con.close();
- }
- catch(Exceptione)
- {
- e.printStackTrace();
- }
- }
- publicvoidrs()
- {
- try
- {
- con.close();
- }
- catch(Exceptione)
- {
- e.printStackTrace();
- }
- }
- publicstaticConnectiongetCon()
- {
- returncon;
- }
- publicstaticvoidsetCon(Connectioncon)
- {
- InitDB.con=con;
- }
- publicstaticResultSetgetRs()
- {
- returnrs;
- }
- publicstaticvoidsetRs(ResultSetrs)
- {
- InitDB.rs=rs;
- }
- publicstaticStatementgetStmt()
- {
- returnstmt;
- }
- publicstaticvoidsetStmt(Statementstmt)
- {
- InitDB.stmt=stmt;
- }
- }
第三步,以及接下来的几步,我们来分别编写clob和blob的插入数据库和读出数据库的代码,新建com.test.InsertBlobData.java,顾名思义,它是用来插入blob类型数据的类。代码如下:
- classInsertBlobData{
- privateResultSetrs=null;
- privateInitDBidb=null;
- InsertBlobData()
- {
- idb=newInitDB();
- }
- publicvoidinsertBlob(Stringsql1)throwsSQLException
- {
- Connectioncon=idb.getCon();
- try
- {
- con.setAutoCommit(false);//不设置自动提交
- BLOBblob=null;//插入空的Blob
- PreparedStatementpstmt=con
- .prepareStatement("insertintocdl_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("rslengthis:");
- oracle.sql.BLOBb=(oracle.sql.BLOB)rs.getBlob("img");
- System.out.println("cloblengthis:"+b.getLength());
- Filef=newFile("d:\\1.jpg");//1.jpg一张QQ的截图
- System.out.println("filepathis:"+f.getAbsolutePath());
- BufferedInputStreamin=newBufferedInputStream(
- newFileInputStream(f));
- BufferedOutputStreamout=newBufferedOutputStream(b
- .getBinaryOutputStream());
- intc;
- while((c=in.read())!=-1)
- {
- out.write(c);
- }
- in.close();
- out.close();
- }
- con.commit();
- }
- catch(Exceptione)
- {
- con.rollback();//出错回滚
- e.printStackTrace();
- }
- }
- }
第四步,新建com.test.InsertClobData.java,用于插入clob数据类型的,代码如下:
- classInsertClobData
- {
- privateResultSetrs=null;
- privateInitDBidb=null;
- InsertClobData()
- {
- idb=newInitDB();
- }
- publicvoidinsertClob(Stringsql1)throwsSQLException
- {
- Connectioncon=idb.getCon();
- try
- {
- con.setAutoCommit(false);//不设置自动提交
- BLOBblob=null;//插入空的Clob
- PreparedStatementpstmt=con
- .prepareStatement("insertintocdl_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.CLOBcb=(oracle.sql.CLOB)rs.getClob("doc");
- Filef=newFile("d:\\1.txt");//1.txt一本小说《风云》马荣成
- System.out.println("filepathis:"+f.getAbsolutePath());
- BufferedWriterout=newBufferedWriter(cb
- .getCharacterOutputStream());
- BufferedReaderin=newBufferedReader(newFileReader(f));
- intc;
- while((c=in.read())!=-1)
- {
- out.write(c);
- }
- in.close();
- out.close();
- }
- con.commit();
- }
- catch(Exceptione)
- {
- con.rollback();//出错回滚
- e.printStackTrace();
- }
- }
- }
第五步,新建com.test.ReadBlobData.java,用于读取blob类型的数据,代码如下:
- classReadBlobData
- {
- privateResultSetrs=null;
- privateInitDBidb=null;
- ReadBlobData()
- {
- idb=newInitDB();
- }
- publicvoidgetBlob(Stringsql2)throwsSQLException
- {
- Connectioncon=idb.getCon();
- con.setAutoCommit(false);
- try
- {
- System.out.println("sq2is:"+sql2);
- System.out.println("stmtis:"+con);
- rs=con.createStatement().executeQuery(sql2);
- while(rs.next())
- {
- System.out.println("rslengthis:");
- Blobb=(Blob)rs.getBlob("img");
- Filef=newFile("D:\\1.jpg");
- FileOutputStreamfos=newFileOutputStream(f);
- InputStreamis=b.getBinaryStream();//读出数据后转换为二进制流
- byte[]data=newbyte[1024];
- while(is.read(data)!=-1)
- {
- fos.write(data);
- }
- fos.close();
- is.close();
- }
- con.commit();//正式提交
- }
- catch(Exceptione)
- {
- e.printStackTrace();
- }
- finally
- {
- //rs.close();
- }
- }
- }
第六步,新建com.test.ReadClobData.java,用于读取clob类型的数据,代码如下:
- classReadClobData
- {
- privateResultSetrs=null;
- privateInitDBidb=null;
- ReadClobData()
- {
- idb=newInitDB();
- }
- publicvoidgetClob(Stringsql2)throwsSQLException
- {
- Connectioncon=idb.getCon();
- try
- {
- con.setAutoCommit(false);//不设置自动提交
- System.out.println("sq2is:"+sql2);
- rs=con.createStatement().executeQuery(sql2);
- while(rs.next())
- {
- oracle.sql.CLOBclob=(oracle.sql.CLOB)rs.getClob("doc");
- Filef=newFile("d:\\1.txt");
- BufferedReaderin=newBufferedReader(clob
- .getCharacterStream());
- //setCharacterStream()方法,可用于将CLOB字段与字节流相关联,
- BufferedWriterout=newBufferedWriter(newFileWriter(f));
- intc;
- while((c=in.read())!=-1)
- {
- out.write(c);
- }
- out.close();
- in.close();
- }
- con.commit();//正式提交
- rs.close();
- }
- catch(Exceptione)
- {
- e.printStackTrace();
- con.rollback();
- }
- }
- }
最后,就是新建测试类了,每每用着main方法,我就感觉这世界是多么的有爱啊,junit什么的,最讨厌了.....
- publicclassTestBlob_Clob
- {
- publicstaticvoidmain(String[]args)
- {
- Stringsql1="select*fromcdl_testforupdate";//悲观锁锁定需更新的行
- Stringsql2="select*fromcdl_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
- {
- Scannersc=newScanner(System.in);
- inti=sc.nextInt();
- System.out.println("sss:"+i);
- switch(i)
- {
- case1:
- InsertBlobDataisd=newInsertBlobData();
- //插入图片
- isd.insertBlob(sql1);
- break;
- case2:
- InsertClobDataicd=newInsertClobData();
- //插入小说
- icd.insertClob(sql1);
- break;
- case3:
- ReadBlobDatarb=newReadBlobData();
- //得到图片
- rb.getBlob(sql2);
- break;
- case4:
- ReadClobDatarc=newReadClobData();
- //得到小说
- rc.getClob(sql2);
- break;
- case5:
- System.exit(0);
- }
- }
- catch(Exceptione)
- {
- e.printStackTrace();
- }
- }
- }
- }