SQLData 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 Sample code files.

The following example includes a Java™ class that implements the java.sql.SQLData interface.

Here is a sample database schema:

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

This is the fullname Java class:

import java.sql.*;
public class fullname implements SQLData 
{ 
   public String first; 
   public String last; 
   private String sql_type = "fullname_t";

   public String getSQLTypeName() 
   { 
      return sql_type; 
   }
   public void readSQL (SQLInput stream, String type) throws 
      SQLException 
   { 
      sql_type = type; 
      first = stream.readString(); 
      last = stream.readString(); 
   }
   public void writeSQL (SQLOutput stream) throws SQLException 
   { 
      stream.writeString(first); 
      stream.writeString(last); 
   }
   /*
     * Function not required by SQLData interface, but makes
     * it easier for displaying results.
     */
   public String toString()
   {
      String s = "fullname: ";
      s += "first: " + first + " last: " + last;
      return s;
   }
}

This is the person Java class:

import java.sql.*;
public class person implements SQLData 
{ 
   public int id; 
    public fullname name; 
    public int age; 
    private String sql_type = "person_t";
   
   public String getSQLTypeName() 
   { 
      return sql_type; 
   }
   public void readSQL (SQLInput stream, String type) throws SQLException 
   { 
      sql_type = type; 
      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;
   }
}

Here is an example of fetching a named row:

java.util.Map map = conn.getTypeMap();
conn.setTypeMap(map);
map.put("fullname_t", Class.forName("fullname")); 
map.put("person_t", Class.forName("person"));

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

rs = pstmt.executeQuery();
System.out.println("executetQuery()...ok");

while (rs.next())
   {
   person who = (person) rs.getObject(1);
   System.out.println("getObject()...ok");
   System.out.println("Data fetched:");
   System.out.println("row: " + who.toString());
   }
pstmt.close();

The conn.getTypeMap() method returns the named row mapping information from the java.util.Map object through the Connection object.

The map.put() method registers the mappings between the nested named row on the database server, fullname_t, and the Java class fullname, and between the named row on the database server, person_t, and the Java class person.

The person who = (person) rs.getObject(1) statement retrieves the named row into the Java object who. HCL OneDB™ JDBC Driver recognizes that this object who is a named row, a distinct type, or an opaque type, because the information sent by the database server has an extended name of person_t.

The driver looks up person_t and finds it is a named row. The driver calls the map.get() method with the key person_t, which returns the person class object. An object of class person is instantiated.

The readSQL() method in the person class calls methods defined in the SQLInput interface to convert each field in the ROW column into a Java object and assign each to a member in the person class.

The following example shows a method for inserting a Java object into a named row column using the setObject() method:

java.util.Map map = conn.getTypeMap();
map.put("fullname_t", Class.forName("fullname")); 
map.put("person_t", Class.forName("person"));

...
PreparedStatement pstmt;
System.out.println("Populate person and fullname objects");
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')";
pstmt = conn.prepareStatement (s);
System.out.println("prepared...ok");

pstmt.setObject(1, who);
System.out.println("setObject()...ok");

int rowcount = pstmt.executeUpdate();
System.out.println("executeUpdate()...ok");
pstmt.close();

The conn.getTypeMap() method returns the named row mapping information from the java.util.Map object through the Connection object.

The map.put() method registers the mappings between the nested named row on the database server, fullname_t, and the Java class fullname and between the named row on the database server, person_t, and the Java class person.

HCL OneDB JDBC Driver recognizes that the object who implements the SQLData interface, so it is either a named row, a distinct type, or an opaque type. HCL OneDB JDBC Driver calls the getSQLTypeName() method for this object (required for classes implementing the SQLData interface), which returns person_t. The driver looks up person_t and finds it is a named row.

The writeSQL() method in the person class calls the corresponding SQLOutput.writeXXX() method for each member in the class, each of which maps to one field in the named row person_t. The writeSQL() method in the class contains calls to the SQLOutput.writeObject(name) and SQLOutput.writeInt(id) methods. Each member of the class person is serialized and written into a stream.