GBase 8s provides support for the ROW type, a composite data type that can contain multiple fields, each with its own data type. ROW types can be used to define table columns, program variables, statement variables, and routine return values, making it more flexible and intuitive to handle nested data structures in JDBC.
Defining ROW Types
The following statements define four ROW types. Note that person_t nests fullname_t, demonstrating multi-level composite structures:
CREATE ROW TYPE r1_t (i INT, b BOOLEAN);
CREATE ROW TYPE r2_t (f FLOAT, s MULTISET(INT8 NOT NULL), i INT);
CREATE ROW TYPE fullname_t (first CHAR(20), last CHAR(20));
CREATE ROW TYPE person_t (id INT, name fullname_t, age INT);
SQLData Implementation Classes
Use the SQLData interface to map Java objects to ROW types. Regular fields are read and written with methods like readInt and writeDouble; nested ROW types use readObject/writeObject; collection types such as MULTISET require IfmxComplexSQLInput/IfmxComplexSQLOutput.
r1_t Implementation
public class r1_t implements SQLData {
int int_col;
boolean bool_col;
public String toString() {
String str = "int_col: " + int_col + " bool_col: " + bool_col;
return (str);
}
}
r2_t Implementation
public class r2_t implements SQLData {
double dbl_col;
int int_col;
Collection mset_col;
public void readSQL(SQLInput stream, String typeName) throws SQLException {
dbl_col = stream.readDouble();
mset_col = (Collection) ((IfmxComplexSQLInput) stream).readObject();
int_col = stream.readInt();
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeDouble(dbl_col);
((IfmxComplexSQLOutput) stream).writeObject(mset_col);
stream.writeInt(int_col);
}
public String toString() {
String str = "dbl_col: " + dbl_col + "\n";
str += " int_col: " + int_col + "\n";
str += " mset_col: \n";
Iterator it = mset_col.iterator();
while (it.hasNext()) {
str += "\t\t\telement: " + it.next() + "\n";
}
return (str);
}
}
fullname Implementation
public class fullname implements SQLData {
public String first;
public String last;
}
person Implementation
public class person implements SQLData {
public int id;
public fullname name;
public int age;
public void readSQL(SQLInput stream, String type) throws SQLException {
id = stream.readInt();
name = (fullname) stream.readObject();
age = stream.readInt();
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeInt(id);
stream.writeObject(name);
stream.writeInt(age);
}
public String toString() {
String s = "person:";
s += "id: " + id + "\n";
s += " name: " + name.toString() + "\n";
s += " age: " + age + "\n";
return s;
}
}
Seven Practical Examples
Example 1: Fetching a Named ROW into a SQLData Object
Create the teachers table, insert a row, then use a custom TypeMap to map the person column to a Java object.
stmt.executeUpdate("create table teachers (person person_t, dept char (20))");
stmt.executeUpdate("insert into teachers values ('row(100, row(Bill, Smith), 27)', 'physics')");
java.util.Map map = conn.getTypeMap();
map.put("fullname_t", Class.forName("com.jdbc.demo.fullname"));
map.put("person_t", Class.forName("com.jdbc.demo.person"));
PreparedStatement pstmt = conn.prepareStatement("select person from teachers");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
person who = (person) rs.getObject(1);
System.out.println("row: " + who.toString());
}
Output:
row: person:id: 100
name: fullname: first: Bill last: Smith
age: 27
Example 2: Inserting a SQLData Object into a Named ROW Column
Build a person object and insert it with setObject. When querying, cast the ROW to lvarchar for display.
stmt.executeUpdate("create table teachers (person person_t, dept char (20))");
java.util.Map map = conn.getTypeMap();
map.put("fullname_t", Class.forName("com.jdbc.demo.fullname"));
map.put("person_t", Class.forName("com.jdbc.demo.person"));
person who = new person();
fullname name = new fullname();
name.last = "Jones";
name.first = "Sarah";
who.id = 567;
who.name = name;
who.age = 17;
String s = "insert into teachers values (?, 'physics')";
PreparedStatement pstmt = conn.prepareStatement(s);
pstmt.setObject(1, who);
pstmt.executeUpdate();
pstmt = conn.prepareStatement("select person::lvarchar from teachers");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println("lvarchar: " + rs.getString(1));
}
Output:
lvarchar: ROW(567 ,ROW('Sarah ','Jones '),17 )
Example 3: Fetching an Unnamed SQLROW Column into a Struct Object
For unnamed ROW columns, use java.sql.Struct. Retrieve nested fields with getAttributes().
stmt.executeUpdate("create table teachers (person row(id int, name row(first char(20), last char(20)), age int), dept char (20))");
stmt.executeUpdate("insert into teachers values ('row(100, row(Bill, Smith), 27)', 'physics')");
PreparedStatement pstmt = conn.prepareStatement("select person from teachers");
ResultSet rs = pstmt.executeQuery();
rs.next();
Struct person = (Struct) rs.getObject(1);
System.out.println("person row description: " + person.getSQLTypeName());
Object[] elements = person.getAttributes();
Integer id = (Integer) elements[0];
Struct name = (Struct) elements[1];
Integer age = (Integer) elements[2];
System.out.println("person.id: " + id);
System.out.println("person.age: " + age);
System.out.println("name row description: " + name.getSQLTypeName());
Object[] nameAttrs = name.getAttributes();
String first = (String) nameAttrs[0];
String last = (String) nameAttrs[1];
System.out.println("name.first: " + first);
System.out.println("name.last: " + last);
Output:
person row description: row ( id int , name row ( first char(20) , last char(20) ) , age int )
person.id: 100
person.age: 27
name row description: name row ( first char(20) , last char(20) )
name.first: Bill
name.last: Smith
Example 4: Inserting a Struct Object into a Named ROW
Implement a custom GenericStruct class satisfying java.sql.Struct, build attributes, and insert with setObject.
public class GenericStruct implements java.sql.Struct {
private Object[] attributes = null;
private String typeName = null;
GenericStruct() {}
GenericStruct(String name, Object[] obj) {
typeName = name;
attributes = obj;
}
public String getSQLTypeName() { return typeName; }
public Object[] getAttributes() { return attributes; }
public Object[] getAttributes(Map map) throws SQLException { return attributes; }
public void setAttributes(Object[] objArray) { attributes = objArray; }
public void setSQLTypeName(String name) { typeName = name; }
}
stmt.executeUpdate("create table teachers (person person_t, dept char (20))");
PreparedStatement pstmt = conn.prepareStatement("insert into teachers values (?, 'Math')");
Object[] name = new Object[2];
name[0] = new String("Jane");
name[1] = new String("Smith");
String rowType = "row(first char(20), last char(20))";
GenericStruct gs = new GenericStruct(rowType, name);
Object[] person = new Object[3];
person[0] = new Integer(99);
person[1] = gs;
person[2] = new Integer(56);
rowType = "row(id int, name row(first char(20), last char(20)), age int)";
gs = new GenericStruct(rowType, person);
pstmt.setObject(1, gs);
pstmt.executeUpdate();
pstmt = conn.prepareStatement("select person::lvarchar from teachers");
ResultSet rs = pstmt.executeQuery();
while (rs.next()) {
System.out.println("lvarchar: " + rs.getString(1));
}
Output:
lvarchar: ROW(99 ,ROW('Jane ','Smith '),56 )
Example 5: Reading and Writing a Simple ROW Type (r1_t)
Use a custom TypeMap to map r1_t. Demonstrates reading data both as SQLData and as Struct, then re-inserting using the retrieved Struct.
stmt.executeUpdate("create table row_tab (int_col int, row_col r1_t)");
// Insert an SQLData object
PreparedStatement pstmt = conn.prepareStatement("insert into row_tab (int_col, row_col) values (?, ?)");
java.util.Map customtypemap = conn.getTypeMap();
customtypemap.put("r1_t", Class.forName("com.jdbc.demo.r1_t"));
r1_t row = new r1_t(99, true);
pstmt.setInt(1, 1);
pstmt.setObject(2, row);
pstmt.executeUpdate();
// Read as SQLData
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select int_col, row_col from row_tab order by int_col");
rs.next();
r1_t rowData = (r1_t) rs.getObject("row_col");
System.out.println(rowData); // int_col: 99 bool_col: true
// Read as Struct
rs = stmt.executeQuery("select int_col, row_col from row_tab order by int_col");
rs.next();
Struct rowStruct = (Struct) rs.getObject("row_col", null);
Object[] objArray = rowStruct.getAttributes();
for (int i = 0; i < objArray.length; i++) {
System.out.println("\t\tfield: " + objArray[i]);
}
// Re-insert using the retrieved Struct objects (table truncation omitted for brevity)
pstmt = conn.prepareStatement("insert into row_tab values (?, ?)");
for (int i = 0; i < objVector.size(); i++) {
pstmt.setInt(1, i);
pstmt.setObject(2, objVector.get(i));
pstmt.executeUpdate();
}
rs = stmt.executeQuery("select int_col, row_col::lvarchar from row_tab order by int_col");
rs.next();
System.out.println(rs.getString(2)); // ROW(99 ,'t')
Sample output:
int_col: 99 bool_col: true
field: 99
field: true
row_col ROW(99 ,'t')
Example 6: Reading and Writing a ROW with MULTISET (r2_t)
r2_t includes a MULTISET(INT8 NOT NULL) field. The example builds a TreeSet, inserts the row, and queries it back.
stmt.executeUpdate("create table row_tab (int_col int, row_col r2_t)");
java.util.Map customtypemap = conn.getTypeMap();
customtypemap.put("r2_t", Class.forName("r2_t"));
String s = "insert into row_tab (int_col, row_col) values (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(s);
TreeSet set = new TreeSet();
set.add(new Long(1234567890));
set.add(new Long(-1234567890));
set.add(new Long(5));
r2_t row = new r2_t(1.67e30, set, 99);
pstmt.setInt(1, 1);
pstmt.setObject(2, row);
pstmt.executeUpdate();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select int_col, row_col from row_tab order by int_col");
rs.next();
r2_t rowData = (r2_t) rs.getObject("row_col");
System.out.println(rowData);
Output:
dbl_col: 1.67E30
int_col: 99
mset_col:
element: -1234567890
element: 5
element: 1234567890
Example 7: Reading and Writing an Unnamed ROW with LIST
For unnamed ROWs containing a LIST field, use GenericStruct in the same way. The ArrayList is passed and automatically parsed on retrieval.
stmt.executeUpdate("create table row_tab (int_col int, row_col row(f float, l list(lvarchar not null), i int))");
String s = "insert into row_tab (int_col, row_col) values (?, ?)";
PreparedStatement pstmt = conn.prepareStatement(s);
ArrayList list = new ArrayList();
list.add(new String("John Smith"));
list.add(new String("Granny Apple"));
list.add(new String("Barry White"));
Object[] objArray = new Object[3];
objArray[0] = new Double(1.67e30);
objArray[1] = list;
objArray[2] = new Integer(99);
GenericStruct struct = new GenericStruct("row(f float, l list(lvarchar not null), i int)", objArray);
pstmt.setInt(1, 1);
pstmt.setObject(2, struct);
pstmt.executeUpdate();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select int_col, row_col from row_tab order by int_col");
rs.next();
Struct row = (Struct) rs.getObject("row_col");
System.out.println("Class: " + row.getClass().getName());
System.out.println("SQLTypeName: " + row.getSQLTypeName());
Object[] attrs = row.getAttributes();
for (int i = 0; i < attrs.length; i++) {
System.out.println("field: " + attrs[i]);
}
Output:
Class: com.gbasedbt.jdbc.IfxStruct
SQLTypeName: row ( f float , l list ( lvarchar not null) , i int )
field: -1.67E30
field: [Roger Jones, Jeff Brown]
field: -99
Through this series of examples, you can systematically master reading and writing ROW types in the GBase 8s JDBC environment, enabling efficient interaction between Java objects and nested database structures in a gbase database.