1. Home
  2. Documentation
  3. JDBC Type Mapping

JDBC Type Mapping

Overview

There are four strategies in DataPipeline v8.0+ to map JDBC/database types to fields in records.

  1. OPINIONATED - the default mapping type using our preferred algorithm. This strategy uses the types in java.sql.Types along with other properties (like size, decimals, and other metadata).
  2. STRICT - the mapping defined by Oracle/Sun and implemented as the default by most JDBC drivers. This strategy uses the types in java.sql.Types alone.
  3. ORIGINAL - the mapping used by DataPipeline prior to version 8.0. This strategy is similar to OPINIONATED with improvements after analyzing the behavior of several databases and drivers
  4. custom - your own custom, programmed mapping.

JDBC Type Mapping Examples

  1. Read from database using OPINIONATED type mapping
  2. Read from database using STRICT type mapping
  3. Read from database using ORIGINAL, pre-version 8.0 type mapping

Custom Type Mappings

You can build a custom mapping by implementing com.northconcepts.datapipeline.jdbc.JdbcValueReader and passing it to JdbcReader.setValueReader(JdbcValueReader).

Built-In Type Mappings

JDBC Type OPINIONATED STRICT ORIGINAL
BIT BOOLEAN BOOLEAN BOOLEAN
BOOLEAN BOOLEAN BOOLEAN BOOLEAN
TINYINT INTEGER INTEGER
DOUBLEdecimals > 0 && displaySize + decimals < 310
BIG_DECIMAL decimals > 0 && displaySize + decimals >= 310
BYTEdecimals == 0 && displaySize < 4
INTEGERdecimals == 0 && displaySize < 11
LONGdecimals == 0 && displaySize <= 20
BIG_DECIMALdecimals == 0 && displaySize > 20
SMALLINT INTEGER INTEGER
INTEGER INTEGER INTEGER
BIGINT LONG LONG
REAL FLOAT FLOAT
FLOAT FLOAT DOUBLE
DOUBLE DOUBLE DOUBLE
NUMERIC
BIG_DECIMALdecimals != 0 || displaySize <= 0 || displaySize >= 19
BYTEdisplaySize < 3
INTEGERdisplaySize < 10
LONGdisplaySize < 19
* displaySize is reduced by 1 if the number is signed prior to the above logic. 		BIG_DECIMAL
DECIMAL BIG_DECIMAL
CHAR STRING STRING
CHARdisplaySize == 1
STRINGdisplaySize != 1
VARCHAR STRING STRING
LONGVARCHAR STRING STRING
CLOB STRING STRING
NCHAR STRING STRING
NVARCHAR STRING STRING
LONGNVARCHAR STRING STRING
NCLOB STRING STRING
NULL STRING STRING STRING
DATE DATE DATE DATE
TIME TIME TIME TIME
TIMESTAMP DATETIME DATETIME DATETIME
BLOB BLOB BLOB BLOB
BINARY BLOB BLOB BLOB
VARBINARY BLOB BLOB BLOB
LONGVARBINARY BLOB BLOB BLOB
ARRAY UNDEFINED UNDEFINED UNDEFINED
DATALINK UNDEFINED UNDEFINED UNDEFINED
DISTINCT UNDEFINED UNDEFINED UNDEFINED
JAVA_OBJECT UNDEFINED UNDEFINED UNDEFINED
OTHER UNDEFINED UNDEFINED UNDEFINED
REF UNDEFINED UNDEFINED UNDEFINED
REF_CURSOR UNDEFINED UNDEFINED UNDEFINED
ROWID UNDEFINED UNDEFINED UNDEFINED
SQLXML UNDEFINED UNDEFINED UNDEFINED
STRUCT UNDEFINED UNDEFINED UNDEFINED
TIMESTAMP_WITH_TIMEZONE UNDEFINED UNDEFINED UNDEFINED
TIME_WITH_TIMEZONE UNDEFINED UNDEFINED UNDEFINED

UUID JDBC to DataPipeline Field Type Mapping

# Database DB Data Type JDBC Type Datapipeline Field Type*
1 MariaDB 11.0.3 UUID OTHER UUID
2 Microsoft SQL Server 2022 UNIQUEIDENTIFIER CHAR String
3 Postgres SQL 15.2 UUID OTHER UUID
4 MySQL 5.7.22 (Not Supported)
5 MySQL 8.0.16 (Not Supported)
6 Oracle 21c (Not Supported)
7 IBM DB2 11.5.0.0a (Not Supported)
8 Sybase ASE 16.0 (Not Supported)

* The above result is same for all four strategies (OPINIONATED, STRICT, ORIGINAL and custom) to map JDBC/database types to fields in records.

Mobile Analytics