The JavaTM Tutorial

| Start of Tutorial > Start of Trail > Start of Lesson | Search

Trail: JDBC(TM) Database Access
Lesson: New Features in the JDBC 2.0 API

## Using SQL3 Datatypes

> The datatypes commonly referred to as SQL3 types are the new datatypes being adopted in the next version of the ANSI/ISO SQL standard. The JDBC 2.0 API provides interfaces that represent the mapping of these SQL3 datatypes into the Java programming language. With these new interfaces, you can work with SQL3 datatypes the same way you do other datatypes.

> The new SQL3 datatypes give a relational database more flexibility in what can be used as a type for a table column. For example, a column may now be used to store the new type `BLOB` (Binary Large Object), which can store very large amounts of data as raw bytes. A column may also be of type `CLOB` (Character Large Object), which is capable of storing very large amounts of data in character format. The new type `ARRAY` makes it possible to use an array as a column value. Even the new SQL user-defined types (UDTs), structured types and distinct types, can now be stored as column values.

> The following list gives the JDBC 2.0 interfaces that map the SQL3 types. We will discuss them in more detail later.

> - A `Blob` instance maps an SQL `BLOB` instance
> - A `Clob` instance maps an SQL `CLOB` instance
> - An `Array` instance maps an SQL `ARRAY` instance
> - A `Struct` instance maps an SQL structured type instance
> - A `Ref` instance maps an SQL `REF` instance

### Using SQL3 Datatypes

> You retrieve, store, and update SQL3 datatypes the same way you do other datatypes. You use either `ResultSet.` `getXXX` or `CallableStatement.` `getXXX` methods to retrieve them, `PreparedStatement.` `setXXX` methods to store them, and `updateXXX` to update them. Probably 90 percent of the operations performed on SQL3 types involve using the `getXXX` `,` `setXXX` , and `updateXXX` `` methods. The following table shows which methods to use:

> SQL3 type

> getXXX method

> setXXX method

> updateXXX method

> `BLOB`

> `getBlob`

> `setBlob`

> updateBlob

> `CLOB`

> `getClob`

> `setClob`

> updateClob

> `ARRAY`

> `getArray`

> `setArray`

> updateArray

> Structured type

> `getObject`

> `setObject`

> updateObject

> `REF` (structured type)

> `getRef`

> `setRef`

> updateRef

For example, the following code fragment retrieves an SQL `ARRAY` value. For this example, the column `SCORES` in the table `STUDENTS` contains values of type `ARRAY` . The variable `stmt` is a `Statement` object.

>     ResultSet rs = stmt.executeQuery(
>         "SELECT SCORES FROM STUDENTS   WHERE ID = 2238");
>     rs.next();
>     Array scores = rs.getArray("SCORES");

The variable `scores` is a logical pointer to the SQL `ARRAY` object stored in the table `STUDENTS` in the row for student 2238.

If you want to store a value in the database, you use the appropriate `setXXX` method. For example, the following code fragment, in which `rs` is a `ResultSet` object, stores a `Clob` object:

>     Clob notes = rs.getClob("NOTES");
>     PreparedStatement pstmt = con.prepareStatement(
>         "UPDATE MARKETS SET COMMENTS = ? WHERE SALES < 1000000",
>         ResultSet.TYPE_SCROLL_INSENSITIVE,
>         ResultSet.CONCUR_UPDATABLE);
>     pstmt.setClob(1, notes);

This code sets `notes` as the first parameter in the update statement being sent to the database. The `CLOB` value designated by `notes` will be stored in the table `MARKETS` in column `COMMENTS` in every row where the value in the column `SALES` is less than one million.

### Blob, Clob, and Array Objects

> An important feature about `Blob` , `Clob` , and `Array` objects is that you can manipulate them without having to bring all of the data from the database server to your client machine. An instance of any of these types is actually a logical pointer to the object in the database that the instance represents. Because an SQL `BLOB` , `CLOB` , or `ARRAY` object may be very large, this feature can improve performance dramatically.

