A distinct type is a user-defined data type that is internally
represented as a built-in SQL data type. You create a distinct type
by executing the SQL statement CREATE DISTINCT TYPE.
In a JDBC program, you can create a distinct type using the executeUpdate method
to execute the CREATE DISTINCT TYPE statement. You can also use executeUpdate to
create a table that includes a column of that type. When you retrieve
data from a column of that type, or update a column of that type,
you use Java identifiers with
data types that correspond to the built-in types on which the distinct
types are based.
The following example creates a distinct type that is based on
an INTEGER type, creates a table with a column of that type, inserts
a row into the table, and retrieves the row from the table: Figure 1. Creating and using
a distinct type
Connection con;
Statement stmt;
ResultSet rs;
String empNumVar;
int shoeSizeVar;
…
stmt = con.createStatement(); // Create a Statement object
stmt.executeUpdate(
"CREATE DISTINCT TYPE SHOESIZE AS INTEGER");
// Create distinct type
stmt.executeUpdate(
"CREATE TABLE EMP_SHOE (EMPNO CHAR(6), EMP_SHOE_SIZE SHOESIZE)");
// Create table with distinct type
stmt.executeUpdate("INSERT INTO EMP_SHOE " +
"VALUES ('000010', 6)"); // Insert a row
rs=stmt.executeQuery("SELECT EMPNO, EMP_SHOE_SIZE FROM EMP_SHOE);
// Create ResultSet for query
while (rs.next()) {
empNumVar = rs.getString(1); // Get employee number
shoeSizeVar = rs.getInt(2); // Get shoe size (use int
// because underlying type
// of SHOESIZE is INTEGER)
System.out.println("Employee number = " + empNumVar +
" Shoe size = " + shoeSizeVar);
}
rs.close(); // Close ResultSet
stmt.close(); // Close Statement