Struct examples

This example fetches an unnamed ROW column. Here is a sample database schema:

CREATE TABLE teachers
   (
   person row(
      id int,
      name row(first char(20), last char(20)),
      age int
      ),
   dept char(20)
   );
INSERT INTO teachers VALUES ("row(100, row('Bill', 'Smith'), 27)", "physics");

This is the rest of the example:

PreparedStatement pstmt;
ResultSet rs;
pstmt = conn.prepareStatement("select person from teachers");
System.out.println("prepare ...ok");
rs = pstmt.executeQuery();
System.out.println("executetQuery()...ok");

rs.next();
Struct person = (Struct) rs.getObject(1);
System.out.println("getObject()...ok");
System.out.println("\nData fetched:");

Integer id;
Struct name;
Integer age;
Object[] elements;

/* Get the row description */
String personRowType = person.getSQLTypeName();
System.out.println("person row description: " + personRowType);
System.out.println("");

/* Convert each element into a Java object */
elements = person.getAttributes();

/* 
 * Run through the array of objects in 'person' getting out each structure 
 * field. Use the class Integer instead of int, because int is not an object.
 */
id = (Integer) elements[0];
name = (Struct) elements[1];
age = (Integer) elements[2];
System.out.println("person.id: " + id);
System.out.println("person.age: " + age);
System.out.println("");

/* Convert 'name' as well. */
/* get the row definition for 'name' */
String nameRowType = name.getSQLTypeName();
System.out.println("name row description: " + nameRowType);

/* Convert each element into a Java object */
elements = name.getAttributes();

/* 
 * run through the array of objects in 'name' getting out each structure 
 * field. 
 */
String first = (String) elements[0];
String last = (String) elements[1];
System.out.println("name.first: " + first);
System.out.println("name.last: " + last);
pstmt.close();

The elements = person.getAttributes(); statement performs the following actions:

• Allocates an array of java.lang.Object objects with the correct number of elements
• Converts each element in the row into a Java™ object

  If the element is an opaque type, you must provide type mapping in the Connection object or pass in a java.util.Map object in the call to the getAttributes() method.

The following example uses a user-created class, GenericStruct, which implements the java.sql.Struct interface. As an alternative, you can use a Struct object returned from the ResultSet.getObject() method instead of the GenericStruct class.

import java.sql.*;
import java.util.*;
public class GenericStruct implements java.sql.Struct 
{
   private Object [] attributes = null;
   private String typeName = null;

   /*
    * Constructor
    */
   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
   {
      // this class shouldn't be used if there are elements
      // that need customized type mapping.
      return attributes;
   }
   public void setAttributes(Object [] objArray)
   {
      attributes = objArray;
   }
   public void setSQLTypeName(String name)
   {
      typeName = name;
   }
} 

The following Java program inserts a ROW column:

PreparedStatement pstmt;
ResultSet rs;
GenericStruct gs;
String rowType;

pstmt = conn.prepareStatement("insert into teachers values (?, 'Math')");
System.out.println("prepare insert...ok\n");

System.out.println("Populate name struct...");
Object[] name = new Object[2];

// populate inner row first
name[0] = new String("Jane");
name[1] = new String("Smith");

rowType = "row(first char(20), last char(20))";
gs = new GenericStruct(rowType, name);
System.out.println("Instantiate GenericStructObject...okay\n");

System.out.println("Populate person struct...");
// populate outer row next
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);
System.out.println("Instantiate GenericStructObject...okay\n");

pstmt.setObject(1, gs);
System.out.println("setObject()...okay");
pstmt.executeUpdate();
System.out.println("executeUpdate()...okay");
pstmt.close();