java读写Oracle COLB字段

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里面的数据";

/**

* @throws ClassNotFoundException

* @throws SQLException

* 数据库里已经建好表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);// 得到连接对象 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();

}

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);

}

// 将字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;

}

/**

* @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());

// 输入测试

clobtest.text = _input.toString();

clobtest.clobImport();

// 输出测试

// clobtest.clobExport();

}

}