User Defined Types (UTS's) in Pivotal GemFireXD

The CREATE TYPE statement creates a user-defined type (UDT). A UDT is a serializable Java class whose instances are stored in columns. The class must implement the java.io.Serializable interface. In this example below we create a TYPE with just one string property to highlight how it's done and then how we can create a FUNCTION to allow us to insert the TYPE using sqlf command line.

1. Create 2 Java classes as shown below. One is our UDT class while the other is used to create an instance of it and expose it as a FUNCTION

SqlfireString.java

 
package pas.au.apples.sqlfire.types;

public class SqlfireString implements java.io.Serializable
{
 public String value;
 
 public SqlfireString()
 { 
 }
 
 public SqlfireString(String value)
 {
  this.value = value;
 }

 @Override
 public String toString() 
 {
  return value;
 }
}

SqlfireStringFactory.java

  
package pas.au.apples.sqlfire.types;

public class SqlfireStringFactory 
{
 public static SqlfireString newInstance (String s)
 {
  return new SqlfireString(s);
 }
}

2. Create TYPE as shown below

  
CREATE TYPE LARGE_STRING 
EXTERNAL NAME 'pas.au.apples.sqlfire.types.SqlfireString' LANGUAGE JAVA;

3. Create FUNCTION to enable us to use TYPE

  
CREATE FUNCTION udt_large_string(VARCHAR(32672)) RETURNS LARGE_STRING 
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO SQL
EXTERNAL NAME 'pas.au.apples.sqlfire.types.SqlfireStringFactory.newInstance';  

4. Create TABLE using TYPE and insert data

  
create table udt_table (id int, text LARGE_STRING);

insert into udt_table values (1, udt_large_string('pas')); 

Note: When using JDBC you would use a PreparedStatement and setObject method to add the TYPE column as shown below.

  
SqlfireString udtLargeString = new SqlfireString("pas");        
pstmt.setObject(1, udtLargeString);

More Information

http://gemfirexd-05.run.pivotal.io/index.jsp?topic=/com.pivotal.gemfirexd.0.5/reference/language_ref/rrefsqljcreatetype.html