> You can use SQL commands and the JDBC 1.0 and 2.0 API with `Blob` , `Clob` , and `Array` objects just as if you were operating on the actual object in the database. If you want to work with any of them as an object in the Java programming language, however, you need to bring all their data over to the client, which we refer to as materializing the object. For example, if you want to use an SQL `ARRAY` object in an application as if it were an array in the Java programming language, you need to materialize the `ARRAY` object on the client and then convert it to an array in the Java programming language. Then you can use array methods in the Java programming language to operate on the elements of the array. The interfaces `Blob` , `Clob` , and `Array` all have methods for materializing the objects they represent. Refer to the second edition of JDBC Database Access with Java if you want more details or examples.

### Struct and Distinct Types

> SQL structured types and distinct types are the two datatypes that a user can define in SQL. They are often referred to as UDTs (user-defined types), and you create them with an SQL `CREATE` `TYPE` statement.

> An SQL structured type is similar to structured types in the Java programming language in that it has members, called attributes, that may be of any datatype. In fact, an attribute may itself be another structured type. Here is an example of a simple definition creating a new SQL datatype:

> >     CREATE TYPE PLANE_POINT
> >     (
> >     		X FLOAT,
> >     		Y FLOAT
> >     )

> Unlike `Blob` , `Clob` , and `Array` objects, a `Struct` object contains values for each of the attributes in the SQL structured type and is not just a logical pointer to the object in the database. For example, suppose that a `PLANE_POINT` object is stored in column `POINTS` of table `PRICES` .

> >     ResultSet rs = stmt.executeQuery(
> >         "SELECT POINTS FROM PRICES  WHERE PRICE > 3000.00");
> >     while (rs.next()) {
> >     	Struct point = (Struct)rs.getObject("POINTS");
> >     	// do something with point
> >     }

> If the `PLANE_POINT` object retrieved has an X value of 3 and a Y value of -5, the `Struct` object `point` will contain the values 3 and -5.

> You might have noticed that `Struct` is the only type not to have a `getXXX` and `setXXX` method with its name as `XXX`. You must use `getObject` and `setObject` with `Struct` instances. This means that when you retrieve a value using the method `getObject`, you will get an `Object` in the Java programming language that you must explicitly cast to a `Struct`, as was done in the previous code example.

> The second SQL type that a user can define in an SQL `CREATE` `TYPE` statement is a distinct type. An SQL distinct type is similar to a `typedef` in C or C++ in that it is a new type based on an existing type. Here is an example of creating a distinct type:

> >     CREATE TYPE MONEY AS NUMERIC(10, 2)

> This definition creates the new type called `MONEY` , which is a number of type `NUMERIC` that is always base 10 with two digits after the decimal point. `MONEY` is now a datatype in the schema in which it was defined, and you can store instances of `MONEY` in a table that has a column of type `MONEY` .

> An SQL distinct type is mapped to the type in the Java programming language to which its underlying type would be mapped. For example, `NUMERIC` maps to `java.math.BigDecimal` , so the type `MONEY` maps to `java.math.BigDecimal` . To retrieve a `MONEY` object, you use `ResultSet.getBigDecimal` or `CallableStatement.getBigDecimal` ; to store a `MONEY` object, you use `PreparedStatement.setBigDecimal` .

### SQL3 Advanced Features

> Some aspects of working with SQL3 types can get quite complex. We mention some of the more advanced features here so that you will know about them, but a deeper explanation is not appropriate for a basic tutorial. JDBC Database Access with Java contains a complete explanation of all JDBC features if you want to know more.

> The interface `Struct` is the standard mapping for an SQL structured type. If you want to make working with an SQL structured type easier, you can map it to a class in the Java programming language. The structured type becomes a class, and its attributes become fields. You do not have to use a custom mapping, but it can often be more convenient.

> Sometimes you may want to work with a logical pointer to an SQL structured type rather than with all the values contained in the structured type. This might be true, for instance, if the structured type has many attributes or if the attributes are themselves large. To reference a structured type, you can declare an SQL `REF` type that represents a particular structured type. An SQL `REF` object is mapped to a `Ref` object in the Java programming language, and you can operate on it as if you were operating on the structured type object that it represents.

| Start of Tutorial > Start of Trail > Start of Lesson | Search