18.6.2 PGX Data Type Compatibility and Casting
You can configure a compatibility mode for the PGX JDBC driver to determine
the data type returned when calling the ResultSet#getObject()
method.
The PGX JDBC driver supports the following compatibility modes:
- PGX (default): By default, the PGX JDBC driver will use the PGX compatibility mode to return the PGX native types.
- ORACLE_JDBC19: This mode enables the driver to return data types that are compatible with Oracle Database 19c.
- ORACLE_JDBC23: This mode enables the driver to return data types that are compatible with Oracle Database 23ai.
The following table describes the data types returned by the driver for the different compatibility modes.
Table 18-4 Data Type Compatibility
PGX Type | PGX (Default) | ORACLE_JDBC19 | ORACLE_JDBC23 |
---|---|---|---|
BOOLEAN |
BOOLEAN |
java.math.BigDecimal |
Boolean |
INTEGER /LONG /FLOAT /DOUBLE |
INTEGER /LONG /FLOAT /DOUBLE |
java.math.BigDecimal |
java.math.BigDecimal |
LOCAL_DATE |
java.sql.Date |
java.sql.Timestamp |
java.sql.Timestamp |
TIME |
java.sql.Time |
Not supported | Not supported |
TIME_WITH_TIMEZONE |
java.time.OffsetTime |
Not supported | Not supported |
TIMESTAMP |
java.sql.Timestamp |
oracle.sql.TIMESTAMP |
oracle.sql.TIMESTAMP |
TIMESTAMP_WITH_TIMEZONE |
java.time.OffsetDateTime
|
oracle.sql.TIMESTAMPZ |
oracle.sql.TIMESTAMPZ |
Setting the Compatibility Mode in the PGX JDBC Driver
If you wish to use a different compatibility mode (other than the default PGX mode), then this can be set as part of the properties as shown:
import static oracle.pgx.jdbc.CONNECTION_PROPERTY_COMPATIBILITY_MODE;
import static oracle.pgx.jdbc.ORACLE_JDBC19;
...
Properties properties = new Properties();
properties.put(...);
properties.put(CONNECTION_PROPERTY_COMPATIBILITY_MODE, ORACLE_JDBC19); // set compatibility mode
Connection connection = DriverManager.getConnection("<jdbc_url>", properties);
Data Type Conversions through Casting
The PGX JDBC driver allows casting of one type into another (regardless of the compatibility mode) as shown in the following table.
Table 18-5 Additional Supported Types through Casting
PGX Type | Default Mapping (PGX Compatibility) | Additional Supported Types (through Casting) |
---|---|---|
BOOLEAN |
BOOLEAN |
String , BigDecimal |
INTEGER |
INTEGER |
String , BigDecimal ,
Long , Float ,
Double |
FLOAT |
FLOAT |
String , BigDecimal ,
Double |
LONG |
LONG |
String , BigDecimal ,
Double |
DOUBLE |
DOUBLE |
String , BigDecimal |
LOCAL_DATE |
java.sql.Date |
String , java.time.LocalDate ,
java.sql.Timestamp |
TIME |
java.sql.Time |
String ,
java.time.LocalTime |
TIME_WITH_TIMEZONE |
java.time.OffsetTime |
String |
TIMESTAMP |
java.sql.Timestamp |
String ,
java.time.LocalDateTime ,
oracle.sql.TIMESTAMP |
TIMESTAMP_WITH_TIMEZONE |
java.time.OffsetDateTime |
String ,
oracle.sql.TIMESTAMPZ |
Data Type Conversions for PreparedObject#setObject(int index, Object
o)
The following table describes the data type conversions when calling the
PreparedObject#setObject(int index, Object o)
method.
Table 18-6 Data Type Conversions for setObject
Method
Given Object Type | PGX Type |
---|---|
BOOLEAN |
BOOLEAN |
INTEGER /FLOAT /LONG /DOUBLE |
INTEGER /FLOAT /LONG /DOUBLE |
BIGDECIMAL |
DOUBLE |
java.sql.Date ,
java.time.LocalDate |
LOCAL_DATE |
java.sql.Time ,
java.time.LocalTime |
TIME |
java.time.OffsetTime |
TIME_WITH_TIMEZONE |
java.sql.Timestamp ,
java.time.LocalDateTime ,
oracle.sql.TIMESTAMP |
TIMESTAMP |
java.time.OffsetDateTime ,
oracle.sql.TIMESTAMPZ |
TIMESTAMP_WITH_TIMEZONE |
Parent topic: Executing PGQL Queries Using the PGX JDBC Driver