Notable New Classes

JdbcReader

JdbcWriter

JdbcAsyncWriter

Out-Of-Box implementation

JDBC Connector is designed to be usable out of the box.  It is designed to plug into a pattern where you have a specific table name that (probably) wont change. 

1) Just create a region with the same name, add on a JdbcWriter (for write through) or JdbcAsyncWriter (for write behind) or JdbcReader (for read through). 

2) Create a jdbc-connection to your backend DB, create a jdbc-mapping to match the region to the connection.

3) Begin putting entity objects into the region the default behavior will attempt to write to a table matching the name of the region, into columns matching the field names on your object.

If your names don't match you can also configure specific region name to table names and field names to columns names in the jdbc-mapping command.

Getting Started

JDBC connector works best when starting from an empty region and empty table (thus synced).  And using only one application--one region--one table tenancy.

1. You’ll need an existing jdbc datasource with a table you want to inline (map) a region with and an entity object that implements PdxSerializable.

   ex: a MySQL RDS instance running in AWS

   
   

2. In gfsh you’ll need to add the jdbc jar to the server classpath when you start the server.

gfsh>start server --name=s1 --classpath="/Users/fkrone/workspace/mysql-connector-java-5.1.45-bin.jar"

3. Create a region with the same name as the table you want to persist to (optionally you can use a table/region name mismatch and configure the mapping with the jdbc-mapping command)

gfsh>create region --name="employee" --type=REPLICATE --cache-loader="org.apache.geode.connectors.jdbc.JdbcLoader"

4. Create a jdbc-connection with DB url and credentials

gfsh>create jdbc-connection --name=aws_mysql --url="jdbc:mysql://rds-mysql-test.123.us-west-2.rds.amazonaws.com:3306/test?user=emma&password=iamthegreatest"

5. map the new connection to a region using the jdbc-mapping command.

gfsh>create jdbc-mapping --connection="aws_mysql" --region="employee"

Risks and Mitigations

It is strongly recommended that you do your own use-case specific testing when using JDBC Connector.  Test an accurate read/write load you would expect to the region to the DB and make sure JDBC Connector performs according to your SLAs.  Make sure you understand the number of connections made to your DB and review against your licensing agreement where applicable. It is also recommended that you not read or write directly to a DB which is your system of record.  If possible, use a DB that is designed to be a backup of your cache and ETL from there.

Dates

For your database date columns it is encouraged to use integer for a timestamp as it’s much simpler and consistent all around.  Translate later. Barring that use a date type for the column and java.util.Date in your domain object date field.

Ex: Date date = new java.sql.Date(Calendar.getInstance().getTimeInMillis());

PdxInstance.writeDate("hire_date", date).create();

Entity object structure

Nested structures are not supported.  Flat table structures with unique key columns must be used.

This is for persisting flat domain objects to a table.  Pull from those flat tables for more complex transformations, etc.

Your entity object needs to implement PdxSerializable.

public class Employee implements PdxSerializable {

Implement the toData, fromData methods from the interface.  Jdbc returns objects in results which the JdbcLoader will try to convert.  Implement toData and fromData to cast object returns to specific fields.

example:

@Override

public void toData(PdxWriter writer) {

 writer.writeObject("first_name", this.first_name);

writer.writeObject("last_name", this.last_name);

writer.writeObject("hire_date", this.hire_date);

writer.writeObject("id", this.id);

}

@Override

public void fromData(PdxReader reader) {

 this.first_name = (String)reader.readObject("first_name");

 this.last_name = (String)reader.readObject("last_name");

 this.hire_date = (java.util.Date)reader.readObject("hire_date");

 this.id = (int) reader.readObject("id");

}