JDBC Extension

The JDBC extension enables reading from and writing to databases with a JDBC interface in Grainite applications.

Setup

In order to be able to use the JDBC extension, first include it as a dependency in your application's pom.xml file.

...
<dependency>
  <groupId>ext.grainite</groupId>
  <artifactId>grainite-jdbc</artifactId>
  <version>{GRAINITE-VERSION}</version>
</dependency>
...

Replace {GRAINITE-VERSION} with the version of Grainite you are also using for libgrainite (the Grainite Client library for Java).

Contents

The JDBC Extension includes:

• JDBCReaderTask: Task to continuously call a stored procedure on a given database, with a timestamp parameter, to get incremental changes.

• JDBCWriterHandler: Handler that will write its payload to a given table in the database

JDBCReaderTask

The reader assumes that there is a stored procedure it can invoke with a timestamp parameter, that will return changed rows along with their timestamp. It can then invoke this continuously to establish a "poor-man’s change data capture (CDC)."

Also, it is expected that the returned resultset should have a column recordId that represents the ID of the record, when found, it is expected that the row is being updated and inserted when recordId is not found in the record. Similarly, there is an expected entityName on the returned row, this is then used as the key for the topic or grain message.

The JDBCReaderTask also backs-off when it does not get any results - this backoff is exponential with a max of 60 seconds.

Usage

To include this task in your application, you must specify the taskClass ext.grainite.tasks.jdbcreader.JDBCReaderTask and taskInstanceClass ext.grainite.tasks.jdbcreader.JDBCReaderInstance in your application's configuration YAML file.

app.yaml

...
tasks:
  - task_name: my_jdbc_reader_task
    taskClass: ext.grainite.tasks.jdbcreader.JDBCReaderTask
    taskInstanceClass: ext.grainite.tasks.jdbcreader.JDBCReaderInstance
    config:
...

Below are the configuration options that can be passed in under config:

Property	Required?	Value	Description
connectionURL	REQUIRED	Example: jdbc:mysql://IP_ADDRESS/test_db	JDBC Connection URL for the database
userid	Optional	Example: user_me	User ID to pass in when connecting with the JDBC interface
password	Optional	Example: pwd_xyz	Password to pass in when connecting with the JDBC interface
jdbcDriverClass	REQUIRED	Example: com.microsoft.sqlserver.jdbc.SQLServerDriver	Provide this so that the Handler can check if the driver class exists.
procedure_name	REQUIRED	Example: my_procedure	Stored procedure to call from the database. This procedure must accept one parameter of type timestamp and is expected to return rows updated after the given timestamp.
record.name	REQUIRED	Example: my_key	Field name in the query result representing the name of the record, which will be used as the key.
record.id	REQUIRED	Example: my_id	Field name in the query result representing the ID of the record.
timestamp_field	Optional	Example: my_timestamp	Field name in the query result that can be used as the timestamp for the returned row.
output.topic	Optional	Example: my_events	Grainite topic to emit output of this task to
output.table	Optional	Example: my_table	Grainite table to emit output of this task to via Grain message (output.table_action must also be provided)
output.table_action	Optional	Example: my_table_action	Given action to invoke via Grain Message to the table specified in output.table

JDBCWriterHandler

changeType is expected in each record with a value of ‘CREATE’, ‘UPDATE’ or ‘DELETE’.

Usage

To include this handler in your application, you must specify the class_name ext.grainite.handlers.jdbcwriter.JDBCWriterHandler in your application's configuration YAML file.

app.yaml

...
tables:
  - table_name: my_jdbc_writer
    key_type: string
    action_handlers:
      - name: my_jdbc_writer
        type: java
        class_name: ext.grainite.handlers.jdbcwriter.JDBCWriterHandler
        config:
...

Below are the configuration options that can be passed in under config:

Property	Required?	Value	Description
connectionURL	REQUIRED	Example: jdbc:mysql://IP_ADDRESS/test_db	JDBC Connection URL for the database
userid	Optional	Example: user_me	User ID to pass in when connecting with the JDBC interface
password	Optional	Example: pwd_xyz	Password to pass in when connecting with the JDBC interface
failBadRequest	Optional	true or false Default: true	When true, an exception in processing will simply fail the payload and move on to the next. When false, it will throw an uncaught exception, causing Grainite to retry.
failOnUnknownFields	Optional	true or false Default: true	When true, if there is a field in the payload that is not a column on the target table, the request is failed (see failBadRequest handling). If false, the field is ignored.
table	Required if tableColumn not specified	Example: Lead	Table name that this handler writes to. When not provided, the tableColumn property is used to identify the table from each record.
tableColumn	Required if table not specified	Example: TableName	Field name in payload that provides the name of table to write to. When provided the tableColumn property is preferred over a fixed table provided in the config.
keyColumn	Required if record changeType is ‘UPDATE’ or ‘DELETE’	Example: Id	field name in payload that is the key for the record (used for UPDATE and DELETE statements)
upsert	Optional	true or false Default: false	When true , if an update doesn’t work because key is not present on database, follow up with an INSERT statement. When false , if an update doesn’t go through because key is not found, ignore/skip it.
jdbcDriverClass	REQUIRED	Example: com.microsoft.sqlserver.jdbc.SQLServerDriver	Provide this so that the Handler can check if the driver class exists.
debug	Optional	true or false Default: false	When true, the handler produces extra output in the gxapps and jetty logs

JDBC is a trademark of ORACLE AMERICA, INC.