The latest release of HCI 1.2, introduces us to new connector plugins that connect to external databases like mySql and Postgresql. With HCI 1.2 , we have this ability to create custom JDBC connectors for different databases using a base template and the whole development process is simplified by using the HCI plugin-sdk.
Before developing the jdbc connector make sure the following dependencies are available :
1) The latest HCI Plugin-sdk.jar
2) jdbc-1.2.0.jar -- this jar is located in the HCI installation directory.
Example : /opt/hci/plugins/jdbc-1.2.0.jar
3) Database Specific driver jar.
Example: ojdbc7.jar, sqljdbc.jar
4) Java jdk 1.8 or higher
5) Any build tool like gradle,maven to package the plugin jar with required dependencies.
Create a new project and extend the BaseJdbcConnectorPlugin Class present in the jdbc-1.2.0.jar.
Add all the unimplemented methods and the methods to override in your custom connector plugin.
The main methods to override are:
The "getJDBCDriver" method should provide the database specific driver. For example , to connect to an Oracle database use the "oracle.jdbc.driver.OracleDriver" driver.
The "getBatchSizeLimitPredicate" method should return the predicate for providing the default batch size to fetch when we test the data connection.
"OFFSET 0 ROWS FETCH NEXT " + batchSize + " ROWS ONLY";
In the above example the "bathSize" is determined by the Query Batch Size property value entered by the user during the data connection creation. This is required in order to prevent the plugin from fetching all the rows in the table. This is used as a safeguard against retrieving millions of rows from a database table, especially during a test.
Structure of the plugin:
The whole process of connecting to a database and executing SQL statements is abstracted by the BaseJDBCConenctorPlugin which simplifies development without having to worry about any database connections.
Use any build tool like gradle, maven to include/exclude dependencies while packaging the plugin.Make sure the plugin.json manifest file is present in the META-INF directory of the plugin.