Skip to content

JDBC - DATA SOURCE

Description

The JDBC data source is designed to retrieve data from a relational database using the Java Database Connectivity (JDBC) API. JDBC provides a standard interface for accessing relational databases, allowing applications to interact with a wide range of database management systems (DBMS) using SQL. This method is particularly useful for accessing and processing data stored in relational databases, enabling applications to perform complex queries, updates, and transactions. The JDBC data source supports various configuration options to customize the data retrieval process, including specifying the JDBC driver class, database URL, username, password, setup SQL statements, and the query to execute.

Config

REQUIRED

Config Parameters

Name Description
dbDriverClass The fully qualified class name of the JDBC driver. This is a required parameter.
dbUrl The URL of the database to connect to. This is a required parameter.
dbUsername The username for authentication with the database. This is a required parameter.
dbPassword The password for authentication with the database. This is a required parameter.
setUpSQL SQL statements to execute before the query, such as setting session variables or preparing the database state. This is a required parameter.
query The SQL query to execute against the database. This is a required parameter.

Config Example

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
<?xml version="1.0" encoding="UTF-8"?>
<apiroConf version="1" xmlns="http://apiro.com/apiro/v1/root">
    <loadOrder>10</loadOrder>
    <dataSources>
        <dataSource definition="JDBC" execPriority="10" name="ESG_DATA_OPS_JDBC">
            <description/>
            <config>
                <![CDATA[
                    {
                        "dbDriverClass" : "jdbc.driver",
                        "dbUrl" : "mydb.url",
                        "dbUsername" : "MY_USERNAME",
                        "dbPassword" : "MY_PASSWORD",
                        "setUpSQL" : "MY_SETUPSQL",
                        "query" : "Select * from esg_scores_table"
                    }
                ]]>
            </config>
        </dataSource>
    </dataSources>
</apiroConf>

Inline Feed Source

A JDBC can also be used directly inline within a dataFeed. An inline JDBC source uses the same parameters. The payload for data points is accessed using json list path operators. An example is provided below:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<dataFeeds> 
    <dataFeed definition="EXPR_JSON_FEED2" name="JDBCFEED"> 
      <execPriority>10</execPriority>  
      <enabled>true</enabled>  
      <push>true</push>  
      <pull>true</pull>  
      <schema>ESG2</schema>  
      <config><![CDATA[{
  "dataSource" : {
    "config" : {
        "dbDriverClass" : "jdbc.driver",
        "dbUrl" : "mydb.url",
        "dbUsername" : "MY_USERNAME",
        "dbPassword" : "MY_PASSWORD",
        "setUpSQL" : "MY_SETUPSQL",
        "query" : "Select * from esg_scores_table"
    },
    "entity" : "JDBC"
  },
  "itemLimit" : 10,
  "listPath" : "$.results",
  "explicitMappings" : [ {
    "dictionary" : "ROOT_DOC",
    "value" : "#GRV{PAYLOAD.resolve('$')}"
  }, {
    "dictionary" : "CUSIP",
    "value" : "#GRV{PAYLOAD.resolve('$.cusip')}"
  }, {
    "dictionary" : "CODE",
    "value" : "#GRV{PAYLOAD.resolve('$.code')}"
  }, {
    "dictionary" : "COMPANY_ID",
    "value" : "#GRV{PAYLOAD.resolve('$.company_id')}"
  }
  ]
}]]></config> 
    </dataFeed> 
  </dataFeeds>

Common Mistakes

  • Incorrect JDBC Driver Class: Ensure that the dbDriverClass parameter matches the fully qualified class name of the JDBC driver for the database you are connecting to.
  • Database URL Issues: Verify that the dbUrl parameter is correctly formatted and points to the correct database instance.
  • Authentication Failures: If you encounter authentication issues, check the dbUsername and dbPassword parameters to ensure they are correct and have the necessary permissions to access the database.
  • SQL Query Errors: If you encounter errors executing the SQL query, ensure that the query parameter is correctly formatted and compatible with the database schema.