oracle存储大数据类型(Clob/Blob)

oracle本身支持对大数据类型的各种处理，但是平常大家可能用的不多，其中clob(一般用于大类型的字符型的存取)和blob(一般用于大类型的二进制类型的存取)我想应该就是用的比较少的。所以我写下这篇博文，主要是对这两个数据类型的文件进行的操作，如，保存这类文件进入数据库，读取这类文件到本地磁盘。

本来按照老习惯是该先上效果图的，可惜我的机器现在巨卡，10g跑起来基本让我泪流满面，所以我就偷下懒，图就给省略了 -_-!

 

第一步，新建一个java项目(为啥是java项目而不是web呢，主要因为它便于测试，出效果也快...)orclTest

 

第二步，新建一个数据库连接的类，我这里是com.test.InitDB.java，代码如下：

class InitDB{
    private static Connection con = null;
    private static Statement stmt = null;
    private static ResultSet rs = null;

    //链接oracle数据库
    InitDB()
    {
        try
        {
            Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
            String url = "jdbc:oracle:thin:@localhost:1521:YYSMID"; 
            String user = "zhangsan";
            String password = "Lc123456";
            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;
    }
}

 

第三步，以及接下来的几步，我们来分别编写clob和blob的插入数据库和读出数据库的代码，新建com.test.InsertBlobData.java，顾名思义，它是用来插入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:\\1.jpg"); //1.jpg一张QQ的截图
                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();
        }
    }
}

 

第四步，新建com.test.InsertClobData.java，用于插入clob数据类型的，代码如下：

 

 

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:\\1.txt"); //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();
        }
    }
}

 

 

 

第五步，新建com.test.ReadBlobData.java，用于读取blob类型的数据，代码如下：

 

 

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:\\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();
        }
    }
}

 

 

第六步，新建com.test.ReadClobData.java，用于读取clob类型的数据，代码如下：

 

 

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:\\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();
        }
    }
}

 

最后，就是新建测试类了，每每用着main方法，我就感觉这世界是多么的有爱啊，junit什么的，最讨厌了.....

 

 

public class TestBlob_Clob
{
    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();
            }
        }
    }
}