JDBC对于 clob 与 blob 的存取

首先在数据库中建立有clob和blob数据类型的表

  create table my(
  id number priamry key,
  data clob
  );

  create table you(
  id number priamry key,
  data blob
  );

java中对于clob与blob的存取

package com.briup.jdbc;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CLobBlobTest {
    private String driver="oracle.jdbc.driver.OracleDriver";
    private String url="jdbc:oracle:thin:@192.168.43.216:1521:XE";
    private String user="jd1812";
    private String password="briup";
    public static void main(String[] args) {
//      new CLobBlobTest().insertClob();
//      new CLobBlobTest().readClob();
//      new CLobBlobTest().insertBlob();
        new CLobBlobTest().readBlob();
    }

    //1.从数据库读取blob类型数据
    public void readBlob(){
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(driver);
            conn=DriverManager.getConnection(url, user, password);
            String sql="select id,data from you";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                int id=rs.getInt(1);
//              Blob blob=rs.getBlob(2);
//              InputStream is=blob.getBinaryStream();
                //获取blob字段的值
                InputStream is=rs.getBinaryStream(2);
                BufferedReader br=
                        new BufferedReader(new InputStreamReader(is));
                String str=null;
                while((str=br.readLine())!=null){
                    System.out.println(str);
                }
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                if(ps!=null)ps.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    //2.向数据库写入blob类型数据
    public void insertBlob(){
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            Class.forName(driver);
            conn=DriverManager.getConnection(url, user, password);
            String sql="insert into you values(?,?)";
            ps=conn.prepareStatement(sql);
            ps.setInt(1, 1);
            File file=new File("src/jd1812_jdbc.txt");
            ps.setBlob(2, new FileInputStream(file));
            ps.execute();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                if(ps!=null)ps.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    //从数据库读取Clob类型数据
    public void readClob(){
        Connection conn=null;
        PreparedStatement ps=null;
        ResultSet rs=null;
        try {
            Class.forName(driver);
            conn=DriverManager.getConnection(url, user, password);
            String sql="select id,data from my";
            ps=conn.prepareStatement(sql);
            rs=ps.executeQuery();
            while(rs.next()){
                int id=rs.getInt("id");
                //可以用，有可能取不到值
                //Reader reader=rs.getCharacterStream("data");
                Clob clob=rs.getClob("data");
                Reader reader=clob.getCharacterStream();
                BufferedReader br=
                        new BufferedReader(reader);
                String str=null;
                while((str=br.readLine())!=null){
                    System.out.println(str);
                }
            }
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                if(ps!=null)ps.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }

    //4.向数据库写入clob类型数据
    public void insertClob(){
        Connection conn=null;
        PreparedStatement ps=null;
        try {
            Class.forName(driver);
            conn=DriverManager.getConnection(url, user, password);
            String sql="insert into my values(?,?)";
            ps=conn.prepareStatement(sql);
            ps.setInt(1, 3);
            //如果内容全是字母和数字Ascii
            //第二个参数是字节流
            //文本路径
//          File file=new File("src/com/briup/jdbc/jdbc.txt");
            File file=new File("src/jd1812_jdbc.txt");
            //ps.setAsciiStream(2, new FileInputStream(file));
//          ps.setAsciiStream(2, new FileInputStream(file),file.length());
            //如果内容有中文等字符
            ps.setCharacterStream(2, new FileReader(file));
            ps.execute();
        } catch (ClassNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }finally {
            try {
                if(ps!=null)ps.close();
                if(conn!=null)conn.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }
}