1、 读取操作
CLOB
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
//获得数据库连接 Connection con = ConnectionFactory.getConnection(); con.setAutoCommit(false); Statement st = con.createStatement(); //不需要“for update” ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1"); if (rs.next()) { java.sql.Clob clob = rs.getClob("CLOBATTR"); Reader inStream = clob.getCharacterStream(); char[] c = new char[(int) clob.length()]; inStream.read(c); //data是读出并需要返回的数据，类型是String data = new String(c); inStream.close(); } inStream.close(); con.commit(); con.close(); |
BLOB
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
//获得数据库连接 Connection con = ConnectionFactory.getConnection(); con.setAutoCommit(false); Statement st = con.createStatement(); //不需要“for update” ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1"); if (rs.next()) { java.sql.Blob blob = rs.getBlob("BLOBATTR"); InputStream inStream = blob.getBinaryStream(); //data是读出并需要返回的数据，类型是byte[] data = new byte[input.available()]; inStream.read(data); inStream.close(); } inStream.close(); con.commit(); con.close(); |
2、写入操作
CLOB
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
//获得数据库连接 Connection con = ConnectionFactory.getConnection(); con.setAutoCommit(false); Statement st = con.createStatement(); //插入一个空对象empty_clob() st.executeUpdate("insert into TESTCLOB (ID, NAME, CLOBATTR) values (1, "thename", empty_clob())"); //锁定数据行进行更新，注意“for update”语句 ResultSet rs = st.executeQuery("select CLOBATTR from TESTCLOB where ID=1 for update"); if (rs.next()) { //得到java.sql.Clob对象后强制转换为oracle.sql.CLOB oracle.sql.CLOB clob = (oracle.sql.CLOB) rs.getClob("CLOBATTR"); Writer outStream = clob.getCharacterOutputStream(); //data是传入的字符串，定义：String data char[] c = data.toCharArray(); outStream.write(c, 0, c.length); } outStream.flush(); outStream.close(); con.commit(); con.close(); |
BLOB
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
//获得数据库连接 Connection con = ConnectionFactory.getConnection(); con.setAutoCommit(false); Statement st = con.createStatement(); //插入一个空对象empty_blob() st.executeUpdate("insert into TESTBLOB (ID, NAME, BLOBATTR) values (1, "thename", empty_blob())"); //锁定数据行进行更新，注意“for update”语句 ResultSet rs = st.executeQuery("select BLOBATTR from TESTBLOB where ID=1 for update"); if (rs.next()) { //得到java.sql.Blob对象后强制转换为oracle.sql.BLOB oracle.sql.BLOB blob = (oracle.sql.BLOB) rs.getBlob("BLOBATTR"); OutputStream outStream = blob.getBinaryOutputStream(); //data是传入的byte数组，定义：byte[] data outStream.write(data, 0, data.length); } outStream.flush(); outStream.close(); con.commit(); con.close(); |
3、读写CLOB/BLOB数据到文件
TNS：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
# tnsnames.ora Network Configuration File: d:\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora # Generated by Oracle configuration tools. ORADB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521)) ) (CONNECT_DATA = (SID = ORCL) ) ) MYORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.100)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = myorcl) ) ) |
Table：
|
1
2
3
4
5
6
|
create table TEST_ORALOB ( ID VARCHAR2(20), TSBLOB BLOB not null, TSCLOB CLOB not null ) |
测试代码：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
package lavasoft.oralob.common; import oracle.sql.BLOB; import java.io.*; import java.sql.*; /** * JDBC读写Oracle10g的CLOB、BLOB * */ public class TestOraLob { public static void main(String[] args) { insertBlob(); queryBlob(); } public static void insertBlob() { Connection conn = DBToolkit.getConnection(); PreparedStatement ps = null; try { String sql = "insert into test_oralob (ID, TSBLOB, TSCLOB) values (?, ?, ?)"; ps = conn.prepareStatement(sql); ps.setString(1, "100"); //设置二进制BLOB参数 File file_blob = new File("C:\\a.jpg"); InputStream in = new BufferedInputStream(new FileInputStream(file_blob)); ps.setBinaryStream(2, in, (int) file_blob.length()); //设置二进制CLOB参数 File file_clob = new File("c:\\a.txt"); InputStreamReader reader = new InputStreamReader(new FileInputStream(file_clob)); ps.setCharacterStream(3, reader, (int) file_clob.length()); ps.executeUpdate(); in.close(); } catch (IOException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { DBToolkit.closeConnection(conn); } } public static void queryBlob() { Connection conn = DBToolkit.getConnection(); PreparedStatement ps = null; Statement stmt = null; ResultSet rs = null; try { String sql = "select TSBLOB from TEST_ORALOB where id ='100'"; stmt = conn.createStatement(); rs = stmt.executeQuery(sql); if (rs.next()) { //读取Oracle的BLOB字段 InputStream in = rs.getBinaryStream(1); File file = new File("c:\\a1.jpg"); OutputStream out = new BufferedOutputStream(new FileOutputStream(file)); byte[] buff1 = new byte[1024]; for (int i = 0; (i = in.read(buff1)) > 0;) { out.write(buff1, 0, i); } out.flush(); out.close(); in.close(); //读取Oracle的CLOB字段 char[] buff2 = new char[1024]; File file_clob = new File("c:\\a1.txt"); OutputStreamWriter writer = new OutputStreamWriter(new FileOutputStream(file_clob)); Reader reader = rs.getCharacterStream(1); for (int i = 0; (i = reader.read(buff2)) > 0;) { writer.write(buff2, 0, i); } writer.flush(); writer.close(); reader.close(); } rs.close(); stmt.close(); } catch (IOException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } finally { DBToolkit.closeConnection(conn); } } } |
注：如果是具体的字符串写入CLOB字段，简化写法：
|
1
2
3
4
5
|
//设置二进制CLOB参数 String xxx = "abcdefg"; ps.setCharacterStream(3, new StringReader(xxx), xxx.getBytes("GBK").length); ps.executeUpdate(); in.close(); |
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持，如有疑问请留言或者到本站社区交流讨论，感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！
原文链接：http://blog.csdn.net/zmx729618/article/details/51568850
