Connector/J extension points – statement interceptors

Continuing the review of MySQL Connector/J’s built-in extension points from my recent JavaOne and Silicon Valley Code Camp presentations, this blog posting will focus on the StatementInterceptor extension point.  As the name suggests, this allows you to hook into statement execution and alter behavior – without changing application-side code.  This corresponds to slide #59 in my slide deck, and there are two Java files we’ll reference:

  • demo.connectorj.StatementInterceptorExample
  • demo.connectork.plugins.ExampleStatementInterceptor

To implement a statement interceptor, you need to do the following:

  1. Create a Java class which implements com.mysql.jdbc.StatementInterceptorV2
  2. Configure Connector/J to use your statement interceptor by passing the fully-qualified class name as the value for the “statementInterceptors” property.

This extension point is stackable – you can create multiple statement interceptors, passing them in as a comma-delimited list of fully-qualified class names.

The example provided in the demo code is pretty bland, but illustrates what can be done.  In the demo code, we’ve implemented the preProcess() method to check for a certain trigger (“/* test */” in this case), which triggers entirely different behavior than what would normally transpire.  This code simply returns the result of  “SELECT NOW()” when triggered, instead of whatever would normally be executed on the server.  You’ll probably never need this particularly functionality, but there’s other interesting stuff you could do:

  • Add memcached without changing a line of application code, by checking memcached before executing a query, and caching the results after retrieving non-cached data from MySQL.
  • Work around MySQL server problem areas without changing application code.  Maybe you have slow-performing subqueries in an application you cannot change?  Use statement interceptors to rewrite them to more better-performing JOIN syntax equivalents.
  • Perform some standard result set transformation by wrapping ResultSetInternalMethods returned object.
  • Add fine-grained conditional audit logging or access control without changing application code.
  • Track down problematic statements that you suspect may be issued by the application, but not logged anywhere else.
  • Shard your data, and use statement interceptors to route statements to appropriate MySQL instances (or combine results fetched from multiple MySQL servers)  – again without changing application code.
  • Implement low-overhead “ping” operation for connection pools that don’t allow you to define your own validation query.

There’s a lot that can be done with statement interceptors, and they are very easy to wire up – there’s really only five methods you need to implement:

  • init()  – You can set up state variables here, if needed.  Returns void, so leaving this empty is fine.
  • preProcess() – This is where you return a ResultSetInternalMethods object if you want to bypass the normal operation of the statement.  This is called before the statement is sent to the server, so you can change what is sent – or even bypass the sending – here.  If you return null, the driver executes the statement as it normally would.  If you return a non-null object, further execution is bypassed.  Note that the SQL String argument will be null for PreparedStatement objects; you’ll need to handle Statement and PreparedStatement executions differently in order to examine the SQL being sent.
  • postProcess() – Like preProcess(), but invoked by the driver after the server has returned a result set.  This allows you to change the results returned or wrap them with some sort of custom decorator.
  • executeTopLevelOnly() – Return true if you are issuing queries inside preProcess() or postProcess() that could cause infinite recursion.
  • destroy() – Clean up any local references you created in init() here.

The demo code provided will give you a simple example of how to implement statement interceptors, but what you do with them is really limited only by your imagination.

 

2 thoughts on “Connector/J extension points – statement interceptors

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.