JarCastingCurrent 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>
}