转贴
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();
- }
- }
- }
- }