Using the Struct Interface

The JDBC documentation does not specify that Struct objects can be parameters to the PreparedStatement.setObject() method. However, IBM Informix JDBC Driver can handle any object passed by the PreparedStatement.setObject() or ResultSet.getObject() method that implements the java.sql.Struct interface.

You must use the Struct interface to access unnamed rows.

You do not need to create your own class to implement the java.sql.Struct interface. However, you must perform a fetch to retrieve the ROW data and type information before you can insert or update the ROW data. IBM Informix JDBC Driver automatically calls the getSQLTypeName() method, which returns the type name for a named row or the row definition for an unnamed row.

If you create your own class to implement the Struct interface, the class you create must implement all the java.sql.Struct methods, including the getSQLTypeName() method. You can choose what the getSQLTypeName() method returns.

Although you must return the row definition for unnamed rows, you can return either the row name or the row definition for named rows. Each has advantages:

• Row definition. The driver does not need to query the database server for the type information. In addition, the row definition returned does not have to match the named row definition exactly, because the database server provides casting, if needed. This is useful if you want to use strings to insert into an opaque type in a row, for example.
• Row name. If a user-defined routine takes a named row as a parameter, the signature has to match, so you must pass in a named row.

  For more information about user-defined routines, see the following manuals: IBM Informix: J/Foundation Developer's Guide (for information specific to Java); IBM Informix: User-Defined Routines and Data Types Developer's Guide and IBM Informix: Guide to SQL Reference (both for general information about user-defined routines); and IBM Informix: Guide to SQL Syntax (for the syntax to create and invoke user-defined routines).

Struct Examples

The complete versions of all of the examples in this section are in the demo/complex-types directory where you installed the driver. For more information, see Appendix A. Sample Code Files.

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 Struct person = (Struct) rs.getObject(1) statement instantiates a Struct object if column 1 is a ROW type and there is no extended data type name (if it is a named row).

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 String personrowType = person.getSQLTypeName(); statement returns the row type information. If this type is a named row, the statement returns the name. Because the type is not a named row, the statement returns the row definition: row(int id, row(first char(20), last char(20)) name, int age).

The example then goes through the same steps for the unnamed row name as it did for the unnamed row person.

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();

At the pstmt.setObject(1, gs) statement in this example, IBM Informix JDBC Driver determines that the information is to be transported from the client to the database server as a ROW column, because the GenericStruct object is an instance of the java.sql.Struct interface.

Each element in the array is serialized, verifying that each element matches the type as defined by the getSQLTypeName() method.