ARRAY and STRUCT classes belong to the same collection class and are usually used together.
ARRAY descriptor
To create and use an ARRAY object, you need to specify a descriptor, that is, an instance of the oracle.sql.ArrayDescriptor class. For any number of ARRAY objects corresponding to the same SQL type, only one ArrayDescriptor object is required.
ARRAY class methods
The oracle.sql.ARRAY class offers the following methods:
getDescriptorThis method returns an
ArrayDescriptorobject that describes the array type.
getArrayThis method retrieves the content of an array of the default JDBC type. If it retrieves an array of objects,
getArraydetermines the type by using the default type mapping of database connection objects.getOracleArrayThis method is the same as
getArray, except that it retrieves elements in theoracle.sql*format.
getBaseTypeThis method returns the SQL type code of the array elements.
getBaseTypeNameThis method returns the SQL type name of the array elements.
getSQLTypeNameThis method returns the standard SQL type name for the entire array. This method is an Oracle extension.
getResultSetThis method materializes array elements into a result set.
getJavaSQLConnectionThis method returns the connection instance associated with this array.
lengthThis method returns the number of elements in the array.
Examples of using the ARRAY and STRUCT classes
@BeforeClass()
public static void initClass() throws SQLException {
createTable(tablenameArray, "c1 int primary key,c2 interval day(6) to second(5)");
createTable(tablenameStruct, " c1 int primary key,c2 interval year(4) to month");
createTable(array1, "c1 int");
createTable(array2, "c1 char(100)");
createTable(array3, "c1 date");
createTable(struct1, "c1 int");
createTable(struct2, "c1 varchar(100)");
createTable(struct3, "c1 date");
createTable(raw1, "c1 int ,c2 raw(100)");
createTable(refcursor1, "c1 varchar(20), c2 number");
}
public void showArrayRes(Array array) throws Exception {
ResultSet arrayRes = array.getResultSet();
while (arrayRes.next()) {
int index = arrayRes.getInt(1);
Struct struct = (Struct) arrayRes.getObject(2);
Object[] objArr = struct.getAttributes();
for (Object obj : objArr) {
System.out.printf("index j %d obj now is %s\n", index, obj);
}
}
}
public void executeSqls(String[] sqls, Connection conn) {
PreparedStatement ps;
for (String sql : sqls) {
try {
ps = conn.prepareStatement(sql);
ps.execute();
ps.close();
} catch (Exception e) {
System.out.println("sql:" + sql);
// ignore, maybe table does not exist
e.printStackTrace();
}
}
}
// Example 1
public void basicArrayAndStructTest() throws SQLException {
Assume.assumeTrue(sharedUsePrepare());
Connection conn = null;
try {
conn = sharedPSConnection;
PreparedStatement ps = null;
Statement stmt = conn.createStatement();
stmt.execute("CREATE OR REPLACE TYPE my_obj as object (c1 int, c3 date)");
stmt.execute("CREATE OR REPLACE TYPE obj_array IS TABLE OF my_obj");
// test input
String createPlSql = "CREATE OR REPLACE PROCEDURE my_proc_objarr(X IN obj_array) IS "
+ "BEGIN " + " FOR idx IN 1..X.count LOOP " + " INSERT INTO "
+ array1 + " VALUES(X(idx).c1);"
+ " END LOOP; "
// + " FOR idx IN 1..X.count LOOP" + " INSERT INTO " + array2
// + " VALUES(X(idx).c2);" + " END LOOP; "
+ " FOR idx IN 1..X.count LOOP" + " INSERT INTO " + array3
+ " VALUES(X(idx).c3);" + " END LOOP; " + "END;";
stmt.execute(createPlSql);
Integer[] intArray = new Integer[10];
for (int i = 0; i < 10; ++i) {
if (i % 2 == 0) {
intArray[i] = null;
} else {
intArray[i] = i;
}
}
// String[] strArray = new String[10];
// for (int i = 0; i < 10; ++i) {
// if (i % 2 == 0) {
// strArray[i] = "luyun_" + i;
// } else {
// strArray[i] = null;
// }
// }
java.sql.Timestamp[] dateArray = new Timestamp[10];
for (int i = 0; i < 10; ++i) {
if (i % 2 == 0) {
dateArray[i] = Timestamp.valueOf("2019-06-04 10:29:11.123456");
} else {
dateArray[i] = null;
}
}
Object[] structArray = new Object[10];
for (int i = 0; i < 10; ++i) {
structArray[i] = conn.createStruct("my_obj", new Object[] { intArray[i],
dateArray[i] });
// strArray[i], dateArray[i] });
}
Array array = conn.createArrayOf("my_obj", structArray);
// ((ArrayImpl)array).getComplexType().setTypeName("obj_array");
{
System.out.println("=======prepareStatement input test =============");
ps = conn.prepareStatement("call my_proc_objarr(?)");
ps.setArray(1, array);
ps.execute();
ps.close();
ps = conn.prepareStatement("select * from " + array1);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("c1"));
}
ps.close();
// ps = conn.prepareStatement("select * from " + array2);
// rs = ps.executeQuery();
// while (rs.next()) {
// System.out.println(rs.getString("c1"));
// }
// ps.close();
ps = conn.prepareStatement("select * from " + array3);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getDate("c1"));
}
ps.close();
}
{
System.out.println("=======callable Statement input test =============");
ps = conn.prepareCall("call my_proc_objarr(?)");
ps.setArray(1, array);
ps.execute();
ps.close();
ps = conn.prepareStatement("select * from " + array1);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getInt("c1"));
}
ps.close();
// ps = conn.prepareStatement("select * from " + array2);
// rs = ps.executeQuery();
// while (rs.next()) {
// System.out.println(rs.getString("c1"));
// }
// ps.close();
ps = conn.prepareStatement("select * from " + array3);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getDate("c1"));
}
ps.close();
}
// test output
createPlSql = "CREATE OR REPLACE PROCEDURE my_proc_obj_out(x OUT obj_array) IS"
+ " i int :=1;" + " BEGIN" + " x.extend(10);" + " for idx in 1..5 loop"
// + " x(i).c1 := idx;" + " x(i).c2 := idx;"
+ " x(i).c1 := idx;" + " x(i).c3 := date '1970-01-01';"
+ " x(i + 1).c1 := null;"
// + " x(i + 1).c2 := null;" + " x(i + 1).c3 := null;"
+ " x(i + 1).c3 := null;" + " i := i + 2;" + " end loop;" + "END;";
{
stmt.execute(createPlSql);
ps = conn.prepareStatement("call my_proc_obj_out(?)");
ps.setArray(1, null);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
Array resArray = rs.getArray(1);
showArrayRes(resArray);
}
}
// callable test
{
System.out.println("=========callable stmt test==========");
CallableStatement csmt = conn.prepareCall("{call my_proc_obj_out(?)}");
csmt.registerOutParameter(1, Types.ARRAY, "obj_array");
csmt.execute();
Array resArray = csmt.getArray(1);
showArrayRes(resArray);
}
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
// Example 2
public void basicRawTest() throws SQLException {
Connection conn = null;
try {
conn = sharedPSConnection;
PreparedStatement ps = null;
ps = conn.prepareStatement("insert into " + raw1 + " values(?,?)");
ps.setInt(1, 11);
ps.setBytes(2, new byte[] { 1, 2, 3 });
ps.execute();
ps = conn.prepareStatement("select c1 ,c2 from " + raw1 + " where c1 = 11");
ResultSet rs = ps.executeQuery();
// Assert.assertEquals("[B", rs.getMetaData().getColumnClassName(2));
Assert.assertEquals("RAW", rs.getMetaData().getColumnTypeName(2));
Assert.assertEquals(-3, rs.getMetaData().getColumnType(2));
Assert.assertEquals(2, rs.getMetaData().getColumnCount());
Assert.assertEquals("C2", rs.getMetaData().getColumnLabel(2));
Assert.assertEquals(100, rs.getMetaData().getColumnDisplaySize(2));
Assert.assertEquals("UNITTESTS", rs.getMetaData().getCatalogName(2));
Assert.assertEquals("C2", rs.getMetaData().getColumnName(2));
Assert.assertEquals(0, rs.getMetaData().getScale(2));
Assert.assertEquals("TEST_RAW1", rs.getMetaData().getTableName(2));
// assertFalse(rs.getMetaData().isCaseSensitive(2));
assertTrue(rs.next());
Assert.assertEquals(11, rs.getInt(1));
Assert.assertTrue(Arrays.equals(new byte[] { 1, 2, 3 }, rs.getBytes(2)));
Assert.assertEquals("010203", rs.getString(2));
Assert.assertEquals(3, rs.getBinaryStream(2).available());
rs.close();
ps.close();
} catch (Exception e) {
e.printStackTrace();
fail();
}
}
// Example 3
public void testRefCursor() {
for (int loop = 0; loop < 2; loop++) {
Connection conn = sharedPSConnection;
{
{
String sql = "insert into " + refcursor1 + " values(?, ?)";
for (int i = 0; i < 10; i++) {
try {
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(1, "test" + i);
statement.setInt(2, i);
statement.execute();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
try {
String sql = "select * from " + refcursor1;
PreparedStatement statement = conn.prepareStatement(sql);
statement.execute();
ResultSet resultSet = statement.getResultSet();
while (resultSet.next()) {
int columnCnt = resultSet.getMetaData().getColumnCount();
for (int j = 1; j <= columnCnt; j++) {
System.out.println(resultSet.getMetaData().getColumnName(j) + ":"
+ resultSet.getString(j));
}
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
Statement statement = conn.createStatement();
String createPlSql = "CREATE OR REPLACE PROCEDURE test_cursor(p_cursor OUT sys_refcursor) "
+ "is BEGIN "
+ " open p_cursor for select * from "
+ refcursor1 + ";" + "end;";
statement.execute(createPlSql);
CallableStatement csmt = conn.prepareCall("call test_cursor(?)",
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
csmt.setFetchSize(10);
csmt.registerOutParameter(1, Types.REF);
csmt.execute();
ResultSet resultSet = (ResultSet) csmt.getObject(1);
resultSet.setFetchSize(2);
System.out.println("=========refcursor output==========");
while (resultSet.next()) {
int columnCnt = resultSet.getMetaData().getColumnCount();
for (int j = 1; j <= columnCnt; j++) {
System.out.println(resultSet.getMetaData().getColumnName(j) + ":"
+ resultSet.getString(j));
}
}
resultSet.close();
resultSet = (ResultSet) csmt.getObject(1);
System.out.println("=========refcursor output==========");
try {
while (resultSet.next()) {
int columnCnt = resultSet.getMetaData().getColumnCount();
for (int j = 1; j <= columnCnt; j++) {
System.out.println(resultSet.getMetaData().getColumnName(j) + ":"
+ resultSet.getString(j));
}
}
} catch (SQLException e) {
Assert.assertEquals(e.getErrorCode(), 600);
}
} catch (SQLException e) {
e.printStackTrace();
Assert.assertEquals(e.getMessage(), "cursor is not open");
}
}
}
}