SQL Datatypes and Java Datatypes
Return to the Java Programming Corner.
Support for different datatypes in SQL2 is poor. Since Java is an object-oriented language, support is extremely rich. Therefore a huge disconnect exists between what sits in the database adn the way you want it represented in your Java application. The SQL concept of a variable width, single-byte character array, for example, is the VARCHAR datatype. Java actually has no concept of a variable width, single-byte character array: Java doesn't even have a single-byte character type. The closest thing is the String class.
To make matters worse, many database engines internally support their own datatypes and loosely translate them to a SQL2 type. All Oracle numeric types, for example, map to the SQL NUMERIC type. JDBC, fortunately, lets you retrieve data in their Java forms defined by a JDBC-specified datatype mapping. You do not need to worry that a SQL LONG has a different representation in Sybase than it does in Oracle. You just call the ResultSet getLong() method to retrieve numbers you wish to treat as Java longs.
You do need to be somewhat concerned when designing the database, however. If you pull a 64-bit number into a Java application via getInt(), you risk getting bad data. Similarly, if you save a Java float into a numeric field with a scale of 0, you will lose data. The important rule of thumb for Java programming, however, is think and work in Java and use the database to support the Java application. Do not let the database drive Java.
These mappings are simply the JDBC specification for direct type mappings and not a law prescribing the format you must use in Java for your SQL data. In other words, you can retrieve an INTEGER column into Java as a long or put a Java Date object in a TIMESTAMP field. Some conversions are, nevertheless, nonsensical. You cannot save a Java boolean into a database DATE field.
JDBC Specification SQL to Java Datatype Mappings (SQL3 Types in Italic)
SQL Type (from java.sql.Types) Java Type BIT boolean TINYINT byte SMALLINT short INTEGER int BIGINT long REAL float FLOAT double DOUBLE double DECIMAL java.math.BigDecimal NUMERIC java.math.BigDecimal CHAR java.lang.String VARCHAR java.lang.String LONGVARCHAR java.lang.String DATE java.sql.Date TIME java.sql.Time TIMESTAMP java.sql.Timestamp BINARY byte VARBINARY byte LONGVARBINARY byte BLOB java.sql.Blob CLOB java.sql.Clob ARRAY java.sql.Array REF java.sql.Ref STRUCT java.sql.Struct
JDBC Specification Java to SQL Datatype Mappings
Java Type SQL Type (from java.sql.Types) boolean BIT byte TINYINT short SMALLINT int INTEGER long BIGINT float REAL double DOUBLE java.math.BigDecimal NUMERIC java.lang.String VARCHAR or LONGVARCHAR byte VARBINARY or LONGVARBINARY java.sql.Date DATE java.sql.Time TIME java.sql.Timestamp TIMESTAMP java.sql.Blob BLOB java.sql.Clob CLOB java.sql.Array ARRAY java.sql.Ref REF java.sql.Struct STRUCT