Data Types

Whether you move data between two computers, computer systems, or programs written in different programming languages, you'll need to identify which data types can be moved from one setup to another and how. This problem arises when you retrieve data from an Oracle database in a Java program and store data from a Java program in the database. It's a function of the JDBC driver to know how to move or convert the data as it moves between your Java program and Oracle, but you as the programmer must know what is possible or, more importantly, legal. Table 10-1 lists the Oracle SQL data types and all their valid Java data type mappings.

Table 10-1. Valid Oracle SQL-to-Java data type mappings

Oracle SQL data type Valid Java data type mappings

BFILE oracle.sql.BFILE

BLOB

oracle.sql.BLOB

java.sql.Blob

CHAR, VARCHAR2, LONG

oracle.sql.CHAR java.lang.String java.sql.Date java.sql.Time java.sql.Timestamp java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.math.BigDecimal

byte short int long float double CLOB oracle.sql.CLOB java.sql.Clob DATE oracle.sql.DATE java.sql.Date java.sql.Time java.sql.Timestamp java.lang.String OBJECT oracle.sql.STRUCT java.sql.Struct oracle.sql.CustomDatum java.sql.SQLData NUMBER oracle.sql.NUMBER java.lang.Byte java.lang.Short java.lang.Integer java.lang.Long java.lang.Float java.lang.Double java.math.BigDecimal byte short int long

float

double

RAW, LONG RAW

oracle.sql.RAW byte[] REF oracle.sql.REF java.sql.Ref ROWID oracle.sql.CHAR oracle.sql.ROWID java.lang.String

TABLE (nested), VARRAY

oracle.sql.ARRAY

java.sql.Array

Any of the above SQL types

oracle.sql.CustomDatum

oracle.sql.Datum

Besides the standard Java data types, Oracle's JDBC implementation also provides a complete set of Oracle Java data types that correspond to the Oracle SQL data types. These classes, which all begin with oracle.sql, store Oracle SQL data in byte arrays similar to how it is stored natively in the database.

For now, we will concern ourselves only with the SQL data types that are not streamable and are available with relational SQL. These data types are:

• CHAR • VARCHAR2 • DATE • NUMBER • RAW • ROWID

We will cover the other data types in the chapters that follow. For the most part, since the CHAR, RAW, and ROWID data types are rarely used, this leaves us with the Oracle SQL data types: VARCHAR2, NUMBER, and DATE. The question is how to map these Oracle SQL types to Java types. Although you can use any of the SQL-to-Java data type mappings in Table 10-1, I suggest you use the following strategies:

• For SQL character values, map VARCHAR2 to java.lang.String.

• For SQL numeric values, map an integer type NUMBER to java.lang.Long or long, and map a floating-point type NUMBER to java.lang.Double or double.

• For SQL date and time values, map a DATE to java.sql.Timestamp.

Why? Well, let's start with the SQL character types. The only feasible mapping for character data, unless you are writing data-processing-type stored procedures, is to use java.lang.String. When designing tables for a database, I recommend you use VARCHAR2 for all character types that are not large objects. As I stated in Chapter 8, there is no good reason to use an Oracle CHAR data type. CHAR values are fixed-length character values right-padded with space characters. Because they are right-padded with spaces, they cause comparison problems when compared with VARCHAR2 values.

For NUMBER values, there are two possible types of values you can encounter. The first is an integer type NUMBER definition such as NUMBER(18) or NUMBER. You can map such integer values to a java.lang.Integer or int, but you'll have only nine significant digits. By using an integer, you constrain your program in such a way that it may require modifications at a later date. It's much easier to use a data type that can hold all possible values now and in the future. For Java, this is java.math.BigDecimal. However, using BigDecimal is inefficient if full precision is not needed, so I recommend using java.lang.Long or long, both of which have 18 significant digits for precision. For floating-point type NUMBER definitions such as

NUMBER(16,2) or NUMBER, I suggest you use a java.lang.Double or double, which also have 18 significant digits for precision for the same reason -- you don't want to have to modify your program later to handle larger values than you first anticipated. In designing tables for a database, I recommend you don't constrain NUMBER columns unless there is a compelling reason to do so. That means defining both integer and floating-point values as NUMBER. For DATE values, I suggest you use java.sql.Timestamp instead of java.sql.Date or

java.lang.Time for two reasons. First, Timestamp supports the parsing of SQL92 Timestamp escape syntax. Second, it's good programming practice to set times manually to midnight if you are using only the date portion. The fact that Timestamp supports SQL92 escape syntax makes it easier to set the time to midnight.

Remember what I said earlier: "...unless you are writing data-processing-type stored procedures." Since conversions take place whenever an Oracle SQL data type is accessed as a Java data type, it can be more efficient to use the proprietary Oracle Java types such as

oracle.sql.CHAR, oracle.sql.DATE, and oracle.sql.NUMBER in some situations. If you are writing a data-intensive program such as a conversion program to read data from one set of tables and write it to another set, then you should consider using the proprietary Oracle data types. To use them, you'll have to cast your java.sql.ResultSet to an

oracle.jdbc.driver.OracleResultSet.

Now that you have a thorough understanding of which data type mappings are possible and a mapping strategy, let's look at the accessor methods you can use to perform the mapping and get the values from a ResultSet object.