当前位置: 技术问答>java相关
数据类型转换？
来源: 互联网 发布时间：2015-02-05
本文导语: 请问在读写数据库是数据时，若数据类型不同怎么办。我是在JSP中遇到问题的。 | Contents | Prev | Next JDBCTM Guide: Getting Started ------------------------------------------------------------------------------...
请问在读写数据库是数据时，若数据类型不同怎么办。我是在JSP中遇到问题的。
|
Contents | Prev | Next JDBCTM Guide: Getting Started
--------------------------------------------------------------------------------
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 type LONG 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 types CHAR, 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 types BINARY, 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 type BIT 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 type TINYINT 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 type SMALLINT 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 type INTEGER 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 type BIGINT 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 type REAL 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 type DOUBLE 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 type FLOAT 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 types DECIMAL 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 SQL DATE 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 SQL TIME type is defined in SQL-92, but it is implemented by only a subset of the major databases. As with DATE, some databases offer alternative SQL types that support similar semantics
The JDBC TIMESTAMP type represents DATE plus TIME plus a nanosecond field. The corresponding SQL TIMESTAMP type is defined in SQL-92, but it is implemented by only a very small number of databases.
Because the standard Java class 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 SQL DATE information. The hour, minute, second, and millisecond fields of the java.util.Date base class are set to zero.
java.sql.Time for SQL TIME information. The year, month, and day fields of the java.util.Date base class are set to 1970, January, and 1. This is the "zero" date in the Java epoch.
java.sql.Timestamp for SQL TIMESTAMP information. This class extends java.util.Date by adding a nanosecond field.
All three of the JDBC time-related classes are subclasses of 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 interfaces ResultSet, 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 a ResultSet 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
--------------------------------------------------------------------------------
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 type LONG 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 types CHAR, 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 types BINARY, 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 type BIT 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 type TINYINT 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 type SMALLINT 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 type INTEGER 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 type BIGINT 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 type REAL 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 type DOUBLE 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 type FLOAT 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 types DECIMAL 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 SQL DATE 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 SQL TIME type is defined in SQL-92, but it is implemented by only a subset of the major databases. As with DATE, some databases offer alternative SQL types that support similar semantics
The JDBC TIMESTAMP type represents DATE plus TIME plus a nanosecond field. The corresponding SQL TIMESTAMP type is defined in SQL-92, but it is implemented by only a very small number of databases.
Because the standard Java class 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 SQL DATE information. The hour, minute, second, and millisecond fields of the java.util.Date base class are set to zero.
java.sql.Time for SQL TIME information. The year, month, and day fields of the java.util.Date base class are set to 1970, January, and 1. This is the "zero" date in the Java epoch.
java.sql.Timestamp for SQL TIMESTAMP information. This class extends java.util.Date by adding a nanosecond field.
All three of the JDBC time-related classes are subclasses of 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 interfaces ResultSet, 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 a ResultSet 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
您可能感兴趣的文章:
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。
站内导航:
特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！