User Guide : Map Connectors : Source and Target Map Connectors : JDBC
 
Share this page             
JDBC
JDBC drivers are a standard that is used for connecting to relational databases, NoSQL databases, and other repositories. The JDBC connector allows connecting to repositories using existing drivers without any custom connectors.
Prerequisites
JDBC driver must reside in the local filesystem
Use the latest Jar file
Connection Parts
The following are the connection parts that you must specify on the Source/Target tab to establish a connection with the JDBC connector.
Connection Part
Description
JDBC URL
Full JDBC URL of the database as recognized by the driver. The standard format is:
jdbc:subprotocol:subname://hostname:port/database_name
For example, for MySQL database: jdbc:mysql://localhost/sakila
Note:  This may vary based on the driver.
User
User ID to access the JDBC URL
Password
Password to access the JDBC URL
Additional Properties
Any additional connection properties that must be appended to the connection string. For example, rewriteBatchedStatements=true.
Driver Jar Location
Path of the driver Jar file, from where to load the JDBC driver. This path can be folder on the file system or a jar file. In case of folder, all the jar files at the location is recursively added to the classpath for resolving the JDBC driver class.
Note:  If this value is not specified, then the Java classpath of the DataConnect instance that is running is used to resolve the driver class.
Driver Class
Fully qualified Java class name of a JDBC driver. If this value is not specified, then java.sql.DriverManager is used to resolve the JDBC URL. This will only work if any driver class was initialized when DataConnect was started and the driver registered itself with driver manager.
Note:  It is recommended to specify the driver class.
Table/View
Table to be read from (Source Only).
Query Statement
SQL query file (Source Only).
Query File
SQL query statement (Source only).
Connector Properties
You can set the following source (S) and target (T) properties.
Property
Source(S)/Target(T)
Description
Batch Size
S/T
Number of source records that the connector caches before processing them in a map. Default value is 2000.
Fetch Size
S
Indicates the number of rows to fetch in a single call to the server. Default value is 1000.
WhereStmt
S
Provides a pass-through mechanism for SQL connectors where advanced users can construct the Where clause of the SQL query themselves. It can be used as an alternative to writing a lengthy query statement. You may use this to instruct the SQL database server to filter the data based on a particular condition before sending it to the integration platform. There is no default value for this property.
Note:  This property is not applicable when the source connection is a query statement or file. This property enables data filtering when you select a table.
Show System Tables
S/T
If set to True, it allows you to view all the tables created by the DBA. The system table names appear in the table list. The default value is False.
Note:  This property is applicable only if you are logged onto the database as the database administrator. Only the DBA has access to System Tables.
Show Views
S/T
If set to True, this property allows you to see the view names in the table list along with the table names. Default is True.
Note:  This property supports only Append and DeleteAndAppend output modes and does not support the Replace output mode.
Transaction Isolation Level
S/T
Allows you to specify the isolation levels when reading from or writing to a database table with JDBC.
The available options are:
Read Uncommitted: Permits Dirty Read, Non-repeatable Read, and Phantoms.
Read Committed: Permits Non-repeatable Read and Phantoms. Does not permit Dirty Read.
Repeatable Read: Permits Phantoms. Does not permit Dirty Read and Non-repeatable Read.
Serializable (default): Does not permit Dirty Read, Non-repeatable Read, and Phantoms.
Cursor Type
S/T
The type of cursor to use for retrieving records from the source table. The available options are:
Forward Only (default value): Cursor will only move in the forward direction.
Scrollable - Insensitive to changes: Cursor is scrollable but generally not sensitive to changes in the underlying data.
Scrollable - Sensitive to changes: Cursor is scrollable but sensitive to changes in the underlying data.
Flush Frequency
T
Number of records buffered in the memory before being written to the target connector. Default value is 0 (all records are written at once). If you are inserting many records, then changing the default to a higher value may improve performance.
Batch Response
T
File to which CRUD operation results are written. The format is one entry per record, indicating success or failure. If the operation fails, information about cause of failure is returned. Default is to not write a batch response file.
Update Null Fields
T
If set to True, nulls or empty strings are sent to the server. Default value is False.
Enable Batch Processing
T
Batch processing groups related SQL statements into a batch and submits them in one call to the database server. This is a performance tuning option. Default value is True.
Auto Commit
T
Allows you to automatically commit changes as they are made by each SQL statement, instead of waiting until the end of the transaction. If this property is set to True, there is no way to roll back changes once they have been made. Default value is False.
Supported Output Modes
JDBC connector supports the Insert, Update, Delete, and Upsert output modes. For information about output modes, see Target Output Modes.
Data Types
The following data types are available:
BIGINT
BIGINT UNSIGNED
BINARY
BIT
BLOB
BOOL
CHAR
DATE
DATETIME
DECIMAL
DOUBLE
DOUBLE PRECISION
ENUM
FLOAT
INT
INT UNSIGNED
INTEGER
INTEGER UNSIGNED
LONG VARBINARY
LONG VARCHAR
LONGBLOB
LONGTEXT
MEDIUMBLOB
MEDIUMINT
MEDIUMINT UNSIGNED
MEDIUMTEXT
NUMERIC
REAL
SET
SMALLINT
SMALLINT UNSIGNED
TEXT
TIME
TIMESTAMP
TINYBLOB
TINYINT
TINYINT UNSIGNED
TINYTEXT
VARBINARY
VARCHAR
YEAR
If you are appending data to an existing table, the data type of each field uses the data type in the selected table by default.
Additional Information
If the connection is not established with the connector and mapping is done, then the default data type for any non-text data type is "Text" which has the size as 1. After mapping, connecting to the target does not change the size. You will have to delete the fields and map again for accurate mapping or modify individual field properties.
Therefore, it is recommended to perform all the mapping after the connection is established for both source and target.