Brought to you by EarthWeb
ITKnowledge Logo Login Graphic Click here to enter the VB Zone
Click here to enter the VB Zone
ITKnowledge
Search this book:
 
Search the site:
 
EXPERT SEARCH ----- nav

EarthWeb Direct

EarthWeb Direct

EarthWeb sites: other sites

Previous Table of Contents Next


Mapping Data Types to Java

When 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:

  CHAR
  VARCHAR
  LONGVARCHAR

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:

  java.sql.Date sets the java.util.date.hour, .minute, .second, and .milliseconds fields to zero
  java.sql.Time sets the java.util.date.year, .month, and .day fields according to 1970, January 1st
  java.sql.Timestamp has a similar behavior but also sets a nanosecond field

Type Mapping Tables

Table 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-1 MAPPING TABLE FROM SQL TYPES TO JAVA TYPES

SQL type Java type
CHAR String
VARCHAR String
LONGVARCHAR String
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
SQL type Java type
BIT boolean
TINYINT byte
SMALLINT short
INTEGER int
BIGINT long
REAL float
FLOAT double
SQL type Java type
DOUBLE double
BINARY byte[]
VARBINARY byte[]
LONGVARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp

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.

Table 6-2 MAPPING TABLE FROM JAVA TYPES TO SQL TYPES

Java type SQL 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

SQL Escape Syntax

To 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 Syntax

The 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}


Previous Table of Contents Next
HomeAbout UsSearchSubscribeAdvertising InfoContact UsFAQs
Use of this site is subject to certain Terms & Conditions.
Copyright (c) 1996-1999 EarthWeb Inc. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.