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里面的数据
"
;
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;
}
//
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();
}
}