|
|
Start of Tutorial > Start of Trail > Start of Lesson |
Search
Feedback Form |
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 typeCLOB(Character Large Object), which is capable of storing very large amounts of data in character format. The new typeARRAYmakes 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.
You retrieve, store, and update SQL3 datatypes the same way you do other datatypes. You use either
ResultSet.getXXXorCallableStatement.getXXXmethods to retrieve them,PreparedStatement.setXXXmethods to store them, andupdateXXXto update them. Probably 90 percent of the operations performed on SQL3 types involve using thegetXXX,setXXX, andupdateXXXmethods. The following table shows which methods to use:
BLOBCLOBARRAYStructured type REF(structured type)For example, the following code fragment retrieves an SQL
ARRAYvalue. For this example, the columnSCORESin the tableSTUDENTScontains values of typeARRAY. The variablestmtis aStatementobject.ResultSet rs = stmt.executeQuery( "SELECT SCORES FROM STUDENTS WHERE ID = 2238"); rs.next(); Array scores = rs.getArray("SCORES");The variable
scoresis a logical pointer to the SQLARRAYobject stored in the tableSTUDENTSin the row for student 2238.If you want to store a value in the database, you use the appropriate
setXXXmethod. For example, the following code fragment, in whichrsis aResultSetobject, stores aClobobject: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
notesas the first parameter in the update statement being sent to the database. TheCLOBvalue designated bynoteswill be stored in the tableMARKETSin columnCOMMENTSin every row where the value in the columnSALESis less than one million.
An important feature about
Blob,Clob, andArrayobjects 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 SQLBLOB,CLOB, orARRAYobject 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, andArrayobjects 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 SQLARRAYobject in an application as if it were an array in the Java programming language, you need to materialize theARRAYobject 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 interfacesBlob,Clob, andArrayall 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.
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
CREATETYPEstatement.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, andArrayobjects, aStructobject 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 aPLANE_POINTobject is stored in columnPOINTSof tablePRICES.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_POINTobject retrieved has an X value of 3 and a Y value of -5, theStructobjectpointwill contain the values 3 and -5.You might have noticed that
Structis the only type not to have agetXXXandsetXXXmethod with its name asXXX. You must usegetObjectandsetObjectwithStructinstances. This means that when you retrieve a value using the methodgetObject, you will get anObjectin the Java programming language that you must explicitly cast to aStruct, as was done in the previous code example.The second SQL type that a user can define in an SQL
CREATETYPEstatement is a distinct type. An SQL distinct type is similar to atypedefin 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 typeNUMERICthat is always base 10 with two digits after the decimal point.MONEYis now a datatype in the schema in which it was defined, and you can store instances ofMONEYin a table that has a column of typeMONEY.An SQL distinct type is mapped to the type in the Java programming language to which its underlying type would be mapped. For example,
NUMERICmaps tojava.math.BigDecimal, so the typeMONEYmaps tojava.math.BigDecimal. To retrieve aMONEYobject, you useResultSet.getBigDecimalorCallableStatement.getBigDecimal; to store aMONEYobject, you usePreparedStatement.setBigDecimal.
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
Structis 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
REFtype that represents a particular structured type. An SQLREFobject is mapped to aRefobject 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-2004 Sun Microsystems, Inc. All rights reserved.