Java DB is a relational database management system that is based on the Java programming language and SQL. This is the Oracle release of the Apache Software Foundation’s open source Derby project. Java DB is included in the Java SE 7 SDK.
A user defined type (UDT) is a Java class whose instances (objects) are stored in database table columns. UDTs are defined as column data type and UDT instances are stored as column values. UDTs can be created and used in a Java DB database.
The following are the contents of this post:
- User Defined Type (UDT)
- Create and use UDT
- Designing UDT – considering the effects of updating UDT on the existing data
- Example Code
- Notes &References
1. User Defined Type (UDT)
A UDT defines a data type in the database. A UDT is a Java class with public access modifier. This class implements the java.io.Serializable interface. The class must be visible on the classpath of the database application (or a tool) referring the class.
The UDT class is used to define a table or view column’s data type – a user defined data type. The UDT data is an instance (a Java object) of the UDT class; and is stored as column data. The UDT can also be referred in stored procedures and functions (in Java DB, these are Java based) as data types.
1.1.UDT‘s Attributes (Characteristics)
- A UDT can have subtypes; and the subtype data can be populated to the main type i.e., a UDT class’s sub class instance can be populated as a UDT value. For example: (a) A Java class Type1 and a UDT defined in database as dbtype1 with it, (b) a Java class Subtype1 is a subclass of Type1, and (c) a dbtype1 table column can also be populated with an instance of Subtype1 (in addition to instances of Type1).
- A UDT cannot be indexed, ordered, or compared; and may not be used with operators, grouped or aggregated in SQL expressions (for example, =, LIKE, DISTINCT, GROUP …).
2. Create and Use a UDT
- 2.1. Create a UDT
- 2.2.Use the UDT
2.1.Create a UDT
Create a Java class and define the UDT in the database.Create a Java class, for example TestType1.java(see code at: 4.1 TestType1.java), to be used as a UDT in the database. Compile the source code.
The SQL CREATE TYPE statement creates a UDT in the database. The syntax is:
CREATE TYPE udtTypeName EXTERNAL NAME javaClassName LANGUAGE JAVA
The command creates a UDT in the default or a specified schema, where:
- udtTypeName is the name identifier for the UDT in the database.
- javaClassName is the fully qualified name of the Java class.
For example, create the UDT in Java DB database using the ij tool (ij is a command line tool included with Java DB. ij is a JDBC tool used to run interactive queries on a Java DB database.):
ij> CONNECT 'jdbc:derby:testDB'; ij> CREATE TYPE type1 EXTERNAL NAME 'TestType1' LANGUAGE JAVA;
In the above example, testDB is an existing database. The UDT with the name type1 is created in the testDB database.
NOTE
The Java class file must be in the classpath to be referred from the ij tool.
2.1.1.Verify, Delete and Update a UDT
The created UDT can be verified using the following SQL command:
ij> SELECT alias, javaclassname FROM SYS.SYSALIASES WHERE aliastype='A';
To remove a UDT from the database use the DROP TYPE SQL command. The following is an example:
ij> DROP TYPE udtTypeName RESTRICT;
In the above example, the udtTypeName is the UDT name as defined in the database.
A UDT cannot be dropped if a database object is using (or referring) the UDT. For example, (a) if a table column is of type UDT, that UDT cannot be dropped, unless until the corresponding table column is dropped, or (b) if a database function is referring a UDT’s class (instance) the UDT cannot be dropped, unless until the function is modified not to refer that UDT class.
To update a UDT with the updated Java code, (re)compile the UDT class. This affects the objects of the UDT type. This may also affect the data stored in UDT objects, depending on the way a UDT is defined and used in an application. See the topic: 3. Designing UDT – considering the effects of updating UDT on the existing data.
2.2.Use the UDT
Create database objects with the UDT and manipulate the UDT data (insert, update, delete and query). The UDT data can be used in the database either with SQL interactively or with JDBC API in a Java program.
2.2.1. Interactive SQL
The following describes creating a database table column of UDT type, inserting data and querying the inserted data.
- (i) Create a database table with UDT as a column type.
For example:
CREATE TABLE test_table1 ( id INT, type1col type1, // column with UDT )
- (ii) Insert data into the table.
A custom built database function is used to insert data into the table column defined with UDT.For details about creating a custom function to insert UDT data into the table column, see 4.2. Example_Fn1 – Function.
The example function Example_Fn1 has a signature Example_Fn1(String input) and returns an instance of TestType1 (where TestType1 is the Java class that represents the UDT).
ij> INSERT INTO test_table1(id, type1col) VALUES(1, Example_Fn1("udt value 1"));The above SQL command inserts a row in the table with the UDT column value of TestType1Java object. The function Example_Fn1 invokes a TestType1 class’s constructor with a String input parameter to build an object; and that object is stored in the table column.
- (iii) Query the inserted data.
A custom function can be used to get the data from the UDT column. In the following example, the overridden Object class’s toString() method of the UDT class TestType1 returns the string value of the stored instance.
ij> SELECT * FROM test_table1; ID | TYPE1COL ------------------------- 1 | udt value 1
2.2.2. Using JDBC API
PreparedStatement and ResultSet interfaces defined in the java.sql package are used to insert and get the database UDT data respectively.
- PreparedStatement‘s setObject() method is used to store the UDT data as an object in the UDT table column. The method setObject(int parameterIndex, Object obj) sets the value of the designated parameter using the given object.
- ResultSet’s getObject() method is used to retrieve the stored UDT data from the UDT table column. The method getObject(int columnIndex) gets the value of the designated column in the current row of this ResultSet object. The returned data is an Object.
The following Java code snippets show the usage:
// insert data into a table
int idValue = 2;
TestType1 obj = new TestType1("udt value 2");
PreparedStatement ps = conn.prepareStatement("INSERT INTO test_table1 VALUES (?, ?)";
ps.setInt(1, idValue); // where 1 is the parameter index
ps.setObject(2, obj); // UDT data
ps.executeUpdate();
...
// retrieve data from a table
PreparedStatement ps = conn.prepareStatement("SELECT * FROM test_table1");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
int idValue = rs.getInt(1);
TestType1 testType1 = (TestType1) rs.getObject(2);
// where 2 is the column index in ResultSet object
...
}
NOTE
- In the above code, conn is the database Connection object.
- The UDT Java class file must be in the classpath to be referred from the JDBC code.
- Designing UDT – considering the effects of updating UDT on the existing data
A UDT is used to store data. This UDT (and data) may change (i.e., evolve) over the lifecycle of an application. It is required to design the UDT to take this into consideration.
In addition, note that the UDT class always implements Serializable interface. The effects of data serialization and various versions of UDT data objects must be considered. In simple cases, just compiling the changed UDT class code may suffice.
Here are two ways a UDT can be designed and used.
- UDT class implements Serializable and the application (with the UDT) uses data conversion applications as the UDT data evolves.
- UDT class implements Externalizable (instead of Serializable) and uses data conversion functionality within the UDT class. An example, with UDT class’s code is shown below.
About Externalizable
A UDT class must implement Serializable interface; and java.io.Externalizable extends Serializable.
When this interfaceis implemented, only the identity of the class (not the state) of an Externalizable instance is written in the serialization stream. It is the responsibility of the class to save and restore the contents (state) of its instances.
There are two methods that must be implemented:
- readExternal(ObjectInput in): The object implements the readExternal() method to restore its contents.
- writeExternal(ObjectOutput out): The object implements the writeExternal() method to save its contents.
3.1 UDT class implements Serializable
UDT class implements Serializable and the application (that uses the UDT) uses data conversion applications as the UDT data evolves – the following are the overview steps:
- UDT implements Serializable.
- Create and use a UDT with an initial version.
- Save (store) the previous version’s data, before updating the UDT with the next version.
- Create next version UDT (update the earlier version).
- Convert the initial version data to the current updated version data.
Note that, in this case, all the previous version data is converted to current (new) version, all at a time.
3.2 UDT class implements Externalizable
UDT class implements Externalizable (instead of Serializable) and uses data conversion functionality within it – the following are the overview steps:
- UDT implements Externalizable.
- Create and use a UDT with an initial version.
- No actions are required before updating the UDT with the next version.
- Create next version UDT (update the earlier version), with the data conversion functionality built within.
In this case, the previous version data is converted to current (new) version whenever data is queried or updated. The version information is included within the UDT class.
The following is an example with details and code for UDT class.
- Create a UDT class, with version 1: Testtype2.java (see code at: 4.3Testtype2.java (version 1))
- Compile
- Create the UDT in the database: type2
- Create a table with a column of UDT: test_table3
- Insert data into the UDT column
- Query the UDT data
- Update the UDT class, with version 2: Testtype2.java(see code at: 4.3 Testtype2.java (version 2))
- Compile
- Insert (version 2) data into the UDT column
- Query the UDT data – both the version 1 and version 2 data
NOTE
The example in this topic does not show details or code of SQL commands and functions usedfor inserting and querying UDT data. These can be similar to the example shown earlier in this post.
4. Example Code
- 4.1. TestType1.java
- 4.2. Example_Fn1 – Function
- 4.3.Testtype2.java (version 1)
- 4.4.Testtype2.java (version 2)
4.1.TestType1.java
public class TestType1
implements java.io.Serializable {
private String value = "DEFAULT";
public TestType1() {
}
public TestType1(String s) {
value = s;
}
@Override
public String toString() {
return value;
}
} // class
4.2.Example_Fn1 – Function
This is the function to insert data into the UDT column, referred from the example in 2.2. Use the UDT.
- (i) Create a Java class, with a public static method with the function’s functionality.
- (ii) Create the function in the database using the CREATE FUNCTION command.
The following are the Java class for the function and the CREATE FUNCTION command. The Java class is compiled and the CREATE FUNCTION command is run using the ij tool interactively.
public class FunctionClass {
public static TestType1 FnMethod1(String s) {
return new TestType1(s);
}
}CREATE FUNCTION Example_Fn1(VARCHAR(25)) RETURNS type1 LANGUAGE JAVA PARAMETER STYLE JAVA NO SQL EXTERNAL NAME 'FunctionClass.FnMethod1';
NOTE
The SQL commands SHOW FUNCTIONS and DROP FUNCTION are used from ijto verify the created function and to remove it from the database respectively.
4.3.Testtype2.java (version 1)
import java.io.*;
public class Testtype2
implements Externalizable {
private static final long serialVersionUID = 1L;
private static final int FIRST_VERSION = 1; // initial version id
private String value = "DEFAULT";
public Testtype2() {
}
public Testtype2(String s) {
value = s;
}
@Override
public void writeExternal(ObjectOutput out)
throws IOException {
// first write the version id
out.writeInt(FIRST_VERSION);
// next write the state
out.writeObject(value);
}
@Override
public void readExternal(ObjectInput in)
throws IOException, ClassNotFoundException {
// read the version id
int version = in.readInt();
if (version < FIRST_VERSION) {
throw new IOException("Corrupt data stream (no such version).");
}
if (version > FIRST_VERSION) {
throw new IOException("Can't deserialize from the future versions.");
}
// read object (state)
value = (String) in.readObject() + "_V" + version;
} // readExternal()
@Override
public String toString() {
return value;
}
} // version 1 class4.4.Testtype2.java (version 2)
import java.io.*;
public class Testtype2
implements Externalizable {
private static final long serialVersionUID = 1L;
private static final int FIRST_VERSION = 1; // initial version id
private static final int NEW_VERSION = 2;
private String value = "DEFAULT";
private double newData;
public Testtype2() {
}
public Testtype2(String s, double i) {
value = s;
newData = i;
}
@Override
public void writeExternal(ObjectOutput out)
throws IOException {
// first write the version id
out.writeInt(NEW_VERSION);
// next write the state
out.writeObject(value);
out.writeDouble(newData);
}
@Override
public void readExternal(ObjectInput in)
throws IOException, ClassNotFoundException {
if (version < FIRST_VERSION) {
throw new IOException("Corrupt data stream (no such version).");
}
if (version > NEW_VERSION) {
throw new IOException("Can't deserialize from the future versions.");
}
// read object
value = (String) in.readObject() + "_V" + version;
// read new version's data
if (version == NEW_VERSION) {
newData = in.readDouble();
}
else { // if FIRST_VERSION
// newData is its default value, 0
}
} // readExternal()
@Override
public String toString() {
return value + ":" + newData;
}
} // version 2 classNOTE
- Externalizable‘sreadExternal()method must read the values in the same sequence and with the same types as were written by writeExternal() method.
- In the above example code theserialVersionUID variable is optional.
5. Notes &References
- An example usage in a Java Swing text editor application: A GUI text editor creates a text document as a java.swing.text.PlainDocument class’s instance. A UDT Java class is created with contents – like the PlainDocument instance, the document name, created date, etc. and is used in the application to store the data.
- Oracle 10g database supports creating and using Java based UDT’s; these are referred to as SQLJ types. The Java classes representing the UDT implement the java.sql.SQLData or oracle.sql.ORAData interface, not the java.io.Serializable. These UDT’s are created using the CREATE TYPE SQL statement and are stored to the server, and are accessed through SQL.
- Link to Apache Derby > Documentation (10.8 Manuals): http://db.apache.org/derby/manuals/index.html
