Start of Tutorial > Start of Trail > Start of Lesson

Search Feedback Form

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 Feedback Form

Copyright 1995-2001 Sun Microsystems, Inc. All rights reserved.