BusMod Persistor for JDBC support in vert.x
Java JavaScript Groovy
Latest commit b9b5d1c Jul 8, 2015 Tim Yates Updated version in README

README.md

Current module id:

com.bloidonia~mod-jdbc-persistor~2.1.5

Built for Vert.x 2.1.5

For Vert-x 1.3.1, please use the unsupported v1.2 of this mod

See the current tests for it in action...

I have also written a modified version of the webapp example using this module.

Default config:

{
  address  : "com.bloidonia.jdbcpersistor",

  // JDBC connection settings
  driver   : "org.hsqldb.jdbcDriver",
  url      : "jdbc:hsqldb:mem:test",
  username : "",
  password : "",

  // Values 'yes', 'no', or 'maybe'. Defaults to 'no'. See https://issues.apache.org/jira/browse/DBUTILS-101
  // 'maybe' means that instance of the JdbcProcessor will switch to 'yes' if an SQLException is caught
  pmdKnownBroken : "no",

  // Values may be true/false (defaults to false).  If set to true, then insert will not return auto-generated
  // keys (to get round issues with the SAP HANA JDBC driver)
  ignoreAutoGeneratedKeys : false,

  // Pool settings
  minpool  : 5,
  maxpool  : 20,
  acquire  : 5,

  // Defaults
  batchtimeout       : 5000,
  transactiontimeout : 10000
}

When the mod is loaded successfully, it will send a message:

{ status: "ok" }

To the address in the config with .ready appended to the end.

This means you can do:

var persistorConfig = { address: 'test.persistor', url: 'jdbc:hsqldb:mem:' + vertx.generateUUID() + '?shutdown=true' }
var readyAddress = persistorConfig.address + '.ready'
var readyHandler = function( msg ) {
  if( msg.status === 'ok' ) {
    eb.unregisterHandler( readyAddress, readyHandler ) ;

    // MOD IS READY TO GO!!!
  }
} ;

// This will get called by the jdbc-persistor when it has installed the work-queue
eb.registerHandler( readyAddress, readyHandler ) ;
vertx.deployModule('com.bloidonia~mod-jdbc-persistor~2.1.2', persistorConfig, 1, function() {} ) ;

And when the readyHandler is called, you know your work-queue is up and running.

You can also add the following properties to the config object to test the connection (see the c3p0 documentation for these properties here)

c3p0.automaticTestTable
c3p0.idleConnectionTestPeriod
c3p0.preferredTestQuery
c3p0.testConnectionOnCheckin
c3p0.testConnectionOnCheckout
c3p0.acquireRetryAttempts
c3p0.acquireRetryDelay

Currently attempts to support:

Interface Specification

The following message types are supported.

EXECUTE

The execute action is for running parameterless SQL which does not generate a ResultSet

Inputs

{
  action: "execute",
  stmt:   "CREATE TABLE test ( id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL," +
                             " name VARCHAR(80), age  INTEGER, CONSTRAINT testid PRIMARY KEY ( id ) )"
}

OUTPUTS

{
  status: "ok"
}

or

{
  status: "error",
  message: <message>
}

SELECT

Call some SQL that generates a ResultSet.

Takes an optional list of lists (same order as the ? placeholders) as parameters to the query.

Inputs

{
  action: "select",
  stmt: "SELECT * FROM xxx"
}

or

{
  action: "select",
  stmt: "SELECT * FROM xxx WHERE a=? AND b=?",
  values: [ [ 10, 20 ], ... ]
}

Outputs

One of:

{
  status: "ok",
  result: [ { "NAME":"a", "AGE":32 }, ... ]
}

{
  status: "error",
  message: <message>
}

Response Batching

If you want the resulting list of maps to be batched into a certain number of results, you can add the parameters batchsize (default -1 for no batching) and batchtimeout (in ms: default 10000) to your query message.

ie; consider a table with 5 rows:

|NAME |

| tim | | sarah | | alan | | cerys | | si |

If we execute:

{
  action: "select",
  stmt: "SELECT NAME FROM TABLENAME",
  batchsize: 3,
  batchtimeout: 10000
}

Then the first response will be:

{
  status: "more-exist",
  result: [ { "NAME":"tim" }, { "NAME":"sarah" }, { "NAME":"alan" } ]
}

Along with a handler to fetch the rest of the results. A call to this will result in the response:

{
  status: "ok",
  result: [ { "NAME":"cerys" }, { "NAME":"si" } ]
}

The ok specifying that we have reached the end of this batch. If you do not call this batch handler within batchtimeout milliseconds, the ResultSet, Statement and Connection will be closed (unless you are inside a transaction (see below), in which case the Connection will remain open).

INSERT

Takes an optional list of lists (same order as the ? placeholders) as parameters to the query.

Returns the primary keys generated by the insert.

You may also pass the optional parameters batchsize and batchtimeout if you want these keys returned in batches as with select

Inputs

{
  action: "insert",
  stmt: "INSERT INTO xxx( a, b ) VALUES( ?, ? )",
  values: [ [ 10, 20 ], ... ]
}

Outputs

One of:

{
  status: "ok",
  result: [ { "ID":1 }, { "ID":2 }, ... ]
  updated: <nrows>
}

or (if the ignoreAutoGeneratedKeys configuration flag is set)

{
  status: "ok",
  updated: <nrows>
}

or

{
  status: "error",
  message: <message>
}

A note on Timestamps

Inserting timestamps can be done by specifying them as Strings in the correct Timestamp format, ie in a table defined by:

CREATE TABLE test ( id INTEGER, time TIMESTAMP )

We can insert data using:

{
  action: "insert",
  stmt: "INSERT INTO test( id, time ) VALUES( ?, ? )",
  values: [ [ 1, '2013-02-14 12:30:44' ], ... ]
}

UPDATE

Inputs

{
  action: "update"
  stmt: "UPDATE xxx SET( a=?, b=? ) WHERE c=?",
  values: [ [ 10, 20, 30 ], ... ]
}

OUTPUTS

{
  status: "ok",
  updated: <nrows>
}

or

{
  status: "error",
  message: <message>
}

TRANSACTION

This starts an SQL transaction, and returns a handler to execute any of the above messages inside.

After each response, if no reply is heard for more than timout milliseconds (default 10000), then the transaction is rolled back and the connection is closed.

Once you are done with a transaction, then handler needs to be sent a commit or rollback message (see below)

Inputs

{
  action: "transaction",
  timeout: 10000
}

OUTPUTS

{
  status:"ok"
}

or

{
  status: "error",
  message: <message>
}

COMMIT

Inform the Transaction handler to commit any changes to the connection, and close the connection.

Inputs

{
  action: "commit"
}

OUTPUTS

{
  status:"ok"
}

or

{
  status: "error",
  message: <message>
}

ROLLBACK

Inform the Transaction handler to rollback any changes to the connection, and close the connection.

Inputs

{
  action: "rollback"
}

OUTPUTS

{
  status: "ok"
}

or

{
  status: "error",
  message: <message>
}

POOL STATUS

Get the current status of the pool

Inputs

{
  action: "pool-status"
}

OUTPUTS

{
  status: "ok",
  connections: 10,
  idle: 10,
  busy: 0,
  orphans: 0
}

or

{
  status: "error",
  message: <message>
}