8 - Mapping SQL and Java Types
This overview is excerpted from JDBCTM Database Access from JavaTM: A Tutorial and Annotated Reference, currently in progress at JavaSoft. This book, both a tutorial and the definitive reference manual for JDBC, will be published in the spring of 1997 by Addison-Wesley Publishing Company as part of the Java series.8.1 Overview
Since SQL data types and Java data types are not identical, there needs to be some mechanism for reading and writing data between an application using Java types and a database using SQL types.To accomplish this, JDBC provides sets of getXXX
and setXXX
methods, the method registerOutParameter
, and the class Types
.
This section brings together information about data types affecting various classes and interfaces and puts all the tables showing the mappings between SQL types and Java types in one place for easy reference.
8.2 Mapping SQL Data Types into Java
Unfortunately there are significant variations between the SQL types supported by different database products. Even when different databases support SQL types with the same semantics, they may give those types different names. For example, most of the major databases support an SQL data type for large binary values, but Oracle calls this typeLONG RAW
, Sybase calls it IMAGE
, Informix calls it BYTE
, and DB2 calls it LONG VARCHAR FOR BIT DATA
.Fortunately, JDBC programmers will normally not need to concern themselves with the actual SQL type names used by a target database. Most of the time JDBC programmers will be programming against existing database tables, and they need not concern themselves with the exact SQL type names that were used to create these tables.
JDBC defines a set of generic SQL type identifiers in the class java.sql.Types
. These types have been designed to represent the most commonly used SQL types. In programming with the JDBC API, programmers will normally be able to use these JDBC types to reference generic SQL types, without having to be concerned about the exact SQL type name used by the target database. These JDBC types are fully described in the next section.
The one major place where programmers may need to use SQL type names is in the SQL CREATE TABLE
statement when they are creating a new database table. In this case programmers must take care to use SQL type names that are supported by their target database. We recommend that you consult your database documentation if you need exact definitions of the behavior of the various SQL types on a particular database.
If you want to be able to write portable JDBC programs that can create tables on a variety of different databases, you have two main choices. First, you can restrict yourself to using only very widely accepted SQL type names such as INTEGER
, NUMERIC
, or VARCHAR
, which are likely to work for all databases. Or second, you can use the java.sql.DatabaseMetaData.getTypeInfo
method to discover which SQL types are actually supported by a given database and select a database-specific SQL type name that matches a given JDBC type.
JDBC defines a standard mapping from the JDBC database types to Java types. For example, a JDBC INTEGER
is normally mapped to a Java int
. This supports a simple interface for reading and writing JDBC values as simple Java types.
The Java types do not need to be exactly isomorphic to the JDBC types; they just need to be able to represent them with enough type information to correctly store and retrieve parameters and recover results from SQL statements. For example, a Java String
object does not precisely match any of the JDBC CHAR
types, but it gives enough type information to represent CHAR
, VARCHAR
, or LONGVARCHAR
successfully.
8.3 JDBC Types
This section describes the different JDBC data types and how they are related to standard SQL types and to Java types.8.3.1 CHAR, VARCHAR, and LONGVARCHAR
The JDBC typesCHAR
, VARCHAR
, and LONGVARCHAR
are closely related. CHAR
represents a small, fixed-length character string, VARCHAR
represents a small, variable- length character string, and LONGVARCHAR
represents a large, variable-length character string.The SQL CHAR
type corresponding to JDBC CHAR
is defined in SQL-92 and is supported by all the major databases. It takes a parameter that specifies the string length. Thus CHAR(12)
defines a 12-character string. All the major databases support CHAR
lengths up to at least 254 characters.
The SQL VARCHAR
type corresponding to JDBC VARCHAR
is defined in SQL-92 and is supported by all the major databases. It takes a parameter that specifies the maximum length of the string. Thus VARCHAR(12)
defines a string whose length may be up to 12 characters. All the major databases support VARCHAR
lengths up to 254 characters. When a string value is assigned to a VARCHAR
variable, the database remembers the length of the assigned string and on a SELECT, it
will return the exact original string.
Unfortunately there is no consistent SQL mapping for the JDBC LONGVARCHAR
type. All the major databases support some kind of very large variable-ength string supporting up to at least a gigabyte of data, but the SQL type names vary.
Java programmers do not need to distinguish among the three types of JDBC strings, CHAR
, VARCHAR
, and LONGVARCHAR
. Each can be expressed as a Java String
, and it is possible to read and write an SQL statement correctly without knowing the exact data type that was expected.
CHAR
, VARCHAR
, and LONGVARCHAR
could have been mapped to either String
or char[]
, but String
is more appropriate for normal use. Also, the String
class makes conversions between String
and char[]
easy: There is a method for converting a String
object to a char[]
and also a constructor for turning a char[]
into a String
object.
One issue that had to be addressed is how to handle fixed-length SQL strings of type CHAR(n)
. The answer is that JDBC drivers (or the DBMS) perform appropriate padding with spaces. Thus, when a CHAR(n)
field is retrieved from the database, the driver will convert it to a Java String
object of length n
, which may include some padding spaces at the end. Conversely, when a String
object is sent to a CHAR(n)
field, the driver and/or the database will add any necessary padding spaces to the end of the string to bring it up to length n
.
The method ResultSet.getString
, which allocates and returns a new String
object, is recommended for retrieving data from CHAR
, VARCHAR
, and LONGVARCHAR
fields. This is suitable for retrieving normal data, but can be unwieldy if the JDBC type LONGVARCHAR
is being used to store multi-megabyte strings. To handle this case, two methods in the ResultSet
interface allow programmers to retrieve a LONGVARCHAR
value as a Java input stream from which they can subsequently read data in whatever size chunks they prefer. These methods are getAsciiStream
and getUnicodeStream
, which deliver the data stored in a LONGVARCHAR
column as a stream of Ascii or Unicode characters.
8.3.2 BINARY, VARBINARY, and LONGVARBINARY
The JDBC typesBINARY
, VARBINARY
, and LONGVARBINARY
are closely related. BINARY
represents a small, fixed-length binary value, VARBINARY
represents a small, variable-length binary value, and LONGVARBINARY
represents a large, variable-length binary value.Unfortunately, the use of these various BINARY
types has not been standardized and support varies considerably among the major databases.
The SQL BINARY
type corresponding to JDBC BINARY
is a non-standard SQL extension and is only implemented on some databases. It takes a parameter that specifies the number of binary bytes. Thus BINARY(12)
defines a 12-byte binary type. Typically, BINARY
values are limited to 254 bytes.
The SQL VARBINARY
type corresponding to JDBC VARBINARY
is a non-standard SQL extension and is only implemented on some databases. It takes a parameter that specifies the maximum number of binary bytes. Thus VARBINARY(12)
defines a binary type whose length may be up to 12 bytes. Typically, VARBINARY
values are limited to 254 bytes. When a binary value is assigned to a VARBINARY
variable, the database remembers the length of the assigned value and on a SELECT
, it
will return the exact original value.
Regrettably, there is no consistent SQL type name corresponding to the JDBC LONGVARBINARY
type. All the major databases support some kind of very large variable length binary type supporting up to at least a gigabyte of data, but the SQL type names vary.
BINARY
, VARBINARY
, and LONGVARBINARY
can all be expressed identically as byte
arrays in Java. Since it is possible to read and write SQL statements correctly without knowing the exact BINARY
data type that was expected, there is no need for Java programmers to distinguish among them.
The method recommended for retrieving BINARY
and VARBINARY
values is ResultSet.getBytes
. If a column of type JDBC LONGVARBINARY
stores a byte array that is many megabytes long, however, the method getBinaryStream
is recommended. Similar to the situation with LONGVARCHAR
, this method allows a Java programmer to retrieve a LONGVARBINARY
value as a Java input stream that can be read later in smaller chunks.
8.3.3 BIT
The JDBC typeBIT
represents a single bit value that can be zero or one.SQL-92 defines an SQL BIT
type. However, unlike the JDBC BIT
type this SQL-92 BIT type can be used as a parameterized type to define a fixed-length binary string. Fortunately, SQL-92 also permits the use of the simple non-parameterized BIT
type to represent a single binary digit, and this usage corresponds to the JDBC BIT
type. Unfortunately, the SQL-92 BIT
type is only required in "full" SQL-92 and is currently supported by only a subset of the major databases. Portable code may therefore prefer to use the JDBC SMALLINT
type, which is widely supported.
The recommended Java mapping for the JDBC BIT
type is as a Java boolean.
8.3.4 TINYINT
The JDBC typeTINYINT
represents an 8-bit unsigned integer value between 0 and 255.The corresponding SQL type, TINYINT,
is currently supported by only a subset of the major databases. Portable code may therefore prefer to use the JDBC SMALLINT
type, which is widely supported.
The recommended Java mapping for the JDBC TINYINT
type is as either a Java byte
or a Java short
. The 8-bit Java byte
type represents a signed value from -128 to 127, so it may not always be appropriate for larger TINYINT
values, whereas the 16-bit Java short
will always be able to hold all TINYINT
values.
8.3.5 SMALLINT
The JDBC typeSMALLINT
represents a 16-bit signed integer value between -32768 and 32767.The corresponding SQL type, SMALLINT
, is defined in SQL-92 and is supported by all the major databases. The SQL-92 standard leaves the precision of SMALLINT
up to the implementation, but in practice, all the major databases support at least 16 bits.
The recommended Java mapping for the JDBC SMALLINT
type is as a Java short
.
8.3.6 INTEGER
The JDBC typeINTEGER
represents a a 32-bit signed integer value between - 2147483648 and 2147483647.The corresponding SQL type, INTEGER,
is defined in SQL-92 and is widely supported by all the major databases. The SQL-92 standard leaves the precision of INTEGER
up to the implementation, but in practice all the major databases support at least 32 bits.
The recommended Java mapping for the INTEGER
type is as a Java int
.
8.3.7 BIGINT
The JDBC typeBIGINT
represents a 64-bit signed integer value between -9223372036854775808 and 9223372036854775807.The corresponding SQL type BIGINT
is a non-standard extension to SQL. In practice the SQL BIGINT
type is not yet currently implemented by any of the major databases, and we recommend that its use should be avoided in portable code.
The recommended Java mapping for the BIGINT
type is as a Java long.
8.3.8 REAL
The JDBC typeREAL
represents a "single precision" floating point number which supports 7 digits of mantissa.The corresponding SQL type REAL
is defined in SQL-92 and is widely, though not universally, supported by the major databases. The SQL-92 standard leaves the precision of REAL
up to the implementation, but in practice all the major databases supporting REAL
support a mantissa precision of at least 7 digits.
The recommended Java mapping for the REAL
type is as a Java float
.
8.3.9 DOUBLE
The JDBC typeDOUBLE
represents a "double precision" floating point number which supports 15 digits of mantissa.The corresponding SQL type is DOUBLE
PRECISION,
which is defined in SQL- 92 and is widely supported by the major databases. The SQL-92 standard leaves the precision of DOUBLE
PRECISION
up to the implementation, but in practice all the major databases supporting DOUBLE
PRECISION
support a mantissa precision of at least 15 digits.
The recommended Java mapping for the DOUBLE
type is as a Java double
.
8.3.10 FLOAT
The JDBC typeFLOAT
is basically equivalent to the JDBC type DOUBLE
. We provided both FLOAT
and DOUBLE
in a possibly misguided attempt at consistency with previous database APIs. FLOAT
represents a "double precision" floating point number that supports 15 digits of mantissa.The corresponding SQL type FLOAT
is defined in SQL-92.
The SQL-92 standard leaves the precision of FLOAT
up to the implementation, but in practice all the major databases supporting FLOAT
support a mantissa precision of at least 15 digits.
The recommended Java mapping for the FLOAT
type is as a Java double
. However, because of the potential confusion between the double precision SQL FLOAT
and the single precision Java float
, we recommend that JDBC programmers should normally use the JDBC DOUBLE
type in preference to FLOAT
.
8.3.11 DECIMAL and NUMERIC
The JDBC typesDECIMAL
and NUMERIC
are very similar. They both represent fixed-precision decimal values.The corresponding SQL types DECIMAL
and NUMERIC
are defined in SQL-92 and are very widely implemented. These SQL types takes precision and scale parameters. The precision is the total number of decimal digits supported, and the scale is the number of decimal digits after the decimal point. The scale must always be less than or equal to the precision. So for example, the value "12.345" has a precision of 5 and a scale of 3, and the value ".11" has a precision of 2 and a scale of 2. JDBC requires that all DECIMAL
and NUMERIC
types support both a precision and a scale of at least 15.
The sole distinction between DECIMAL
and NUMERIC
is that the SQL-92 specification requires that NUMERIC
types be represented with exactly the specified precision, whereas for DECIMAL
types, it allows an implementation to add additional precision beyond that specified when the type was created. Thus a column created with type NUMERIC(12,4)
will always be represented with exactly 12 digits, whereas a column created with type DECIMAL(12,4)
might be represented by some larger number of digits.
The recommended Java mapping for the DECIMAL
and NUMERIC
types is java.math.BigDecimal
, a Java type that also expresses fixed-point numbers with absolute precision. The java.math.BigDecimal
type provides math operations to allow BigDecimal
types to be added, subtracted, multiplied, and divided with other BigDecimal
types, with integer types, and with floating point types.
The method recommended for retrieving DECIMAL
and NUMERIC
values is ResultSet.getBigDecimal
. JDBC also allows access to these SQL types as simple Strings
or arrays of char
. Thus, Java programmers can use getString
to receive a DECIMAL
or NUMERIC
result. However, this makes the common case where DECIMAL
or NUMERIC
are used for currency values rather awkward, since it means that application writers have to perform math on strings. It is also possible to retrieve these SQL types as any of the Java numeric types.
8.3.12 DATE, TIME, and TIMESTAMP
There are three JDBC types relating to time:- The JDBC
DATE
type represents a date consisting of day, month, and year. The corresponding SQLDATE
type is defined in SQL-92, but it is implemented by only a subset of the major databases. Some databases offer alternative SQL types that support similar semantics. - The JDBC
TIME
type represents a time consisting of hours, minutes, and seconds. The corresponding SQLTIME
type is defined in SQL-92, but it is implemented by only a subset of the major databases. As withDATE
, some databases offer alternative SQL types that support similar semantics - The JDBC
TIMESTAMP
type representsDATE
plusTIME
plus a nanosecond field. The corresponding SQLTIMESTAMP
type is defined in SQL-92, but it is implemented by only a very small number of databases.
java.util.Date
does not match any of these three JDBC date-time types exactly (it includes both DATE
and TIME
information but has no nanoseconds), JDBC defines three subclasses of java.util.Date
to correspond to the SQL types. They are:java.sql.Date
for SQLDATE
information. The hour, minute, second, and millisecond fields of thejava.util.Date
base class are set to zero.java.sql.Time
for SQLTIME
information. The year, month, and day fields of thejava.util.Date
base class are set to 1970, January, and 1. This is the "zero" date in the Java epoch.java.sql.Timestamp
for SQLTIMESTAMP
information. This class extendsjava.util.Date
by adding a nanosecond field.
java.util.Date
, and as such, they can be used where a java.util.Date
is expected. For example, internationalization methods take a java.util.Date
object as an argument, so they can be passed instances of any of the JDBC time-related classes.A JDBC Timestamp
object has its parent's date and time components and also a separate nanoseconds component. If a java.sql.Timestamp
object is used where a java.util.Date
object is expected, the nanoseconds component is lost. However, since a java.util.Date
object is stored with a precision of one millisecond, it is possible to maintain this degree of precision when converting a java.sql.Timestamp
object to a java.util.Date
object. This is done by converting the nanoseconds in the nanoseconds component to whole milliseconds (by dividing the number of nanoseconds by 1,000,000) and then adding the result to the java.util.Date
object. Up to 999,999 nanoseconds may be lost in this conversion, but the resulting java.util.Date
object will be accurate to within one millisecond.
The code fragment below is an example of converting a java.sql.Timestamp
object to a java.util.Date
object that is accurate to within one millisecond:
Timestamp t = new Timestamp(100, 0, 1, 15, 45, 29, 987245732); java.util.Date d; d = new java.util.Date(t.getTime() + (t.getNanos() / 1000000));
8.4 Examples of Mapping
In any situation where a Java program retrieves data from a database, there has to be some form of mapping and data conversion. In most cases, JDBC programmers will be programming with knowledge of their target database's schema. They would know, for example, what tables the database contains and the data type for each column in those tables. They can therefore use the strongly-typed access methods in the interfacesResultSet
, PreparedStatement
, and CallableStatement
. This section presents three different scenarios, describing the data mapping and conversion required in each.8.4.1 Simple SQL Statement
In the most common case, a user executes a simple SQL statement and gets back aResultSet
object with the results. The value returned by the database and stored in a ResultSet
column will have a JDBC data type. A call to a ResultSet.getXXX
method will retrieve that value as a Java data type. For example, if a ResultSet
column contains a JDBC FLOAT
value, the method getDouble
will retrieve that value as a Java double
. The table in Section 8.6.6 shows which getXXX
methods may be used to retrieve which JDBC types. (A user who does not know the type of a ResultSet
column can get that information by calling the method ResultSet.getMetaData
and then invoking the ResultSetMetaData
methods getColumnType
or getColumnTypeName
.) The following code fragment demonstrates getting the column type names for the columns in a result set:String query = "select * from Table1"; ResultSet rs = stmt.executeQuery(query); ResultSetMetaData rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); for (int i = 1; i <= columnCount; i++) { String s = rsmd.getColumnTypeName(i); System.out.println ("Column " + i + " is type " + s); }
8.4.2 SQL Statement with IN Parameters
In another possible scenario, the user sends an SQL statement which takes input parameters. In this case, the user calls thePreparedStatement.setXXX
methods to assign a value to each input parameter. For example, PreparedStatement.setLong(1, 2345678)
will assign the value 2345678
to the first parameter as a Java long
. The driver will convert 2345678
to a JDBC BIGINT
in order to send it to the database. Which JDBC type the driver sends to the database is determined by the standard mapping from Java types to JDBC types, which is shown in the table in Section 8.6.2.8.4.3 SQL Statement with INOUT Parameters
In yet another scenario, a user wants to call a stored procedure, assign values to its INOUT parameters, retrieve values from the results, and retrieve values from the parameters. This case is rather uncommon and more complicated than most, but it gives a good illustration of mapping and data conversion.In this scenario, the first thing to do is to assign values to the INOUT parameters using PreparedStatement.setXXX
methods. In addition, since the parameters will also be used for output, the programmer must register each parameter with the JDBC type of the value that the database will return to it. This is done with the method CallableStatement.registerOutParameter
, which takes one of the JDBC types defined in the class Types
. A programmer retrieves the results returned to a ResultSet
object with ResultSet.getXXX
methods and retrieves the values stored in the output parameters with CallableStatement.getXXX
methods.
The XXX
type used for ResultSet
.getXXX
methods is fairly flexible in some cases. The table in Section 8.6.6 shows which ResultSet
.getXXX
methods can be used to retrieve which JDBC types.
The XXX
type used for CallableStatement
.getXXX
must map to the JDBC type registered for that parameter. For example, if the database is expected to return an output value whose type is JDBC
REAL
, the parameter should have been registered as java.sql.Types.REAL
. Then to retrieve the JDBC
REAL
value, the method CallableStatement.getFloat
should be called (the mapping from JDBC types to Java types is shown in the table in Section 8.6.1). The method getFloat
will return the value stored in the output parameter after converting it from a JDBC REAL
to a Java float
. To accommodate various databases and make an application more portable, it is recommended that values be retrieved from ResultSet
objects before values are retrieved from output parameters.
The following code demonstrates calling a stored procedure named getTestData
, which has two parameters that are both INOUT parameters. First the Connection
object con
creates the CallableStatement
object cstmt
. Then the method setByte
sets the first parameter to 25
as a Java byte
. The driver will convert 25
to a JDBC TINYINT
and send it to the database. The method setBigDecimal
sets the second parameter with an input value of 83.75
. The driver will convert this java.math.BigDecimal
object to a JDBC NUMERIC
value. Next the two parameters are registered as OUT parameters, the first parameter as a JDBC TINYINT
and the second parameter as a JDBC DECIMAL
with two digits after the decimal point. After cstmt
is executed, the values are retrieved from the ResultSet
object using ResultSet.getXXX
methods. The method getString
gets the value in the first column as a Java String
object, getInt
gets the value in the second column as a Java int
, and getInt
gets the value in the third column as a Java int
.
Then CallableStatement.getXXX
methods retrieve the values stored in the output parameters. The method getByte
retrieves the JDBC
TINYINT
as a Java byte
, and getBigDecimal
retrieves the JDBC
DECIMAL
as a java.math.BigDecimal
object with two digits after the decimal point. Note that when a parameter is both an input and an output parameter, the setXXX
method uses the same Java type as the getXXX
method (as in setByte
and getByte
). The registerOutParameter
method registers it to the JDBC type that is mapped from the Java type (a Java byte
maps to a JDBC TINYINT
, as shown in the table in Section 8.6.2).
CallableStatement cstmt = con.prepareCall( "{call getTestData(?, ?)}"); cstmt.setByte(1, 25); cstmt.setBigDecimal(2, 83.75); // register the first parameter as a JDBC TINYINT and the second //parameter as a JDBC DECIMAL with two digits after the decimal point cstmt.registerOutParameter(1, java.sql.Types.TINYINT); cstmt.registerOutParameter(2, java.sql.Types.DECIMAL, 2); ResultSet rs = cstmt.executeUpdate(); // retrieve and print values in result set while(rs.next()) { String name = rs.getString(1); int score = rs.getInt(2); int percentile = rs.getInt(3); System.out.print("name = " + name + ", score = " + score + ", " System.out.println("percentile = " + percentile); // retrieve values in output parameters byte x = cstmt.getByte(1); java.math.BigDecimal n = cstmt.getBigDecimal(2, 2);To generalize, the
XXX
in CallableStatement.getXXX
and PreparedStatement.setXXX
methods is a Java type. For setXXX
methods, the driver converts the Java type to a JDBC type before sending it to the database (using the standard mappings shown in the table in Section 8.6.2). For getXXX
methods, the driver converts the JDBC type returned by the database to a Java type (using the standard mappings shown in the table in Section 8.6.1) before returning it to the getXXX
method.The method registerOutParameter
always takes a JDBC type as an argument, and the method setObject
may take a JDBC type as an argument.
Note that if a JDBC type is supplied in its optional third argument, the method setObject
will cause an explicit conversion of the parameter value from a Java type to the JDBC type specified. If no target JDBC type is supplied to setObject
, the parameter value will be converted to the JDBC type that is the standard mapping from the Java type (as shown in Section 8.6.2). The driver will perform the explicit or implicit conversion before sending the parameter to the database.
8.5 Dynamic Data Access
In most cases, the user wants to access results or parameters whose data types are known at compile time. However, some applications, such as generic browsers or query tools, are compiled with no knowledge of the database schema they will access. For this reason, JDBC provides support for fully dynamically-typed data access in addition to static data type access.Three methods and one constant facilitate accessing values whose data types are not known at compile time:
ResultSet.getObject
PreparedStatement.setObject
CallableStatement.getObject
java.sql.Types.OTHER
(used as an argument toCallableStatement.registerOutParameter
)
ResultSet
object, it can use the method ResultSet.getObject
.The methods ResultSet.getObject
and CallableStatement.getObject
retrieve a value as a Java Object
. Since Object
is the base class for all Java objects, an instance of any Java class can be retrieved as an instance of Object
. However, the following Java types are built-in "primitive" types and are therefore not instances of the class Object
: boolean
, char
, byte
, short
, int
, long
, float
, and double
. As a result, these types cannot be retrieved by getObject
methods. However, each of these primitive types has a corresponding class that serves as a wrapper. Instances of these classes are objects, which means that they can be retrieved with the methods ResultSet.getObject
and CallableStatement.getObject
. Table 8.6.3 on page 67 shows the mapping from a JDBC type to a Java Object
type. This table differs from the standard mapping from JDBC type to Java type in that each primitive Java type is replaced by its wrapper class, except that JDBC TINYINT
and JDBC SMALLINT
are mapped to the Java class Integer
.
The method getObject
can also be used to retrieve user-defined Java types. With the advent of abstract data types (ADTs) or other user-defined types in some database systems, some vendors may find it convenient to use getObject
for retrieving these types.
8.6 Tables for Data Type Mapping
This section contains the following tables relating to JDBC and Java data types:Section 8.6.1-JDBC Types Mapped to Java Types
Section 8.6.2-Java Types Mapped to JDBC Types
Section 8.6.3-JDBC Types Mapped to Java Object
Types
Section 8.6.4-Java Object
Types Mapped to JDBC Types
Section 8.6.5- Conversions by setObject
Section 8.6.6-JDBC Types Retrieved by ResultSet.getXXX
methods
8.6.1 JDBC Types Mapped to Java Types
JDBC type | Java type |
---|---|
CHAR | String |
VARCHAR | String |
LONGVARCHAR | String |
NUMERIC | java.math.BigDecimal |
DECIMAL | java.math.BigDecimal |
BIT | boolean |
TINYINT | byte |
SMALLINT | short |
INTEGER | int |
BIGINT | long |
REAL | float |
FLOAT | double |
DOUBLE | double |
BINARY | byte[] |
VARBINARY | byte[] |
LONGVARBINARY | byte[] |
DATE | java.sql.Date |
TIME | java.sql.Time |
TIMESTAMP | java.sql.Timestamp |
8.6.2 Java Types Mapped to JDBC Types
This table shows the reverse mapping of Table 8.6.1, from Java types to JDBC types.Java Type | JDBC type |
---|---|
String | VARCHAR or LONGVARCHAR |
java.math.BigDecimal | NUMERIC |
boolean | BIT |
byte | TINYINT |
short | SMALLINT |
int | INTEGER |
long | BIGINT |
float | REAL |
double | DOUBLE |
byte[] | VARBINARY or LONGVARBINARY |
java.sql.Date | DATE |
java.sql.Time | TIME |
java.sql.Timestamp | TIMESTAMP |
The mapping for String will normally be VARCHAR
but will turn into LONGVARCHAR
if the given value exceeds the driver's limit on VARCHAR
values. The same is true for byte[]
and VARBINARY
and LONGVARBINARY
values.
8.6.3 JDBC Types Mapped to Java Object Types
Since the Java built-in types such asboolean
and int
are not subtypes of Object
, there is a slightly different mapping from JDBC types to Java object types for the getObject
/setObject
methods. This mapping is shown in the following table:JDBC Type | Java Object Type |
---|---|
CHAR | String |
VARCHAR | String |
LONGVARCHAR | String |
NUMERIC | java.math.BigDecimal |
DECIMAL | java.math.BigDecimal |
BIT | Boolean |
TINYINT | Integer |
SMALLINT | Integer |
INTEGER | Integer |
BIGINT | Long |
REAL | Float |
FLOAT | Double |
DOUBLE | Double |
BINARY | byte[] |
VARBINARY | byte[] |
LONGVARBINARY | byte[] |
DATE | java.sql.Date |
TIME | java.sql.Time |
TIMESTAMP | java.sql.Timestamp |
8.6.4 Java Object Types Mapped to JDBC Types
Java Object Type | JDBC Type |
---|---|
String | VARCHAR or LONGVARCHAR |
java.math.BigDecimal | NUMERIC |
Boolean | BIT |
Integer | INTEGER |
Long | BIGINT |
Float | REAL |
Double | DOUBLE |
byte[] | VARBINARY or LONGVARBINARY |
java.sql.Date | DATE |
java.sql.Time | TIME |
java.sql.Timestamp | TIMESTAMP |
Note that the mapping for String
will normaly be VARCHAR
but will turn into LONGVARCHAR
if the given value exceeds the driver's limit on VARCHAR
values. The case is similar for byte[]
and VARBINARY
and LONGVARBINARY
values.
8.6.5 Conversions by setObject
The methodsetObject
converts Java object types to JDBC types.T I N Y I N T | S M A L L I N T | I N T E G E R | B I G I N T | R E A L | F L O A T | D O U B L E | D E C I M A L | N U M E R I C | B I T | C H A R | V A R C H A R | L O N G V A R C H A R | B I N A R Y | V A R B I N A R Y | L O N G V A R B I N A R Y | D A T E | T I M E | T I M E S T A M P | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
String | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x |
java.math.BigDecimal | x | x | x | x | x | x | x | x | x | x | x | x | x | ||||||
Boolean | x | x | x | x | x | x | x | x | x | x | x | x | x | ||||||
Integer | x | x | x | x | x | x | x | x | x | x | x | x | x | ||||||
Long | x | x | x | x | x | x | x | x | x | x | x | x | x | ||||||
Float | x | x | x | x | x | x | x | x | x | x | x | x | x | ||||||
Double | x | x | x | x | x | x | x | x | x | x | x | x | x | ||||||
byte[] | x | x | x | ||||||||||||||||
java.sql.Date | x | x | x | x | x | ||||||||||||||
java.sql.Time | x | x | x | x | |||||||||||||||
java.sql.Time- stamp | x | x | x | x | x | x |
Conversion from Java object types to JDBC types.
8.6.6 JDBC Types Retrieved by ResultSet.getXXX Methods
An "x" means that the method can retrieve the JDBC type. An "X" means that the method is recommended for the JDBC type.T I N Y I N T | S M A L L I N T | I N T E G E R | B I G I N T | R E A L | F L O A T | D O U B L E | D E C I M A L | N U M E R I C | B I T | C H A R | V A R C H A R | L O N G V A R C H A R | B I N A R Y | V A R B I N A R Y | L O N G V A R B I N A R Y | D A T E | T I M E | T I M E S T A M P | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
getByte | X | x | x | x | x | x | x | x | x | x | x | x | x | ||||||
getShort | x | X | x | x | x | x | x | x | x | x | x | x | x | ||||||
getInt | x | x | X | x | x | x | x | x | x | x | x | x | x | ||||||
getLong | x | x | x | X | x | x | x | x | x | x | x | x | x | ||||||
getFloat | x | x | x | x | X | x | x | x | x | x | x | x | x | ||||||
getDouble | x | x | x | x | x | X | X | x | x | x | x | x | x | ||||||
getBigDecimal | x | x | x | x | x | x | x | X | X | x | x | x | x | ||||||
getBoolean | x | x | x | x | x | x | x | x | x | X | x | x | x | ||||||
getString | x | x | x | x | x | x | x | x | x | x | X | X | x | x | x | x | x | x | x |
getBytes | X | X | x | ||||||||||||||||
getDate | x | x | x | X | x | ||||||||||||||
getTime | x | x | x | X | x | ||||||||||||||
getTimestamp | x | x | x | x | X | ||||||||||||||
getAsciiStream | x | x | X | x | x | x | |||||||||||||
getUnicodeStream | x | x | X | x | x | x | |||||||||||||
getBinaryStream | x | x | X | ||||||||||||||||
getObject | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x | x |
No hay comentarios.:
Publicar un comentario