SQL3 Data Types

After studying new features of the result sets and statements in the JDBC 2.0 API, let's focus on the new data types defined in the new version. Data types such as integer, number, date, character, and so on were available in JDBC 1.0 API because they were compliant with the ANSI/SQL standards. The ANSI/SQL standards have been updated to include new data types defined for databases. Databases are now able to support the storage of large text data as well as binary data (like images). These data types are also referred to as SQL3 data types. Not all databases support these data types. Oracle RDBMS database supports all of these major data types. To enable Java programs to utilize these data types, the JDBC 2.0 API has provided support for the following new data types:

• BLOB Stores a large amount of binary data in the database. Previously, in an Oracle database, for example, you could store binary data in a LONGRAW data type. Now, the BLOB data type is specifically added to enable manipulation of binary data. The JDBC 2.0 API provides a Blob class to manipulate the data. The getBlob(), setBlob(), and updateBlob() methods of the Blob Java class are available to manipulate the BLOB data type. A code snippet for retrieving data from a column called blob_value of the BLOB data type, from a table called blob_table, follows:

   java.sql.Blob blobValue = null;  ... Connection conn = getConnection(url); Statement stmt = conn.createStatement(); stmt.execute("select * from blob_table where ..."); ResultSet rs = stmt.getResultSet(); while ( rs.next() ) {    blobValue = rs.getBlob("blob_value");    ... } java.io.InputStream myInputStream = blobValue.getBinaryStream(); ... 

• CLOB Similar to the BLOB data type, it stores large amounts of text data. The JDBC 2.0 API provides a Clob class to manipulate the data. The getClob(), setClob(), and updateClob() methods of the Clob Java class are provided to manipulate the CLOB data type. A code snippet for retrieving data from a column called clob_value of the CLOB data type, from a table called clob_table, is given below:

   java.sql.Clob clobValue = null;  ... Connection conn = getConnection(url); Statement stmt = conn.createStatement(); stmt.execute("select * from clob_table where ..."); ResultSet rs = stmt.getResultSet(); while ( rs.next() ) {    clobValue = rs.getClob("clob_value");    ... } java.io.InputStream myInputStream = clobValue.getAsciiStream(); ... 

• ARRAY Represents an array of data in the database. The ARRAY data type can be retrieved and updated using the methods available in the Array class of the JDBC 2.0 API. The methods getArray(), setArray(), and updateArray() of the Array class can be used to manipulate the ARRAY data type. A code snippet for retrieving data from a column called array_value of the ARRAY data type, from a table called array_table, is given below:

   ...  Connection conn = getConnection(url); Statement stmt = conn.createStatement(); Stmt.execute("select * from array_table where ...") ResultSet rs = stmt.getResultSet(); Array arrayValue  = rs.getArray("array_value");         String[] names = (String[])arrayValue.getArray(); ... 

• STRUCT A composite data type created by combining different columns or fields into a structure. The STRUCT data type must always be created using SQL. For example, to create a STRUCT data type BOOKDETAIL, you need to execute the following SQL statement in your database that supports SQL3 STRUCT data type:

   CREATE TYPE BOOKDETAIL  {     BOOK_ID INTEGER,     BOOK_TITLE VARCHAR(50),     BOOK_PRICE NUMBER(6,2) }; 

  This SQL statement creates a user-defined structure data type called BOOKDETAIL. The STRUCT data type is also referred to as a user-defined type (UDT). The STRUCT data type is analogous to a Java object. Once you've created it, the BOOKDETAIL data type can be used like any other data type in the database. Because the STRUCT data type is similar to a Java object, the getObject(), setObject(), and updateObject() methods are available in the JDBC 2.0 API to manipulate the STRUCT data type. The STRUCT data type is supported by the WebLogic JDBC driver for the Oracle database only.

• REF A logical reference to a STRUCT data type. The REF data type is associated permanently with a structure and therefore is stored separately. The REF data type is not created by a developer. Only the table required to store the references to the structure is created by the developer.

• DISTINCT A special data type because it is a user-defined data type. A user-defined data type is a composite data type made up of existing data types. The DISTINCT data type is most useful when persisting or retrieving user-defined data types. Because the DISTINCT data type is based on existing data types, you can use the methods of the existing data types like char, number, and so on to manipulate the data. The DISTINCT data type is also referred to as a user-defined type (UDT). The DISTINCT data type must always be created using SQL. For example, to create a data type PRICE, you need to execute the following SQL statement in a database that supports SQL3 DISTINCT data type:

   CREATE DISTINCT PRICE AS NUMBER(8,2);  

  This SQL statement creates a data type called PRICE that can be used like any other data type in the database. Because the PRICE data type is essentially a decimal number, you can use methods available to manipulate a float, double, or BigDecimal data type.