java oracle clob

create table TEST_CLOB
(
  ID      INTEGER not null,
  NAME    VARCHAR2(20),
  CONTENT CLOB
)
alter table TEST_CLOB
  add primary key (ID);

create table TEST_CLOB
(
  ID      INTEGER not null,
  NAME    VARCHAR2(20),
  CONTENT CLOB
)
alter table TEST_CLOB
  add primary key (ID);

 

package com.logcd.common;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.io.Reader;
import java.io.Writer;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.io.FileUtils;

import oracle.sql.CLOB;

public class OracleClob {

    public static void main(String[] args) {

        Integer id = saveClobDataUseSQL("测试", getFileContentAsString(
                "D:/uploadDir/test.txt", true));

        readClobDataUseSQL(id, new File("D:/uploadDir/test2.txt"));
    }

    /**
     * 保存Clob数据
     *
     * @param name
     *            数据名称
     * @param data
     *            字串数据
     */
    @SuppressWarnings("deprecation")
    public static Integer saveClobDataUseSQL(String name, String data) {
        Connection conn = getConnection();
        Integer id = (int) (Math.random() * 100000);

        StringBuilder sqlBuilder = new StringBuilder();
        try {
            conn.setAutoCommit(false);
            Statement stmt = conn.createStatement();

            sqlBuilder.append("insert into TEST_CLOB(ID, NAME, CONTENT) ");
            sqlBuilder.append("values ( " + id);
            sqlBuilder.append(",'" + name + "'");
            sqlBuilder.append(", empty_clob()) ");// 插入一个空对象empty_clob()

            stmt.executeUpdate(sqlBuilder.toString());
            // 锁定数据行进行更新，注意“for update”语句
            String sqlUpd = "select CONTENT from TEST_CLOB where ID = " + id
                    + " for update";
            ResultSet rs = stmt.executeQuery(sqlUpd);
            if (rs.next()) {
                // 得到java.sql.Clob对象后强制转换为oracle.sql.CLOB
                CLOB clob = (CLOB) rs.getClob("CONTENT");
                Writer outStream = clob.setCharacterStream(0L);
                // data是传入的字符串，定义：String data
                char[] c = data.toCharArray();
                outStream.write(c, 0, c.length);

                outStream.flush();
                outStream.close();

                conn.commit();
                stmt.close();
            }

        } catch (Exception e) {
            try {
                conn.rollback();
                id = null;
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return id;
    }

    /**
     * 读出数据并存成文件
     *
     * @param id
     * @param file
     */
    public static void readClobDataUseSQL(Integer id, File file) {
        Connection conn = getConnection();
        try {
            Statement st = conn.createStatement();
            String sql = "select CONTENT from TEST_CLOB where ID = " + id;
            ResultSet rs = st.executeQuery(sql);

            if (rs.next()) {

                CLOB clob = (CLOB) rs.getClob("CONTENT");

                String result = convertClobToString(clob);
                System.out.println(result);
                FileUtils.writeStringToFile(file,result, "utf-8");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 将CLOB转换成字串
     * @param clob
     * @return
     */
    public static String convertClobToString(CLOB clob) {
        String reString = "";
        try {
            Reader is = clob.getCharacterStream();// 得到流
            BufferedReader br = new BufferedReader(is);
            String s = br.readLine();
            StringBuffer sb = new StringBuffer();
            while (s != null) {
                sb.append(s);
                sb.append("\n");
                s = br.readLine();
            }
            reString = sb.toString().trim();
        } catch(Exception e) {
            e.printStackTrace();
        }
        return reString;
    }

    /**
     * 取得数据库连接
     *
     * @return
     */
    public static Connection getConnection() {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@195.2.199.5:1521:orcl";
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, "testdb", "logcd");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return conn;
    }

    /**
     * 读取文件内容
     *
     * @param _url
     * @param isLocal
     * @return
     */
    public static String getFileContentAsString(String _url, boolean isLocal) {
        StringBuilder strb = new StringBuilder();
        try {
            InputStreamReader read = null;
            ;
            if (!isLocal) {
                URL url = new URL(_url);
                HttpURLConnection connection = (HttpURLConnection) url
                        .openConnection();
                read = new InputStreamReader(connection.getInputStream());
            } else {
                File file = new File(_url);
                read = new InputStreamReader(new FileInputStream(file));
            }
            BufferedReader br = new BufferedReader(read);

            char[] cbuf = new char[1024];
            while (br.read(cbuf) != -1) {
                strb.append(cbuf);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return strb.toString();
    }

}