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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
|
/*
* This sample demonstrate basic Lob support.
*/
import java.sql.*;
import java.io.*;
import java.util.*;
// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.*;
//needed for new CLOB and BLOB classes
import oracle.sql.*;
public class LobExample
{
public static void main (String args [])
throws Exception
{
// Register the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
String url = "jdbc:oracle:oci8:@";
try {
String url1 = System.getProperty("JDBC_URL");
if (url1 != null)
url = url1;
} catch (Exception e) {
// If there is any security exception, ignore it
// and use the default
}
// Connect to the database
Connection conn =
DriverManager.getConnection (url, "scott", "tiger");
// It's faster when auto commit is off
conn.setAutoCommit (false);
// Create a Statement
Statement stmt = conn.createStatement ();
try
{
stmt.execute ("drop table basic_lob_table");
}
catch (SQLException e)
{
// An exception could be raised here if the table did not exist already.
}
// Create a table containing a BLOB and a CLOB
stmt.execute ("create table basic_lob_table (x varchar2 (30), b blob, c clob)");
// Populate the table
stmt.execute ("insert into basic_lob_table values ('one', '010101010101010101010101010101', 'onetwothreefour')");
stmt.execute ("insert into basic_lob_table values ('two', '0202020202020202020202020202', 'twothreefourfivesix')");
System.out.println ("Dumping lobs");
// Select the lobs. Since the Lob contents will be modified, we need
// to lock the table rows. This can be done by doing "select ... for
// update", but we don't need to select for update here because we
// have "autocommit" turned off and the previous "create table"
// statement already have the whole table locked.
ResultSet rset = stmt.executeQuery ("select * from basic_lob_table");
while (rset.next ())
{
// Get the lobs
BLOB blob = ((OracleResultSet)rset).getBLOB (2);
CLOB clob = ((OracleResultSet)rset).getCLOB (3);
// Print the lob contents
dumpBlob (blob);
dumpClob (clob);
// Change the lob contents
fillClob (clob, 2000);
fillBlob (blob, 4000);
}
System.out.println ("Dumping lobs again");
rset = stmt.executeQuery ("select * from basic_lob_table");
while (rset.next ())
{
// Get the lobs
BLOB blob = ((OracleResultSet)rset).getBLOB (2);
CLOB clob = ((OracleResultSet)rset).getCLOB (3);
// Print the lobs contents
dumpBlob (blob);
dumpClob (clob);
}
// Close all resources
rset.close();
stmt.close();
conn.close();
}
// Utility function to dump Clob contents
static void dumpClob (CLOB clob)
throws Exception
{
// get character stream to retrieve clob data
Reader instream = clob.getCharacterStream();
// create temporary buffer for read
char[] buffer = new char[10];
// length of characters read
int length = 0;
// fetch data
while ((length = instream.read(buffer)) != -1)
{
System.out.print("Read " + length + " chars: ");
for (int i=0; i<length; i++)
System.out.print(buffer[i]);
System.out.println();
}
// Close input stream
instream.close();
}
// Utility function to dump Blob contents
static void dumpBlob (BLOB blob)
throws Exception
{
// Get binary output stream to retrieve blob data
InputStream instream = blob.getBinaryStream();
// Create temporary buffer for read
byte[] buffer = new byte[10];
// length of bytes read
int length = 0;
// Fetch data
while ((length = instream.read(buffer)) != -1)
{
System.out.print("Read " + length + " bytes: ");
for (int i=0; i<length; i++)
System.out.print(buffer[i]+" ");
System.out.println();
}
// Close input stream
instream.close();
}
// Utility function to put data in a Clob
static void fillClob (CLOB clob, long length)
throws Exception
{
Writer outstream = clob.getCharacterOutputStream();
int i = 0;
int chunk = 10;
while (i < length)
{
outstream.write(i + "hello world", 0, chunk);
i += chunk;
if (length - i < chunk)
chunk = (int) length - i;
}
outstream.close();
}
// Utility function to put data in a Blob
static void fillBlob (BLOB blob, long length)
throws Exception
{
OutputStream outstream = blob.getBinaryOutputStream();
int i = 0;
int chunk = 10;
byte [] data = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
while (i < length)
{
data [0] = (byte)i;
outstream.write(data, 0, chunk);
i += chunk;
if (length - i < chunk)
chunk = (int) length - i;
}
outstream.close();
}
} |
ORACLE / JDBC => Insérer un CLOB ...
Répondre avec citation
Partager