Data Types (Java Enterprise in a Nutshell)

8.2. Data Types

Each column in a table has a particular data type associated with it. SQL-92 defines a fairly broad set of data types. Different RDBMS packages provide extensions to these basic types, for multimedia data, Java objects, and so on. Oracle's NUMBER data type, for instance, allows database designers to specify the exact precision of integer and floating point data types. The basic SQL-92 types are listed in Table 8-1. The names in parentheses are SQL-92 specified alternates for the main type names.

Table 8-1. SQL-92 Data Types

SQL Data Type	Description
`INTEGER` (`INT`)	A signed integer value. The number of bits represented is implementation-dependent.
`SMALLINT`	A smaller signed integer value, used when storage or memory is at a premium. Generally 8 bits, but implementation-dependent.
`NUMERIC`	A signed fixed-precision decimal. When creating a `NUMERIC`, you must specify the total length of the number (including the decimal point) and the number of decimal places. `NUMERIC(8, 4)` allows three digits, a decimal point, and four more digits.
`DECIMAL`	Defined the same way as a `NUMERIC`, but may store additional precision (more decimal places).
`REAL`	A single-precision floating-point value. Range and accuracy are implementation-dependent.
`DOUBLEPRECISION` (`DOUBLE`)	A double-precision floating-point value. Range and accuracy are implementation dependent, but are equal to or better than a `REAL`.
`BIT`	A fixed number (one or more) of bits. A length specifier (`BIT(`n`)`) is optional. The default size is 1 bit.
`BITVARYING`	Storage for up to n bits (`BIT VARYING (`n`)`). Many databases have alternate implementations of this data type, such as Oracle's `VARBINARY`.
`DATE`	A date value (day, month, and year).
`TIME`	A time value. Precision is implementation-dependent.
`TIMESTAMP`	A date and time.
`CHARACTER` (`CHAR`)	A fixed-length character string. Specified as `CHAR(`n`)`. Unused characters are padded with blanks. The default size is 1.
`CHARACTERVARYING` (`VARCHAR`)	A variable length string, up to size n. Specified as `VARCHAR(`n`)`.
`INTERVAL`	A date or time interval. Not directly supported by JDBC.

Note that the data types given here are not the same as those in Table 2-1 in Chapter 2, "JDBC". When mapping between SQL types and Java types, a JDBC driver matches the physical data type (either SQL-92 or database-specific) and the closest Java type. For example, calling the getType() method of ResultSet on a BITVARYING field generally returns a Types.LONGVARBINARY value. This translation allows most JDBC programs to switch between different databases without losing functionality. Also, many databases implement additional types: the FLOAT type is so common that many people think it is required by the specification.