Returning values from Nested Tables using JDBC
Posted by Steve Racanovic | Posted in JDBC | Posted on 1:25 PM
0
When running a select statement in sqlplus on a nested tables we get the results returned. However the same can not be said when running the same statement through JDBC. He is an example of returning values from a nested table with JDBC.
1. Here's how the sql script looks like to create the type and tables.
drop table person;
drop type nested_addresses;
drop type address;
create or replace type address as object(
street varchar2(50),
city varchar2(20),
postcode varchar2(8),
state varchar(8))
/
create or replace type nested_addresses as table of address
/
create table person (
firstname varchar(20),
lastname varchar(20),
address_list nested_addresses
) nested table address_list store as person_addresses
/
insert into person values ('Fred','Flinstone', nested_addresses(address('345 Cave Stone Road', 'Bedrock', 'BR345','BR'),address('1313 Cobblestone Way', 'Bedville', 'BV1313','BV')))
/
commit
/
2. If I now run the select statement we can see the results:
scott@orcl> set head on feedback on
scott@orcl> set pages 999
scott@orcl> set linesize 120
scott@orcl> col firsname format a20
scott@orcl> col lastname format a20
scott@orcl> col address_list format a50
scott@orcl> select firstname, lastname, address_list from person;
FIRSTNAME LASTNAME ADDRESS_LIST(STREET, CITY, POSTCODE, STATE)
-------------------- -------------------- --------------------------------------------------
Fred Flinstone NESTED_ADDRESSES(ADDRESS('345 Cave Stone Road', 'B
edrock', 'BR345', 'BR'), ADDRESS('1313 Cobblestone
Way', 'Bedville', 'BV1313', 'BV'))
1 row selected.
3. However if I now run the same select query, through a java client using JDBC we will get null values returned.
import java.sql.*;
public class Person {
public static void main(String[] args) throws SQLException, ClassNotFoundException {
// connection parameters
String dbUrl = "jdbc:oracle:thin:@sracanov-au2.au.oracle.com:1522:orcl";
String dbUser = "scott";
String dbPass = "tiger";
// load the driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// create connection
Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);
// query the table
Statement s = con.createStatement();
String query = "select firstname, lastname, address_list from person";
ResultSet res = s.executeQuery(query);
// look at the result set
res.next();
System.out.println("First Name: " + res.getString(1));
System.out.println("Last Name: " + res.getString(2));
System.out.println("Address: " + res.getString(3));
// close connection
con.close();
}
}
[sracanov@sracanov-au C]$ java -version
java version "1.6.0_16"
Java(TM) SE Runtime Environment (build 1.6.0_16-b01)
Java HotSpot(TM) Client VM (build 14.2-b01, mixed mode, sharing)
[sracanov@sracanov-au C]$ javac -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person.java
[sracanov@sracanov-au C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person
First Name: Fred
Last Name: Flinstone
Address: null
This is because we need to get a collection of objects and handle them in java by returning arrays elements. The correct way to return this is:
import java.sql.*;
public class Person1 {
public static void main(String[] args) throws SQLException,
ClassNotFoundException {
// connection parameters
String dbUrl = "jdbc:oracle:thin:@sracanov-au2.au.oracle.com:1522:orcl";
String dbUser = "scott";
String dbPass = "tiger";
// load the driver
Class.forName("oracle.jdbc.driver.OracleDriver");
// create connection
Connection con = DriverManager.getConnection(dbUrl, dbUser, dbPass);
// query the table
Statement s = con.createStatement();
String query = "select firstname, lastname, address_list from person";
ResultSet res = s.executeQuery(query);
// look at the result set
while (res.next()) {
int addresscounter=1;
System.out.println("First Name: " + res.getString(1));
System.out.println("Last Name: " + res.getString(2));
Array array = res.getArray(3);
Object[] address_list = (Object[]) array.getArray();
for (int i = 0; i < address_list.length; i++) {
Struct address = (Struct) address_list[i];
Object[] attrib = address.getAttributes();
for (int j = 0; j < attrib.length; j++) {
System.out.println("Address " + addresscounter + ": " + attrib[j]);
}
addresscounter++;
}
}
// close connection
con.close();
}
}
[sracanov@sracanov-au C]$ javac -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person1.java
[sracanov@sracanov-au C]$ java -cp .;"D:\My Contents\My Software\Oracle\JDBC\Drivers\11.2.0.1.0\ojdbc6.jar" Person1
First Name: Fred
Last Name: Flinstone
Address 1: 345 Cave Stone Road
Address 1: Bedrock
Address 1: BR345
Address 1: BR
Address 2: 1313 Cobblestone Way
Address 2: Bedville
Address 2: BV1313
Address 2: BV
http://download.oracle.com/docs/cd/E11882_01/java.112/e10589/oraarr.htm#i1059642