Java 存储和读取 oracle CLOB 类型字段的实用方法

素颜马尾好姑娘i 2023-01-01 01:59 173阅读 0赞

import java.io.BufferedReader;
    import java.io.IOException;
    import java.io.Reader;
    import java.io.StringReader;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import oracle.jdbc.OracleDriver;
    import oracle.sql.CLOB;
    
    public class ClobTest {
        String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
        String user = "his_xs";
        String pwd = "123456";
        String text = "这是要插入到CLOB里面的数据";
    
        private void clobImport() throws ClassNotFoundException, SQLException {
            // TODO Auto-generated method stub
            DriverManager.registerDriver(new OracleDriver());
            Connection conn = DriverManager.getConnection(url, user, pwd);// 得到连接对象
            String sql = "insert into clob_test(id,str) values ('1',?)";// 要执行的SQL语句
    
            PreparedStatement stmt = conn.prepareStatement(sql);// 加载SQL语句
            // PreparedStatement支持SQL带有问号？，可以动态替换？的内容。
            Reader clobReader = new StringReader(text); // 将 text转成流形式
            stmt.setCharacterStream(1, clobReader, text.length());// 替换sql语句中的？
            int num = stmt.executeUpdate();// 执行SQL
            if (num > 0) {
                System.out.println("ok");
            } else {
                System.out.println("NO");
            }
            stmt.close();
            conn.close();
        }
    
        // 将字CLOB转成STRING类型
        public String ClobToString(CLOB clob) throws SQLException, IOException {
    
            String reString = "";
            Reader is = clob.getCharacterStream();// 得到流
            BufferedReader br = new BufferedReader(is);
            String s = br.readLine();
            StringBuffer sb = new StringBuffer();
            while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
                sb.append(s);
                s = br.readLine();
            }
            reString = sb.toString();
            return reString;
        }
    
        private void clobExport() throws ClassNotFoundException, SQLException,
                IOException {
            // TODO Auto-generated method stub
            CLOB clob = null;
            String sql = "select * from clob_test where id=1";
            DriverManager.registerDriver(new OracleDriver());
            Connection conn = DriverManager.getConnection(url, user, pwd);// 得到连接对象
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            String id = "";
            String content = "";
            if (rs.next()) {
                id = rs.getString("id");// 获得ID
                clob = (oracle.sql.CLOB) rs.getClob("str"); // 获得CLOB字段str
                // 注释： 用 rs.getString("str")无法得到 数据 ，返回的 是 NULL;
                content = ClobToString(clob);
            }
            stmt.close();
            conn.close();
            // 输出结果
            System.out.println(id);
            System.out.println(content);
        }
    
        
        public static void main(String[] args) throws Exception {
            ClobTest a = new ClobTest();
            a.clobImport();
            a.clobExport();
        }
    
    }