Row数据类型，由一个或多个任意的数据类型组成。
例如，创建一个r1_t的Row数据类型，具有integer类型的i和boolean类型的b字段，参考语句如下：
CREATE ROW TYPE r1_t(i int,b boolean);
本文章详细介绍如何通过GBase8s驱动实现Row类型数据的插入与查询。
1、 创建基础数据
首先，需要创建r1_t 用户自定义row类型，下列示例展示一个定义 row类型的 SQL 语句以及创建包含row类型的主表：
create row type r1_t (i int, b boolean)；//创建r1_t自定义类型
create table row_tab (int_col int, row_col r1_t)；//创建row_tab表，其中包含r1_t列类型。
其次，使用jdbc驱动实现r1_t类型数据的插入与检索，还需将r1_t数据类型映射为r1_t Java数据类型，下列代码将row类型(r1_t类型)映射至名为Java对象(r1_t对象)，java实体类定义如下：
public class r1_t implements SQLData {//实现SQLData接口
int int_col; //自定义列
boolean bool_col; //自定义列
String sqlType = "r1_t"; //指定sql类型为r1_t
public r1_t() {
}
/*
* This constructor isn't required by the java.sql.SQLData
* interface
*/
public r1_t(int i, boolean b) {
int_col = i;
bool_col = b;
}
public String getSQLTypeName() throws SQLException {
return (sqlType);
}
public void readSQL(SQLInput stream, String typeName) throws SQLException {
int_col = stream.readInt();
bool_col = stream.readBoolean();
}
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeInt(int_col);
stream.writeBoolean(bool_col);
}
/*
* This constructor isn't required by the java.sql.SQLData
* interface
*/
public String toString() {
String str = "int_col: " + int_col + " bool_col: " + bool_col;
return (str);
}
}
2、 通过jdbc插入r1_t类型数据
定义完r1_t java映射类后，通过以下代码实现用户自定义类型r1_t类型数据的插入：
PreparedStatement pstmt = null;
try {
String s = "insert into row_tab (int_col, row_col) values (?, ?)";
System.out.println(s);
pstmt = conn.prepareStatement(s);
System.out.println("prepare...ok");
} catch (SQLException e) {
System.out.println("***FAILED: " + e.getErrorCode() + " " + e.getMessage());
e.printStackTrace();
return;
}
System.out.println();
// ------------------
// Setting up typemap
// ------------------
try {
System.out.println("Setting up typemap :-");
java.util.Map customtypemap = conn.getTypeMap();
System.out.println("getTypeMap...ok");
if (customtypemap == null) {
System.out.println("\n***FAILED: typemap is null!");
return;
}
customtypemap.put("r1_t", Class.forName("r1_t"));
} catch (ClassNotFoundException e) {
System.out.println("\n***FAILED: " + e);
} catch (SQLException e) {
System.out.println("\n***FAILED: " + e.getErrorCode() + " " + e.getMessage());
e.printStackTrace();
return;
}
System.out.println();
try {
// Insert (i) -
// Insert into list column using SQLData object
// Should succeed.
System.out.println("Insert (i)");
r1_t row = new r1_t(99, true);
System.out.println("Inserting row(99, true)");
pstmt.setInt(1, 1);
pstmt.setObject(2, row);
pstmt.executeUpdate();
System.out.println("execute...ok");
row = new r1_t(-99, false);
System.out.println("inserting row(-99, false)");
pstmt.setInt(1, 2);
pstmt.setObject(2, row);
pstmt.executeUpdate();
System.out.println("execute...ok");
pstmt.close();
} catch (SQLException e) {
System.out.println("***FAILED: " + e.getErrorCode() + " " + e.getMessage());
e.printStackTrace();
}
System.out.println();
需要注意的是通过jdbc端，需要注册自定义类型映射关系，具体来说 通过如下代码实现类型注册：
java.util.Map customtypemap = conn.getTypeMap();
customtypemap.put("r1_t", Class.forName("r1_t"));
注册完成后，可以正常使用改类型数据。
3、 通过jdbc检索数据示例
通过以下代码实现用户自定义类型r1_t类型数据的检索：
System.out.println("Select (i)");
String s = "select int_col, row_col from row_tab order by int_col";
System.out.println(s);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(s);
System.out.println("execute...ok");
System.out.println("Fetching data ...");
int curRow = 0;
r1_t row;
while (rs.next()) {
curRow++;
System.out.println("currentrow=" + curRow + " : ");
int intret = rs.getInt("int_col");
System.out.println("\tint_col " + intret);
row = (r1_t) rs.getObject("row_col");
System.out.println("\trow_col");
if (rs.wasNull())
System.out.println("<null>");
else
printData((Object) row);
} //while
System.out.println("total rows expected: " + curRow);
if (curRow != 2) {
System.out.println("FAILED: total rows returned: " + curRow + " total rows expected 2");
}
stmt.close();
