Informix JDBC Driver Programmer's Guide Manipulating Informix Data Types

Sun's JDBC 2.0 specification describes only one method to exchange collection data between a Java client and a relational database: an array.

Because the array interface does not include a constructor, Informix JDBC Driver includes an extension that allows a java.util.Collection object to be used in the PreparedStatement.setObject() and ResultSet.getObject() methods. If you prefer to use an Array object, use the PreparedStatement.setArray() and ResultSet.getArray() methods.

By default, the driver maps LIST columns to java.util.ArrayList objects and SET and MULTISET columns to java.util.HashSet objects during a fetch. You can override these defaults, but the class you use must implement the java.util.Collection interface.

To override this default mapping, you can use other classes in the java.util.Collection interface, such as the TreeSet class. You can also create your own classes that implement the java.util.Collection interface. In either case, you must provide a customized type map using the Connection.setTypeMap() method.

During an INSERT operation, any java.util.Collection object that is an instance of the java.util.Set interface is mapped to an Informix MULTISET data type. An instance of the java.util.List interface is mapped to an Informix LIST data type. You can override these defaults by creating a customized type mapping.

For information about customized type mappings, see Mapping Data Types.

Important: Sets are by definition unordered. If you select collection data using a HashSet object, the order of the elements in the HashSet object might not be the same as the order specified when the set was inserted. For example, if the data on the database server is the set {1, 2, 3}, it might be retrieved into the HashSet object as {3, 2, 1} or any other order.

Collection Examples

Here is the database schema:

create table tab ( a set(integer not null), b integer);

insert into tab values ("set{1, 2, 3}", 10);

Here is a fetch example using a java.util.HashSet object. The complete demonstration is in the demo5.java file in the complex-types directory. For more information, see Appendix A, Sample Code Files.

java.util.HashSet set;

PreparedStatement pstmt;

ResultSet rs;

pstmt = conn.prepareStatement("select * from tab");

System.out.println("prepare ... ok");

rs = pstmt.executeQuery();

System.out.println("executeQuery ... ok");

rs.next();

set = (HashSet) rs.getObject(1);

System.out.println("getObject() ... ok");

/* The user can now use HashSet.iterator() to extract

* each element in the collection.

*/

Iterator it = set.iterator();

Object obj;

Class cls = null;

int i = 0;

while (it.hasNext())

{

obj = it.next();

if (cls == null)

{

cls = obj.getClass();

System.out.println(" Collection class: " + cls.getName());

}

System.out.println(" element[" + i + "] = " +

obj.toString());

i++;

}

pstmt.close();

In the set = (HashSet) rs.getObject(1) statement of this example, Informix JDBC Driver gets the type for column 1. Because it is a SET type, a HashSet object is instantiated. Next, each collection element is converted into a Java object and inserted into the collection.

Here is a fetch example using a java.util.TreeSet object. The complete demonstration is in the demo6.java file in the complex-types directory. For more information, see Appendix A, Sample Code Files.

java.util.TreeSet set;

PreparedStatement pstmt;

ResultSet rs;

/*

* Fetch a SET as a TreeSet instead of the default

* HashSet. In this example a new java.util.Map object has

* been allocated and passed in as a parameter to getObject().

* Connection.getTypeMap() could have been used as well.

*/

java.util.Map map = new HashMap();

map.put("set", Class.forName("java.util.TreeSet"));

System.out.println("mapping ... ok");

pstmt = conn.prepareStatement("select * from tab");

System.out.println("prepare ... ok");

rs = pstmt.executeQuery();

System.out.println("executeQuery ... ok");

rs.next();

set = (TreeSet) rs.getObject(1, map);

System.out.println("getObject(Map) ... ok");

/* The user can now use HashSet.iterator() to extract

* each element in the collection.

*/

Iterator it = set.iterator();

Object obj;

Class cls = null;

int i = 0;

while (it.hasNext())

{

obj = it.next();

if (cls == null)

{

cls = obj.getClass();

System.out.println(" Collection class: " + cls.getName());

}

System.out.println(" element[" + i + "] = " +

obj.toString());

i++;

}

pstmt.close();

In the map.put("set", Class.forName( "java.util.TreeSet" )); statement, the default mapping of set = HashSet is overridden.

In the set = (TreeSet) rs.getObject(1, map) statement, Informix JDBC Driver gets the type for column 1 and finds that it is a SET object. Then the driver looks up the type mapping information, finds TreeSet, and instantiates a TreeSet object. Next, each collection element is converted into a Java object and inserted into the collection.

Here is an example of an insert. The complete demonstration is in the demo7.java file in the complex-types directory. For more information, see Appendix A, Sample Code Files.

java.util.HashSet set = new HashSet();

Integer intObject;

int i;

/* Populate the Java collection */

for (i=0; i < 5; i++)

{

intObject = new Integer(i);

set.add(intObject);

}

System.out.println("populate java.util.HashSet...ok");

PreparedStatement pstmt = conn.prepareStatement

("insert into tab values (?, 20)");

System.out.println("prepare...ok");

pstmt.setObject(1, set);

System.out.println("setObject()...ok");

pstmt.executeUpdate();

System.out.println("executeUpdate()...ok");

pstmt.close();

The pstmt.setObject(1, set) method in this example first serializes each element of the collection. Next, the type information is constructed as each element is converted into a Java object. If the types of any elements in the collection do not match the type of the first element, an exception is thrown. The type information is sent to the database server.

Here is the database schema:

create table tab ( a set(integer not null), b integer);

insert into tab values ("set{1, 2, 3}", 10);

Here is a fetch example using a java.sql.Array object. The complete demonstration is in the demo8.java file in the complex-types directory. For more information, see Appendix A, Sample Code Files.

PreparedStatement pstmt = conn.prepareStatement("select a from tab");

System.out.println("prepare ... ok");

ResultSet rs = pstmt.executeQuery();

System.out.println("executeQuery ... ok");

rs.next();

java.sql.Array array = rs.getArray(1);

System.out.println("getArray() ... ok");

pstmt.close();

/*

* The user can now materialize the data into either

* an array or else a ResultSet. If the collection elements

* are primitives then the array should be an array of primitives,

* not Objects. Mapping data can be provided at this point.

*/

Object obj = array.getArray((long) 1, 2);

int [] intArray = (int []) obj; // cast it to an array of ints

int i;

for (i=0; i < intArray.length; i++)

{

System.out.println("integer element = " + intArray[i]);

}

pstmt.close();

The java.sql.Array array = rs.getArray(1) statement instantiates a java.sql.Array object. Data is not converted at this point.

The Object obj = array.getArray((long) 1, 2); statement converts data into an array of integers (primitive int types, not Integer objects). Because the getArray() method has been called with index and count values, only a subset of data is returned.