Collection examples

Following is a sample database schema:

create table tab ( a set(integer not null), b integer);
insert into tab values ("set{1, 2, 3}", 10);

The following is a fetch example using a java.util.HashSet object:

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

The following fetch example uses a java.util.TreeSet object:

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

The following example shows an insert. This example inserts the set (0, 1, 2, 3, 4) into a SET column:

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