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
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
| /*
* This example demonstrates the Oracle 9i inheritance feature. It
* demonstrates the access of subtype objects by using JDBC default
* mapping.
*
* This example creates the object types hierarchy as follows --
*
* Person_t -+- Employee_t
* |
* +- Student_t -- ParttimeStudent_t
*
* and a object table of Person_t.
*
* This example inserts the Person_t, Employee_t, Student_t and
* ParttimeStudent_t objects into the object table, and selects
* the objects and prints out their values.
*/
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
public class Inheritance3
{
public static void main (String args []) throws Exception
{
// Load the Oracle JDBC driver
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
String urlDefault = "jdbc:oracle:oci8:@";
String url = null;
try
{
url = System.getProperty("JDBC_URL");
}
catch (Exception e)
{
// If there is any security exception, ignore it
// and use the default
}
finally
{
if (url == null) url = urlDefault;
}
// Connect to the database
Connection conn = DriverManager.getConnection (url, "scott", "tiger");
// Create the object types and the database table
createTables (conn);
// Insert Person_t, Student_t, ParttimeStudent_t and Employee_t
// objects into the database.
insert (conn);
// Select the objects previously inserted
select (conn);
// Remove the objects previously created in the database
cleanup (conn);
// Disconnect
conn.close ();
}
/**
* Create the database data structure to be used by this example.
*/
public static void createTables (Connection conn) throws SQLException
{
// Remove database data created by this example
cleanup (conn);
// Create a Statement
Statement stmt = conn.createStatement ();
// Create a database object type "Person_t".
stmt.execute ("CREATE TYPE Person_t AS OBJECT "+
"( ssn NUMBER, "+
" name VARCHAR2(30), "+
" address VARCHAR2(100) "+
") NOT FINAL");
// Create a database object type "Student_t" that inherits
// "Person_t".
stmt.execute ("CREATE TYPE Student_t UNDER Person_t "+
"( deptid NUMBER, "+
" major VARCHAR2(30) "+
") NOT FINAL");
// Create a database object type "Employee_t" that inherits
// "Person_t".
stmt.execute ("CREATE TYPE Employee_t UNDER Person_t"+
"( empid NUMBER, "+
" mgr VARCHAR2(30) "+
")");
// Create a database object type "PartTimeStudent_t" that
// inherits "Student_t".
stmt.execute ("CREATE TYPE PartTimeStudent_t UNDER Student_t"+
"( numhours NUMBER "+
")");
// Create a database table
stmt.execute ("CREATE TABLE PersonTab of Person_t");
// Close the statement
stmt.close ();
}
/**
* Remove database data created by this example
*/
public static void cleanup (Connection conn) throws SQLException
{
// Create a Statement
Statement stmt = conn.createStatement ();
try
{
stmt.execute ("drop table PersonTab");
}
catch (SQLException e)
{
// An exception could be raised here if the table did not exist already.
}
try { stmt.execute ("drop type Employee_t"); } catch (SQLException e) {}
try { stmt.execute ("drop type ParttimeStudent_t"); } catch (SQLException e) {}
try { stmt.execute ("drop type Student_t"); } catch (SQLException e) {}
try { stmt.execute ("drop type Person_t"); } catch (SQLException e) {}
// Close the statement
stmt.close ();
}
/**
* This method inserts Person_t, Student_t, ParttimeStudent_t
* and Employee_t objects into the PersonTab table. This
* demonstrates the column substitutablity feature of the
* Oracle 9i database.
*/
public static void insert (Connection conn) throws SQLException
{
// Prepare the insert statement
PreparedStatement pstmt =
conn.prepareStatement ("insert into PersonTab values (?)");
// A place holder for Person_t objects
STRUCT person = null;
// Create and insert a Person_t object into the database
{
// Obtain the Person_t type descriptor
StructDescriptor personDesc =
StructDescriptor.createDescriptor ("SCOTT.PERSON_T", conn);
// Prepare the Person_t attributes
Datum[] personAttrs =
{
new NUMBER (1001),
new CHAR ("Scott", null),
new CHAR ("SF", null)
};
// Create a Person_t object
person = new STRUCT (personDesc, conn, personAttrs);
// Bind the Person_t object
pstmt.setObject (1, person, OracleTypes.STRUCT);
// Execute the insertion
if (pstmt.executeUpdate () == 1)
System.out.println ("Successfully inserted a Person_t object");
else
System.out.println ("Insertion failed");
}
// Create and insert a Student_t object into the database
{
// Obtain the Student_t type descriptor
StructDescriptor studentDesc =
StructDescriptor.createDescriptor ("SCOTT.STUDENT_T", conn);
// Prepare the Student_t attributes
Datum[] studentAttrs =
{
new NUMBER (1002),
new CHAR ("Peter", null),
new CHAR ("NY", null),
new NUMBER (100),
new CHAR ("EE", null)
};
// Create a Student_t object
person = new STRUCT (studentDesc, conn, studentAttrs);
// Bind the Student_t object
pstmt.setObject (1, person, OracleTypes.STRUCT);
// Execute the insertion
if (pstmt.executeUpdate () == 1)
System.out.println ("Successfully inserted a Student_t object");
else
System.out.println ("Insertion failed");
}
// Create and insert a ParttimeStudent_t object into the database
{
// Obtain the ParttimeStudent_t type descriptor
StructDescriptor parttimestudentDesc =
StructDescriptor.createDescriptor ("SCOTT.PARTTIMESTUDENT_T", conn);
// Prepare the ParttimeStudent_t attributes
Datum[] parttimestudentAttrs =
{
new NUMBER (1003),
new CHAR ("John", null),
new CHAR ("LA", null),
new NUMBER (101),
new CHAR ("CS", null),
new NUMBER (20)
};
// Create ParttimeStudent_t object
person = new STRUCT (parttimestudentDesc, conn, parttimestudentAttrs);
// Bind the ParttimeStudent_t object
pstmt.setObject (1, person, OracleTypes.STRUCT);
// Execute the insertion and print the update count
if (pstmt.executeUpdate () == 1)
System.out.println ("Successfully inserted a ParttimeStudent_t object");
else
System.out.println ("Insertion failed");
}
// Create and insert a Employee_t object into the database
{
// Obtain the Employee_t type descriptor
StructDescriptor employeeDesc =
StructDescriptor.createDescriptor ("SCOTT.EMPLOYEE_T", conn);
// Prepare the Employee_t attributes
Datum[] employeeAttrs =
{
new NUMBER (1004),
new CHAR ("David", null),
new CHAR ("SF", null),
new NUMBER (1111),
new CHAR ("SCOTT", null)
};
// Create a Employee_t object
person = new STRUCT (employeeDesc, conn, employeeAttrs);
// Bind the Employee_t object
pstmt.setObject (1, person, OracleTypes.STRUCT);
// Execute the insertion
if (pstmt.executeUpdate () == 1)
System.out.println ("Successfully inserted a Employee_t object");
else
System.out.println ("Insertion failed");
}
// Close the PreparedStatment
pstmt.close ();
}
/**
* This method queries the database PersonTab table and print the
* object table values.
*/
public static void select (Connection conn) throws SQLException
{
System.out.println ("List the Person_t objects : ");
// Create a statement
Statement stmt = conn.createStatement ();
// Execute the query
ResultSet rset = stmt.executeQuery ("select value(t) from PersonTab t");
// Iterate the result set
while (rset.next ())
{
// The object value can be a Person_t, Student_t, ParttimeStudent_t
// or Employee_t.
STRUCT person = (STRUCT) rset.getObject (1);
// Obtain the type descriptor
StructDescriptor desc = person.getDescriptor ();
// Obtain the type metadata to display the attribute names
ResultSetMetaData md = desc.getMetaData ();
// Create a StringBuffer to save the attribute information
StringBuffer sbuf = new StringBuffer ();
// Write the SQL type name to hte StringBuffer
sbuf.append (person.getSQLTypeName()+": ");
// Access the Person_t attributes
Datum[] attrs = person.getOracleAttributes ();
// Iterate the attributes
for (int i=0; i<attrs.length; i++)
{
// For each attribute, we first write the attribute name
// the string buffer
sbuf.append (md.getColumnName(i+1)+"=");
// Write the attribute value to the string buffer
sbuf.append (attrs[i].stringValue()+" ");
}
// Print the stringBuffer value
System.out.println (sbuf.toString());
}
// Close the result set
rset.close ();
// Close the statement
stmt.close ();
}
} |
Passer un objet à une procédure ORACLE
Répondre avec citation
Envoyé par SheikYerbouti
Partager