Comprehensive mapping between Oracle SQL types and their corresponding Java Object types as recognized by the Oracle JDBC driver (OJDBC) when using the setObject method in a PreparedStatement.
Additionally, where applicable, String alternatives are also included (i.e., String representations that can be used provided they conform to the expected format for the SQL type).
| Oracle SQL Type | Recommended Java Object Type | String Alternative | Notes |
|---|---|---|---|
| NUMBER | java.math.BigDecimal (preferred) |
||
java.lang.Integer |
|||
java.lang.Long |
|||
java.lang.Double |
|||
java.lang.Float |
"12345" (valid numeric string) |
- Precision Handling: BigDecimal is preferred for high-precision requirements.<br>- Range Considerations: Choose Integer, Long, etc., based on the expected numeric range. |
|
| VARCHAR2 | java.lang.String |
"Sample Text" |
- Length Constraints: Maximum 4000 bytes in Oracle versions prior to 12c; can be extended to 32,767 bytes in Oracle 12c and later with certain configurations. |
| CHAR | java.lang.String |
"FixedLen" |
- Fixed-Length: CHAR types are fixed-length and padded with spaces as necessary.<br>- Length Constraints: Similar to VARCHAR2. |
| CLOB | java.sql.Clob |
||
java.lang.String |
String (suitable for small to moderate text) |
- Large Text Handling: Use Clob for managing large text data efficiently.<br>- Memory Management: Prefer Clob over String for very large texts to optimize memory usage. |
|
| BLOB | byte[] |
||
java.sql.Blob |
Base64-encoded String (preferred over hex representation) |
- Binary Data Handling: Use byte[] or Blob for binary content to prevent data corruption.<br>- Encoding: Base64 is more common and efficient for representing binary data as strings. |
|
| DATE | java.sql.Date <br> java.util.Date |
"2023-10-15" (formatted as yyyy-MM-dd) |
- Time Zone Considerations: java.util.Date does not store time zone information.<br>- Usage: Suitable for date-only values without time components. |
| TIMESTAMP | java.sql.Timestamp java.util.Date |
"2023-10-15 13:45:30.123" (formatted accordingly) |
- Precision: Supports fractional seconds.<br>- Time Zone: Does not inherently handle time zones; consider TIMESTAMPTZ for time zone support. |
| TIMESTAMP WITH TIME ZONE | oracle.sql.TIMESTAMPTZ |
||
java.sql.Timestamp |
"2023-10-15 13:45:30.123 +05:00" (includes time zone information) |
- Time Zone Handling: Requires precise formatting to include time zone data.<br>- Oracle-Specific: oracle.sql.TIMESTAMPTZ is Oracle-specific and should be used accordingly. |
|
| TIMESTAMP WITH LOCAL TIME ZONE | oracle.sql.TIMESTAMPLTZ <br> java.sql.Timestamp |
Similar to TIMESTAMP WITH TIME ZONE but normalized | - Normalization: Database handles time zone normalization.<br>- Usage: Use when time zone information should be normalized to the database's time zone. |
| BINARY_FLOAT | java.lang.Float |
"123.45" (valid float string) |
- Precision: Single-precision floating-point.<br>- Performance: Slightly more efficient than using Double when appropriate. |
| BINARY_DOUBLE | java.lang.Double |
"123.45" (valid double string) |
- Precision: Double-precision floating-point.<br>- Usage: Preferred for high-precision floating-point computations. |
| BOOLEAN (Non-Native) | java.lang.Boolean (Emulated) |
"TRUE" or "FALSE" (typically via NUMBER(1) or CHAR(1)) |
- Oracle Support: Oracle SQL does not have a native BOOLEAN type in table definitions.<br>- Emulation: Often emulated using NUMBER(1) (e.g., 1/0) or CHAR(1) (e.g., 'Y'/'N'). |
java.math.BigDecimal *(preferred)`: Ideal for high-precision decimal numbers, such as financial data.java.lang.Integer, java.lang.Long, java.lang.Double, java.lang.Float: Suitable for whole numbers or floating-point numbers within specific ranges."12345".BigDecimal should be used when precise decimal representation is required to avoid rounding errors.Integer, Long, etc.) based on the expected numeric range of the SQL NUMBER to prevent overflow or precision loss.java.lang.String."Sample Text".VARCHAR2 up to 4000 bytes in Oracle versions prior to 12c and up to 32,767 bytes in Oracle 12c and later with specific configurations).CHAR is fixed-length and padded with spaces, whereas VARCHAR2 is variable-length.java.sql.Clob, java.lang.String.Clob for large text data to manage memory efficiently. For smaller texts, String can suffice.byte[], java.sql.Blob.String (preferred over hex).