java oracle clob string 大字串儲存【轉】

標籤：cep   not   插入   manager   code   lin   ade   ati   pre   

單位用到了oracle儲存string類型到資料庫裡的clob，上網查看資料找到解決方案。如下：

 1     public class ClobTest {   2        3         static String url = "jdbc:oracle:thin:@10.12.10.18:1521:orcl";   4         static String user = "cwbe1_9999";   5         static String pwd = "or777";   6         static String text = "這是要插入到CLOB裡面的資料,更新資料！" + "update";   7        8         private static int clobImport() throws ClassNotFoundException, SQLException {   9             DriverManager.registerDriver(new OracleDriver());  10             Connection conn = DriverManager.getConnection(url, user, pwd);// 得到連線物件  11             String sql = "insert into ETLNEEDPARAM(F_KEY,F_VALUE) values (‘defaultpo‘,?)";// 要執行的SQL語句  12             PreparedStatement stmt = conn.prepareStatement(sql);// 載入SQL語句  13             // PreparedStatement支援SQL帶有問號？，可以動態替換？的內容。  14             Reader clobReader = new StringReader(text); // 將 text轉成流形式  15             stmt.setCharacterStream(1, clobReader, text.length());// 替換sql語句中的？  16             int num = stmt.executeUpdate();// 執行SQL  17             if (num > 0) {  18                 System.out.println("ok");  19             } else {  20                 System.out.println("NO");  21             }  22             stmt.close();  23             conn.close();  24             return num;  25         }  26       27         private static int clobUpdate(String key) throws ClassNotFoundException, SQLException {  28       29             DriverManager.registerDriver(new OracleDriver());  30             Connection conn = DriverManager.getConnection(url, user, pwd);// 得到連線物件  31             String sql = "update ETLNEEDPARAM set F_VALUE = ? where F_KEY = ? ";// 要執行的SQL語句  32             PreparedStatement stmt = conn.prepareStatement(sql);// 載入SQL語句  33             // PreparedStatement支援SQL帶有問號？，可以動態替換？的內容。  34             Reader clobReader = new StringReader(text); // 將 text轉成流形式  35             stmt.setString(2, key);  36             stmt.setCharacterStream(1, clobReader, text.length());// 替換sql語句中的？  37             int num = stmt.executeUpdate();// 執行SQL  38             if (num > 0) {  39                 System.out.println("ok");  40             } else {  41                 System.out.println("NO");  42             }  43             stmt.close();  44             conn.close();  45             return num;  46         }  47       48         private static String clobExport() throws ClassNotFoundException, SQLException, IOException {  49       50             CLOB clob = null;  51             String sql = "select F_VALUE from ETLNEEDPARAM where F_KEY =‘test1‘";  52             DriverManager.registerDriver(new OracleDriver());  53             Connection conn = DriverManager.getConnection(url, user, pwd);// 得到連線物件  54             PreparedStatement stmt = conn.prepareStatement(sql);  55             ResultSet rs = stmt.executeQuery();  56             String content = "";  57             if (rs.next()) {  58                 clob = (oracle.sql.CLOB) rs.getClob("F_VALUE"); // 獲得CLOB欄位str  59                 // 注釋： 用 rs.getString("str")無法得到 資料 ，返回的 是 NULL;  60                 content = ClobToString(clob);  61             }  62             stmt.close();  63             conn.close();  64             return content;  65         }  66       67         // 將字CLOB轉成STRING類型  68       69         public static String ClobToString(CLOB clob) throws SQLException, IOException {  70             String reString = "";  71             Reader is = clob.getCharacterStream();// 得到流  72             BufferedReader br = new BufferedReader(is);  73             String s = br.readLine();  74             StringBuffer sb = new StringBuffer();  75             // 執行迴圈將字串全部取出付值給StringBuffer由StringBuffer轉成STRING  76             while (s != null) {  77                 sb.append(s);  78                 s = br.readLine();  79             }  80             reString = sb.toString();  81             return reString;  82         }  83       84         public static void main(String[] args) throws IOException,ClassNotFoundException, SQLException {              85             // System.out.println(clobImport());  86             System.out.println(clobUpdate("fmo"));  87             System.out.println(clobUpdate("epo"));  88       89             // System.out.println(clobExport());  90       91         }  

轉自：http://bestxiaok.iteye.com/blog/1027733

java oracle clob string 大字串儲存【轉】