今天遇到了向oracle里存儲(chǔ)大字符串情況，varchar2類型存儲(chǔ)長(zhǎng)度不夠，只能用CLOB類型存儲(chǔ)，于是學(xué)習(xí)學(xué)習(xí)如何用java進(jìn)行存儲(chǔ)和讀取oracle clob類型的字段。
下面是一個(gè)如何存儲(chǔ)和讀取clob字段的小例子：
package oracle.clob;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
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.driver.OracleDriver;
import oracle.sql.CLOB;
public class ClobTest {
String url = "jdbc:oracle:thin:@192.168.2.157:1521:orcl";
String user = "xj";
String pwd = "xj";
String text = "這是要插入到CLOB里面的數(shù)據(jù)";
/**
* @throws ClassNotFoundException
* @throws SQLException
* 數(shù)據(jù)庫(kù)里已經(jīng)建好表CLOB_TEST(ID NUMBER,STR CLOB);
*/
private void clobImport() throws ClassNotFoundException, SQLException {
// TODO Auto-generated method stub
DriverManager.registerDriver(new OracleDriver());
Connection conn = DriverManager.getConnection(url, user, pwd);// 得到連接對(duì)象
String sql = "insert into clob_test(id,str) values ('1',?)";// 要執(zhí)行的SQL語(yǔ)句
PreparedStatement stmt = conn.prepareStatement(sql);// 加載SQL語(yǔ)句
// PreparedStatement支持SQL帶有問(wèn)號(hào)？，可以動(dòng)態(tài)替換？的內(nèi)容。
Reader clobReader = new StringReader(text); // 將 text轉(zhuǎn)成流形式
stmt.setCharacterStream(1, clobReader, text.length());// 替換sql語(yǔ)句中的？
int num = stmt.executeUpdate();// 執(zhí)行SQL
if (num > 0) {
System.out.println("ok");
} else {
System.out.println("NO");
}
stmt.close();
conn.close();
}
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);// 得到連接對(duì)象
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")無(wú)法得到 數(shù)據(jù) ，返回的 是 NULL;
content = ClobToString(clob);
}
stmt.close();
conn.close();
// 輸出結(jié)果
System.out.println(id);
System.out.println(content);
}
// 將字CLOB轉(zhuǎn)成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) {// 執(zhí)行循環(huán)將字符串全部取出付值給StringBuffer由StringBuffer轉(zhuǎn)成STRING
sb.append(s);
s = br.readLine();
}
reString = sb.toString();
return reString;
}
/**
* @param args
* @throws IOException
* @throws SQLException
* @throws ClassNotFoundException
*/
// TODO Auto-generated method stub
public static void main(String[] args) throws IOException,
ClassNotFoundException, SQLException {
// TODO Auto-generated method stub
ClobTest clobtest = new ClobTest();
// read file
FileReader _frd = new FileReader(new File("D:\\DOS.txt"));
BufferedReader _brd = new BufferedReader(_frd);
String _rs = _brd.readLine();
StringBuffer _input = new StringBuffer();
while (_rs != null) {
_input.append(_rs);
_rs = _brd.readLine();
}
// System.out.println(_input.toString());
// 輸入測(cè)試
clobtest.text = _input.toString();
clobtest.clobImport();
// 輸出測(cè)試
// clobtest.clobExport();
}
}