Skip to content

Commit 5a8fff6

Browse files
nsundaransundara
committed
Added New code samples
New code samples for index-by table (Associative Arrays)
1 parent 41745f2 commit 5a8fff6

File tree

2 files changed

+700
-0
lines changed

2 files changed

+700
-0
lines changed

‎java/jdbc/AdvancedSamples/AssociativeArraysSample1.java

Lines changed: 344 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,344 @@
1+
/* $Header: dbjava/demo/samples/generic/AssociativeArraysSample1.java /main/1 2019/09/06 07:14:40 cmahidha Exp $ */
2+
3+
/* Copyright (c) 2019, Oracle and/or its affiliates. All rights reserved.*/
4+
5+
/*
6+
* DESCRIPTION
7+
*
8+
* This sample demonstrates the usage of Associative Arrays (Index-By-Tables) in JDBC.
9+
* Uses scalar data types as elements.
10+
*
11+
* Each unit of this sample demonstrates the following:
12+
* 1. defining a PL/SQL package that groups Index-By SQL type and a stored procedure,
13+
* 2. creating an Oracle Array with an array or a map of elements for Index-By type.
14+
* 2. invoking the Stored Procedure with IN, OUT, IN OUT Index-By type parameters,
15+
* 3. and the correspondence of IN/OUT parameter with get/set/register methods.
16+
*
17+
* It is required that applications have Oracle JDBC driver 18c release (ojdbc8.jar) in
18+
* the class-path. The previous release of Oracle JDBC drivers provided support only for
19+
* PL/SQL Associative Arrays of Scalar data types. Also, the support was restricted only to
20+
* the values of the key-value pairs of the Arrays. Oracle Database Release 18c supports
21+
* accessing both the keys (indexes) and values of Associative Arrays, and also provides
22+
* support for Associative Arrays of object types.
23+
*
24+
* The following APIs can be used for Index By table types on Oracle database release
25+
* version 12c and higher.
26+
*
27+
* Array createOracleArray(String arrayTypeName,
28+
* Object elements)
29+
* throws SQLException
30+
31+
* ARRAY createARRAY(String typeName,
32+
* Object elements)
33+
* throws SQLException
34+
*
35+
* It is recommended to continue using the following deprecated APIs on Oracle database
36+
* releases earlier than 12c.
37+
*
38+
* oracle.jdbc.OraclePreparedStatement.setPlsqlIndexTable()
39+
* oracle.jdbc.OracleCallableStatement.getPlsqlIndexTable()
40+
* oracle.jdbc.OracleCallableStatement.getOraclePlsqlIndexTable()
41+
* oracle.jdbc.OracleCallableStatement.registerIndexTableOutParameter()
42+
*
43+
* For detailed documentation, refer 4.7 Accessing PL/SQL Associative Arrays in Oracle
44+
* Database JDBC Developer's Guide, Release 18c.
45+
*
46+
* To run the sample, you must provide non-default and working values for ALL 3
47+
* of user, password, and URL. This can be done by either updating
48+
* this file directly or supplying the 3 values as command-line options
49+
* and user input. The password is read from console or standard input.
50+
* java AssociativeArraysSample1 -l <url> -u <user>
51+
* If you do not update all the defaults, the program proceeds but
52+
* will hit error when connecting.
53+
*/
54+
55+
import java.io.BufferedReader;
56+
import java.io.InputStreamReader;
57+
import java.sql.Array;
58+
import java.sql.CallableStatement;
59+
import java.sql.Connection;
60+
import java.sql.SQLException;
61+
import java.sql.Statement;
62+
import java.sql.Types;
63+
import java.util.Arrays;
64+
import java.util.HashMap;
65+
import java.util.Map;
66+
import java.util.stream.Collectors;
67+
68+
import oracle.jdbc.OracleArray;
69+
import oracle.jdbc.OracleConnection;
70+
import oracle.jdbc.pool.OracleDataSource;
71+
72+
public class AssociativeArraysSample1 {
73+
private final static String DEFAULT_URL = "jdbc:oracle:thin:@//myhost:myport/myservice";
74+
private final static String DEFAULT_USER = "myuser";
75+
private final static String DEFAULT_PASSWORD = "mypassword";
76+
// You must provide non-default values for ALL 3 to execute the program
77+
private static String url = DEFAULT_URL;
78+
private static String user = DEFAULT_USER;
79+
private static String password = DEFAULT_PASSWORD;
80+
81+
private static String PACKAGE_NAME = "indexbydemopkg";
82+
private static String INDEX_BY_TYPE_NAME = "indexbytype";
83+
private static String PROCEDURE_NAME = "proc";
84+
85+
private static String FULLY_QUALIFIED_INDEX_BY_TYPE_NAME = (PACKAGE_NAME + "." + INDEX_BY_TYPE_NAME).toUpperCase();
86+
private static String FULLY_QUALIFIED_PROCEDURE_NAME = (PACKAGE_NAME + "." + PROCEDURE_NAME).toUpperCase();
87+
88+
private static String CREATE_PKG_DDL =
89+
"CREATE OR REPLACE PACKAGE "+PACKAGE_NAME+" IS "
90+
+ "\n\tTYPE "+INDEX_BY_TYPE_NAME+" IS TABLE OF VARCHAR2(40) INDEX BY BINARY_INTEGER;"
91+
+ "\n\tPROCEDURE "+PROCEDURE_NAME+"( "
92+
+ "\n\t\tp_in IN "+INDEX_BY_TYPE_NAME+", "
93+
+ "\n\t\tp_out OUT "+INDEX_BY_TYPE_NAME+", "
94+
+ "\n\t\tp_inout IN OUT "+INDEX_BY_TYPE_NAME
95+
+ "\n\t); "
96+
+ "\nEND "+PACKAGE_NAME+";";
97+
98+
private static String CREATE_PKG_BODY_DDL =
99+
"CREATE OR REPLACE PACKAGE BODY "+PACKAGE_NAME+" AS"
100+
+ "\n\tPROCEDURE "+PROCEDURE_NAME+"( "
101+
+ "\n\t\tp_in IN "+INDEX_BY_TYPE_NAME+", "
102+
+ "\n\t\tp_out OUT "+INDEX_BY_TYPE_NAME+", "
103+
+ "\n\t\tp_inout IN OUT "+INDEX_BY_TYPE_NAME
104+
+ "\n\t) IS "
105+
+ "\n\tl_idx BINARY_INTEGER;"
106+
+ "\n\tBEGIN"
107+
+ "\n\t\tl_idx := p_in.FIRST;"
108+
+ "\n\t\tWHILE(l_idx IS NOT NULL)"
109+
+ "\n\t\tLOOP"
110+
+ "\n\t\t\tp_out(l_idx)\t:=\tp_in(l_idx);"
111+
+ "\n\t\t\tp_inout(l_idx)\t:=\tp_in(l_idx);"
112+
+ "\n\t\t\tl_idx := p_in.NEXT(l_idx);"
113+
+ "\n\t\tEND LOOP;"
114+
+ "\n\tEND;"
115+
+ "\nEND;";
116+
117+
public static void main(String args[]) throws Exception {
118+
Util.getRealUserPasswordUrl(args);
119+
120+
AssociativeArraysSample1 sample = new AssociativeArraysSample1();
121+
sample.run();
122+
}
123+
124+
private void run() {
125+
try (Connection conn = getConnection()) {
126+
// Create PLSQL package in the database.
127+
createPlSqlPackage(conn);
128+
129+
// Demonstrates usage of Index-By table and instantiation of
130+
// java.sql.Array with an array of elements.
131+
demoIndexByTableWithElementsAsArray(conn);
132+
133+
// Demonstrates usage of Index-By tables and instantiation of
134+
// java.sql.Array with a map of elements.
135+
demoIndexByTableWithElementsAsMapOfEntries(conn);
136+
137+
} catch (SQLException sqlEx) {
138+
Util.showError("run", sqlEx);
139+
}
140+
}
141+
142+
/**
143+
* Creates PLSQL package in the database.
144+
*/
145+
private void createPlSqlPackage(Connection conn) throws SQLException {
146+
try (Statement stmt = conn.createStatement()) {
147+
System.out.println("CREATE PACKAGE DDL:\n\n" + CREATE_PKG_DDL + "\n" + CREATE_PKG_BODY_DDL);
148+
stmt.execute(CREATE_PKG_DDL);
149+
stmt.execute(CREATE_PKG_BODY_DDL);
150+
}
151+
}
152+
153+
/**
154+
* Demonstrates usage of Index-By table and instantiation of java.sql.Array
155+
* with an array of elements.
156+
*/
157+
private void demoIndexByTableWithElementsAsArray(Connection conn) throws SQLException {
158+
// Initialize an array of strings.
159+
final String[] inParamArrayOfStrings = { "str1", "str2", "str3str3str3str3str3str3str3str3str3", null, null,
160+
"str444444444444444444444444444", null, "", " ", "\n", " hi " };
161+
162+
// Create Oracle Array.
163+
final Array inParam = ((OracleConnection) conn).createOracleArray(FULLY_QUALIFIED_INDEX_BY_TYPE_NAME,
164+
inParamArrayOfStrings);
165+
166+
// Prepare CallableStatement with stored procedure call SQL and execute.
167+
try (CallableStatement cStmt = prepareCallAndExecute(conn, inParam);) {
168+
// Read values of OUT/IN OUT parameter as an array of elements.
169+
readOutParamsAsArray(cStmt);
170+
171+
// Read values of OUT/IN OUT parameter as an array of elements.
172+
readOutParamsAsMap(cStmt);
173+
}
174+
}
175+
176+
/**
177+
* Demonstrates usage of Index-By tables and instantiation of java.sql.Array
178+
* with a map of elements.
179+
*/
180+
private void demoIndexByTableWithElementsAsMapOfEntries(Connection conn) throws SQLException {
181+
// Initialize the map. The indices of Associative Array can be sparse and negative.
182+
final Map<Integer, String> map = new HashMap<>();
183+
map.put(-10, "str1");
184+
map.put(20, "str2");
185+
map.put(-30, "str3str3str3str3str3str3str3str3str3");
186+
map.put(10, null);
187+
map.put(-20, null);
188+
map.put(40, "str444444444444444444444444444");
189+
map.put(45, null);
190+
map.put(33, "");
191+
map.put(-15, " ");
192+
map.put(15, "\n");
193+
map.put(18, " hi ");
194+
195+
// Create Oracle Array.
196+
final Array inParam = ((OracleConnection) conn).createOracleArray(FULLY_QUALIFIED_INDEX_BY_TYPE_NAME, map);
197+
198+
// Prepare CallableStatement with stored procedure call SQL and execute.
199+
try (CallableStatement cStmt = prepareCallAndExecute(conn, inParam)) {
200+
// Read values of OUT/IN OUT parameter as an array of elements.
201+
readOutParamsAsArray(cStmt);
202+
203+
// Read values of OUT/IN OUT parameter as an array of elements.
204+
readOutParamsAsMap(cStmt);
205+
}
206+
}
207+
208+
/**
209+
* Prepare CallableStatement with stored procedure call SQL and execute.
210+
*/
211+
private CallableStatement prepareCallAndExecute(Connection conn, Object elements) throws SQLException {
212+
final CallableStatement cStmt = conn.prepareCall("BEGIN " + FULLY_QUALIFIED_PROCEDURE_NAME + " (?,?,?); END;");
213+
cStmt.setObject(1, elements);
214+
cStmt.registerOutParameter(2, Types.ARRAY, FULLY_QUALIFIED_INDEX_BY_TYPE_NAME);
215+
cStmt.registerOutParameter(3, Types.ARRAY, FULLY_QUALIFIED_INDEX_BY_TYPE_NAME);
216+
cStmt.execute();
217+
218+
return cStmt;
219+
}
220+
221+
/**
222+
* Read values of OUT/IN OUT parameter as an array of elements.
223+
*/
224+
private void readOutParamsAsArray(CallableStatement cStmt) throws SQLException {
225+
// Read OUT parameter.
226+
final Array outParam = cStmt.getArray(2);
227+
final String[] outParamArrayOfStrings = (String[]) outParam.getArray();
228+
System.out.println("\nValues of OUT param read as an array of Strings:");
229+
System.out.println(Arrays.stream(outParamArrayOfStrings).collect(Collectors.joining(", ")));
230+
231+
// Read IN OUT parameter.
232+
final Array inOutParam = cStmt.getArray(3);
233+
final String[] inOutParamArrayOfStrings = (String[]) inOutParam.getArray();
234+
System.out.println("\nValues of IN OUT param read as an array of Strings:");
235+
System.out.println(Arrays.stream(inOutParamArrayOfStrings).collect(Collectors.joining(", ")));
236+
}
237+
238+
/**
239+
* Read values of OUT/IN OUT parameter as a Map of objects.
240+
*/
241+
private void readOutParamsAsMap(CallableStatement cStmt) throws SQLException {
242+
// Read OUT parameter as Map<Integer, Object>.
243+
final Array outParam = cStmt.getArray(2);
244+
@SuppressWarnings("unchecked")
245+
final Map<Integer, String> outParamMap = (Map<Integer, String>) ((OracleArray) outParam).getJavaMap();
246+
System.out.println("\nValues of OUT param read as a Map of <Integer, String> pairs:");
247+
outParamMap.forEach((key, value) -> {
248+
System.out.println(key + "\t:\t" + value);
249+
});
250+
251+
// Read IN OUT parameter as Map<Integer, Object>.
252+
final Array inOutParam = cStmt.getArray(3);
253+
@SuppressWarnings("unchecked")
254+
final Map<Integer, String> inOutParamMap = (Map<Integer, String>) ((OracleArray) inOutParam).getJavaMap();
255+
System.out.println("\nValues of IN OUT param read as a Map of <Integer, String> pairs:");
256+
inOutParamMap.forEach((key, value) -> {
257+
System.out.println(key + "\t:\t" + value);
258+
});
259+
}
260+
261+
/**
262+
* Get a connection using the driver data source.
263+
*/
264+
private Connection getConnection() throws SQLException {
265+
OracleDataSource ods = new OracleDataSource();
266+
ods.setURL(url);
267+
ods.setUser(user);
268+
ods.setPassword(password);
269+
270+
// Creates a physical connection to the database.
271+
return ods.getConnection();
272+
}
273+
274+
// Utility methods.
275+
private static class Util {
276+
277+
static void getRealUserPasswordUrl(String args[]) throws Exception {
278+
// URL can be modified in file, or taken from command-line
279+
url = getOptionValue(args, "-l", DEFAULT_URL);
280+
281+
// DB user can be modified in file, or taken from command-line
282+
user = getOptionValue(args, "-u", DEFAULT_USER);
283+
284+
// DB user's password can be modified in file, or explicitly entered
285+
readPassword("Password for " + user + ": ");
286+
}
287+
288+
public static void show(String msg) {
289+
System.out.println(msg);
290+
}
291+
292+
public static void showError(String msg, Throwable exc) {
293+
System.err.println(msg + " hit error: " + exc.getMessage());
294+
}
295+
296+
// Get specified option value from command-line.
297+
static String getOptionValue(String args[], String optionName, String defaultVal) {
298+
String argValue = "";
299+
try {
300+
int i = 0;
301+
String arg = "";
302+
boolean found = false;
303+
304+
while (i < args.length) {
305+
arg = args[i++];
306+
307+
if (arg.equals(optionName)) {
308+
if (i < args.length)
309+
argValue = args[i++];
310+
if (argValue.startsWith("-") || argValue.equals("")) {
311+
argValue = defaultVal;
312+
}
313+
found = true;
314+
}
315+
}
316+
317+
if (!found) {
318+
argValue = defaultVal;
319+
}
320+
} catch (Exception e) {
321+
showError("getOptionValue", e);
322+
}
323+
324+
return argValue;
325+
}
326+
327+
static void readPassword(String prompt) throws Exception {
328+
if (System.console() != null) {
329+
char[] pchars = System.console().readPassword("\n[%s]", prompt);
330+
if (pchars != null) {
331+
password = new String(pchars);
332+
java.util.Arrays.fill(pchars, ' ');
333+
}
334+
} else {
335+
BufferedReader r = new BufferedReader(new InputStreamReader(System.in));
336+
show(prompt);
337+
password = r.readLine();
338+
}
339+
}
340+
341+
}
342+
343+
}
344+

0 commit comments

Comments
 (0)