![]() |
|||
![]() ![]() |
![]() |
![]()
|
![]() |
Mapping Data Types to JavaWhen types are known at compile time, the following mappings are used. Note that JDBC also provides a mechanism to fully support dynamically typed data access when result and parameter types are not known at compile time (see the Dynamic Database Access section). Character Strings There are three SQL data types for SQL strings:
In Java, we have String and char[] types to hold these SQL data types. String is used as the default mapping, knowing that it is possible to convert a String to a char[] and a char[] to a String. Thus, reading and writing data may be achieved without knowing the exact data type expected. Space padding is automatically done whenever dealing with fixed length types such as SQL CHAR(n). Spaces are added to the end of a string to set its length to n when a SQL CHAR(n) must be sent to the database. When a SQL CHAR(n) field is retrieved, additional padding is done to get a string of length n. Note that a LONGVARCHAR may overflow a string when retrieved using ResultSet.getString(). In this case, it is advised to retrieve the LONGVARCHAR field in small chunks using a Java input stream. This may occur when retrieving BLOBs (Binary Long Objects) from the database. Numbers SQL integer types allow for 1-(TINYINT), 2-(SMALLINT), 4-(INTEGER), and 8 byte-wide values (BIGINT). They can, therefore, be mapped to Java types such as Java byte, short, int, and long respectively. Fixed point numbers may be expressed as SQL DECIMAL and SQL NUMERIC, where absolute precision is preserved. They can be mapped to java.math.BigDecimal without losing precision. This type may be used to perform addition, subtraction, multiplication, and division. Note that these SQL types may also be accessed as Java strings, although it is difficult to perform math on strings. Floating point numbers are mapped as follows: SQL REAL, which requires 7 digits of mantissa precision, to Java float, SQL FLOAT, and SQL DOUBLE, which require 15 digits of mantissa precision, to Java double. Binary Types SQL types BINARY, VARBINARY, and LONGVARBINARY may be expressed as byte arrays in Java. As for LONGVARCHAR fields, LONGVARBINARY fields may be retrieved using Java streams for multimegabyte data values. The SQL BIT data type may be mapped to the Java boolean type. Time-Related Types The SQL DATE, TIME, and TIMESTAMP data types are time-related types. They can be expressed as java.sql.Date (yyyy-mm-dd), java.sql.Time (hh:mm:ss), and java.sql.Timestamp (yyyy-mm-dd hh:mm:ss.nanosecond), respectively. Note that java.sql.Date, java.sql.Time, and java.sql.Timestamp are three subclasses of java.util.date. Depending on which one is in use, different java.util.date members are affected. These subclasses include:
Type Mapping TablesTable 6-1 shows the standard mapping from SQL data types to Java types. Although they are common SQL data types, some databases may not support them.
Table 6-2 shows the default mapping from Java types to SQL data types. The mapping from String is normally VARCHAR. Where the String length exceeds the drivers limit on VARCHAR values, it becomes LONGVARCHAR. The same occurs with byte[] and VARBINARY and LONGVARBINARY.
SQL Escape SyntaxTo be JDBC-compliant, a database driver must support both SQL-2 entry level and semantics for some parts of the ANSI SQL-2 transitional level. Because the syntax used for this level is often different across DBMSs, JDBC provides an escape syntax for these semantics. The JDBC drivers convert the escape syntax into a DBMS-specific syntax, allowing portability of programs that require these features. Escape SyntaxThe escape syntax is the same as the escape syntax of Open Database Connectivity (ODBC). The escape syntax may not be the same as the ANSI syntax. Its form is: {keyword parameters} For Stored Procedures The following escape syntax is adopted to call stored procedures. The ?= may be dropped when the stored procedure does not return a result. The procedure parameters may be IN and/or OUT parameters or simple literals. {[?=] call stored_procedure_name [param1[, param2 ...]]} For example, a stored procedure that returns a value and takes two parameters would be called as shown here: {?= call proc_purge_employees employees, emp_messages}
|
![]() |
|